Buffer pool estimate

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

Quote:

---

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

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

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.

Quote:

---

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?