SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters

Subject:	SCRIPT: Calculating Buffer Cache Hit Ratio without Inputing Parameters
	Doc ID:	Note:1039290.6	Type:	SCRIPT
	Last Revision Date:	06-JAN-2003	Status:	PUBLISHED

Abstract This script calculates the database cache hit ratio through the use of PL/SQL.   Product Name, Product Version Oracle Server, 7.3 to 9.2 Platform Platform Independent Date Created 29-NOV-1997   Instructions Execution Environment:      SQL, SQL*Plus   Access Privileges:      Requires DBA access privileges to be executed.   Usage:      sqlplus sys/   Instructions:      Run the script in SQL*Plus to create the required procedure.                     After creating the procedure, use the following select statement          to retrieve current results.        SQL> select  global_name, :r "Hit Ratio" from  global_name ;             GLOBAL_NAME   Hit Ratio           -----------   ----------           V920.WORLD    .995129671     PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.   Description This script calculates the current database cache hit ratio.   References                        Script rem rem  This script uses PL/SQL. rem  le hit ratio  rem    col global_name for A20  variable d number  variable c number  variable r number  begin    select value into :c    from v$sysstat     where name = 'consistent gets';    select value into :d    from v$sysstat     where name = 'db block gets';    select (1- value/( :c + :d)) into :r      from v$sysstat    where name in ( 'physical reads');     end;  /   After creating the procedure, use the following select statement to retrieve current results.   SQL> select  global_name, :r "Hit Ratio" from  global_name ;        GLOBAL_NAME   Hit Ratio      -----------   ----------      V920.WORLD    .995129671