Solved

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

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 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