Have a look at the following script: http://www.vampired.net/scripts/php/tuning.php (badsql.sql)
It allows you to run it against any database (8.1.6+) without having to adjust the buffer gets, executions, parsed calls to match the norm of the database. On your script, it happens to return 0 rows on my test databases because of the execution count and the buffer gets being higher than my norm. HTH, "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax: (707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -----Original Message----- Sent: Wednesday, October 03, 2001 5:26 PM To: Multiple recipients of list ORACLE-L Yesterday while on a search for inefficient SQL I developed the following SQL; which did a decent job of highlighting 1 - 2 dozen SQL statements that were HIGHLY inefficient. The goal was to identify the SQL that did a "lot" of buffer_gets (> 2000000); that ran "frequently" (> 100 times); that only returned a "few" number of rows (1-2 per execution) and that did "many" (> 1000) buffer_gets for each execution. You should adjust these criteria to better match your own application & environment. select disk_reads, buffer_gets, rows_processed, executions, (buffer_gets/executions) ratio, sql_text from v$sql where buffer_gets > 2000000 and executions > 100 and executions*2 > rows_processed group by disk_reads, buffer_gets, rows_processed, executions, sql_text having (buffer_gets/executions) > 1000 order by 4 / Alternatively this SQL can be order by 5 We are in the process of adding indexes that hopefully will GREATLY increase the selectivity of the SQL, rather than doing cartesian joins across tables. HTH & YMMV! -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 United WE Stand! Justice WILL Be Served! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: Christopher Spence 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).
