Low or Poor Buffer Cache Hit Ratio and Full Table Scans

 Normally DBAs do not much feel bothered by poor buffer cache hit ratio especially in data warehouse environments where full table scans may be quire frequent and acceptable. However, if you have OLTP environment, I still believe that buffer cache hit ratio should be taken care of and DBAs should try to keep it as high as they can. In OLTP environments, full table scans would cause cache hit ratio to be low because frequent full table scans of different tables would cause entire tables to be read in to buffer cache even if only a few block are needed for processing.

db file scattered read
In case of full table scans, “db file scattered read” wait even can be seen in AWR report and that indicates full table scans happening in the database. If this wait event is one of the top wait events, you should certainly look into this in case of OLTP environment because full table scans flood buffer cache hit ratio.

In the following I will present a simple example about how full table scans would reduce the buffer cache hit ratio that may contribute to poor performance of the database. Before I executed this example, I switched off “direct path read” feature that is explained here, because “direct path read” by passes buffer cache in the event of full table scan.

After flushing buffer cache, I queried a table (TEST) a couple of times that cached the table in the buffer cache. After that, I created an AWR snapshot, and then once again queried the table. In the end once again created an AWR snapshot so that I could generate AWR for my testing time window.

SQL> alter system flush buffer_pool all;   System altered.   SQL> select count(*) from test;     COUNT(*) ----------    3163136   SQL> exec dbms_workload_repository.create_snapshot;   PL/SQL procedure successfully completed.   SQL> select count(*) from test;     COUNT(*) ----------    3163136   SQL> select count(*) from test;     COUNT(*) ----------    3163136   SQL> select count(*) from test;     COUNT(*) ----------    3163136   SQL> select count(*) from test;     COUNT(*) ----------    3163136   SQL> exec dbms_workload_repository.create_snapshot;   PL/SQL procedure successfully completed.

AWR report generated based on above 2 snapshots shows that cache hit ratio is 99.51 % because table “TEST” was accessed from the buffer cache.

Next, I queried a new table that involved full table scan of that table. After that I created a new AWR snapshot and in the end generate AWR report based on this snapshot and previous snapshot.

SQL> select count(*) from test2;     COUNT(*) ----------    3163136   SQL> exec dbms_workload_repository.create_snapshot;   PL/SQL procedure successfully completed.

 In the following AWR image you see that after querying a different table that was not in buffer cache, cache hit ratio reduced drastically. In case of a huge database buffer cache, you may not see a huge difference in buffer cache hit ratios, but this example sufficiently explains that how full table scans can impact buffer cache hit ratios and increased physical reads