Re: Identifying Long Running SQL's

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" <ORACLE-L_at_fatcity.com> 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 - helenjmitchell_at_attbi.com
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Helen J Mitchell
> INET: HelenJMitchell_at_attbi.com
>
> 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: ListGuru_at_fatcity.com (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: sqlgreg_at_pacbell.net

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: ListGuru_at_fatcity.com (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).