spool proc_001.rslt 

set linesize 80;
set pagesize 1000;
set long 50;
set echo on;

break on "USER" on "OS USER";

/* ****************************************************** */
/*                                                        */
/* script to show what activity is going on within the    */
/*    database at the time the routine was submitted      */
/* Owner: R. Kupcunas                                     */
/* Date:  May 1, 1995                                     */
/*                                                        */
/*         @c:\oracle\sql\proc_001.sql                    */ 
/*                                                        */
/*  "A" ............... block gets                        */
/*  "B" ............... consistent gets                   */
/*  "C" ............... v$sess_io.physical_reads          */
/*  "D" ............... block changes                     */
/*  "E" ............... consistent changes                */
/*  "F" ............... logical reads                     */
/*  "G" ............... v$sesstat.physical_reads          */
/*  "H" ............... v$sesstat.physical_writes         */
/* ****************************************************** */

select substr(osuser, 1, 10)                               "OS USER",	
       substr(username, 1, 10)                             "USER",       
       substr(process, 1, 10)                              "PID",
       substr(to_char(A.sid), 1, 5)                        "SID",
       substr(to_char(block_gets), 1, 5)                   "-A-",
       substr(to_char(consistent_gets), 1, 8)              "-B-",
       substr(to_char(physical_reads), 1, 8)               "-C-",
       substr(to_char(block_changes), 1, 8)                "-D-",
       substr(to_char(consistent_changes), 1, 8)           "-E-",
       substr(to_char(block_gets + consistent_gets), 1, 8) "-F-",
       round((block_gets + consistent_gets - physical_reads)/
         (greatest(block_gets + consistent_gets, 1))*100, 2)            "HIT RATIO",
       substr(to_char(C.value), 1, 8)                      "G",
       substr(to_char(D.value), 1, 8)                      "H"
from v$session A,
     v$sess_io B,
     v$sesstat C,
     v$sesstat D
where A.sid        = B.sid and
      A.sid        = C.sid and
      A.sid        = D.sid and
      C.statistic# = 39    and
      D.statistic# = 40    and
      block_gets  != 0     and
      consistent_gets != 0 
order by 1 asc,
      2 asc;

set echo off;
spool off;
set long 80;
commit;









