Solved

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

Posted on 2014-10-23
4
362 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
  • 2
  • 2
4 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 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 73

Assisted Solution

by:sdstuber
sdstuber earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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

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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now