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>