does anyone know in that kind of measurement the "Estimated Phys Reads (thousands)" from buffer pool advisory is ?
I mean in seconds ? in hours ? addicted to the time period the report was written ?
Unfortunately I cannot see the picture, but "Estimated Physical Reads" are exactly what they are: the number of individual read operations for the given size of the buffer cache. An advisory allows you to see how the number of physical disks reads can be reduced (memory reads increased) by changing the size of a buffer cache.
I understand that what you are talking about. The problem is that in the advisory the number of physical reads
are nowhere else represented. So the report shows that we have ~80000 physical block reads about an hour and this number 30k is not represented in the advisory there are only numbers around 22k block.
So the question again what represent this number 22k physical reads :-/
If I understand you correctly you'd like to have timing representation of the number of physical blocks read. I'm honestly not sure what value does it give you with regard to buffer cache advisory.
Let's start with the fact that the advisory always gives you the data based on the current size of your buffer cache with 10% increments starting with 10% and ending with 200% of the current cache size. That explains why the highest number of physical reads you see is ~22K that corresponds to the smallest cache size (10%).
Also I prefer to look directly at V$DB_CACHE_ADVICE. That's my personal preference, but it works for me. I'm not sure what to make out of timing column in awr report. Hope someone may enlighten me.
To be honest the absolute values presented by the advisory, be it number of reads or timing, are not that important comparing to the relative values because the goal of the advisory is to give an idea when you start receiving diminishing returns in response to the buffer cache increase. That's why the results are often presented graphically to see when you still have the steep slope.
I'm sure you're aware that many factors such as full table scans, database restarts etc can severely confuse the advisory on top of the fact that it represents estimated results.
Finally to get a rough estimation of timing you can always use workload statistics that give you average timing for read operations. Just query sys.aux_stat$ table.
That number is what it estimates the number of physical reads to be if you shrink it down to 256M. If you use 2,560M, it estimates you will only have 6,635K reads. If you put it up to 5,120M, you will only have 4,958K reads. So doubling it from 2.5G to 5G will reduce your reads by about what percent? 25% db time. Is that worth it? If something else is a bottleneck, you might not notice that at all. If users could be using that extra 2.5G, it could be catastrophic.
I've noticed some situations where you keep increasing the buffers, and it keeps telling you to increase it more. It depends.
What I now think to understand is that the "Estimated Phys Reads (thousands) no represents the
current workload of the system it only gives clue that there will be a change around 3 times more blocks was red.
But if it is like that why these numbers are changing from one to another report....
So again another image.... Maybe my problem is that I try to set the current workload (phys block reads) in comparison with the "Estimated Phy Reads from the Advisory....
Our AWR reports are generated every hour.
Sorry for my understanding problem... I also will try to check if the views gave other information than the ARW report.
The figure in the Buffer Pool Advisory is physical blocks read (not read requests) into the cache since instance startup. If you have multiple buffer pools (nKB, keep, recycle) then summing across all the rows where the "size factor" is 1.0 should give you the value of the v$sysstat statistics "physical reads cache" at about the time of the snapshot.
Generally speaking, unless your application is fairly uniform in the type of activity (i.e. no massive swings between OLTP and Batch style), it is hard to read anything into this report because it smooths out any spikes in activity. Possibly if you see that an increase in memory that you can comfortably afford promises a sharp step in reduced I/O (and time) then you might consider testing the advice.