In this recipe, we will see how to tune the use of Buffer Cache to obtain the best performance.
The following steps will demonstrate how to tune the Buffer Cache:
Connect to the database as
SYSDBA
using SQL*Plus:CONNECT / AS SYSDBA
Show the size of the Buffer Cache:
SHOW PARAMETER CACHE_SIZE
Change the buffer cache size for 16K DB blocks to 24 MB:
ALTER SYSTEM SET DB_16K_CACHE_SIZE = 24M;
Query the statistics related to the Buffer Cache:
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%buffer%';
Verify if the parameter
DB_CACHE_
is enabled:SHOW PARAMETER DB_CACHE_ADVICE
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;
Evaluate the Buffer Cache Hit Ratio from statistics:
SELECT PR.VALUE AS "phy. reads", PRD.VALUE AS "phy. reads direct", PRDL.VALUE...