Re: Hit Ratio

Yong:

I have not seen all the threads on this. So there are chances some body might have
covered this/I may be missing some interesting things..But the issue is, tuning or measuring the
database performance ONLY with Hit Ratios. By high hit ratios Damagement will tend to understand , that percentage of data is read from the cache/memory
and try to add memory till the get closer to 100..

I think what we need to understand is the interpretation of Hit ratio. 90% HIT
ratio does not mean 90% of the data is read from the disk. It just tells a block
or buffer which was read in to cache is RE-READ 9 times before it goes to disk.
I have seen many sites with oversized buffer cache/shared pool targetting 100%
hit ratio and suffering huge latch contention. I have been to a site recently where a FLUSH
shared pool took nearly 5 minutes and checkpoint took close to a minute, with 99.99%CHR.

But simulating high wait times by yout tricks for a particular session may bump the wait times
& You may probably generate high times for enqueue or any of the IO events. But when you use 10046 or V$session_wait for a particular session, the bumped numbers will not be affecting the diagnosability of your problem.

But if you want to start questioning, you can question the timing details of the
wait events. Oracle uses gettimeofday () to get the time of the wait events and
if you alter the system time couple of times, that may give some odd numbers to the entire timing data. But the bottomline is , Hit ratios are beautiful numbers
but, you can not relate the pattern to the performance. May be you can compare
the hit ratio when the system is good/bad and figure out there is a change in IO
pattern between those interval.. IMHO and YMMV.

Regards,
K Gopalakrishnan

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Tuesday, December 23, 2003 8:59 PM

> Hi, Carel-Jan and Rich,
>
> Connor's script to bump up buffer cache hit ratios is meant to be a humor.
Only
> if you carefully comtemplate it will you see that there's no relevance of
the
> fact that you can get any hit ratio to the fact that hit ratios are
> insufficient in performance tuning.
>
> It would be equally easy to write scripts to bump up some wait event
times. If
> you need very long db file reads, create a big table and keep scanning it.
If
> you need long enqueue waits, create a table and insert a row. Create 10 or
100
> sessions (depending on your patience) and delete from that table and wait.
The
> fact that you can get arbitary wait times does not reduce the efficacy of
wait
> event interface as a performance tuning tool.
>
> Buffer cache or library cache hit ratios are not sufficient, very
insufficient
> used alone, to tune the database. The reason is that they don't contain
enough
> information to tune the system with. This is the only reason we should not
> solely rely on them; in fact, not using them at all doesn't hurt much. The
> reason is not that we can get any value we want by playing pranks.
>
> Hit ratios are still used in other performance tuning and not condemned.
> Although in UNIX performance tuning one looks at absolute numbers such as
scan
> rate, CPU usage and netstat output more often, hit ratios in some sar
output
> are still occasionally used. Most ratios could still be distored by a
rogue
> user repeatedly doing, say, "find /" for inodes or "find / -exec grep
SomeThing
> {} \;" for page cache.
>
> In any tuning practice, Oracle or OS, artificially distorting usage
patterns
> invalidates your numbers even if you're using a well respected tuning
method.
> So only play pranks on a play box, not production.
>
> Yong Huang
>
> At 11:14 22-12-03 -0800, you wrote:
> >My BCHR is currently 96.62%. In the past, it was normally over 99%.
What
> >should I do?
> >
> >I'll be waiting for Mladen's reply... :)
> >
> >
> >Rich
> >
> >Rich Jesse System/Database Administrator
> >rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA
>
> Go to www.oracledba.co.uk (Connor) or go to O'Reilly (download page of
> Cary's book), and download one of the fabulous BCHR enhancement scripts.
> Especially when your bonus depends on it, this is a good time to perform
> some BCHR tuning.
>
> Regards, Carel-Jan
>
> __________________________________
> Do you Yahoo!?
> New Yahoo! Photos - easier uploading and sharing.
> http://photos.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Yong Huang
> INET: yong321_at_yahoo.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: K Gopalakrishnan
  INET: kaygopal_at_yahoo.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).