How to check Top 10 SQL statements


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