Oracle9i 性能调整与优化

　　第七课   调整磁盘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.

　　SQL>analyze 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脚本。

查看本文来源