Friday, September 07, 2012

Hit Ratio by Session

This was tested on Oracle 11.2. I recently did a post showing how you could calculate your database's hit ratio. If you need to see the hit ratio broken down by session, you can do it like this:

SQL> select a.sid, trunc
  2  ((1-(physical_reads/(db_block_gets+consistent_gets)))*100,1)||'%'
  3  hit_ratio from
  4  (select sid, ses.value physical_reads
  5   from v$sesstat ses, v$sysstat sys
  6   where ses.statistic# = sys.statistic#
  7   and name = 'physical reads') a,
  8  (select sid, ses.value db_block_gets
  9   from v$sesstat ses, v$sysstat sys
 10   where ses.statistic# = sys.statistic#
 11   and name = 'db block gets') b,
 12  (select sid, ses.value consistent_gets
 13   from v$sesstat ses, v$sysstat sys
 14   where ses.statistic# = sys.statistic#
 15   and name = 'consistent gets') c
 16  where a.sid = b.sid
 17    and b.sid = c.sid
 18    and db_block_gets + consistent_gets <> 0
 19  order by sid
 20  /

       SID HIT_RATIO
---------- --------------------
         2 99.6%
         4 99.9%
         5 99.6%
         6 99.7%
        25 99.5%
        47 100%
        50 99.9%
        51 99.6%
        67 99.5%
        70 100%
        71 100%
        91 98.3%
        92 99.9%
        93 99.4%
       112 95.6%
       113 98.8%
       115 99.3%
       116 99.5%
       133 94.4%
       134 97.7%
       137 98.6%
       139 100%
       140 99.7%
       156 99.2%
       159 99.8%
       160 99.1%

26 rows selected.

SQL>

No comments: