In this section we will learn how to read and analyze the buffer cache statistics for our database using AWR report.
Before we get into the detail of the statistics shown above let us understand what buffer cache and how is it set :-
Oracle database buffer cache is a part of a memory structure in an Oracle Instance. Database stores the most recent used blocks of the data.
The buffer cache contains modified as well as unmodified blocks. The most recently used data is kept in memory which uses less disk I/O and also improves the performance.
A block is placed in the buffer cache when it is read by oracle. This improves the performance by using less disk I/O since the block is already placed in the buffer during previous session.
Reading a block from the buffer cache is less costly (in terms of time) than reading it from the disk.
Buffer cache should not be specified with the parameter db_block_buffers and parameter db_cache_advice should be set to ‘on’ which is default value.
SQL> show parameter db_cach
NAME TYPE VALUE
———— ——————————
db_cache_advice string ON
db_cache_size big integer 0
If db_cache_size is defined it takes it as a default buffer cache value however if SGA_TARGET is set to > 0 and db_cache_size is not defined, then the size of default buffer cache will be allocated by Oracle automatically without low limit. Parameter db_block_size determines the size of the buffers in the default buffer cache.
Other than default buffer cache it also has other subpools
‘D’ – Default buffer cache (always present),
‘K’ – Keep buffer cache (if db_keep_cache_size parameter is defined),
‘R’ – Recycle buffer cache (if db_recycle_cache_size parameter is defined),
<N> – Caches for non-default block sizes (if defined with parameters db_<N>k_cache_size)
If they are not defined in the parameter file they will not appear in the AWR report which can been on the left most column of the above diagram.
This section of the advisory is read row by row and we start from the row where size factor is “1.0“. This size factor shows approximate actual size of the default buffer cache when AWR was executed.
If you check the column “Size for Est(M)” for Size Factor “1.0” it is 596M which means 596MB of memory has been allocated by Oracle either automatically if sga_target > 0 OR manually in the parameter file.
Simultaneously we need to check the column “Estimated Physical Reads” which has a value of 37,612,741.
Lets look at the other value in the table which has size factor other than “1.0”
You will notice that increasing buffer cache size will reduce the number of “Estimated Physical Reads”.
Size Factor “1.0” also has Estimated Phys Read Fator “1.0” with “Estimated Physical Reads” as37,612,741.
Note how ‘Size Factor’ increases and ‘Estimated Phys Read Factor’ decreases.
Also any reduction in the value of buffer cache size would increase the number of “Estimated Physical Reads”.
Looking at the example/diagram above if we increase the buffer cache it will not give us a substantial gain due to the low decrease in the value of “Estimated Physical Reads”.
Hence the above advisor or the size of the buffer cache is in the acceptable range and does not require any changes.
There might be examples where you will see a drastic change in the reduction of “Estimated Physical Reads” if you increase the buffer cache size.
Other example can be decreasing the size of buffer cache if you see that “Estimated Physical Reads” does not drastically increase if you check the Sizing Factors before “1.0“.
In cases when sga_target > 0 and db_cache_size is determined automatically, similar data would mean that allocated by Oracle size of the buffer cache is fairly balanced, i.e. not too small to have excessive number of physical reads and not too big to have unnecessary overhead while serving a bigger buffer cache.
Reference : How to Read Buffer Cache Advisory Section in AWR and Statspack Reports. [ID 754639.1]
