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).
