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 find Dictionary Hit ratio, Library Cache Hit ratio & SQLs with high disk reads.

REM: Script for Dictionary 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:*****************************************

SELECT (1 - (SUM (getmisses) / SUM (gets))) * 100 \"Hit Ratio\"
FROM v$rowcache;

*******************************************************************************
REM: Script for finding Library 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:*****************************************

SELECT SUM (pins) / (SUM (pins) - SUM (reloads)) * 100 \"Hit Ratio\"
FROM v$librarycache;

*******************************************************************************
REM: Script to find sql with high disk reads.
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:*****************************************

SELECT disk_reads, sql_text
FROM v$sqlarea
WHERE disk_reads > 1000
ORDER BY disk_reads DESC;

*******************************************************************************

No comments:

Post a Comment

Thanks for you valuable comments !