Transcript Introduction to Web Graphics Understanding the Basics of

Oracle9i
Performance Tuning
Chapter 2
Tuning the Buffer Cache
Chapter Objectives
• Understand how the buffer cache works
• Learn how to configure the buffer cache
• Learn how to configure a database with one or
multiple database block sizes
• Dynamically allocate SGA memory
• Configure a buffer cache with multiple buffer
pools
Chapter 2: Tuning the Buffer Cache
2
Chapter Objectives (continued)
• Understand how automatic table caching works
• Learn how to use the new Buffer Cache Size
Advice
• Diagnose buffer cache configuration
• Look inside the buffer cache
Chapter 2: Tuning the Buffer Cache
3
Oracle Architecture
Chapter 2: Tuning the Buffer Cache
4
Buffer Cache Overview
• The SGA is the memory structure in which Oracle caches data
that is retrieved for:
•
•
•
•
•
•
Access
Updates
Submitted SQL statements
Executed PL/SQL blocks
Data dictionary definitions
Other cache mechanisms
• The major purpose of the SGA is to enhance data retrieval by
placing the most frequently used data in memory rather than
retrieving it from disk
Chapter 2: Tuning the Buffer Cache
5
Buffer Cache Overview (continued)
• The SGA consists of the following memory
structures:
•
•
•
•
•
Buffer cache
Redo log buffers
Shared pool memory
Large pool
Java pool
Chapter 2: Tuning the Buffer Cache
6
Buffer Cache Overview (continued)
• The buffer cache stores the most frequently
accessed Oracle data blocks to reduce disk I/O
• If data is cached, the Oracle server sends back the data
requested without returning to the data files (this is known as
a cache hit)
• If data is not cached, the Oracle server fetches the data from
data files in the database based on the execution plan (this is
known as a cache miss)
Chapter 2: Tuning the Buffer Cache
7
Buffer Cache Overview (continued)
Chapter 2: Tuning the Buffer Cache
8
Buffer Cache Overview (continued)
• The types of buffers are:
• Default
• Keep
• Recycle
Chapter 2: Tuning the Buffer Cache
9
Configuring the Buffer Cache
• Initial configuration can be based on existing databases and types
of applications
• The DB_BLOCK_BUFFERS parameter is deprecated in
Oracle9i but is still supported for backward compatibility
• DB_BLOCK_BUFFERS cannot be allocated dynamically
• Use DB_CACHE_SIZE to size the buffer
• DB_CACHE_SIZE can be changed without shutting down the
database
• The Buffer Cache Size Advice feature is enabled only if the
DB_CACHE_SIZE parameter is used
• The SGA_MAX_SIZE parameter sets the maximum size the
SGA can grow to
Chapter 2: Tuning the Buffer Cache
10
Database with Multiple Block Sizes
• Buffers support tablespaces with
different block sizes than the one
set with the DB_BLOCK_SIZE
parameter
SQL> CREATE TABLESPACE TEST_DATA_16K
2
LOGGING
3
DATAFILE
4
'C:\ORACLE\ORADATA\SAM\TEST_DATA_16K.ORA'
5
SIZE 100M
6
BLOCKSIZE 16384
7
EXTENT MANAGEMENT LOCAL
8 /
•
Parameters:
•
•
•
•
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
•
DB_32K_CACHE_SIZE
SQL> SELECT TABLESPACE_NAME, BLOCK_SIZE
2
FROM DBA_TABLESPACES;
Chapter 2: Tuning the Buffer Cache
11
Dynamic SGA Allocation
• Use SGA_MAX_SIZE if you need to increase the
SGA allocated memory
• Use V$SGA_DYNAMIC_COMPONENTS to view
the size of the major memory structures in the SGA
• When you are adjusting the size of any memory
structure in the SGA, it is decremented or
incremented in granules
• In Oracle9i, a granule is a memory unit
Chapter 2: Tuning the Buffer Cache
12
Dynamic SGA Allocation (continued)
Chapter 2: Tuning the Buffer Cache
13
Dynamic SGA Allocation (continued)
Chapter 2: Tuning the Buffer Cache
14
Configuring Multiple Buffer Pools
• DB_KEEP_CACHE_SIZE configures memory allocation for the KEEP pool
in the buffer cache
• DB_RECYCLE_CACHE_SIZE configures memory allocation for the
RECYCLE pool in the buffer cache
• Total size of memory for buffers is the sum of the following parameter values:
•
•
•
•
•
•
•
•
DB_CACHE_SIZE
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
SQL> SELECT SUM(VALUE)/(1024*1024)
2
FROM V$PARAMETER
3
WHERE NAME IN('db_cache_size','db_keep_cache_size',
4
'db_recycle_cache_size', 'db_2k_cache_size',
5
'db_4k_cache_size', 'db_8k_cache_size',
6
'db_16k_cahce_size', 'db_32_cache_size')
7 /
Chapter 2: Tuning the Buffer Cache
15
Configuring Multiple Buffer Pools
(continued)
• Cache a table in a specific buffer:
SQL> ALTER TABLE DEPARTMENTS
2
STORAGE ( BUFFER_POOL KEEP)
3 /
Method 1
SQL> ALTER TABLE DEPARTMENTS CACHE;
SQL> SELECT TABLE_NAME, CACHE, BUFFER_POOL
2
FROM USER_TABLES
3
ORDER BY TABLE_NAME
4 /
Method 2
SQL> SELECT /*+ CACHE(CATEGORIES) */ *
FROM CATEGORIES
/
Chapter 2: Tuning the Buffer Cache
16
Automatic Table Caching
• Oracle9i Release 2 has introduced the automatic
caching of small tables based on the size of the table
• Small table: a table is considered small if it is less than 20
blocks or 2% of the total cached blocks
• Medium table: a table is considered medium if it is more than
20 blocks and less than 10% of the total cached blocks
• Automatic table caching is based on these criteria,
caching statistics, and table scan frequency
• These criteria do not apply to any table that has the
CACHE option enabled
Chapter 2: Tuning the Buffer Cache
17
Buffer Cache Advice
Chapter 2: Tuning the Buffer Cache
18
Buffer Cache Advice (continued)
Chapter 2: Tuning the Buffer Cache
19
Buffer Cache Advice (continued)
SQL> SELECT
FROM
WHERE
AND
AND
DECODE(SIZE_FACTOR,
1, '==>', null) " ",
SIZE_FOR_ESTIMATE CSIZE ,
TRUNC(SIZE_FACTOR*100)||'%' PERCENT,
BUFFERS_FOR_ESTIMATE BUFFERS_EST,
ESTD_PHYSICAL_READ_FACTOR E_PHY_READ_FACTOR,
ESTD_PHYSICAL_READS E_PHY_READS
V$DB_CACHE_ADVICE
NAME = 'DEFAULT'
ADVICE_STATUS = 'ON'
BLOCK_SIZE = (SELECT VALUE
FROM V$PARAMETER
WHERE NAME = 'db_block_size');
CACHE SIZE PERCENT
BUFFERS_EST E_PHY_READ_FACTOR
E_PHY_READS
--- ---------- ------- -------------- ----------------- --------------4
33%
500
2.1007
6084420
8
66%
1000
1.3393
3879163
==>
12
100%
1500
1
2896319
16
133%
2000
0.8335
2414111
20
166%
2500
0.7709
2232899
24
200%
3000
0.7646
2214471
28
233%
3500
0.755
2186828
32
266%
4000
0.7444
2156114
. . .
Chapter 2: Tuning the Buffer Cache
20
Buffer Cache Advice (continued)
Chapter 2: Tuning the Buffer Cache
21
Buffer Cache Diagnosis
• Use V$SYSSTAT and V$SESSTAT
• Buffer cache hit ratio is a percentage of physical reads
over logical reads
• Hit ratio = 1 - (physical reads/(block gets + consistent gets))/100
• Physical reads is the total number of data blocks accessed from disk
• Block gets is the total number of buffers that are obtained for update
(means the data block was read for update)
• Consistent gets is the total number of buffers that are obtained in
consistent read (means that the data block that was accessed used the
System Change Number (SCN) to determine that the data block being read
did not change since the query was submitted)
SELECT ROUND( (1 - (PHY.VALUE/(CUR.VALUE + CON.VALUE)))*100, 1)||'%' ratio
2
FROM V$SYSSTAT PHY, V$SYSSTAT CUR, V$SYSSTAT CON
3
WHERE PHY.NAME = 'physical reads'
4
AND CUR.NAME = 'db block gets'
5
AND CON.NAME = 'consistent gets';
Chapter 2: Tuning the Buffer Cache
22
Buffer Cache Diagnosis (continued)
• Buffer cache hit ratio threshold
Chapter 2: Tuning the Buffer Cache
23
Buffer Cache Diagnosis (continued)
• Hit ratio per session
SQL> SELECT PHY.SID,
2
S.USERNAME,
3
1 - (PHY.VALUE)/(CUR.VALUE + CON.VALUE) BUFFER_HITRATIO
4
FROM V$SESSTAT PHY, V$SESSTAT CUR, V$SESSTAT CON,
5
V$STATNAME S1, V$STATNAME S2, V$STATNAME S3,
6
V$SESSION S
7
WHERE S1.NAME = 'physical reads'
8
AND S2.NAME = 'db block gets'
9
AND S3.NAME = 'consistent gets'
10
AND PHY.STATISTIC# = S1.STATISTIC#
11
AND CUR.STATISTIC# = S2.STATISTIC#
12
AND CON.STATISTIC# = S3.STATISTIC#
13
AND CUR.VALUE <> 0
14
AND CON.VALUE <> 0
15
AND PHY.SID = CUR.SID
16
AND PHY.SID = CON.SID
17
AND PHY.SID = S.SID
18 /
Chapter 2: Tuning the Buffer Cache
24
Buffer Cache Diagnosis (continued)
• Buffer pool statistics are shown using the performance dynamic view
V$BUFFER_POOL_STATISTICS
SQL> COLUMN RATIO HEADING "Buffer Cache Hitratio" FORMAT A30 SQL> COLUMN NAME
HEADING 'Buffer Pool' FORMAT A15
SQL> COLUMN BLOCK_SIZE HEADING 'Block
SQL> SELECT Size' SELECT NAME,
2
BLOCK_SIZE,
3
ROUND( (1 - (PHYSICAL_READS/
4
(DB_BLOCK_GETS + CONSISTENT_GETS)))*100) || '%' ratio
5
FROM V$BUFFER_POOL_STATISTICS
6 /
• Block size buffer hit ratio
SQL> SELECT BLOCK_SIZE,
2
ROUND( (1 - AVG((PHYSICAL_READS/
3
(DB_BLOCK_GETS + CONSISTENT_GETS))))*100) || '%' ratio
4
FROM V$BUFFER_POOL_STATISTICS
5
GROUP BY BLOCK_SIZE
6 /
Chapter 2: Tuning the Buffer Cache
25
Inside the Buffer Cache
• Use the V$BH view to:
• Query the view for a list of data objects residing in the buffers
and the number of data blocks in use
• See how many blocks are modified (dirty) and how many are
free or read
• Inspect blocks that have been rewritten and re-read in an
Oracle Real Application Cluster configuration
• Find out more about which data files or tablespaces being
accessed most often and how many buffers they use
Chapter 2: Tuning the Buffer Cache
26
Inside the Buffer Cache (continued)
Chapter 2: Tuning the Buffer Cache
27
Summary
• The buffer cache is a memory structure of the SGA
• The buffer cache facilitates faster access to data, because when
data is retrieved from a disk, the cost to performance is high
• The Least Recent Used algorithm ages out the least retrieved and
changed blocks of data from the buffer
• The buffer cache consists of three major internal structures: the
Default buffer pool, Keep buffer pool, and Recycle buffer pool
• The DB_CACHE_SIZE parameter configures the size of the
buffer cache
Chapter 2: Tuning the Buffer Cache
28