vsqltop.sql

rem  vsqltop.sql
rem
ttitle 'Shared SQL Area - Top SQL Statements'
rem
col sql_text format a80 heading 'SQL Text'
col sharable_mem format 999999 heading 'SHARED|MEMORY'
col persistent_mem format 999999 heading 'PERSIST|MEMORY'
col runtime_mem format 999999 heading 'RUNTIME|MEMORY'
col loads format 9999 heading 'LOADS'
col invalidations format 9999 heading 'INVALID'
col parse_calls format 999999 heading 'PARSE|CALLS'
col executions format 999999 heading 'EXECUTE'
col log_phy format 9999 heading 'LOG/|PHY'
col disk_reads format 9999999 heading 'DISK|READS'
col phy_exe format 999999 heading 'PHY/|EXE'
col buffer_gets format 999999999 heading 'BUFFER|GETS'
col log_exe format 9999999 heading 'LOG/|EXE'
col sorts format 9 heading 'S'
col rows_processed format 99999999 heading 'ROWS|PROCESSED'
col rows_exe format 9999999 heading 'ROWS/|EXE'
rem
select
       loads,
       optimizer_mode,
       rows_processed,
       sorts,
       parse_calls,
       executions,
       disk_reads,
       buffer_gets,
       sql_text
  from v$sqlarea
 where parse_calls >= 0
    and executions >= 2
    and rows_processed >= 5
    and disk_reads >= 100
    and buffer_gets >= 1000
 order by buffer_gets asc;