buffer gets vrs buffer cache

[mybear]

Hi,
Can any body answer these questons like they appear and if possible brief explanaton?

1. Are there any relationships between high buffer gets and low hit ratio in the data buffer cache?

2. Does high buffer gets indicates that most sql's are processed in memory instead of disk

3. Is it advisable to increase the shared_pool size if the hit ratio in the buffer cache is very low (10%) but have very high buffer gets?

[Raminder]

I dont know what you mean by 'Buffer gets' but there are two types of 'gets' in buffer cache, 'consistent gets' and 'db block gets'.

You calculate Buffer cache hit ratio as follows:

Select name, value from V$SYSSTAT where name in ('db block gets', 'consistent gets', 'physical reads');

Now Calculate hit ratio in % by:

(db block gets+ consistent gets - physical reads/db block gets + consistent gets)*100

If gets are higher, hit ratio cannot be low as explained above.

Does high buffer gets indicates that most sql's are processed in memory instead of disk

It means that the data was found in the memory and physical reads were avoided to fetch the data.

Is it advisable to increase the shared_pool size if the hit ratio in the buffer cache is very low (10%) but have very high buffer gets?

What has shared pool got to do with it? I think you are a little mixed up between shared pool and Data Buffer cache. Buffer cache is a place in memory where data is read into from the disk. Shared pool is entirely different area of SGA which comprises the library cache (SQL parsed plans etc), dictionary cache and other things are located.

[roobaron]

It sounds as though you have SQL doing large number of "buffer gets" which could include consistent_gets, db block gets and disk reads(get blocks/buffers from disk)

I would look at the SQL first before muckin around with the memory. It is amazing how suddenly the hit ratios improve when SQL is optimised.

To get what SQL is doing "potentially" bad stuff try searching v$sqlarea for buffer_gets > x and then again where disk_reads > x where x is a integer eg 10000.

The other alternative is to trace active sessions and TKPROF to get SQL executed by that session and associated statistical goodies (consistent gets, disk reads elapsed time etc)

Also it pays to find out what the database is waiting on as well. Once you have solved contention and waiting then have a look at ratios.

SQL tuning, waits and then ratios is a better order to tune a sluggish system.

Have Fun