DB Buffer Cache Hit Ratio

[vijay8282]

Hi,

What things hv an effect on DB Buffer Cache Ratio.
I mean what all factors.

Like Librray Cache HR can be improved by writing sharable codes.

Vijay

[pando]

tune sql statements

[vendechukp]

The question looks stupid.

any way the solution is tuning.

[stmontgo]

basically it's telling us how many disk reads we are having to make. A low ratio is bad, higher is better. Open to interpretation but a common acceptable ratio seems to be 90%. Initialially one might think that they could increas the size of the buffer cache to cut own on read to disk by adding more db_block_buffers. Doing so may ignor the root of the problem.

Look for queries with high disk reads and see if they are indexed correctly.

As an example, consider a 80 million table where you want to get back 100o rows, with no indexes oracle will perform a full table scan to get those rows and blow that ratio out of the water. By indexing correctly you can reduce disk read and have a more appropriate ratio.

[hitenderk]

That is called intelligent answer.

[vijay8282]

Intelligent answer for a Stupid (vendechukp) Question....


Stupidity ---------> Intelligence

[pando]

I would not rely performance diagnostics on hit ratios, it was an old method for older Oracle versions but from Oracle 7 we get better performance pictures with system waits

[ligang]

Hi , Pando , Can you be more specific on system waits issue

[TimHall]

Hi.

The YAPP method ignores hit ratios in favour of reducing/eliminating wait stats, since these are the cause of performance problems. Hit ratios are merely the symptom. To get an idea of what you should really be doing check these out:

http://www.oracle-base.com/Links/yapp_anjo_kolk.pdf
http://www.oracle-base.com/Links/oraperf_bw.pdf
http://www.oracle-base.com/Articles/8i/Statspack8i.asp
http://www.oraperf.com/index.html

Cheers

[anandkl]

Sytem waits are events that are waiting on resource.For example if you query v$session_wait you could see under
event column values like 'db file sequential reads', 'db file scattered read' these are events where in 'db file sequential reads' is a full table scan and 'db file scattered read' is an index scan.These events will wait if both the index segment and data segment are placed in the same drive which will in turn reduce your hit ratio.You have similar events for redo log buffer cache,database buffer cache and library cache.

regards
anandkl