doc

    Name:   lowhits.sql

    Author: Mark Gurry


The following script provides more information on users that are experiencing
poor hit ratios.

#

drop table user_hit_ratios;


create table user_hit_ratios as
(select se.username||'('|| se.sid||')' "User Session",
       sum(decode(name, 'consistent gets',value, 0))  "Consistent Gets",
        sum(decode(name, 'db block gets',value, 0))  "DB Block Gets",
        sum(decode(name, 'physical reads',value, 0))  "Physical Reads",
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  -
        sum(decode(name, 'physical reads',value, 0)))
		       /
       (sum(decode(name, 'consistent gets',value, 0))  +
        sum(decode(name, 'db block gets',value, 0))  )  * 100 "Hit Ratio" 
  from  v$sesstat ss, v$statname sn, v$session se
where   ss.sid    = se.sid
  and   sn.statistic# = ss.statistic#
  and   value != 0
  and   sn.name in ('db block gets', 'consistent gets', 'physical reads') 
group by se.username, se.sid);

break on "User Session"
ttitle 'Objects Being Used by Users with Hit Ratio < 60'

select se0.username||'('|| se0.sid||')' "User Session",
       substr(owner, 1,12) "Object Owner",                              
       substr(object,1,30) "Object"
  from  v$access ac, v$session se0
where   ac.sid    = se0.sid
  and   ob_typ   = 2
  and   60 > 
  (select "Hit Ratio" from user_hit_ratios
   where se0.username||'('|| se0.sid||')' = "User Session")
order by username, se0.sid, owner ;


column "User Session"  format a12;

ttitle 'Cursors that Users currently have Open Where User Hit Ratio < 60%'

select distinct username||'('||v$session.sid||')' "User Session",  sql_text 
from v$open_cursor , v$session
where v$session.saddr       = v$open_cursor.saddr   
  and   60 > 
  (select "Hit Ratio" from user_hit_ratios
   where username||'('||user_hit_ratios.sid||')' = "User Session")
order by username, v$session.sid;
/


ttitle 'Cursors Currently Running for Users with Hit Ratio < 60% '

select v$session.username||'('||v$session.sid||')' "User Session",  
       sql_text 
from v$sqlarea , v$session
where v$session.sql_address = v$sqlarea.address
  and v$session.sql_hash_value = v$sqlarea.hash_value
  and   60 > 
  (select "Hit Ratio" from user_hit_ratios
   where username||'('||user_hit_ratios.sid||')' = "User Session")
order by username, v$session.sid;
/