?
Solved

oracle code / sql monitoring, what are the tools for this ?

Posted on 2014-10-23
4
Medium Priority
?
373 Views
Last Modified: 2014-10-23
I'm pretty familiar with Oracle tracing / tkprofs to monitor sql statements.

But how can PL/SQL code that's not actual SQL statements be monitored, for performance considerations ?

Can the Oracle DBA see what non-Sql statements are being executed, their timing, etc. ?

Also, specifically, if the code was in a non-SQL infinite loop, would the DBA be able to see the line of code it was on ?

like

while v_flag is FALSE
   loop
       if substr(In_value,v_counter,1) = v_compare_value then
           v_position_of_value := v_counter;
           v_flag := TRUE;
       else
           v_counter := v_counter + 1;
      end if;
  end loop;

if it was looping through and never got to v_flag := TRUE, would the DBA be able to pinpoint the line of code ? And if they could, could it show it was on the "while" statement ?
0
Comment
Question by:Gadsden Consulting
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40399554
dbms_profiler or dbms_hprof  will let you generate pl/sql "trace" information.

the former writes to tables, the latter writes to files

they generate similar information though, with the newer hprof being "hierarchical"  which can make diagnosis a little easier.

most of the popular pl/sql GUIs come with debuggers that will let you step through the code line by line.

For example ...
Toad
PL/SQL Developer
SQL Developer

I'm sure others do too
0
 

Author Comment

by:Gadsden Consulting
ID: 40399720
sdstuber,

thanks.

don't the dbms_profiler or dbms_hprof require some set up (for a specific program or session) to capture trace info ?

I was stepping through code and then tried to kill the session, which I wasn't able to do. So I contacted the DBA to kill it. I was then told that my code was in an infinite loop in procedural, non-sql code. So I was wondering how the DBA would actually be able to ascertain this (that it was processing a WHILE statement) ???

Later I found out that the PL/SQL Developer environment has some bug in it regarding debugging. It works fine until you try to kill your session. When I eventually found that out, I was able to then kill PL/SQL Developer and re-start. Annoying, but no need to contact the DBA, and in fact nothing was hanging.

Plus the DBA had told me that he couldn't see my session when I asked it to be killed, so it was all so com-fusing, until I learned of the PL/SQL debugging error (which is likely an environment issue, not PL/SQL developer)
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 40399774
>> don't the dbms_profiler or dbms_hprof require some set up (for a specific program or session) to capture trace info ?

yes - your dba will have to install them

and you have to call the start/stop procedures for the profiling

for example...

BEGIN
    Dbms_Profiler.start_profiler('your test run');
   your_procedure;
    dbms_profiler.stop_profiler;
END;


>>> how the DBA would actually be able to ascertain this

might have looked at your code and found an infinite loop
or might have looked at operations in that kept repeating and assumed infinite (maybe just long running)
that might be in v$sql or in awr reports

if the loop was very tight, then it might be assumed from session information about cpu or memory consumption


or it might have been mistaken speaking but correct nonetheless.
That is, some people will call  a long delay a "lock" when it's not really locked.
or, might call loops that take a long time "infinite" when it's really not

So, it's possible your dba saw some indication of looping and simply guessed infinite and just happened to be right
0
 

Author Comment

by:Gadsden Consulting
ID: 40400212
ok, all makes sense, thanks !
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to recover a database from a user managed backup
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question