buffle cache and SGA

[karfai]

Hi

I my in doubt whether the buffle cache will cache any oracle data ?
Ex: when process does a request for oracle datafiles initially, with this be read to the buffle cache or the read straight goes to the SGA/memory?

regards

[malay boy · Accepted solution]

Hi,
Database Buffer cache is part of the SGA.And it's a part of Oracle memory structure.

Well when a read is request,the oracle will check whether the particular block already in the buffer cache (memory).If not exist , then it will retrieve from datafile and put in the buffer cache.

To verify, do login as sysdba and do :

SQL > SHOW SGA;

This will show that buffer cache are part of the SGA.

Hope this will help.

regards
mB

[Sridhar Bhaskarla]

Hi,

Yes it does and it is referred to as 'double buffering'. Buffering done by oracle through it's SGA and the buffering done by OS with the buffer cache.

So, people that are concerned about double buffering, go for raw devices (as filesystems work with buffer cache unless OnlineJFS options are used), Veritas Quick IO/veritas db edition etc.,

I have systems with databases on both raw devices and on filesystems. I have no complaints on the performance with the database on filesystems.

-Sri

[Sanjay Kumar Suri]

The Buffer Cache is the portion of the SGA that caches copies of the data blocks of the segments the application users have been most recently accessing in the database.

sks

[karfai]

Hi

I have read other forum saying that system does not need BIG buffle cache (dbc_max_pct 5-10% ).
If the datafiles are being cache, the buffle cache of 5 to 10%, logically are not sufficient to contain the datafiles being read. ( ex: 1 datafiles of 2 gb ).

Currently, my SGA are set to 6 GB and user memory area used up the same amount.

Please correct if I am wrong.

regards

[Sanjay Kumar Suri]

Run the following SQL

select 1 - ((physical.value - direct.value - lobs.value) / logical.value) "Buffer Cache Hit Ratio" from v$sysstat physical, v$sysstat direct, v$sysstat lobs, v$sysstat logical
where physical.name = 'physical reads'
and direct.name = 'physical reads direct'
and lobs.name = 'physical reads direct (lob)'
and logical.name = 'session logical reads';


According to Oracle, a well tuned PLTP system should have Database Buffer Cache of 90% or higher.

sks

[karfai]

HI Kumar

How to interpret the output from command ?
Buffer Cache Hit Ratio
----------------------
1015.36052
1014.84095
1015.34176
757.550571
-120209.96
1010.80424
831.588091
1012.89233
1015.36052
1015.36052
1015.36052

Buffer Cache Hit Ratio
----------------------
1013.95595
1015.29119
996.658238
1008.33605
840.769984
1008.0677
1007.98206
1003.1876
1015.36052
1015.36052
1010.90457

Buffer Cache Hit Ratio
----------------------
-102079.27
848.260196
1015.12969
1015.36052
1015.36052
1015.22268
-3328985.8
-105417.84
-12345.343
1015.0571
878.314029

Buffer Cache Hit Ratio
----------------------
769.846656
-5674.5
1013.54894
1015.21615
1015.35237
1015.36052
1015.36052
1015.15334
1015.36052
1015.36052
-6530.3825

Buffer Cache Hit Ratio
----------------------
-1224.7227
1015.36052
1015.36052
1014.95514
1015.36052
1015.36052
1015.35563
1015.35563
1015.35563
1015.36052
1015.36052

Buffer Cache Hit Ratio
----------------------
1015.36052
1015.36052
1015.36052
1014.75285
1014.75285
1015.36052
1015.36052
-8930.9706
-136705.39
1015.35563
1015.36052

Buffer Cache Hit Ratio
----------------------
981.6354
978.460033
1015.29201
1015.34747
1011.82953
1010.18679
-22.06199
1013.01223
1015.0832
-266.51223
-4012768

regards

[Sanjay Kumar Suri]

How come you getting so mouch of output? You should have got one number as below:

SQL> select 1 - ((physical.value - direct.value - lobs.value) / logical.value
Buffer Cache Hit Ratio" from v$sysstat physical, v$sysstat direct, v$sysstat
s, v$sysstat logical
2 where physical.name = 'physical reads'
3 and direct.name = 'physical reads direct'
4 and lobs.name = 'physical reads direct (lob)'
5 and logical.name = 'session logical reads';

Buffer Cache Hit Ratio
----------------------
.859691723

[karfai]

Thanks Guys

[Nicolas Dumeige]

Hello,

Be aware that Buffer Cache Hit Ratio is NOT to be considered a reliable performance measure.

See Optimizing Oracle Performance (O'reilly) from a full explanation.
[ Thanks Hein for you tips :) ]

Cheers

Nicolas