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

There are a number of possible ways to do this, but have a google for tkprof

There's no GUI... it's entirely command line and possibly a touch intimidating for Oracle beginners; but it's very powerful.

This link looks like a good start:

http://www.oracleutilities.com/OSUtil/tkprof.html

Answer 2

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

Answer 3

While searching I got the following query which does the job with one assumption(query execution time >6 seconds)

---

SELECT username, sql_text, sofar, totalwork, units

FROM v$sql,v$session_longops

WHERE sql_address = address AND sql_hash_value = hash_value

ORDER BY address, hash_value, child_number;

---

I think above query will list the details for current user.

Comments are welcome!!

Answer 4

It depends which version of oracle you have, for 9i and below Statspack is what you are after, 10g and above, you want awr , both these tools will give you the top sql's and lots of other stuff.

Answer 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

Answer 6

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 = 1
AND rownum <=5;