Top 5 time-consuming SQL queries in Oracle

Question

How can I find poor performing SQL queries in Oracle?

Oracle maintains statistics on shared SQL area and contains one row per SQL string(v$sqlarea). But how can we identify which one of them are badly performing?

Answer 1

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_time FROM    v$sql ORDER 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.