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.