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-2018 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 firstname.lastname@example.org.
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.