Buffer Cache vs SGA

[Vic S. Kelan]

I have read a lot (and asked) about the buffer cache dbc_max_pct.

I am trying to understand how the buffer cache and oracle SGA (cache) coexists.

For example if a synchronous read request is made, will it go first to the buffer cache and next to the SGA to check if it will find this request in cache or the other way around?

Or does it go to both cache at the same time, if so how will it handle it when it finds the result in for example SGA, will it abort the check of the OS buffer cache or put the request on hold till it gets to know if the request can be found in the buffer cache….

[Hein van den Heuvel · Accepted solution]

Oracle will first look into its SGA.

If it can not find the target block/file (through a hash function), then it will issue a read to the system.

If that read is not direct_io (mount option) then the system will check the buffer cache, reading as many 8KB blocks as needed from storage if the blocks were not a cache.

Hein.

[Patrice Le Guyader]

Hi,

If you want some very good advice about the mount option and some parameter from oracle read this thread.

http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=1011162

You will have good advice about bypassing or not the buffer cache with oracle. And about db_bloc_buffer size and multiblock_read_count in oracle.

hope this helps
Kenavo
Pat

[Sacha Raybaud]

Hello,

I understood thanks to the note from John Joubert in the discussion refered by Patrice that both sga AND buffer cached are important to improve performance with high-demand databases.

Unfortunately, a system (our system) has limited memory resource and therefore, it's not possible to have at the same time significant memory cache and significant sga cache.

In our case, the system has to host several databases and I have have a question regarding the balancing of memory between the buffer cache and the sga:

- should I try to tune the sga of each database with a significant size and limit the size of the buffer cache?

- or should I assign a significant size to the buffer cache with minimum sga cache, knowing that each database will be able to use the buffer cache?

Best regards,

Sacha

[Eric Antunes]

Hi,

You should tune each specific database.

You must keep the buffer cache hit ration equal to or greater than 95%:

select round(((1-(sum(decode(name, 'physical reads', value,0)) /
(sum(decode(name,'db block gets', value,0))+
(sum(decode(name, 'consistent gets', value, 0))))))*100),2)
|| '%' "Buffer Cache Hit Ratio >= 95%"
from v$sysstat;

But don't oversize it. 95% is OK...

Best Regards,

Eric Antunes

[Hein van den Heuvel]

Sacha,

Welcome to the HP ITRC forum for HP-UX.

If you need more help with your question, kindly just start your very own topic.
It is considered 'bad form' to 'hi-jack' someone elses thread.

Yes, it is a related problem, but the original author may well still be 'following' the topic but not at all be interested in your specific varian.

Ciao,
Hein.

[Sacha Raybaud]

Hello,

Eric, thanks for your answer.

Hein and Vic, I apologize for the "hitjack" and I'm going to create a dedicated thread.

Best regards,

Sacha