Databases Reference
In-Depth Information
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 AS "phy. reads direct (lob)",
SLR.VALUE AS "session logical reads",
1 - (PR.VALUE - PRD.VALUE - PRDL.VALUE) / SLR.VALUE
AS "hit ratio"
FROM V$SYSSTAT PR, V$SYSSTAT PRD,
V$SYSSTAT PRDL, V$SYSSTAT SLR
