性能调优学习笔记（四）

Sizing the Buffer Cache
一、总述
二、Advisory
三、管理
四、调优目标和方法
五、其它指标
六、计算命中率
七、使用多缓存池
八、视图
九、缓存表
十、自动Segment(段)空间管理
十一、Free Lists
十二、Multiple DBWn(DB Writer) Processes[@more@]

Sizing the Buffer Cache
一、总述
1、缓存区包含数据块的拷贝，是SGA的一部分，可供所有用户使用。
2、相关参数：
. DB_BLOCK_SIZE
The DB_BLOCK_SIZE parameter determines the primary block size, which is the block size used
for the system tablespace and the primary buffer caches (recycle, keep, and default).
For the default block size there can be three buffer pools: keep, recycle and default.
. DB_CACHE_SIZE:决定primary block size的大小
. DB_KEEP_CACHE_SIZE:决定keep buffer pools的大小
. DB_RECYCLE_CACHE_SIZE：决定recycle buffer pools的大小（单位为kb/mb）
. DB_BLOCK_CHECKSUM：建议设置为TRUE
Checksums allow the Oracle database todetect corruption caused by underlying disks, storage systems,
or I/O systems. Turning on this feature typically causes only an additional 1% to 2% overhead.
3、缓存区的状态分以下几种
正在使用的缓存（Pinned buffer）、
剩余缓存（Free/unused buffer）、
已使用缓存（Dirty buffer）、
Clean buffer：Meaning the buffer is now unpinned and is a candidate for immediate aging
out if the current contents (data block) are not referenced again. The contents are
either in synch with disk or the buffer contains a CR snapshot of a block.
二、Advisory
1、Dynamic Buffer Cache Advisory Parameter
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.
2、View to Support Buffer Cache Advisory
Buffer cache advisory information is collected in the v$db_cache_advice view.
列说明：
. ID:Buffer pool ID (ranges from 1–8)
. NAME:Buffer pool name
. BLOCK_SIZE:Block size in bytes for buffers in this pool. Possible values are the
standard block size and non-standard block sizes in powers of two: 2048, 4096, 8192,16384, or 32768.
. ADVICE_STATUS:Status of the advisory.
. SIZE_FOR_ESTIMATE:Cache size for prediction (in megabytes).
. BUFFERS_FOR_ESTIMATE:Cache size for prediction (in terms of buffers).
. ESTD_PHYSICAL_READ_FACTOR:Physical read factor for this cache size which
is a ratio of the number of estimated physical reads to the number of reads in the real
cache. If there are no physical reads into the real cache, then the value of this column is null.
. ESTD_PHYSICAL_READS:Estimated number of physical reads for this cache size.
三、管理
1、数据读取过程
a. First, the server checks whether the required block is available in the buffer cache
using a hash function.
If the buffer is found, it is moved to the most recently used end of the LRU list. This
is a logical read, because no actual I/O took place. The rest of the steps are not
performed because the server process has the block it requires.
If the buffer is not found in the buffer cache, the server process has to read the block
from the data file.
b. Before reading from the data file, the server process searches the LRU list for a free
buffer. Buffers found during the LRU scan that have been modified by a server
process are put on the checkpoint queue for copying back to disk during a
checkpoint.
c. If the checkpoint queue exceeds its size threshold, the server signals DBWn to flush
dirty buffers from the data buffer cache. If the server cannot find a free buffer within
a search threshold, it signals DBWn to flush.
d. After a free buffer is found, the server reads the block from the data file into the free
buffer in the database buffer cache. Oracle server process moves the buffer away
from the LRU end of the LRU list. If the block is not read consistent, then the server
constructs a read-consistent copy from past image information.
2、The DBWn Process and the Database Buffer Cache
DBWn是负责把database buffer cache里的数据写入到disk的进程，在以下情况下进行此操作：
. Checkpoint Queue Exceeds Threshold
. Search Threshold Exceeded
. Alter Tablespace Offline or Alter Tablespace Begin Backup
. Drop a Segment
. Clean Shutdown
四、调优目标和方法
. 目标:
– Servers find data in memory
– No waits on the buffer cache
. 监控手段
– Wait events
v$sesstat
v$system_event
v$session_wait
v$bh：Describes blocks held in the buffer cache.
v$cache：
– Cache hit ratio
SQL> SELECT name, value
FROM v$sysstat
WHERE name in ('session logical reads',
'physical reads', 'physical reads direct',
'physical reads direct (lob)');
SQL> SELECT name, physical_reads, db_block_gets,
consistent_gets
FROM v$buffer_pool_statistics;
– The v$db_cache_advice view：determine if the buffer cache needs resizing.
. 方法
– Reduce the number of blocks required by SQL statements
– Increase buffer cache size
– Use multiple buffer pools
– Cache tables
– Bypass the cache for sorting and parallel reads
五、其它指标
1、SQL> SELECT name, value
FROM v$sysstat
WHERE name = 'free buffer inspected';
2、SQL> SELECT event, total_waits
FROM v$system_event
WHERE event in
('free buffer waits',
'buffer busy waits');
六、计算命中率
1、From v$sysstat:
Hit Ratio = (1 – (physical reads – physical reads direct - physical reads direct (lob) ) )/ session logical reads
SQL> SELECT 1 - (phy.value - lob.value - dir.value)
/ ses.value "CACHE HIT RATIO"
FROM v$sysstat ses, v$sysstat lob,
v$sysstat dir, v$sysstat phy
WHERE ses.name = 'session logical reads'
AND dir.name = 'physical reads direct'
AND lob.name = 'physical reads direct (lob)'
AND phy.name = 'physical reads';
2、From the Statspack report:
Statistic Total Per Per
Trans Logon Second
------------------------- -------- ------ --------
physical reads 15,238 13.0 15,238.0
physical reads direct 863 0.7 863.0
Physical reads direct(lob) 0 0 0
session logical reads 119,376 101.8 119,376.0
七、使用多缓存池
1、三类缓存池
. Keep
This pool is used to retain objects in memory that are likely to be reused. Keeping
these objects in memory reduces I/O operations. Buffers are kept in this pool by
ensuring the pool is sized larger than the total size of the segments assigned to the
pool. This means that buffer do not have to be aged out.
. Recycle
This pool is used for blocks in memory that have little chance of being reused. The
recycle pool is sized smaller than the total size of the segments assigned to the pool.
This means that blocks read into the pool will often have to age out a buffer.
. Default
The pool always exists. It is equivalent to the buffer cache of an instance without a
keep or a recycle pool.
2、定义大小
. Individual pools have their own size defined by:
– DB_CACHE_SIZE
– DB_KEEP_CACHE_SIZE
– DB_RECYCLE_CACHE_SIZE
. These parameters are dynamic.
. Latches are automatically allocated by Oracle RDBMS.
3、使用
eg.CREATE INDEX cust_idx …
STORAGE (BUFFER_POOL DEFAULT …);

ALTER TABLE customer
STORAGE (BUFFER_POOL RECYCLE);

ALTER INDEX cust_name_idx
STORAGE (BUFFER_POOL KEEP);
4、Guidelines
A、KEEP Buffer Pool
? Tuning goal: Keeping blocks in memory
? Size: Holds all or nearly all blocks of the segments
assigned to this pool
? Tool: dbms_stats.gather_table_stats

B、RECYCLE Buffer Pool
. Tuning goal: Eliminate blocks from memory when transactions are completed
. Size: Holds only active blocks
. Tool: v$cache 该视图由catclust.sql 脚本产生
SQL> SELECT owner#, name, count(*) blocks
2 FROM v$cache
3 GROUP BY owner#, name;
. Tool: v$sess_io
SQL> SELECT s.username, io.block_gets,
2 io.consistent_gets, io.physical_reads
3 FROM v$sess_io io, v$session s
4 WHERE io.sid = s.sid ;
5、计算命中率
SQL> SELECT name, 1 - (physical_reads /
(db_block_gets + consistent_gets)) "HIT_RATIO"
FROM v$buffer_pool_statistics
WHERE db_block_gets + consistent_gets > 0;
6、Identifying Candidate Pool Segments
. Keep pool
– Blocks are accessed repeatedly.
– Segment size is less than 10% of the default buffer
pool size.
. Recycle pool
– Blocks are not reused outside of transaction.
– Segment size is more than twice the default buffer pool size.
八、视图
1、These dictionary views have a BUFFER_POOL column that indicates the default buffer pool for the given segment:
. user_segments, dba_segments
. user_clusters, all_clusters, dba_clusters
. user_indexes, all_indexes, dba_indexes
. user_tables, all_tables, dba_tables
. user_object_tables, all_object_tables, dba_object_tables
. user_all_tables, all_all_tables, dba_all_tables
2、v$buffer_pool
九、缓存表
. 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
. Guideline: Do not overcrowd the buffer cache
. Use a keep pool
十、自动Segment(段)空间管理
适用于本地管理的表空间
. Manages free space automatically inside database segments
. Tracks segment free/used space with bitmaps instead of free lists
. Provides better space utilization, especially for the objects with highly varying size rows
. Specified when creating a tablespace，当表空间创建时是自动管理的，那么在此表空间上的所有对象都有此属性。
CREATE TABLESPACE BIT_SEG_TS
DATAFILE '$HOME/ORADATA/u04/bit_seg01.dbf'
SIZE 1M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLE bit_seg_table
(idnum NUMBER)
TABLESPACE bit_seg_ts;
. Supported by Oracle Enterprise Manager
十一、Free Lists
适用于字典管理的表空间
1、定义
A free list for an object maintains a list of blocks that are available for inserts.
2、诊断
. To query v$waitstat:
SQL> SELECT class, count, time
2 FROM v$waitstat
3 WHERE class = 'segment header';
. To query v$system_event:
SQL> SELECT event, total_waits
2 FROM v$system_event
3 WHERE event = 'buffer busy waits';
3、寻找
SELECT s.segment_name, s.segment_type, s.freelists,
w.wait_time, w.seconds_in_wait, w.state
FROM dba_segments s, v$session_wait w
WHERE w.event ='buffer busy waits'
AND w.p1 = s.header_file
AND w.p2 = s.header_block;
4、Reducing Busy Buffer Waits
A、Free list blocks:
- Add more free lists
- Use Automatic Segment Space Management
- In the case of Oracle Parallel Server, make sure that each instance has its own
free list group. An ALTER TABLE statement can change the number of free
lists in a free list group.
B、Segment headers
- Increase the number of free lists or use Automatic Segment Space Management.
- Use free list groups (This can make a difference even in a single instance environment.)
注意：You cannot alter the FREELISTS or FREELIST GROUPS parameters for
segments in tablespaces using Automatic Segment Space Management.
5、Resolving Free List Contention
To increase the number of free lists for the object, do one of the following:
. Move the object to a tablespace using Automatic Segment Space Management.
. Use the ALTER TABLE command to change the number of FREELISTS.
. Use Oracle Enterprise Manager console, under the SCHEMA – TABLE option, to change the number of FREELISTS.
十二、Multiple DBWn(DB Writer) Processes
1、Can be deployed with DB_WRITER_PROCESSES(DBW0 to DBW9)--最多十个
2、Can be useful for SMP systems with large numbers of CPUs
smp-"对称多处理"（Symmetrical Multi-Processing）技术
3、Cannot concurrently be used with multiple I/O slaves
4、The DBA can turn asynchronous(异步) I/O on or off with the DISK_ASYNCH_IO parameter.
5、The DBWR_IO_SLAVES initialization parameter controls I/O slave deployment.
6、调试
Tune the DB Writer processes by looking at the value of the FREE BUFFER WAITS event.
SQL> SELECT total_waits
2 FROM v$system_event
3 WHERE event = 'free buffer waits';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/594892/viewspace-929467/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/594892/viewspace-929467/