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

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 ?
Gadsden ConsultingIT SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gadsden ConsultingIT SpecialistAuthor Commented:
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
sdstuberCommented:
>> 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
Gadsden ConsultingIT SpecialistAuthor Commented:
ok, all makes sense, thanks !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.