Tuning the Buffer Cache

In this recipe, we will see how to tune the use of Buffer Cache to obtain the best performance.

How to do it...

The following steps will demonstrate how to tune the Buffer Cache:

1. Connect to the database as SYSDBA using SQL*Plus:

   CONNECT / AS SYSDBA
   

2. Show the size of the Buffer Cache:

   SHOW PARAMETER CACHE_SIZE
   

3. Change the buffer cache size for 16K DB blocks to 24 MB:

   ALTER SYSTEM SET DB_16K_CACHE_SIZE = 24M;
   

4. Query the statistics related to the Buffer Cache:

   SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%buffer%';
   

5. Verify if the parameter DB_CACHE_ is enabled:

   SHOW PARAMETER DB_CACHE_ADVICE
   

6. Estimate the performance with various sizes for the Buffer Cache and different database block sizes:

   SELECT BLOCK_SIZE, SIZE_FOR_ESTIMATE,
     BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READS
   FROM V$DB_CACHE_ADVICE
   ORDER BY BLOCK_SIZE, SIZE_FOR_ESTIMATE;
   

7. Evaluate the Buffer Cache Hit Ratio from statistics:

   SELECT
     PR.VALUE AS "phy. reads",
     PRD.VALUE AS "phy. reads direct",
     PRDL.VALUE...