Top 5 time-consuming SQL queries in Oracle

I found this SQL statement to be a useful place to start (sorry I can't attribute this to the original author; I found it somewhere on the internet):

SELECT * FROM(SELECT    sql_fulltext,    sql_id,    elapsed_time,    child_number,    disk_reads,    executions,    first_load_time,    last_load_timeFROM    v$sqlORDER BY elapsed_time DESC)WHERE ROWNUM < 10/

This finds the top SQL statements that are currently stored in the SQL cache ordered by elapsed time. Statements will disappear from the cache over time, so it might be no good trying to diagnose last night's batch job when you roll into work at midday.

You can also try ordering by disk_reads and executions. Executions is useful because some poor applications send the same SQL statement way too many times. This SQL assumes you use bind variables correctly.

Then, you can take the sql_id and child_number of a statement and feed them into this baby:-

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id', &child));

This shows the actual plan from the SQL cache and the full text of the SQL.

You could find disk intensive full table scans with something like this:

SELECT Disk_Reads DiskReads, Executions, SQL_ID, SQL_Text SQLText,    SQL_FullText SQLFullText FROM(   SELECT Disk_Reads, Executions, SQL_ID, LTRIM(SQL_Text) SQL_Text,       SQL_FullText, Operation, Options,       Row_Number() OVER          (Partition By sql_text ORDER BY Disk_Reads * Executions DESC)          KeepHighSQL   FROM   (       SELECT Avg(Disk_Reads) OVER (Partition By sql_text) Disk_Reads,           Max(Executions) OVER (Partition By sql_text) Executions,           t.SQL_ID, sql_text, sql_fulltext, p.operation,p.options       FROM v$sql t, v$sql_plan p       WHERE t.hash_value=p.hash_value AND p.operation='TABLE ACCESS'        AND p.options='FULL' AND p.object_owner NOT IN ('SYS','SYSTEM')       AND t.Executions > 1   )    ORDER BY DISK_READS * EXECUTIONS DESC)WHERE KeepHighSQL = 1AND rownum <=5;

You could take the average buffer gets per execution during a period of activity of the instance:

SELECT username,       buffer_gets,       disk_reads,       executions,       buffer_get_per_exec,       parse_calls,       sorts,       rows_processed,       hit_ratio,       module,       sql_text       -- elapsed_time, cpu_time, user_io_wait_time, ,  FROM (SELECT sql_text,               b.username,               a.disk_reads,               a.buffer_gets,               trunc(a.buffer_gets / a.executions) buffer_get_per_exec,               a.parse_calls,               a.sorts,               a.executions,               a.rows_processed,               100 - ROUND (100 * a.disk_reads / a.buffer_gets, 2) hit_ratio,               module               -- cpu_time, elapsed_time, user_io_wait_time          FROM v$sqlarea a, dba_users b         WHERE a.parsing_user_id = b.user_id           AND b.username NOT IN ('SYS', 'SYSTEM', 'RMAN','SYSMAN')           AND a.buffer_gets > 10000         ORDER BY buffer_get_per_exec DESC) WHERE ROWNUM <= 20