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
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.