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 library cache

REM: Script to tune library cache
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
hit NUMBER(10,2);
reload number(10,2);
begin
Select ((sum(pinhits) / sum(pins))*100) into hit From v$librarycache Where namespace in (\\'SQL AREA\\', \\'TABLE/PROCEDURE\\',\\'BODY\\', \\'TRIGGER\\');
select ((sum(reloads)/sum(pins))*100) into reload From v$librarycache Where namespace in (\\'SQL AREA\\', \\'TABLE/PROCEDURE\\', \\'BODY\\', \\'TRIGGER\\');
IF hit<85 then
dbms_output.put_line(\\'The hit ratio should be at least 85% (i.e. 0.85). this is ok \\'||hit);
end if;
IF hit>85 then
dbms_output.put_line(\\'THE hit ratio >85%. check shared pool and open cursor parameter \\'||hit);
end if;
dbms_output.put_line(\\'*************************************************************************\\');
if reload>.02 then
dbms_output.put_line(\\'The reload percent should be very low, 2% (i.e. 0.02) or less. this is ok \\'||reload);
end if;
if reload<.02 then
dbms_output.put_line(\\'The reload >2% \\'||reload);
end if;
end;
/

No comments:

Post a Comment

Thanks for you valuable comments !