DBA Tips Archive for Oracle

  


Hit Ratios by System and Session

by Jeff Hunter, Sr. Database Administrator

Most Oracle DBA books that talk about the buffer cache hit ratio will typically refer to the entire system. They generally provide a pretty straight forward query like the one shown in Script #1. But what if you want to know which active session is bogging down the system with a bad hit ratio? Well that's where Script #2 comes in. This script will use the views V$PROCESS, V$SESSION and V$SESS_IO to calculate the hit ratio for each session and then order it by showing the worst hit ratio first. The ratio of buffer to physical block reads can many times be an indication of the efficiency of the query running. Anything under 90% is typically bad. Very low hit ratios (< 10-30%) in a process can slow down the entire system.

SELECT
    TO_CHAR(SUM(DECODE(name, 'consistent gets', value, 0)), 
            '999,999,999,999,999,999') con
  , TO_CHAR(SUM(DECODE(name, 'db block gets'  , value, 0)), 
            '999,999,999,999,999,999') dbblockgets
  , TO_CHAR(SUM(DECODE(name, 'physical reads' , value, 0)), 
            '999,999,999,999,999,999') physrds
  , ROUND( ( ( 
               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,2
         ) Hitratio
FROM v$sysstat
/
Script #1 : Display hit ratio entire system
SET LINESIZE 180
SET PAGESIZE 66

COLUMN unix_id          FORMAT a10                  HEAD Username
COLUMN oracle_id        FORMAT a10                  HEAD OracleID
COLUMN os_user          FORMAT a10                  HEAD OS_User
COLUMN sid              FORMAT 999                  HEAD SID
COLUMN serial_id        FORMAT 999999               HEAD Serial#
COLUMN unix_pid         FORMAT a9                   HEAD UNIX_Pid
COLUMN consistent_gets  FORMAT 999,999,999,999,999  HEAD Cons_Gets
COLUMN block_gets       FORMAT 999,999,999,999,999  HEAD Block_Gets
COLUMN physical_reads   FORMAT 999,999,999,999,999  HEAD Phys_Reads
COLUMN hit_ratio        FORMAT 999.00               HEAD Hit_Ratio

    SELECT
        p.username            unix_id
      , s.username            oracle_id
      , s.osuser              os_user
      , s.sid                 sid
      , s.serial#             serial_id
      , LPAD(p.spid,7)        unix_pid
      , sio.consistent_gets   consistent_gets
      , sio.block_gets        block_gets
      , sio.physical_reads    physical_reads
      , ROUND((consistent_gets+Block_gets-Physical_reads) /
              (Consistent_gets+Block_gets)*100,2)             hit_ratio
    FROM
        v$process p
      , v$session s
      , v$sess_io sio
    WHERE
          p.addr=s.paddr
      AND s.sid = sio.sid
      AND (sio.consistent_gets + sio.block_gets) > 0
      AND s.username is not null
    ORDER BY hit_ratio
/
Script #2 : Display hit ratio for each session


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Thursday, 05-Aug-1999 00:00:00 EDT
Page Count: 13074