Re: Help: How do i know which sql is a session executing if user is using PL/SQL?
Date: Sat, 18 Jul 2009 18:09:08 +0000 (UTC)
Message-ID: <h3t344$ja0$1_at_solani.org>
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 www.evdbt.com, there is documentation on
OTN, along with many examples. Tim Hall also has some examples on his
site (www.oracle-base.com). 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".
-- http://mgogala.freehostia.comReceived on Sat Jul 18 2009 - 13:09:08 CDT