Buffer pool estimate

[newcomer]

Hi

According to Oracle doc if I query

select SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, STD_PHYSICAL_READS
from v$db_cache_advice
where name='DEFAULT'
and block_size=(select values v$parameter where name='db_block_size')
and advice_status='ON';

it will allow me to predict buffer pool for various cache size.

But block_size and advice_status is not a valid column in v$db_cache_advice view. What to do????????

2ndly If I query only

select SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, STD_PHYSICAL_READS
from v$db_cache_advice
where name='DEFAULT';

the I am always getting value "0" for STD_PHYSICAL_READS
columns. How to get non zero , actual , values.

Can anybody help me.


Thanks

[julian]

Originally posted by newcomer

But block_size and advice_status is not a valid column in v$db_cache_advice view. What to do????????

Yes, they are:

SQL> desc v$db_cache_advice
Name
------------------------------------
ID
NAME
BLOCK_SIZE
ADVICE_STATUS
SIZE_FOR_ESTIMATE
BUFFERS_FOR_ESTIMATE
ESTD_PHYSICAL_READ_FACTOR
ESTD_PHYSICAL_READS

[newcomer]

Hi Julian,

u r right. It is there in Oracle 9. But why I am getting always 0 (zero) values for estd. phy reads column?

Can u pls. tell me?

Thanks

[Shestakov]

Did u setup DB_CACHE_ADVICE = ON in init.ora file.
Without this parameter Cache Advice process doesn't work.
Default value for DB_CACHE_ADVICE = OFF.

[julian]

Originally posted by newcomer
Hi Julian,

u r right. It is there in Oracle 9. But why I am getting always 0 (zero) values for estd. phy reads column?

Can u pls. tell me?

Thanks

Well, as Shestakov said, is DB_CACHE_ADVICE set?