SQL Script DB Cache Advice

Updated: 26-Mar-2005
Version: 2.0

Description

New 9i view which details the potential benefits of increasing the buffer_pool paramaters is the hit ratios are low

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

set pages 2000
col ID   form 99 head "ID"                                  
col NAME form a10 head "Name"                                  
col BLOCK_SIZE form 99,999 heading "Block Size"                           
col ADVICE_STATUS form a5 head "Stat"                         
col SIZE_FOR_ESTIMATE form 99999 head "Size for Est"                     
col SIZE_FACTOR           form 99.99 head "Sz Fact"                 
col BUFFERS_FOR_ESTIMATE form 999,999 head "Buff For Est"                  
col ESTD_PHYSICAL_READ_FACTOR form 999.99 head "Est Phy Rd F"            
col ESTD_PHYSICAL_READS   form 999,999,999 head "Est Phy Rd" 
col hit_ratio form 99.9% heading "Hit Ratio"                

spool dbcache

show parameter db_cache
show parameter db_block

PROMPT 
PROMPT BUffer Pool Hit Ratios
PROMPT

SELECT NAME, PHYSICAL_READS, DB_BLOCK_GETS, CONSISTENT_GETS
      , (1 - (PHYSICAL_READS / (DB_BLOCK_GETS + CONSISTENT_GETS)))*100 hit_Ratio
 FROM V$BUFFER_POOL_STATISTICS;

PROMPT
PROMPT DB Cache Advice
PROMPT

select name
      ,size_for_estimate
      ,size_factor
      ,buffers_for_estimate
      ,estd_physical_read_factor
      ,estd_physical_reads 
from v$db_cache_advice;

spool off

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links