调整缓冲区高速缓存(Buffer Cache)的性能

Buffer cache由数据块组成。

1. Buffer cache的工作原理

LRU列表：MRU………………. LRU。(全表扫描FTS放在LRU端。)

缓冲区块的状态：Free、Pinned、Clean、Dirty。

Dirty List或Write List(写列表)。

数据库写进程DBW0将缓冲区高速缓存中的数据写到数据文件中。

2.测量Buffer cache的性能

测量Buffer cache的命中率：

SQL select 1-((physical.value – direct.value – lobs.value)/logical.value) “Buffer Cache Hit Ratio” from V$SYSSTAT physical, V$SYSSTAT direct,

V$SYSSTAT lobs, V$SYSSTAT logical where physical.name = ‘physical reads’

And direct.name = ‘physical reads direct’ and

lobs.name = ‘physical reads direct (lob)’

And logical.name = ‘session logical reads’;

“Buffer Cache Hit Ratio”的值要 90%。

使用STATSPACK来监视Buffer cache。

使用REPORT.TXT来监视Buffer cache。

非命中率指标：Free Buffer Inspected、Free Buffer Waits、Buffer Busy Waits。(V$sysstat)

使用Performance Manager(数据库例程)来监视Buffer Cache。

3. 提高缓冲区高速缓存性能的方法

加大Buffer Cache的大小：init.ora参数DB_CACHE_SIZE(动态参数)。

使用Buffer Cache Advisory功能决定Buffer Cache的大小：

首先将init.ora参数DB_CACHE_ADVICE设成ON，然后查询V$DB_CACHE_ADVICE。

使用多个缓冲区池：

Keep Pool: DB_KEEP_CACHE_SIZE

Recycle Pool:DB_RECYCLE_CACHE_SIZE

Default Pool: DB_CACHE_SIZE

在内存中缓存表: 表的CACHE选项，对优化小表的全表扫描。

正确创建索引。

4.调整Large Pool和Java POOL

Large Pool用于共享服务器、RMAN、并行查询、DBWR的从属进程。

Large Pool的大小通过init.ora参数Large_pool_size设置。默认为8M。

从V$sgastat中监视free memory的值：

SQLSELECT name,bytes FROM V$sgastat WHERE pool = ‘large pool’;

JAVA_POOL池的默认大小为32M。对于大型Java应用程序，JAVA_POOL池的大小应大于50M。

init.ora参数java_pool_size

从V$sgastat中监视free memory的值。

SQLSELECT name,bytes FROM V$sgastat WHERE pool = ‘java pool’;

调整重做有关的性能

Oracle重做有关的组件包括：Redo Log Buffer、Online Redo Log、LGWR、Archive Log、Checkpoint、Arch0。

1. 监视Redo Log Buffer的性能

Redo Log Buffer不采用LRU(Least Recently Used)算法治理。

当下列事件发生时，Redo Log Buffer的内容存盘：

Commit时、每3秒、空间使用1/3、达到1M、检查点。

假如写入Redo Log Buffer的速度超过LGWR存盘的速度，就会因等待而降低性能。

监视Redo Log Buffer的重试率(<1%)。

Select retries.value/entries.value “Redo Log Buffer Retry Ratio”

From V$sysstat retries, V$sysstat entries

Where retries.name =‘redo buffer allocation retries’

And entries.name =‘redo entries’;

“Redo Log Buffer Retry Ratio”的值要 < 1%。

Select name,value from V$sysstat where name=’redo log space requests’;

假如该值大，需要增加Redo Log Buffer。

2. 提高Redo Log Buffer的性能

增加Redo Log Buffer的大小：init.ora参数log_buffer。

减小重做日志的生成量。(假如设置表的NOLOGGING属性，下列操作不记录在Online Redo Log中：用SQL* Loader的直接路径加载。

NOLOGGING属性还可用于下列SQL语句：CREATE TABLE AS SELECT、CREATE INDEX、

ALTER INDEX REBUILD、CREATE TABELSPACE)。

3. 调整检查点进程的性能

测量检查点进程的性能：没有完成的检查点进程的次数。

select * from V$system_event;

两个事件：checkpoint completed、log file switch(checkpoint incomplete)。

Select * from V$sysstat。

background checkpoints started和background checkpoints completed。

使用Alert日志来记录检查点进程：init.ora参数log_checkpoint_to_alert。

使用Performance Manager来测量检查点进程的性能：I/O中的平均灰数据队列长度(假如为0,说明检查点太频繁)。

建议调整online redo log的大小，使检查点进程每20-30分钟执行一次。

4. 调整联机重做日志文件

使用V$system_event来监视联机重做日志文件的性能：

log file parallel write、log file switch completed。

调整联机重做日志文件的方法：与数据文件、控制文件、归档日志文件分开，放在原始设备上。

5. 调整归档性能

检查归档进程的性能：通过V$system_event中的log file switch(archiving needed)事件。

检查每个归档进程的状态：V$archive_processes。

创建多个归档进程：init.ora参数LOG_ARCHIVE_MAX_PROCESSES(默认为2)。

调整磁盘I/O的性能

哪些操作会导致磁盘I/O：

将Buffer cache中的内容写到数据文件。

写回退段。

将数据文件的内容读到Buffer cache中。

将Redo log Buffer中的内容写到online redo log中。

将online Redo log中的内容归档到 archive log中。

1. 调整表空间和数据文件

测量数据文件I/O：使用V$filestat。

使用STATSPACK来测量数据文件I/O。

使用REPORT.TXT来测量数据文件I/O。

使用Performance Manager(I/O)来测量数据文件I/O。

建议：

不要在SYSTEM表空间存放用户数据。

将 I/O操作均分到几个数据文件上。(监视数据文件的I/O操作数。)

使用本地治理的表空间。

将数据库文件与其它程序的文件分开。

使用分区表和分区索引。

将大表放在单独的表空间。

创建单独的回退表空间。

创建一个或多个临时表空间。

不要将联机重做日志文件和归档联机重做日志文件放在同一个设备上。

至少将一个控制文件放在一个单独的设备上。

检查V$sysstat中的’table scans(long tables)’。

使用init.ora参数DB_FILE_MULTIBLOCK_READ_COUNT(默认为16)来优化表扫描。

2. 调整DBW0性能

监视DBWR0性能。

使用V$system_event监视下列事件：buffer busy waits、free buffer waits、

db file parallel write、write complete waits、

使用init.ora参数DBWR_IO_SLAVES(优化磁盘I/O)、

DB_WRITER_PROCESSES(默认为1，优化Buffer Cache的内部治理)。

假如DBWR_IO_SLAVES设为非0值，DB_WRITER_PROCESSES的值无效。

3．调整段I/O

避免动态空间分配。

表的有关存储特性

空闲百分比(PCTFREE)：每个对象数据块中为今后更新该对象而保留的空间的百分比。可以输入0到99之间的值。

默认值为10%。

已用百分比(PCTUSED)：Oracle数据库为该对象的每个数据块保留的已用空间的最小百分比。当一个块的已用空间低于“已用百分比”的值时，则该块将成为插入行的目标。可以输入1到99之间的值。默认值为40%。

最小数量：创建段时已分配的总区数。默认值为1。可以输入1或大于1的值。

SQL alter table emp allocate extent ;

事务处理数量

初始值：在分配给该对象的每个数据块内分配给事务处理条目的初始数量。可以输入1或2（对于簇和索引）到255之间的值。

最大值：可同时更新分配给对象的数据块的并行事务处理的最大数量。可以输入1到255之间的值。

自由表

列表：表、簇或索引的每个自由表组的自由表数量。可以输入1或大于1的值。默认值为1。

组：表、簇或索引的自由表组的数量。可以输入1或大于1的值。默认值为1。

缓冲池。

行转移(更新行时超过块的可用空间)和行链接(行的大小超过块的大小)的概念。

使用V$sysstat来监视行转移和行链接：table fetch continued row。

SQLanalyze table emp compute statistics;

使用DBA_TABLES来查询统计信息。

SQL alter table emp deallocate unused;

SQL alter table scott.emp move tablespace users;

表的高水位标志High Water Mark(HWM)。

4．调整排序IO

哪些SQL语句需要排序操作:order by、group by、selec distinct、union、

intersect、minus、analyze、create index、联接。

V$sysstat。内存排序和磁盘排序(临时表空间中)。

监视排序性能(内存排序比例95%)。

使用init.ora参数SORT_AREA_SIZE(512K)、SORT_AREA_RETAINED_SIZE、

pga_aggregate_target、WORKAREA_SIZE_POLOCY。

使用Performance Manager(数据库例程)来监视排序。

如何避免排序:SQL语法、正确索引、创建索引、ANALYZE。

v$sort_segment、v$sort_usage。

使用Tablespace Map。

使用 Reorg Wizard。

5．优化回退段

一个回退段的区间可以分配给多个事务，回退段的一个数据块只能分配给一个事务。

测量回退段事务表的争用

select * from V$system_event where event like ‘%undo%’;

回退段事务表的等待时间应接近于0。

select * from V$waitstat;

V$rollstat

回退段事务表访问的成功率应95%。

回退段区间争用

V$waitstat、V$sysstat。

回退段事务环绕(Wrap)：一个事务占用的回退段从一个区间扩展到另一个区间。

回退段的动态区间分配

V$system_event。

使用V$rollstat来监视回退段的使用情况。

使用Performance Manager(后台进程)来测量回退段。

提高回退段的性能

Oracle9i中的撤消表空间。

建议：每四个事务使用一个回退段，最多不超过20个回退段。

会退段的区间大小512k，最小区间数20。

明确分配回退段给事务。

SQL set transaction use rollback segment rbs01;

最小化回退段活动:EXPORT、IMPORT、SQL* Loader时加commit=y参数。

Oracle9i中的撤消表空间。

调整闩(latch)和锁定(lock)

1. 调整闩(latch)

闩可以作为内存性能的另一个指标。

1.闩:等待闩和立即闩(V$lacth。共239个)。

数据库中是否存在闩争用V$system_event("latch free")。

几个重要的闩：shared pool、library cache、cache buffers lru chain、

cache buffers chains、redo allocation、redo copy。

select * from V$latch where misses!=0;

2.自由列表：V$system_event("buffer busy waits")。

V$waitstat。

测量哪些段存在自由列表争用：dba_segments、V$session_wait。

alter table scott.emp storage (freelists 5);

自动段空间治理的表空间。

2. 调整锁定

DML锁(TM)和DDL锁(TX)

锁定模式：

RX：对表UPDATE、INSERT、DELETE时获得。

RS：对表SELECT … FOR UPDATE时获得。

S: LOCK TABLE EMP IN SHARE MODE; 可以是多个用户获得。

SRX: LOCK TABLE EMP IN SHARE ROW EXCLUSIVE MODE; 只能是一个用户获得。

外键约束时的锁定。死锁。

用Lock Monitor监视锁定。

调整操作系统

1. 调整操作系统

观察内存和CPU利用率(<90%)。

2. 使用Resource Manager

资源使用者组。一个用户可以是多个资源使用者组的成员，但一次只有一个组是活动的。

确定用户的CPU利用率。(v$sesstat和v$sysstat)

资源计划:由资源计划指令组成。一次只能有一个资源计划是活动的(V$rsrc_plan)。

alter system set resource_manager_plan=system_plan;

select username,RESOURCE_CONSUMER_GROUP from V$session;

子计划。

资源计划调度。

使用Expert进行优化

第一步：创建一个优化会话(tuning session)。

第二步：确定优化范围(scope)。

第三步：收集数据。

第四步：复查(review)已收集到的数据。

第五步：生成建议案。建议报告。

第六步：创建实现建议所需的SQL脚本。