#!/bin/csh
#
#   This script takes a snapshot of SQL statements running over a 
#   30 second period and documents their efficiency
#   You will need to change the user to the appropriate username
#   and pass to the password at your site.

sqlplus user/pass            << END

set heading off
set pagesize 999
drop table most_disk_reads;   

create table most_disk_reads   
   (buffer_gets, disk_reads, executions, address, hash_value )
as
select   buffer_gets, disk_reads, executions, address, hash_value 
  from v\$sqlarea                                        
/

create index  most_disk_reads_ndx1  on  most_disk_reads  (address, hash_value);
exit 
END

echo
echo    Now Waiting 30 Seconds for Period delay
echo 


sleep 30

echo
echo    30 Seconds Complete *** Continue Processing
echo 

sqlplus user/pass            << END

set heading off
set pagesize 999

drop table most_end_disk_reads;   

create table most_end_disk_reads   
     (buffer_gets, disk_reads, executions, address, hash_value )
as
select   buffer_gets, disk_reads, executions, address, hash_value  
  from v\$sqlarea                                        
/

create index  most_end_disk_reads_ndx1  on  most_end_disk_reads  
    (address, hash_value);

Doc   

    Deleting Statements that Have Not Been Run

#

delete from most_end_disk_reads where (address, hash_value)
IN
 (select a.address, a.hash_value  from most_disk_reads a , most_end_disk_reads b
   where b.disk_reads - a.disk_reads = 0
     and b.buffer_gets - a.buffer_gets = 0
     and b.hash_value  = a.hash_value 
     and b.address     = a.address) 
/


delete from most_disk_reads
 where  (address, hash_value)
IN 
(select address, hash_value
   from most_disk_reads 
 MINUS
 select address, hash_value
   from most_end_disk_reads)
/

Doc       

    Reporting All Database Activity


#
spool $HOME/sqlstmts.lis

column "CPU Usage" format 999.99
column user_session format a20
column nl newline;


select 'Statement has been Running with Many Disk Reads or Buffer Gets' nl,
       'Period Disk Reads = '|| to_char(c.disk_reads - b.disk_reads) nl,
       'Period Buffer Gets= '|| to_char(c.buffer_gets - b.buffer_gets) nl,
       'Period Executions = '|| to_char(c.executions - b.executions) nl,
        sql_text nl,
       'Expected Disk Read Time in Seconds = '|| a.disk_reads / decode(a.executions,0,1,a.executions) / 50 nl , 
       'Expected Buffer Get Time in Seconds = '|| a.buffer_gets / decode(a.executions,0,1,a.executions) / 500 nl , 
       'Total Statement Executions = '||a.executions nl
  from  v\$sqlarea a , most_disk_reads b, most_end_disk_reads c
 where c.hash_value = b.hash_value
   and c.address    = b.address
   and a.hash_value = c.hash_value
   and a.address    = c.address
   and (  c.disk_reads - b.disk_reads > 50 
                      OR
          c.buffer_gets - b.buffer_gets > 1000)
/

spool off

drop table most_disk_reads;   
drop table most_end_disk_reads;   
exit   
END