How to “improve” buffer cache hit ratio
February 13th, 2006 By Vadim Bobrov
Buffer cache hit ratio should be left alone unless you have a specific problem that you traced to buffer cache performance. To prove, the following script will inflate buffer cache hit ratio to satisfy the most demanding user :-) Depending on your system configuration you may need to run it multiple times to reach the desired effect:
declare
res varchar2(1);
begin
for i in 1..10000000 loop
select dummy into res from dual;
end loop;
end;
Don’t do it on production!! (test in development first)
Vadim Bobrov
Oracle Database Tools
http://www.fourthelephant.com
February 13th, 2006 at 10:23 pm
Great!
Now we could save a lot of money tuning efforts would cost!
;-)
… and we are proud of using our CPU resources up to 100%
;-)
February 13th, 2006 at 10:41 pm
thanks :-)
with a little bit of fantasy this idea can further be developed to tune almost anything in the database. For example if the top wait event in the database is “db file sequential read” and it seems to bother anyone - one can provide the script to “promote” the desired wait event to the top instead
Vadim Bobrov
Oracle Database Tools
http://www.fourthelephant.com
February 13th, 2006 at 11:12 pm
Hello Vadim,
your idea is right. A more elaborated example, which allows you to set the buffer cache hit ratio to some specific value, can be found at http://www.oracledba.co.uk/tips/choose.htm
BR,
Martin