In this post, we continue our series on performance tuning. We present the concepts of logical reads and physical reads, and how to use them to compute the buffer cache hit ratio. The buffer cache hit ratio is a performance indicator that may be used together with other indicators to detect bootlenecks.
Logical and Physical Reads
Upon receiving a SQL command, the DBMS performs a series of steps, including a syntactic analysis and the assembly of an execution plan (see our post on execution plans and rewrite commands). As as part of the execution plans, there are forecasts on the data that should be retrieved from tables or indexes. Such page/block data reading operations are called logical reads.
It is said that the DBMS performs physical reads when it performs I/O operations to retrieve the data represented in the logical reads. But before accessing the disk to perform read operations, the DBMS checks whether the data it is looking for is in buffer cache. If so, the DBMS use the data from main memory and no physical reads occurs. If the DBMS does not find the required data in main memory, the physical read corresponding to the logical read is performed.
The buffer cache
However, many of these logical readings do not become physical readings. This is because DBMSs have specialized memory management mechanisms that keep the “most usefull” data available in main memory. The main memory area managed by the DBMS and used to store user data is usually called buffer cache.
Many DBMS use LRU (least recently used) to choose which page data should be replaced from the buffer cache. Some DBMS use MRU (mostly reacently used) to replace data pages/blocks resulting from full table scans.
But maintaining usefull data in main memory is very important, as it reduces the amount of disk access (I / O) operations and improves the system’s performance.
Buffer Cache Hit Ratio
The buffer cache hit ratio is a metric that defines the percentage of pages that the DBMS used in main memory among all those that were required to execute SQL commands. It can be computed as in the following:
You may query the values of logical reads and physical reads using the dynamic management views of your DBMS instance. For instance, let’s see how to compute buffer cache hit ratio in Oracle and in Microsoft SQL Server.
Hit Ratio in Oracle
In Oracle, you should query V$SYSSTAT for the following statistics: ‘db block gets from cache’, ‘consistent gets from cache’ and ‘physical reads cache’, as in the following.
The sum of ‘db block gets from cache’, and ‘consistent gets from cache’ represent the total logical reads. The buffer cache hit ratio may be computed as follows:
Hit Ratio in Microsoft SQL Server
You can query for the buffer cache hit ratio in SQL Server using the following command:
What is the right value for the buffer cache hit ratio?
If your instance has a value higher than 98%, then you should not have memory-size related problems. In fact, a value close to 100% indicates that the DBMS can access all the data required to meet SQL commands without performing disk I/O operations. But of the hit ratio is bellow 95%, then you may have bootlenecks and performance problems.
In this case, you should review your SQL commands and the physical design (e.g. creating an index) of your database to reduce I/O. You may also have to increase the buffer cache size, which may mean you have to increase the size of main memory available to your DBMS instance or resize the DBMS memory pools. But be sure to check other performance indicators before reconfiguring your system. You should not change the system’s configurations only by checking the buffer cache hit ratio.