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
Solved

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

Posted on 2014-10-23
4
370 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 74

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 74

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.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

860 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