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 tune buffer cache hit ratio

REM: Script to tune buffer cache hit ratio
REM:*****************************************
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
REM:*****************************************


DECLARE
DB_BLK NUMBER(10,2);
CONS_GET NUMBER(10,2);
PHY_RDS NUMBER(10,2);
FINAL NUMBER(10,2);
begin
Select value INTO DB_BLK From v$sysstat Where name in (\\'db block gets\\');
Select value INTO CONS_GET From v$sysstat Where name in (\\'consistent gets\\');
Select value INTO PHY_RDS From v$sysstat Where name in (\\'physical reads\\');
FINAL:=(1-(PHY_RDS/(DB_BLK+CONS_GET)))*100;
if final>90 then
dbms_output.put_line(\\'The BUFFER CACHE HIT RATIO IS OK \\'||final);
end if;
if final<90 then
dbms_output.put_line(\\'increaSe the initialisation parameter DB_CACHE_SIZE \\'||final);
end if;
end;
/

No comments:

Post a Comment

Thanks for you valuable comments !