Check top 10 SQL statement ordered by read by execution.
SET LINES 1400
SELECT sql_text,
username,
disk_reads_per_exec,
buffer_gets_per_exec,
buffer_gets,
disk_reads,
parse_calls,
sorts,
executions,
loads,
rows_processed,
hit_ratio,
first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
cpu_time_secs,
cpu_time_secs_per_execute,
elapsed_time_secs,
elapsed_time_secs_per_execute,
address,
hash_value
FROM
(SELECT sql_text,
b.username,
ROUND((a.disk_reads/DECODE(a.executions, 0, 1, a.executions)),2)
disk_reads_per_exec,
a.disk_reads,
a.buffer_gets,
ROUND((a.buffer_gets/DECODE(a.executions, 0, 1, a.executions)),2)
buffer_gets_per_exec,
a.parse_calls,
a.sorts,
a.executions,
a.loads,
a.rows_processed,
100 - ROUND(100*a.disk_reads/GREATEST(a.buffer_gets,1),2) hit_ratio,
a.first_load_time,
sharable_mem,
persistent_mem,
runtime_mem,
ROUND(cpu_time/1000000,3) cpu_time_secs,
ROUND((cpu_time/1000000)/DECODE(a.executions,0,1,a.executions),3)
cpu_time_secs_per_execute,
ROUND(elapsed_time/1000000,3) elapsed_time_secs,
ROUND((elapsed_time/1000000)/DECODE(a.executions,0,1,a.executions),3)
elapsed_time_secs_per_execute,
address,
hash_value
FROM sys.v_$sqlarea a,
sys.all_users b
WHERE a.parsing_user_id = b.user_id
AND b.username NOT IN ('SYS', 'SYSTEM')
ORDER BY 3 DESC)
WHERE rownum < 11;
Comments