Determining the Buffer Cache Size in bytes 3

[eao]

Hi,

My task is to determine, in bytes, the size of the Buffer Cache. My proposed solution is to multiply the values returned by the following two queries:

select value from v$parameter where name = 'db_block_size';
select value from v$parameter where name = 'db_block_buffers';

Am I on the right track? Thank you.

Eric

 

[SantaMufasa]

Yes, Eric, you are correct. You can also get Oracle to do your math for you if you say:

col x heading "Buffer|Cache|Size" format 999,999,999,999
Select db_size*db_blocks x from
(select value db_size from v$parameter where name = 'db_block_size'),
(select value db_blocks from v$parameter where name = 'db_block_buffers');

Cheers,

Dave

 

[sem]

As this query is addressed to 9i forum, I should remind about db_cache_size parameter. Yes, your query is correct, but do you really need that value? It may return 0, but it doesn't mean no cache in use.

Regards, Dima

 

[eao]

Thanks for the help, Dave and Dima.

Dima, why might this query return 0? Aren't these values set in init.ora; to return 0 one of the two values would need to be 0 (or both). If db_block_size was 0, I have a feeling Oracle wouldn't run. If db_block_buffers was 0, would that be like saying "don't allocate any buffer cache"?

 

[sem]

Eao, be more careful: I talked about db_cache_size, not db_block_size. You may successfully work with 9i database even if db_block_buffers=0.

Regards, Dima

 

[sem]

Dave, of course in this specific case performance is not an issue, but to demostrate how to avoid redundant table scans when converting rows to columns, I'd like to provide another variant for this query:

Select max(decode(name,'db_block_size',value, 0))* max(decode(name,'db_block_buffers',value, 0))
from v$parameter
where name in( 'db_block_size','db_block_buffers')

It accesses v$parameter only once (though performs some extra simple sorting). When self-join on bigger table is needed, the gain may be quite significant.

Regards, Dima