Home » RDBMS Server » Performance Tuning » Buffer Cache Hit Ratio
Buffer Cache Hit Ratio [message #65485] Tue, 05 October 2004 10:54 Go to next message
Shariq T
Messages: 29
Registered: February 2003
Junior Member
I executed the following query on my data warehouse

select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;


and my results were

logical_reads phys_reads BUFFER HIT RATIO
------------- ---------- ----------------
   3.6893E+19 1.8447E+19               50

Is this acceptable for a DW environment or should I be asking the DBA for some serious tuning

Shariq
Re: Buffer Cache Hit Ratio [message #65488 is a reply to message #65485] Wed, 06 October 2004 06:35 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
for a DW environment a lower buffer hit ratio is common.
Actually considering buffer cache hit ratio's as measure for tuning is flawed.
COnnor Mcdonald & Jonathan lewis have given a classic example to prove this. With his method, you can actually get any hit ratio YOU WANT!!
http://oracledba.co.uk/tips/choose.htm
 In these days where people are still mistakenly 
worrying about hit ratios, here is a simple routine to 
bump up your hit ratio to any value desired. Thanks to 
Jonathan Lewis for some of the strategy used. 

create or replace
procedure choose_a_hit_ratio(p_ratio number default 99,p_show_only boolean default false) is
  v_phy number;
  v_db  number;
  v_con number;
  v_count number;
  v_additional_congets number;
  v_hit number;
  procedure show_hit is
  begin
    select p.value, d.value, c.value
    into v_phy, v_db, v_con
    from 
      ( select value from v$sysstat where name = 'physical reads' ) p,
      ( select value from v$sysstat where name = 'db block gets' ) d,
      ( select value from v$sysstat where name = 'consistent gets' ) c;
    v_hit := 1-(v_phy/(v_db+v_con));
    dbms_output.put_line('Current ratio is: '||round(v_hit*100,5));
  end;
begin
--
-- First we work out the ratio in the normal fashion
--
  show_hit;

  if p_ratio/100 < v_hit or p_ratio > 99.9999999 then
    dbms_output.put_line('Sorry - I cannot help you');
    return;
  end if;
--
-- Flipping the formula we can work out how many more consistent gets
-- we need to increase the hit ratio
--
  v_additional_congets := trunc(v_phy/(1-p_ratio/100)-v_db - v_con);

  dbms_output.put_line('Another '||v_additional_congets||' consistent gets needed...');

  if p_show_only then return; end if;
--
-- Create a simple table to hold 200 rows in a single block
--
  begin
    execute immediate 'drop table dummy';
  exception 
    when others then null;
  end;

  execute immediate 'create table dummy (n primary key) organization index as '||
                    'select rownum n from all_objects where rownum <= 200';
--
-- Turn off any new 9i connect-by features to ensure we still do lots of 
-- logical IO
--
  begin
    execute immediate 'alter session set "_old_connect_by_enabled" = true';
  exception 
    when others then null;
  end;
--
-- Grind away until we do all those additional gets
--
  execute immediate '
    select count(*) 
    from (
      select n
      from dummy
      connect by n > prior n
      start with n = 1 )
    where rownum < :v_additional_congets' into v_count using v_additional_congets;

  show_hit;
end;
/

And some output to keep the hit ratio fanatics happy!

SQL> exec choose_a_hit_ratio(85,true);
Current ratio is: 82.30833
Another 29385 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(85);
Current ratio is: 82.30833
Another 29385 consistent gets needed...
Current ratio is: 86.24548

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90,true);
Current ratio is: 86.24731
Another 79053 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(90);
Current ratio is: 86.24731
Another 79053 consistent gets needed...
Current ratio is: 90.5702

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98,true);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...

PL/SQL procedure successfully completed.

SQL> exec choose_a_hit_ratio(98);
Current ratio is: 90.5709
Another 1141299 consistent gets needed...
Current ratio is: 98.02386

PL/SQL procedure successfully completed.

[Updated on: Thu, 09 March 2006 12:43]

Report message to a moderator

Previous Topic: PLUSTRACE Role
Next Topic: URGET:-SQL statement not performing well.
Goto Forum:
  


Current Time: Mon Apr 22 06:46:56 CDT 2024