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:
- Connect to the database as
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
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, ...