set echo off;
set feedback off;
rem *  Filename         : @C:\rkupcuna05_ora\Oracle\SQL\sstmt1.sql - Version 1.5
rem *  Author           : Rick Kupcunas, Oracle USA
rem *  Original         : 17-MAY-95
rem *  Last Modified    :  -
rem *  Descriptnion     : SQL statement activity
rem *  Usage            : start sstmt1.sql <min disk rds> <min buff rds>

set pagesize 100;
spool sstmt1.rslt;

def aps_prog    = 'sstmt1.sql'
def aps_title   = 'Summary SQL Statement Activity'

select to_char(sysdate, 'YY-MON-DD HH:MI:SS am') from dual;

col mod         heading 'Stmt Ident'            format 999999999999
col dr          heading 'Disk Rds'              format  999,999,999
col bg          heading 'Buff Gets'             format  999,999,999
col sr          heading 'Sorts'                 format      999,999
col exe         heading 'Runs'                  format      999,999
col load        heading 'Load Factor'           format      999,999

select  distinct
        trunc(a.hash_value/99999)           mod,
        a.disk_reads                    dr,
        a.buffer_gets                   bg,
        a.sorts                         sr,
        a.executions                    exe,
        (a.disk_reads*10+a.buffer_gets)/1000    load
from    v$sqlarea a, v$sqltext b
where   a.disk_reads > &min_disk_reads
  and   a.buffer_gets  > &min_buffer_gets
  and   a.hash_value = b.hash_value
order by a.disk_reads*10+a.buffer_gets desc;

undefine min_disk_reads;
undefine min_buffer_gets;

spool off;
set echo on;
set feedback on;
commit;