You can find the SQL's that use the most resources by querying v$sqlarea. A www.google.com search for v$sqlarea will turn up variations on such scripts. Simply tuning the five SQL's that do the most work will get results, especially if you repeat this process every week or two. For clever variations on the use of v$sqlarea, see Jonathon Lewis's book, Practical Oracle 8i. It's quick and easy to learn how to use v$sqlarea.
A more precise approach is to look at wait events, which point you to actual inefficiencies in the database. From there you can drill down to find the SQL that's causing these inefficiencies. This involves a few scripts and is explained in the Oracle Press book Tuning 101. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Thursday, February 07, 2002 10:58 PM > Does anyone have a script that can identify the most inefficient SQL code running in the application > ranking from least to most efficient? > > > > Helen J Mitchell > Oracle Database Administrator > E - [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Helen J Mitchell > INET: [EMAIL PROTECTED] > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > -------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Moore INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
