script for buffer hit ratio ??

I’m seeking some help understanding a PL*SQL script for determining buffer hit ratios. I picked up the script from Oracle Magazine’s “Tip and Code Archive.” Specifically, it is “listing 6” located at http://www.oracle.com/oramag/code/cod46dba.html. As published there, the section of code dealing with buffer hit ratio has two blocks of code, one of which is commented out. When I run the code as published, the buffer hit ratio is reported as a negative number in the thousands. This made no sense to me, so I uncommented the other block of code and ran it, getting number that made sense. After closely analyzing the two methods, I find that the one that was originally commented out is essentially saying

buffer hit ratio = 1 – physical_reads / (db_block_gets + consistent_gets)

While the originally active code is saying

buffer hit ratio = ((opens_of_replaced_files + db_block_gets) – consistent_gets) / (opens_of_replaced_files + db_block_gets))

Does anyone have any comments on the use or meaning of either of these pieces of code?

TIA

• Ed Stevens

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 22 1999 - 10:49:37 CDT