Re: Help: How do i know which sql is a session executing if user is using PL/SQL?

From: Mladen Gogala <>
Date: Sat, 18 Jul 2009 18:09:08 +0000 (UTC)
Message-ID: <h3t344$ja0$>

On Sat, 18 Jul 2009 06:11:07 -0700, Edward wrote:

> We are using Oracle 10g2
> One of the session is showing wait as "db file sequential read", but
> from v$session and v$sqltext, i can only see he is executing
> BEGIN package_name.procedure_name('xxx', 'yyyy'); END;
> How could you digging further to see which SQL is he executing, so we
> can further tune the sql?
> Thanks for your help

You can profile your procedure by using DBMS_PROFILER. Tim Gorman has a nice presentation on his site, there is documentation on OTN, along with many examples. Tim Hall also has some examples on his site ( Profiler will tell where is the time spent during the procedure execution. Also, you can do things the old fashioned way: by enabling the event 10046, level 12 and analyzing it by tkprof, orasrp or Hotsos profiler. Analyzing the 10046 trace file will give you the most expensive SQL statement with respect to the elapsed time, number of rows processed or fetched, CPU time or whatever your problem may be. That is known as "method R".
Alternatively, you can increase the buffer cache and thus increase the cache hit ratio which used to be a solution to all problems. That is known as "method C".

Received on Sat Jul 18 2009 - 13:09:08 CDT

Original text of this message