Re: Poor Buffer Hit Ratio

From: Mark D Powell <markp7832_at_my-deja.com>
Date: 2000/05/08
Message-ID: <8f6ip5$6vn$1@nnrp1.deja.com>#1/1

In article <8evdm7$qc4$1_at_nnrp1.deja.com>,   junderhi_at_my-deja.com wrote:
> Thanks for the excellent info Mark. I have since kicked my
> db_block_buffers down to 40000 (our original setting) and performance
> appears unchanged. Unfortunately, I am not familiar with how to
 assess
> the two parameters you mentioned:
>
> 'How many free buffers
> do you have on average and what are your buffer pool latch hit
 ratios?'
>
> Are these available through v$sysstat or might you have a snippet that
> you use to monitor these? Thanks in advance.
>

You may want to look at the Performance and Tuning Manual to verify these scripts for your release:

set echo off
rem file: buf_free.sql
rem PL/SQL script to determine the average number of available (free) rem db buffer cache buffers at the end of the LRU. rem
set serveroutput on
declare

v_dbwr_free_buf_found    number  := 0 ;
v_dbwr_make_free_req     number  := 0 ;
v_free_buf_per           number  := 0 ;

begin
select value
  into v_dbwr_free_buf_found
  from v$sysstat
 where name = 'DBWR free buffers found' ;
select value
  into v_dbwr_make_free_req
  from v$sysstat
 where name = 'DBWR make free requests' ;
v_free_buf_per := trunc(v_dbwr_free_buf_found / v_dbwr_make_free_req) ;

dbms_output.put_line('DBWR Free Buffers
Found '||v_dbwr_free_buf_found) ;
dbms_output.put_line('DBWR Make Free

Requests '||v_dbwr_make_free_req ) ;
dbms_output.put_line('Avg. No. Free
Buffers '||v_free_buf_per ) ;
end ;
/

set echo off
rem file: buf_latch.sql
rem SQL*Plus script to display buffer pool latch statistics rem
rem 19980203 m d powell New script.
rem
column name format a30
column GHR format 990.9 heading "Get Hit|Ratio" column IGR format 990.9 heading "IGet Hit|Ratio" select name,

       round((( gets - misses ) / gets) * 100,3)  GHR,
       sleeps,
       round((( immediate_gets - immediate_misses ) /
                decode(immediate_gets,0,1,immediate_gets) * 100),3)  IGR

from v$latch
where name like 'cache_buff%'
/

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Mon May 08 2000 - 00:00:00 CDT