I was perusing some old content during a hard drive “spring clean” the other day, and I found an old gem from way back in 2001. A time when the database community were trying to dispel the myth that all database performance issues could be tracked back to, and solved via, the database buffer cache hit ratio. Thankfully, much of that folklore has now passed into the realm of fiction, but I remember at the time, as a means of showing how silly some of the claims were, I published a routine that would generate any buffer cache hit ratio you desired. It just simply ran a query to burn through logical I/O’s (and burn a whole in your CPU!) until the required number of operations bumped up the buffer cache hit ratio to whatever number you liked 
Less performance, more work done…. all to get a nice summary number.
The kinds of statistics that the database collects, and what each one represents has changed over the years and versions of Oracle, but I figured I’d present the routine in original form as a nostalgic reminder that statistics without an understanding behind them are as good as no statistics at all.
Enjoy !
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 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 prior n
start with n = 1 )
where rownum < :v_additional_congets' into v_count using v_additional_congets;
show_hit;
end;
/
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.
Learning is not a spectator sport 
Got some thoughts? Leave a comment