User hit ratios

posted Sep 27, 2010, 6:36 AM by Sachchida Ojha
SELECT   (DECODE ((INITCAP (se.username)), NULL, 'SYS', se.username)
          ) username,
          se.SID usersession,
          SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0)) consisgets,
          SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0)) dbblkgets,
          SUM (DECODE (sn.NAME, 'physical reads', ss.VALUE, 0)) physreads,
          ROUND(  (  SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0))
             + SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0))
             - SUM (DECODE (sn.NAME, 'physical reads', ss.VALUE, 0))
            )
          / (  SUM (DECODE (sn.NAME, 'consistent gets', ss.VALUE, 0))
             + SUM (DECODE (sn.NAME, 'db block gets', ss.VALUE, 0))
             + 1
            ), 6)
          * 100 hitratio,
          MAX (UPPER(ins.instance_name)) instancename
    FROM v$sesstat ss, v$statname sn, v$session se, v$instance ins
   WHERE ss.SID = se.SID
     AND sn.statistic# = ss.statistic#
     AND ss.VALUE != 0
     AND sn.NAME IN ('db block gets', 'consistent gets', 'physical reads')
GROUP BY se.username, se.SID
ORDER BY 1,2
Comments