| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Composite Index Order VS Query Order ?? , Tuning Docs URL ?.
I have had a huge amount of success using PL/SQL profiler (available from
8i) to tune PL/SQL code. The PL/SQL profiler gives a line-by-line account of
how much time is spent in the PL/SQL code (to the nearest millionth of a
second, I think). It is all very well to re-write everything and you could
make an improvement (or make it far worse) every time you attempt a
re-write, but this is a luxury that is not always available to people due to
time-constraints.
Using PL/SQL profiler is pretty simple and identifies bottlenecks in the time it takes to run the badly-performing code (OK, there is an overhead due to the profiler and it may take just a bit longer than a normal run). In the PL/SQL User's guide and Reference manual, there is an entire chapter devoted to tuning PL/SQL (Chapter 12 in the 9.2 manuals). Read this first and apply before considering a re-write. The profiler may very well identify the whole of the code to be badly performing in which case you have the re-write option, but this has been necessary very few times in my experience.
Cheers,
Vasan.
-----Original Message-----
From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
Sent: 13 July 2004 17:20
To: oracle-l_at_freelists.org
Subject: RE: Composite Index Order VS Query Order ?? , Tuning Docs URL ?.
>From experience, the best way to tune PL/SQL is to scrap everything and
>rewrite from scratch.
>Get rid of count(*) and cursors in loops for a start.
Yes! :)
I may have misinterpreted the poster's original question here. I =
answered
the name of a good SQL optimization book. If the issue is really PL/SQL
optimization, then you need to have three books in your library:
Kyte, T.: Expert Oracle One on One
Kyte, T.: Effective Oracle by Design
McDonald, C.; et al.: Mastering Oracle PL/SQL
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 7/20 Cleveland, 8/10 Boston, 9/14 San =
Francisco
- SQL Optimization 101: 7/26 Washington DC, 8/16 Minneapolis, 9/20 =
Hartford
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Stephane Faroult
Sent: Tuesday, July 13, 2004 10:15 AM
To: oracle-l_at_freelists.org
Subject: Re: Composite Index Order VS Query Order ?? , Tuning Docs URL =
?.
=20
Hi,
I was working on optimising a piece of code which was taking 10hrs + to
execute and all.So my queries are..
#1) Saw a table where the "order of query" on the table and the "order =
of
key"
are different . For faster results shouldn't they be in the same order.
NO.
#2) Can anybody recommend me any site , where I will get reliable informationon Tuning PL/SQL ?.
>From experience, the best way to tune PL/SQL is to scrap everything and
rewrite from scratch.
Get rid of count(*) and cursors in loops for a start.
HTH S Faroult
-- 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_at_freelists.org 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 ----------------------------------------------------------------- ___________________________________________________________________________ This email and any attached to it are confidential and intended only for the individual or entity to which it is addressed. If you are not the intended recipient, please let us know by telephoning or emailing the sender. You should also delete the email and any attachment from your systems and should not copy the email or any attachment or disclose their content to any other person or entity. The views expressed here are not necessarily those of Churchill Insurance Group plc or its affiliates or subsidiaries. Thank you. Churchill Insurance Group plc. Company Registration Number - 2280426. England. Registered Office: Churchill Court, Westmoreland Road, Bromley, Kent BR1 1DP. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org 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 -----------------------------------------------------------------Received on Wed Jul 14 2004 - 05:37:24 CDT
 |
 |