SCRIPT: Script to Report SGA Buffer Summary

Subject:	SCRIPT: Script to Report SGA Buffer Summary
	Doc ID:	Note:1019635.6	Type:	SCRIPT
	Last Revision Date:	21-MAY-2006	Status:	PUBLISHED

Abstract Script to report statistics about the buffers in the SGA.   Product Name, Product Version Oracle Server, 7.3.X to 9.x Platform Platform Independent Date Created 19-Jun-2000   Instructions Execution Environment:        Access Privileges:      This script needs to be run connected as SYS.   Usage:      sqlplus SYS/ @[SCRIPTFILE]   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 The following script reports statistics about the buffers in the SGA. It will  return information about the types of buffers in the SGA, how many of them are on the dirty queue and how many are not.   Sample Output =============       Class       Not Dirty      Dirty   On Dirty      Total     ---------- ---------- ---------- ---------- ----------     7                   1         00          1     Data             2194        598          0       2792     Header             35          0          0         35     Rollback          370          2          0        372   References     Script SET ECHO off REM NAME:   TFSSGABF.SQL REM USAGE:"@path/tfssgabf" REM ------------------------------------------------------------------------ REM REQUIREMENTS: REM    Needs to be run as SYS REM ------------------------------------------------------------------------ REM AUTHOR:  REM    Virag Saksena, Oracle Corporation      REM    (c)1996 Oracle Corporation REM ------------------------------------------------------------------------ REM PURPOSE: REM    Reports statistics about the buffers in the SGA. It will print REM    the information about the types of buffers in the SGA, how many of REM    them are on the dirty queue and how many are not. REM ------------------------------------------------------------------------ REM DISCLAIMER: REM    This script is provided for educational purposes only. It is NOT  REM    supported by Oracle World Wide Technical Support. REM    The script has been tested and appears to work as intended. REM    You should always run new scripts on a test instance initially. REM ------------------------------------------------------------------------ REM Main text of script follows:   col class form A10 select decode(greatest(class,10),10,decode(class,1,'Data',2             ,'Sort',4,'Header',to_char(class)),'Rollback') "Class",        sum(decode(bitand(flag,1),1,0,1)) "Not Dirty",  sum(decode(bitand(flag,1),1,1,0)) "Dirty",        sum(dirty_queue) "On Dirty",count(*) "Total" from x$bh group by decode(greatest(class,10),10,decode(class,1,'Data',2          ,'Sort',4,'Header',to_char(class)),'Rollback') /