RE: tuning PL/SQL

That should have been 'query + current in a tkprof file'

sorry

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Chris Stephens
Sent: Tuesday, August 31, 2004 9:03 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: tuning PL/SQL

Runstats is another tool to tell the resource consumption of a pl/sql
program.  It's on asktom.

Lio is just the query column in a tkprof file and cr + cu in a 10046.

Hth
chris

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Michael Thomas
Sent: Monday, August 30, 2004 8:24 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: tuning PL/SQL

Hi,

--- David <thump@xxxxxxxxxxxxxxxx> wrote:
> I have been working on tracing session statements to
> derive the explain
> plan and LIO values.
> What does one do to accomplish the same when PL/SQL
> code is encountered.
> Extended SQL Tracing provides no explain plan or
> fetch(buffer gets) values
> Also, to confirm is it disk or query fetch divided
> by count to determine
> LIO figure?
> Thanks
> --=3D20
> ..
> David
>=3D20

1) One solution might be to identify how much time
each=3D20
line of PL/SQL consumes and how many times each
line executes. Have you tried DBMS_PROFILER?

The 9.2 Oracle docs on DBMS_PROFILER start here:
http://tinyurl.com/6pwmf

It would help identify specific portions of code
to for optimization work. For example, this might
give you prioritized information on which SQL
statements need tuning (based on response time).

2) I believe the Hotsos SQL test harness can now
profile PL/SQL, including statistics and timed events,
based on my review of the latest test harness version.
But, this is a relatively new feature, and I have
not tested it yet. Docs says it does not do plans=3D20
on SQL statements within PL/SQL.

3) There are other good methods to measure PL/SQL=3D20
performance. Examples include code in C McDonald's=3D20
new PL/SQL book, and using logoff session triggers=3D20
to gather OWI related details suggested in the new=3D20
OWI book.

HTH.

Good luck.

Regards,

Mike Thomas


        =3D09
_______________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: