Re: Poor Buffer Hit Ratio

In article <3915F389.76C8_at_btinternet.com>,   High.Flight_at_btinternet.com wrote:
> Never mind about your buffer cache, you've clearly got an application
> design / SQL design / Logical Data Model issue here!
>
> Start looking at v$sql and v$sqlarea.
>
> Extract the SQL statements that look difficult and 'replay' them
 either
> from SQL*Plus or alternatively, look at tracing SQL statement on a
> session or instance-wide basis. SQL Trace and TKProf can also help
 here.
>
> David P.
>
> Oracle Certified DBA.
>

Why do you say that? The original post just refered to the buffer hit ratio and my post of a note from Oracle states that for this version of Oracle the buffer pool statistics are not even maintained correctly by Oracle so you can not put much weight on the value you calculate for the buffer hit ratio. And for version 8.1+ the formula has to be adjusted for another io statistic. The buffer hit ratio should be used in conjuction with the other buffer pool statistics before any conclusions are reached.

More importantly, a hit ratio of 70 can be acceptable. For an OLTP 70 is normally too low, but not always, while for DSS, OLAP, or warehouse it may be fine. In fact it can be fine for an OLTP when the OLTP is very large and the IO is very random. What counts is the application performance. The ratio is best used for trend analysis, i.e., it is level, trending up, or trending down. It is not very meaningful in itself and is often given too much weight since one bad SQL could be causing the low hit ratio or it could just be how the application works.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.