v$db_cache_advice

Hello,

I could not able to understand how to the read the results from the view v$db_cache_advice.
Here is the output and please let me know how to find the proper buffer_cache value from the following output?

I have modified the column headings accordingly:v$db_cache_advice

SizeEstimate SizeFactor BuffersEstimate Estd_PhysicalRead_factor Estd_PhysicalReads

------------ ------------- ---------------- ------------------------ ------------------
48 .1 5955 1.3764 32804
96 .2 11910 1.2167 28999
144 .3 17865 1.1849 28240
192 .4 23820 1.0966 26137
240 .5 29775 1 23834
288 .6 35730 1 23834
336 .7 41685 1 23834
384 .8 47640 1 23834
432 .9 53595 1 23834
480 1.0 59550 1 23834
528 1.1 65505 1 23834
576 1.2 71460 1 23834
624 1.3 77415 1 23834
672 1.4 83370 1 23834
720 1.5 89325 1 23834
768 1.6 95280 1 23834
816 1.7 101235 1 23834
864 1.8 107190 1 23834
912 1.9 113145 1 23834
960 2.0 119100 1 23834

Thanks,
Nikee

From the SizeFactor column you know what is the size of your buffer cache (look for the row that has 1.0). Take the size estimate for that row. That is the size of your buffer cache in MB (480 in this case).

From the Estd_PhysicalReads column you can see that, according to the algorithm that populates this view, if you reduce your buffer cache to 240 MB, you would have the same number of physical read. Basically it says that you can downsize the buffer cache from 480MB to 240MB without increasing the number of physical reads required.

Please bear in mind that this are estimates that are affected by:

- the algorithm Oracle uses to populate the table
- the workload the server had in the time window in which data for estimates has been collected: this is from the time init parameter set db_cache_advice has been turned on to the time you have run the query.

Hope it helps,

Corrado