Transcript Document

Tuning the Buffer Cache
Copyright © 2006, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Describe the buffer cache architecture
• Size the buffer cache
• Resolve common performance issues related to
the buffer cache
• Use common diagnostic indicators to suggest a
possible solution
9-2
Copyright © 2006, Oracle. All rights reserved.
Oracle Database Architecture
Instance
SGA
nK
Default Keep Recycle
Redo
Sort Global Flash
Fixed Shared Large Java Streams buffer buffer buffer buffer
ASH
log
extent context back
size pool
pool
pool pool
cache cache cache caches buffer buffer pool
pool
buffer
PMON
SMON
RECO
MMON
MMAN
MMNL
QMNC
LGWR
CTWR
ARCn
S000
RVWR
D000
FMON
Qnnn
Password
file
Control
files
Redo log
files
Temp
SYSTEM
SYSAUX
Data file
Change
tracking
file
Undo
9-3
CKPT
CJQ0
PSP0
Spfile
DBWn
Copyright © 2006, Oracle. All rights reserved.
Flashback
logs
Archive
log files
Buffer Cache: Highlights
•
Scalable architecture:
– Multiversion concurrency control
– Proprietary LRU-based replacement policy
– Cache fusion
•
•
•
9-4
Incremental checkpointing mechanism
Advisors
Private pool for I/O intensive operations
Copyright © 2006, Oracle. All rights reserved.
Database Buffers
SGA
DB buffer cache
Server
DBWn
9-5
Copyright © 2006, Oracle. All rights reserved.
Buffer Hash Table For Lookups
Cache
buffer
chains
latches
Hash
buckets
table
Buffer
hash
chains
A
B
C
C
F
E
E
C
CR clones
C
F
Buffer headers
B
A
Buffer
cache
9-6
Copyright © 2006, Oracle. All rights reserved.
Working Sets
Auto-tuned
Recycle
Keep
Default
nK Buffer
cache
F
B
E
Working set 2
Working set 1
E
D
A
C
F
F
C
A
D
D
…
C
B
A
LRU
CKPTs
LRU
CKPTs
Buffer
cache
9-7
Copyright © 2006, Oracle. All rights reserved.
Tuning Goals and Techniques
•
•
It is recommended to use Automatic Shared
Memory Management (ASMM).
Tuning goals:
– Servers find data in memory
– No waits on the buffer cache
•
Diagnostic measures:
– Wait events
– The V$DB_CACHE_ADVICE view and cache hit ratio
•
Tuning techniques:
– Review ADDM recommendations if ASMM is used.
– Reduce the number of blocks required by SQL
statements.
– Increase buffer cache size, use multiple buffer pools.
9-9
Copyright © 2006, Oracle. All rights reserved.
Symptoms
The symptoms that indicate a buffer cache problem:
• Latch:cache buffer chains
• Latch:cache buffer LRU chains
• Buffer busy waits
• Read waits
• Free buffer waits
• Cache hit ratio
9-11
Copyright © 2006, Oracle. All rights reserved.
Cache Buffer Chains Latch Contention
Contention for this latch indicates:
• Multiple processes attempting to access the same
“hot” block
• Excessive block replacement
9-12
Copyright © 2006, Oracle. All rights reserved.
Finding Hot Segments
•
Characteristics of cache buffer chains latch
contention:
– Many accesses to one or more block under the
same latch
– Worse with larger block sizes
•
To find hot segments:
SQL> SELECT * FROM ( SELECT owner, object_name,
2
object_type, statistic_name, sum(value)
3
FROM V$SEGMENT_STATISTICS
4
GROUP BY owner, object_name, object_type,
5
statistic_name
6
ORDER BY SUM(value) DESC)
7 WHERE ROWNUM < 10;
9-13
Copyright © 2006, Oracle. All rights reserved.
Buffer Busy Waits
•
•
Application-level contention for buffers in the
buffer cache
Identify buffer busy waits contention:
SELECT class, count
FROM V$WAITSTAT
WHERE count>0
ORDER BY count DESC;
SELECT object_name, value
FROM V$SEGMENT_STATISTICS
WHERE statistic_name 'buffer busy waits' AND
value > 20000;
9-14
Copyright © 2006, Oracle. All rights reserved.
Calculating the Buffer Cache Hit Ratio
SELECT name, value
FROM v$sysstat
WHERE name IN ('db block gets from cache',
'consistent gets from cache',
'physical reads cache');
PHYSICAL READS = 'physical reads cache')
LOGICAL READS = ('consistent gets from cache' +
'db block gets from cache')
HIT RATIO = 1 - PHYSICAL READS/LOGICAL READS
9-15
Copyright © 2006, Oracle. All rights reserved.
Buffer Cache Hit Ratio Is Not Everything
•
•
•
•
A badly tuned database can still have a hit ratio
of 99% or better.
Hit ratio is only one part in determining tuning
performance.
Hit ratio does not determine whether a database is
optimally tuned.
Use the wait interface to examine what
is causing a bottleneck:
– V$SESSION_WAIT
– V$SESSION_EVENT
– V$SYSTEM_EVENT
•
9-16
Tune SQL statements.
Copyright © 2006, Oracle. All rights reserved.
Interpreting Buffer Cache Hit Ratio
•
Hit ratio is affected by data access methods:
–
–
–
–
•
Full table scans
Repeated scans of the same tables
Large table with random access
Data or application design
Investigate increasing the cache size if:
– Hit ratio is low
– Application is tuned to avoid full table scans
9-17
Copyright © 2006, Oracle. All rights reserved.
Read Waits
•
List of wait events performing disk reads into the
buffer cache:
– db file sequential read
– db file parallel read
– db file scattered read
•
If wait time for reads is high:
– Tune the SQL statement that issues most disk reads
by sorting V$SQL by DISK_READS and BUFFER_GETS.
– Grow buffer cache if needed.
– Reduce writes due to checkpointing.
– Add more disk capacity.
9-19
Copyright © 2006, Oracle. All rights reserved.
Free Buffer Waits
Server
D
C
Server
Buffer
state
dirty
A
J
Server
Write
B
free
K
LRU
list
9-21
Copyright © 2006, Oracle. All rights reserved.
DBWn
Solutions
The buffer cache solutions are applied depending on
the symptoms:
• Properly size the buffer cache.
• Cache objects.
• Use the keep and recycle pools.
• Increase the writing speed of DBWn.
• Use private I/O pool.
9-22
Copyright © 2006, Oracle. All rights reserved.
Sizing the Buffer Cache
Buffer cache size affects several tuning diagnostics. If
the cache is too small:
• Extra reads due to block replacement
• Extra writes to move dirty blocks to disk
• Buffer cache LRU chains contention
9-23
Copyright © 2006, Oracle. All rights reserved.
Buffer Cache Size Parameters
•
Set the primary block size for the recycle, keep,
and default buffer pools: DB_BLOCK_SIZE
•
Set the size of the buffer pools:
– DB_CACHE_SIZE
– DB_KEEP_CACHE_SIZE
– DB_RECYCLE_CACHE_SIZE
•
•
Represent all memory for the buffer cache
Are required to use buffer cache features:
– Dynamic grow/shrink
– Buffer cache advice
– Multiple block sizes
9-24
Copyright © 2006, Oracle. All rights reserved.
Dynamic Buffer Cache
Advisory Parameter
•
•
•
The buffer cache advisory feature enables and
disables statistics gathering for predicting
behavior with different cache sizes.
Use the information provided by these statistics to
size the buffer cache optimally for a given
workload.
The buffer cache advisory is enabled by means of
the DB_CACHE_ADVICE initialization parameter:
– This parameter is dynamic and can be changed
using ALTER SYSTEM.
– Three values are allowed: OFF, ON, and READY.
9-25
Copyright © 2006, Oracle. All rights reserved.
View to Support Buffer Cache Advisory
•
Buffer cache advisory information is collected in
the V$DB_CACHE_ADVICE view.
•
The view contains different rows that estimate the
number of physical reads for cache sizes between
10% and 200% of the current cache size.
The rows also compute a physical read factor,
which is the ratio of the number of estimated
reads to the number of actual reads.
Simulation is done for all buffer pools.
•
•
9-26
Copyright © 2006, Oracle. All rights reserved.
Using the V$DB_CACHE_ADVICE View
SELECT size_for_estimate, buffers_for_estimate,
estd_physical_read_factor, estd_physical_reads
FROM V$DB_CACHE_ADVICE
WHERE name = 'DEFAULT' AND
advice_status = 'ON' AND
block_size = (SELECT value FROM V$PARAMETER
WHERE name = 'db_block_size');
Estd Phys
Estd Phys
Cache Size (MB)
Buffers Read Factor
Reads
---------------- ------------ ----------- -----------(10%)
30
3,802
18.70 192,317,943
…
243
30,416
1.33
13,720,149
273
34,218
1.13
11,583,180
(Current)
304
38,020
1.00
10,282,475
334
41,822
.93
9,515,878
…
577
72,238
.67
6,895,122
(200%)
608
76,040
.66
6,739,731
9-27
Copyright © 2006, Oracle. All rights reserved.
Using the Buffer Cache Advisory with EM
9-28
Copyright © 2006, Oracle. All rights reserved.
Caching Tables
•
Enable caching during full table scans by:
– Creating the table with the CACHE clause
– Altering the table with the CACHE clause
– Using the CACHE hint in a query: select /* + cache */
•
Caching tables put blocks at the MRU end of the
LRU list.
Guideline: Do not overcrowd the buffer cache.
Use a keep pool.
•
•
9-29
Copyright © 2006, Oracle. All rights reserved.
Multiple Buffer Pools
•
Three buffer pools:
– Default: SYS and nonflagged table or indexes
– Keep: Hot objects
– Recycle: Infrequent access
•
Useful for small, simple schemas with well-known
access paths
Auto-tuned
Default
9-30
Keep
Recycle
Copyright © 2006, Oracle. All rights reserved.
Enabling Multiple Buffer Pools
Buffer pool이 변경
-새로 읽어 들인 블록은 새로운 buffer pool에 들어감
-이전에 남아 있던 블록은 유지 (flush out될때까지)
•
Use the BUFFER_POOL clause.
•
This clause is valid for tables, clusters, and
indexes.
When altered, buffer pool is used for future reads.
Objects can have more than one buffer pool.
•
•
CREATE INDEX cust_idx …
STORAGE (BUFFER_POOL KEEP …);
ALTER TABLE customer
STORAGE (BUFFER_POOL RECYCLE);
ALTER INDEX cust_name_idx
STORAGE (BUFFER_POOL KEEP);
9-32
Copyright © 2006, Oracle. All rights reserved.
Calculating the Hit Ratio for Multiple Pools
SQL>
2
3
4
SELECT name, 1 - (physical_reads /
(db_block_gets + consistent_gets)) "HIT_RATIO"
FROM V$BUFFER_POOL_STATISTICS
WHERE db_block_gets + consistent_gets > 0;
NAME
HIT_RATIO
--------------------------KEEP
.983520845
RECYCLE
.503866235
DEFAULT
.790350047
9-33
Copyright © 2006, Oracle. All rights reserved.
Multiple Block Sizes
•
•
•
•
Allow buffer caches for nonstandard block sizes
Parameters: DB_nK_CACHE_SIZE {n = 2, 4, 8, 16, 32}
BLOCKSIZE attribute of CREATE TABLESPACE
storage clause
Intended for transportable tablespaces
Default
16 KB cache
Keep
9-35
Copyright © 2006, Oracle. All rights reserved.
Set#8
Set#7
Set#6
Set#5
Set#4
Set#3
Set#2
Set#1
Recycle
Multiple Database Writers
DBW0
9-36
Set#4
Set#1
•
•
Set#2
•
Multiple database writers are a means to increase
write throughput useful for large SMP systems.
Buffer cache is partitioned between database
writers by working sets.
Each DBWn process scans its own assigned sets.
The number of database writes can be manually
controlled by DB_WRITER_PROCESSES.
Set#3
•
DBW1
Copyright © 2006, Oracle. All rights reserved.
•
•
•
– DBWR_IO_SLAVES
– DISK_ASYNCH_IO
9-37
Copyright © 2006, Oracle. All rights reserved.
Set#4
Set#3
•
•
Allow DBW0 to write in parallel
when asynchronous I/O is not
well supported
DBW0 gathers batch of buffers
Queues batch in round-robin
order with I/O slaves
Waits for all slaves to complete
Cannot combine multiple DBWn
with multiple I/O slaves
Controlled by:
Set#2
•
Set#1
Multiple I/O Slaves
DBW0
i000
i001
i002
Use Multiple Writers or I/O Slaves
To reduce free buffer waits:
• Implement asynchronous I/O first
• Use multiple writers when a database writer is
consuming 100% of a CPU
• Use I/O slaves when asynchronous I/O is not
supported
9-38
Copyright © 2006, Oracle. All rights reserved.
Private Pool for I/O Intensive Operations
•
Use for operations that:
– Do not require caching
– Require efficient I/O
•
•
•
Use for large asynchronous I/O
Requires global checkpoint to ensure consistent
data
Use only for direct path I/O:
– Parallel direct inserts
– SQL*Loader direct path : 직접 쓰기
9-39
Copyright © 2006, Oracle. All rights reserved.
Automatically Tuned
Multiblock Reads
•
DB_FILE_MULTIBLOCK_READ_COUNT is
automatically tuned.
•
•
Simplifies the determination of the best value
Optimal I/O size is platform dependent:
– Cannot exceed 10% of the cache
Prefetch limited to 64 KB
– Prevents swamping of the cache
•
9-40
Automatically enabled if not set or set to zero
Copyright © 2006, Oracle. All rights reserved.
Faster Instance Startup for
Ultralarge Buffer Caches
•
•
•
•
9-41
Database available at 10% initialization of buffer
cache
Remaining 90% formatted in the background by
CKPT
Leverages the dynamic buffer cache infrastructure
Especially useful for very large buffer caches
Copyright © 2006, Oracle. All rights reserved.
Flushing the Buffer Cache
(for Testing Only)
ALTER SYSTEM FLUSH BUFFER_CACHE;
9-42
Copyright © 2006, Oracle. All rights reserved.
Practice: Overview
This practice covers the following topics:
• Use the DB Buffer Cache Advisor to size the buffer
cache
• Use the Keep Pool
9-43
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Describe the buffer cache architecture
• Size the buffer cache
• Resolve common performance issues related to
the buffer cache
• Use common diagnostic indicators to suggest a
possible solution
9-44
Copyright © 2006, Oracle. All rights reserved.