Script: To Get Top SQL
While finding the top 10 most expensive SQL, you must keep all the below concept in your mind:
– Is it Logical I/O per Execution.
– Is it Physical I/O per Execution.
– Is it CPU used.
– Is the number of child version found.
– Is it based on the parse call.
– Is it number of execution.
TOP SQL based on number of elapsed time
SELECT * FROM
(SELECT sql_text, child_number, disk_reads,
executions, first_load_time,last_load_time
FROM v$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10;
Based on the I/O
select * from (select disk_reads,executions,sql_text from v$sqlarea
order by 1 desc, 2 desc) where rownum <=10;
The above query finds the TOP sql statements that currently stored in SQL cache ordered by elapsed time
SELECT b.username username, a.disk_reads phyreads, a.executions noexec, a.disk_reads
/ DECODE (a.executions, 0, 1, a.executions) rds_ex_ratio, a.command_type, a.sql_text sqlqry
FROM v$sqlarea a, dba_users b
WHERE a.parsing_user_id = b.user_id AND a.disk_reads > 10000
ORDER BY a.disk_reads DESC;
The above query will find top SQL execution on the basis of reads execution ration.
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;
Find 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;
Note: It is always better to use Statspack and AWR report for this purpose.
