Oracle & MySQL Support

InfraStack-Labs Oracle & MySQL DBA Services help you manage, maintain, and optimize your critical Oracle systems. We deliver 24/7, year-round support with flexible monthly contracts that don’t lock you in.

Please contact me :- ajith.narayanan@infrastack-labs.in

Friday, May 9, 2008

Script to measure the Buffer Cache Hit Ratio

Script to measure the Buffer Cache Hit Ratio
---------------------------------------------
rem :
rem Purpose:Measure the Buffer Cache Hit Ratio
rem Note:Hit ratio based tuning is not recommended!
rem -----------------------------------------------------------------------

-- Get initial Buffer Hit Ratio reading...
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) \"Cache Hit Ratio\"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/

-- Let's artificially increase the buffer hit ratio...
DECLARE
v_dummy dual.dummy%TYPE;
BEGIN
FOR I IN 1..1000 LOOP
SELECT dummy INTO v_dummy FROM dual;
END LOOP;
END;
/

-- Let's measure it again...
SELECT ROUND((1-(phy.value / (cur.value + con.value)))*100,2) \"Cache Hit Ratio\"
FROM v$sysstat cur, v$sysstat con, v$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
/

No comments:

Post a Comment

Thanks for you valuable comments !