Oracle 9i 整体性能优化概述草稿之四:调整磁盘I/O

5 调整磁盘I/O 52
5.1 数据文件I/O调整 52
5.1.1 测量数据文件IO 52
5.1.2 改进数据文件I/O 53
5.2 DBW0 56
5.2.1 测量DBW0性能 56
5.2.2 改进DBW0性能 57
5.3 单个段数据块I/O调整 58
5.3.1 测量段性能和调整 59
5.4 排序操作和临时段优化 62
5.4.1 排序 62
5.5 UNDO 64
5.5.1 测量UNDO I/O性能 65
5.5.2 优化undo segment 66

5 调整磁盘I/O
通过适当的确定SGA的大小来尽可能的减少物理I/O；在必要时，尽可能快的执行任何物理剩余的物理I/O。

磁盘I/O的来源：
１） Database buffer cache 到datafile的DBW0写操作和逆向的用户读Server Process。
２） 写数据到UNDO上维护读一致的DBW0。
３） Redo log buffer到logfile的LGWR写操作。
４） 日志内容到归档的ARC0操作。
５） 应用的磁盘排序操作。

基础概念：
段：Oracle的表或索引
范围：为段提供的存储实际数据的逻辑单位，是一个或多个连续的数据块。
Oracle块：具体存储实际数据的Oracle单位，由一个或多个连续的操作系统块组成。
表空间：存储数据的逻辑文件。
数据文件：存储数据的物理文件。

5.1 数据文件I/O调整
5.1.1 测量数据文件IO
v$filestat,v$datafile,v$tempfile
 搜集至数据库启动以来所发生的
确定：init.ora parameters的timed_statistics设置为TRUE
select s.file#||'D',d.name,s.phyrds,s.phywrts,s.avgiotim,s.miniotim,s.maxiowtm,s.maxiortm
from v$filestat s,v$datafile d
where s.file# = d.file#
union
select s.file#||'D',t.name,s.phyrds,s.phywrts,s.avgiotim,s.miniotim,s.maxiowtm,s.maxiortm
from v$filestat s,v$datafile t
where s.file# = t.file#
order by 3 desc;
注：
s.phyrds：物理读次数
s.phywrts：物理写次数
s.avgiotim：执行I/O所花费的平均时间（毫秒）
s.miniotim：执行I/O所花费的最短时间（毫秒）
s.maxiowtm：执行写I/O所花费的最长时间（毫秒）
s.maxiortm：执行读I/O所花费的最长时间（毫秒）

 搜集某个时间以来所发生的
$ORACLE_HOME/rdbms/admin/utlbstat.sql
$ORACLE_HOME/rdbms/admin/utlestat.sql
SQL>Rem Sum IO operations over tablespaces
SQL>Rem I/O should be spread evenly across drives…
如上两段描述了表空间和数据文件分别的I/O信息。

 定时搜集statspack

5.1.2 改进数据文件I/O
5.1.2.1 保证非Oracle I/O不干涉数据文件I/O
不要将非Oracle文件与数据文件放在相同的磁盘驱动器或逻辑卷上。否则不仅可能会存在对磁盘资源的竞争，而且此时的I/O统计数据不能反映真实的磁盘I/O数据。

检查：
使用操作系统命令。

5.1.2.2 使用locally Managed tablespace减少I/O
使用LMT创建的表空间，不再使用free list管理范围，从而减少free list 竞争。这样就不在需要访问SYSTEM表空间中的系统数据目录，而在表空间头使用位图的方法，能更快的分配表空间内的空间。

检查：
SQL>select tablespace_name,status,contents,extent_management from dba_tablespaces
where extent_management != 'LOCAL';

5.1.2.3 把数据文件均分到许多设备、逻辑卷和控制器上
１） 不应把任何应用程序段存储在SYSTEM表空间。保证数据库用户不使用SYSTEM作为缺省或临时表空间。由于执行递归的SQL存放在SYSTEM表空间，所以注意SYSTEM表空间可能有频繁的读活动。
检查：
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024
from dba_segments where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS','MDSYS');

select username,user_id,account_status,default_tablespace,temporary_tablespace,created,profile
from dba_users where username not in
('SYS','SYSTEM','OUTLN','WMSYS','ORDSYS','MDSYS','ORDPLUGINS')
and (default_tablespace = 'SYSTEM' or temporary_tablespace = 'SYSTEM');

(转移对象：alter table .. move tablespace .. ;
alter index .. rebuild tablespace ..;)

２） 应设法把应用于各表空间的数据文件放到不同的磁盘驱动器、卷组或磁盘控制器上，最大限度减少磁盘争用；同时也要把非数据文件的数据库组件（如controlfile,redo log file, archive file ..）进行分开，因为他们的I/O特征是不一样的。
%df -k
- 将系统、临时、回滚段和应用的表和索引分开。
- redo log和archive log分开。
- controlfile与datafile分开。
- dump file由于随机的，且操作不多，可忽略。
- 不在SYSTEM上设置回滚段和临时段。
- 可把段按功能或大小及应用进行分开。

5.1.2.4 在数据文件I/O发生时，尽可能快的完成全部数据文件I/O操作
１） 将高频率的I/O数据文件放到不同的磁盘驱动器和控制器上。
　　
２） 条状化表空间。
把数据文件放在RAID设备上，已经隐含使用了条状化，不需要DBA做相关调整（只需要注意条宽度和条深度）。
手工条状化：
创建一个由几个数据文件组成的表空间，每个数据文件放在不同的物理设备上，接着创建一个段，使它的范围被存储在这些数据文件上。
例：
创建表空间
SQL>create tablespace app1_data
Datafile ‘/u01/oradata/prod/app1_data01.dbf’ size 5M
Extent management local
Autoextend on next 5M maxsize 2000M;
创建emp表的初始范围
SQL> create table emp (emp_id number,last_name varchar2(20))
Storage (initial 4M next 4M pctincrease 0)
Tablespace app1_data;
给app1_data表空间添加数据文件
SQL>alter tablespace app1_data
Add datafile ‘/u02/oradata/prod/app1_data02.dbf’ size 5M;
手工分配emp表的新范围
SQL>alter table emp
Allocate extent (datafile ‘/u02/oradata/prod/app1_data02.dbf’ size 4M);

（调整的表的时候可以使用；但一般与管理他的时间相比，手工条状化费时且好处不多）

３） 调整init.ora 的　DB_FILE_MULTIBLOCK_READ_COUNT。
　　DB_FILE_MULTIBLOCK_READ_COUNT参数指定一个用户server process在执行全表扫描时在单个I/O中读取的最多数据块个数。默认值是８。
通过增加该值，可以每次I/O访问更多的块，减少I/O次数。
（１， CBO可能会受该参数影响，认为使用全表扫描比使用索引有更少的I/O时候，会选择全表扫描而不使用索引
（２， 使用RAID条状时，应将该值设成条大小的一个倍数）。
　　
　　先确定应用程序执行全表扫描的频率：
SQL>select name,value from v$sysstat where name = 'table scans (long tables)';
　　如果值很大，说明增加该值可能性能会有优化。

同时，
select * from v$session_longops　where time_remaining > 0;
监视运行时间很长的选定操作相关的活动情况（每当一个进程扫描了10,000个以上的　Ｏracle块时，table scan操作就会出现在该视图中，并不表示该操作一定是全表扫描），“time_remaining > 0”说明检查当活动的。
如果需要检查具体执行时间，可以使用DBMS_APPLICATION_INFO.SET_SESSION_LONG_OPS向v$session_longops注册一个用户会话的方法了解。
（这个监视应发扬光大）

5.1.2.5 日志文件
建立大小合适的日志文件以最小化竞争；
提供足够的日志文件组以消除等待现象；
将日志文件存放在独立的、能快速访问的存储设备上（日志文件可以创建在裸设备上）。

日志文件以组的方式组织管理，每个组里的日志文件的内容完全相同。
5.1.2.6 归档日志文件
如果选择归档模式，必须要有两个或两个以后的日志组，当从一个组切换到另一个组时，会引起两种操作：DBWn进行Checkpoint；一个日志文件进行归档。

归档有时候会报错：
ARC0：Beginning to archive log# 4 seq# 2772
Current log# 3 seq# 2773……
ARC0: Failed to archive log# 4 seq# 2772
ARCH: Completed to archiving log#4 seq# 2772
建议init参数修改如下：
log_archive_max_processes=2
#log_archive_dest = ‘/u05/prodarch’
log_archive_dest_1 = "location=/u05/prodarch MANDATORY’
log_archive_dest_state_1 = enable

log_archive_dest_2 = "location=/u05/prodarch2 OPTIONAL reopen=10" （或其它目录）
log_archive_dest_state_2 = enable
log_archive_min_succeed_dest=1

log_archive_dest_state_3 = DEFER
log_archive_dest_state_4 = DEFER
log_archive_dest_state_5 = DEFER

5.2 DBW0
DBW0负责把database buffer cache写到数据文件上，在checkpoint和用户server process在database buffer cache搜索自由缓冲区时触发DBW0。
5.2.1 测量DBW0性能
 系统视图检查至数据库启动以来的数据
１）v$system_event
select event,total_waits,average_wait
from v$system_event
where event in
('buffer busy waits','db file parallel write','free buffer waits','write complete waits');
注释：
buffer busy waits：针对database buffer cache中的缓冲区等待正发生，可能包括DBW0写脏缓冲区效率不高所致。
db file parallel write：DBW0并行写遇到等到。可能是因为datafile驻留在一个速度慢的设备上引起，也可能DBW0的处理速度跟不上要求他写的那些请求时间引起。
free buffer waits：用户server process在把脏缓冲区放到dirty list上，同时搜索LRU的一个自由缓冲区时遇到等待。
write complete waits：用户会话一直遇到DBW0从database buffer cache中写缓冲区等到。
如果查询的total_waits很高，且不住增长，所以DBW0没有有效的执行操作。

2）v$sysstat
select name,value from v$sysstat
where name in ('redo log space requests','DBWR buffers scanned','DBWR lru scans');
注：
redo log space requests：表示在日志切换后，等候redo log可用的等待事件。（说明了redo log的组数，成员数，大小需要好好的考虑了）
DBWR buffers scanned：Database buffer cache中检查找出待写脏缓冲区的总数量。
DBWR lru scans：lru扫描次数。
Value以毫秒为单位。

select scanned.value/scans.value "avg.num. buffers scanned"
from v$sysstat scanned,v$sysstat scans
where scanned.name = 'DBWR buffers scanned'
and scans.name = 'DBWR lru scans';
如果"avg.num. buffers scanned"值很高或不断增长，说明DBW0在底效的执行写操作。

 STATSPACK
 Utlbstat.sql utlestat.sql

5.2.2 改进DBW0性能
两个init.ora参数用于改进DBW0性能：
DBWR_IO_SLAVES：启动DBWR0的从属进程
DB_WRITER_PROCESSES：启动DBWR同样的进程

注意：如果DBWR_IO_SLAVES非０，则DB_WRITER_PROCESSES设置无效。
至于是启动从属进程，还是启动DBWRn进程，则需要根据具体等待时间是否需要做buffer cache管理等待等进行判断，即如果仅仅是写等待，则启动从属进程；如果是如free buffer waits，则启动DBWRn进程。

5.2.2.1 DBWR_IO_SLAVES
默认值是０，可以配置的最大值由操作系统决定。
如，Ora_i102_prod，i表示是从属进程，1表示使用第一个适配器（一个内存池），03是使用１号适配置器的第３个进程。
从属DBWR只会执行写操作，不能完成如把缓冲区从LRU list转移到database buffer cache内的dirty list上。
DBWR工作原理：
在有写操作请求时，DBWR0协调从属进程处理，如果所有可用的从属进程都忙，则启动一个从数进程处理（最多启动的从属进程不会超过本参数指定的数值），如果无法启动，则产生等待事件。

注意：
在配置了该参数非０时，每当database writer产生I/O从属进程时，执行磁盘I/O的其他进程也会产生从属进程，如log writer、archiver、recovery manager。

5.2.2.2 DB_WRITER_PROCESSES
默认值是１，最大值是10。
本参数指定启动多少个DBWR进程，而非启动DBWR的从属进程；
启动的DBWR进程，具有与DBWR0完成相同的功能。

5.2.2.3 Checkpoint
Checkpoint进行的操作：DBWn进行IO操作；CKPT更新数据文件头和控制文件。
经常进行Checkpoint的结果：减少恢复所需的时间；降低了系统运行时的性能。

LGWR以循环的方式将日志写到各个日志组，当一个日志组满时，oracle server必须进行一个Checkpoint，这意味着：DBWn将对应log覆盖的所有或部分脏数据块写进数据文件；CKPT更新数据文件头和控制文件。如果DBWn没有完成操作而LGWR需要同一个文件，LGWR只能等待。
在OLTP环境下，如果SGA很大并且checkpoint的次数不多，在Checkpoint的过程中容易出现磁盘竞争的状况，在这种情况下，经常进行Checkpoint可以减少每次Checkpoint涉及到的脏数据块的数目。

调节Checkpoint次数的办法：
增大日志文件；增加日志组以增加覆盖的时间间隔。

5.3 单个段数据块I/O调整
调整目标：最小化检索被请求的应用数据而必须访问的块数量。

有两种块大小需要注意：
１） 主块：在数据库创建时由init.ora的db_block_size参数指定，至少用于创建SYSTEM和TEMP表空间。改变主块大小的唯一办法是重新创建数据库。
一般默认win2000使用2k，SUN使用8k。
２） 局部块：在创建表空间时，指定blocksize关键字（如果没有指定blocksize关键字，则使用主块大小）。

块大小可指定的值的范围为db_nk_cache_size参数的n的数值（要求是操作系统块的整数倍；要求、不超过操作系统最大的I/O大小）。

select tablespace_name,block_size from dba_tablespaces;

一个范围是一组连续的Oracle块，当创建一个段时，将至少分配一个范围（叫初始范围）给段，初始范围的第一个块（也叫头部块）含有一个线路图，描述了该范围内其他所有块的位置。

创建表空间，创建表，创建索引的所有可指定的参数和关键字，值得仔细研究一下。

一个Oracle块分为块头区、保留区（pctfree参考）和自由区（pctused参考）。
 块头区：每个块一般使用分配给他的50到200个字节来存储关于该块内容的一些头部信息。（包括：initrans产生在表创建时指定的transaction slot，该块内含有的行的一个目录和管理该块需要的其他一般性头部信息）
 保留区和自由区：一个块，除了分配给块头区的外，剩下的都是保留区和自由区，其中pctfree参数指定整个块大小的百分之多少留做保留区存储行被更新，剩下的都是可存储行的自由区。

块刚被分配的时候，是加在free list的最顶端等待被使用，当行被insert数据到自由区，整个块大小只剩下pctfree的时候，将从free list中被移走，不再插入新行。
此时如果有对该块的update操作，则使用pctfree中指定的空闲空间来扩展；如果发生delete操作，则一旦删除数据行后，如果整个块的空闲空间占整个块的pctused，则重新把该块放到free list的最顶端（当不连续的自由空间足于可以被insert一新行时，在执行insert前，Oracle会自动合并自由区）。

Pctfree （默认10%）和 pctused（默认40%），可以在段创建时指定，也可在段创建后alter，但对已存储了数据而又没有进入free list的块无效。

Oracle建议的设置方法：
PCTFREE = 100*Avg.Update Size(bytes)/Avg.Row length
PCTUSED = 100 – PCTFREE – 100*Num.Rows in Table*(Avg.Row Length) / Block Size

select * from dba_tables; //查询表pctfree和pctused

5.3.1 测量段性能和调整
5.3.1.1 动态范围分配与性能
Oracle 9i自动使用management local局部管理表空间的方法，在数据文件头文件中使用位图来管理和分配范围，只简单的改变位图的值来表示数据文件内块的状态，只涉及极少的底归I/O，可忽略一向。

而如果不是使用management local，则使用了free list，此时段的可用范围满了后，扩展新段，需要查询SYSTEM表空间的相关管理信息和范围空间的实际获取，会增加更多的I/O操作。只能经常检查，范围使用到一定比例的表，手工分配更多的范围，但Oracle建议，为了性能，一个段的范围数量不应该超过1000个（而management local可以有数千个范围）。

SQL>select owner,table_name,1-(empty_blocks/(empty_blocks+blocks)) "%blocks used"
from dba_tables
where owner != 'SYS'
and 1-(empty_blocks/(empty_blocks+blocks)) > 0.95
order by 1;
查询出的表需要手工分配了。（如果表没有经过analyze,则block和empty_blocks为空值）

SQL>alter　table　app1.sales　allocate　extent;
手工扩展范围。
5.3.1.2 范围大小的确定与性能
越大的范围比越小的范围提供更好一点的性能，因扩展的次数就少了；而且可以在初始范围头部由一个统一的块-----范围地图（etent map）中找出所以范围的位置。如果
db_file_multiblock_read_count的设置正确，读取范围的I/O次数就会减少。
　　大范围的缺点：空间可能会浪费，及在需要一个范围时没有足够大的连续Oracle块可使用。

１） 有随机存取特性的OLTP，小块更好。
２） 小块减少块争用，因每块只含有教少的行。但增加database buffer cache开销，必须访问更多的块。
３） 小块对小行的排序更好。
４） 大块用在DSS比较好。大块增加块争用，并要求较大的database buffer cache大小。

5.3.1.3 行连接和行迁移
行连接和行迁移由块大小所引起。

行连接：插入的行超出Oracle块大小，该行将存储在两个或两个以上的块中，这种横跨多块叫行连接。
对性能的影响是：　操作一行需要读取更多的块。
唯一处理办法：增加块大小或减少插入的大小。

行迁移：只由update操作引起。如果更新的行大小超过pctfree指定的可用范围，Oracle会把该行完全迁移放到一个新块上，而在原来的块上保留一个指向新块的指针。
对性能的影响：操作一行，至少需要读两个块。

 确定行连接或行迁移
检查的两种办法：
１） dba_tables中的chain_cnt列。
必须先执行analyze分析表该字段才有数据，而且不区分行连接或是行迁移。
SQL>select owner,table_name,chain_cnt from dba_tables where chain_cnt > 0;

２） v$sysstat中table fetch continued row的出现。
SQL>select * from v$sysstat where name = 'table fetch continued row';

由于行迁移只在update时出现，所以可以通过简单的删除、插入来纠正（纠正后剩下的肯定就只是行连接了）：
１） export，delete或truncate，然后重新import该表。
２） 使用alter table .. move命令重建该表。
３） 找出并重新插入迁移行。例：
分析表，得出是否存在行连接或行迁移
SQL>analyze table xxxx compute statistics;
SQL>select table_name,chain_cnt from dba_tables where table_name = ‘xxxx’;
执行$ORACLE_HOME/rdbms/admin/ tulchain.sql文件创建chained_rows表。
使用list chained rows重新分析表。
SQL>analyze table xxxx list chained rows;
可在chained_rows中查找出有行连接或行迁移的行头等信息。现在把这部分行备份出来。
备份行
SQL>create table temp_t as select * from xxxx
Where rowed in (select head_rowid from chained_rows);
删除行
SQL>delete from xxxx
where rowed in (select head_rowid from chained_rows);
恢复行
SQL>insert into xxxx select * from temp_t;
行迁移处理完成，此时再使用analyze table xxxx compute statistics;分析表后查看select table_name,chain_cnt from dba_tables where table_name = ‘xxxx’;的chain_cnt的数，都将是行连接的数目。

5.3.1.4 高水位（High Water Mark，HWM）与性能
HWM：创建段的时候分配范围，此时Oracle跟踪已用来存储段数据的最高块的ID，该ID就叫HWM。记录在segment header block中，在segment创建的时候设定在segment的起始位置，当记录被插入的时候以5个block的增量增加，truncate可以重设high water mark的位置，但delete不能。

性能影响：不管中间是否有空块，每次全表扫描都会把HWM以上所有块扫描一次（很多空块可能就这样被扫描，读取了比实际数据更多的块）。

HWM的更改只有：
１） exp,drop或truncate，然后重新导入该表。
２） 使用alter table .. move重建该表（此时需要重建索引）。
有一种情况：一个表被插入很大很大，此时HWM被扩展的很高，然后删除了这个表大量的数据，由于此时HWM不会改变，所以如果该表没有数据再被插入，则删除后实际有数据的块到HWM之间的空间将被浪费掉。此时只有执行analyze 命令分析该表后，使用alter table xxxx deallocate unused;命令来把他们释放给表空间。

使用analyze分析表后，可以使用dba_tables的empty_block来确定HWM块数量，使用
alter table tab_name deallocate unused;命令来释放给表空间。

（也可使用DBMS_SPACE.UNUSED_SPACE来确定）。分析后，dba_tables的empty_blocks列只显示HWM以上有多少块，并不表示这些块是否有数据,blocks显示HWM以下的块，他们加在一起就是一共分给段的范围了。

如果非要估计一个表的空块，则使用：
SQL> select blocks totol_blocks,round((t.avg_row_len * t.num_rows)/s.block_size,0) needed_blocks,
blocks - round((t.avg_row_len*t.num_rows)/s.block_size,0) free_blocks
from dba_tables t,dba_tablespaces s
Where t.tablespace_name = s.tablespace_name
And t.table_name = 'PERF_TCH_QJ'
And t.owner = 'PERF';

5.4 排序操作和临时段优化
5.4.1 排序
优化的最大目标是最大限度的减少排序操作，如不可避免，则尽可能在内存中进行排序。（排序只在内存或磁盘（temp表空间）内进行）

导致排序的SQL：
1) ORDER BY
2) GROUP BY
3) SELECT DISTINCT
4) UNION
5) INTERSECT
6) MINUS
7) ANALYZE
8) CREATE INDEX
9) 表间非索引上的连接

每个用户server process，都会被分配一个指定大小的内存用来排序，如果要排序的内容小于该内存，则在内存排序；如果大于该内存，则内容会被分成组块，排序好的组块被写入TEMP表空间，直到所有组块排序完成，此时再合并所有组块返回给用户。

分配给每个用户server process排序的内存大小受如下参数影响：
 SORT_AREA_SIZE
指定每个用户应留出多少内存空间用于排序。
其默认值受操作系统影响，最小为６个Oracle块大小。
Share server模式中，该内存来自UGA。在专用服务器模式下，排序空间在PGA中.
如果没有建立large pool，UGA处于shared pool中，如果建立了large pool，UGA就处于large pool中，而PGA不在sga中，它是与每个进程对应单独存在的。
PGA：program global area,为单个进程（服务器进程或后台进程）保存数据和控制信息的内存区域。PGA与进程一一对应，且只能被起对应的进程读写，PGA在用户登录数据库创建会话的时候建立。

 SORT_AREA_RETAINED_SIZE
指定：排序完成后，如果排序区还含有需要返回给用户的排序行，则用该参数指定留给这些行的内存容量。
默认与SORT_AREA_SIZE的值一样大，最小可为２个Oracle块。（执行parallel query并行查询需要的大小可能会大于几倍的SORT_AREA_SIZE大小）
 PGA_AGGREGRATE_TARGET
在使用SORT_AREA_SIZE指定了排序内存后，就可以使用该参数指定所有用户一共最多可以使用的排序内存总大小。
默认为０，有效值为10M到4000G。
 WORKAREA_SIZE_POLICY
指定排序内存是明确管理还是隐含管理。
＝　AUTO 　　
Oracle自动管理排序区内存分配，只确保不超过PGA_AGGREGRATE_TARGET大小。
＝　MANUAL　
每个用户的排序区大小等效于SORT_AREA_SIZE大小。

5.4.1.1 测量排序
v$sort_segment
SQL>select mem.value/(mem.value+disk.value) "in-memory sort ratio"
from v$sysstat mem,v$sysstat disk
where mem.name = 'sorts (memory)'
and disk.name = 'sorts (disk)';
测量内存排序占总排序的比率―――95%以上表示正常。

5.4.1.2 优化排序
 避免使用引起排序的SQL语句
使用unicon all 代替union将避免重叠值所正常需要的排序。
对ORDER BY 和GROUP BY字段进行索引可以最小话排序。
保证所有外键被索引，减少主、外键连接排序。
CREATE INDEX .. NOSORT指定创建索引时不排序。
ANALYZE使用estimate选项代替compute选项；考虑使用analyze .. for columns命令只分析与应用有关的列。

 调整init.ora参数鼓励内存排序
增加SORT_RATE_SIZE的大小需要特别注意：太大很多用户同时排序可能会对服务器内存形成负面影响，在初始化排序之前，该内存区不会分配给会话；开始排序后，要有足够的内存区分配给排序。
在加大SORT_RATE_SIZE的同时，减少SORT_AREA_RETAINED_SIZE能帮助减少过度使用内存。
使用Parallel Query环境，将使用的内存良将会是SORT_RATE_SIZE*2*并行度。Oracle建议：1MB对于使用Parallel Query的OLTP环境是合适的。

 正确利用临时表空间，改进与排序有关的I/O读操作
创建临时表空间（多创建一些临时表空间分给不同的用户使用）：
SQL>create temporary tablespace TEMP
Tempfile ‘/u01/oradata/prod/temp01.dbf’ size 500M
Autoextend on next 50M maxsize 2000M
Extent management local
Uniform. size 64K;
指定uniform. size等于(SORT_RATE_SIZE + 1个块)* N ，使得一次I/O可以完成一个排序组块写入。

Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks FROM v$sort_segment ;
Column Description
CURRENT_USERS Number of active users
TOTAL_EXTENTS Total number of extents
USED_EXTENTS Extents currently allocated to sorts
EXTENT_HITS Number of times an unused extent was found in the pool
MAX_USED_BLOCKS Maximum number of used blocks
MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort

临时表空间的配置：
A、initial/next设置为sort_area_size的整数倍，允许额外的一个block作为segment的header
B、pctincrease=0
C、基于不同的排序需要建立多个临时表空间
D、将临时表空间文件分散到多个磁盘上

修改系统默临时表空间：
SQL>alter database default temporary tablespace temp;
指定所有系统用户的缺省临时表空间为temp;不再使用SYSTEM。

当前活动排序的用户，使用的临时表空间和排序的块数
select user,tablespace,blocks from v$sort_usage order by blocks;

排序所用表空间，当前排序用户数，最多使用的排序块数
select tablespace_name,current_users,max_sort_blocks from v$sort_segment;

直接找出当前产生排序的SQL语句：
SQL> select sess.username,sql.sql_text,sort.blocks
from v$session sess,v$sqltext sql,v$sort_usage sort
where sess.serial# = sort.session_num
and sort.sqladdr = sql.address
and sort.sqlhash = sql.hash_value
and sort.blocks > 10

5.5 UNDO
Oracle绝对禁止一个用户查看另一个用户未提交的事务数据。
启动一个DML事务时，已修改数据的象前版本被缓存在database buffer cache，再有一个缓冲副本被写入一个回退段(undo segment)上。

Undo segment三个重要目的：
１） DML用户发布rollback命令，则可用来恢复数据原状态。
２） 其他用户访问DML用户发布commit前的原数据，则提供一个已修改数据的读一致性视图。
３） 在实例恢复期间，用来rollback一个在实例故障刚发生前进行的未提交的事务。

回滚段由范围组成，这个范围由５个或５个以上的Oracle块组成。
回滚段工作方式：
１） 在一个回滚段内，以环形方式循环使用范围，直到段装满。由用户的commit或rollback命令发布后释放回滚段内的范围。
２） 同一个undo segment可以存储很多个像前版本，同时，一个像前版本只会存储在一个undo segment而不会因空间等任何问题连接到其他undo segment。
３） 如果一个undo segment中的一个范围启动了一个事务的像前版本，且逐渐增长装满了该范围，此时会环绕到下一个临近的范围继续使用空间，此时如果下一个临近范围已经被其他事务占领，则绝对不会跳过该临近范围查找其他可用范围，而是会在这个范围之间创建一个新的范围来使用。
４） 一个undo segment能处理的事务个数，取决于Oracle块大小。
５） 每个数据库都至少有一个回滚段（系统回滚段），一旦创建了其他回滚段，则该回滚段将只用于处理数据目录读一致性和事务控制。
６） set transaction use rollback segment命令可以申请一个指定的回滚段。
７） LOB列不使用undo segment，而使用创建时分配给表的空间来存储像前版本。

5.5.1 测量UNDO I/O性能
5.5.1.1 undo segment头部的争用
Oracle 使用undo segment头部块中一个事务表来跟踪使用他的那些事务，其内容通常被缓存在database buffer cache中以便被搜索。OLTP上很有可能会因为访问这个头部发生等待。

SQL> select event,total_waits,time_waited,average_wait
　from v$system_event where event like '%undo%' and event like '%slot%';
average_wait：平均每毫秒等待的次数，等于０或接近于０最好。

SQL>select class,count from v$waitstat
where class in ('undo header','system undo header');
count：等候访问undo segment头部的次数。理想情况下，该值等于０或接近于０最好。

SQL>select n.name,s.usn,
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio"
from v$rollstat s,v$rollname n
where s.usn = n.usn
order by usn;
RBS Header Get Ratio：等于１或接近１最好，至少为95%。

5.5.1.2 undo segment范围的争用
SQL>select class,count from v$waitstat
where class ='system undo block' ;
count：系统回滚段的范围的块争用次数

SQL>select w.count,w.count/s.value as wait_ratio from v$waitstat w,v$sysstat s
where w.class = 'undo block'
and s.name = 'consistent gets';
count：非系统回滚段的范围的块争用次数
wait_ratio：回滚等待率，如果超过１%，则需要调整了。

5.5.1.3 undo segment的环绕
SQL> select n.name,s.usn,
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio",s.wraps
from v$rollstat s,v$rollname n
where s.usn = n.usn
order by usn;
s.wraps：该回滚段被环绕到下一个范围的次数。次数太多表示段范围可能太小。

5.5.1.4 undo segment的动态范围分配
事务的像前版本在undo segment中发生环绕，而下一个范围已经分配给其他事务的像前版本，此时会在他们之间动态创建一个范围来给该事务的像前版本环绕。应避免以减少I/O。

SQL> select event,total_waits,time_waited,average_wait
from v$system_event where event = 'undo segment extension';
time_waited：表示动态分配的等待次数。若值很高或不断增长，说明undo segment太少或太小。

SQL>select n.name,s.usn,s.extends,
decode(s.waits,0,1,1-(s.waits/s.gets)) "RBS Header Get Ratio",s.wraps
from v$rollstat s,v$rollname n
where s.usn = n.usn
order by usn;
s.extends：被动态添加的范围数。若经常发生动态添加，则说明undo segment可能太小。

5.5.2 优化undo segment
优化目标：
１） 用户不用等待，就始终可找到undo segment来使用。
２） 用户始终能得到完成事务所需要的读一致性视图。
３） 回滚段不会引起过多的I/O。

一般就是：
１） 消除对undo segment header或block的争用。
２） 尽量最小化undo segment的扩充和环绕。
３） 避免undo segment用尽。
４） 始终拥有为用户提供一致性视图的undo segment。

做法：
１） 添加更多的undo segment 。
２） 增大现有undo segment。
３） 明确管理大事务的undo segment。
４） undo segment需求最小化。
５） 使用自动管理功能。

5.5.2.1 增加更多的undo segment
最好把新添加的undo segment放在另外的磁盘的表空间内。对undo要求最多的是delete，其次是update，最后是insert。

Oracle建议，为每４个并发事务创建一个undo segment，最多只能添加20个undo segment．

为了更准确的确定到底需要多大的undo segment，可以跟踪用户使用的undo segment大小：
//查询当前用户所使用的undo segment大小
SQL>select s.osuser,s.username,t.used_ublk
from v$session s,v$transaction t
where s.taddr = t.addr;
t.used_ublk：单位为Oracle块，＊block size等于该用户将使用的size。
//查询某个大事务的具体使用大小。
１） 只保留一个undo segment online，其他的都offline。
Alter rollback segment xxxxx offline;
２） 统计当前所使用了的undo segment大小。
Select n.name,s.sun,s.writes from v$rollname n,v$rollstat s
Where n.usn = s.usn and name != ‘SYSTEM’;
s.writes：有多少字节的数据被写到该rollback segment上了。
３） 执行遇到回滚问题的大事务。
如：delete from alarminfo;
４） 重新执行“２）”的统计，使用新查询的s.writes减去（－）“２）”中查询出来的s.writes值，就是“３）”中事务所要使用的undo segment大小.

如果设置的undo segment大小是按大事务来设置，可能会浪费很多空间，只需要明确管理undo segment就可以了。

典型错误：ORA-01555 SNAPSHOT TOO OLD
一个修改事务很长时间未提交，别人查的时候在undo segment中找到了一个像前版本得到一致性读，别人还在查询到该修改前，最先修改的人提交了，并且此时undo segment中因为接受了commit而不守护该范围，此范围被其他事务写了。
这时候就会发生这种错误，只需要在查一次就可以了。
防范：
１） 表上发生小事务时候，设法避免运行时间很长的查询。
２） 增加undo segment的大小和数量。

一般设置：initial=512k,next=512k,minextents=20，这样就会创建一个10M的undo segment．

5.5.2.2 明确管理大事务的undo segment
创建一个很大的undo segment，专用于处理特定的事务．
由于Oracle会自动把任务分配给undo segment，所以一般创建完和使用完后，需要手工把他们offline．

１） 创建大回滚段．
Create private rollback segment rbs_for_large_tran
Storage (initial 10M next 10M) tablespace rbs;
２） 直到在作业开始前，把rbs_for_large_tran　联机．
Alter rollback segment rbs_for_large_tran online;
或：execute dbms_transaction.use_rollback_segment(‘rbs_for_large_tran’);
３） 启动作业．
Delete from alarminfo;
４） 一旦＂３）＂执行完，马上在另外一个窗口把该rbs_for_large_tran段offline．
Alter rollback segment rbs_for_large_tran offline;

注意：在作业中的任何commit，都将导致rbs_for_large_tran脱机．若脱机了，需重新联机．

5.5.2.3 undo segment需求最小化
最大限度的减少写往undo segment的项目数量和大小。
如：
imp的时候使用commit=y．
exp的时候不要使用consistent选项．
Sql*loader时设置适当的commit值．

5.5.2.4 使用自动管理功能
Oracle 9i的新功能，通过配置init.ora，让Oracle自动来进行管理undo segment（Oracle推荐）。
 undo_management
=auto //使用undo 自动管理（AUM）
=manual //不使用AUM。
 undo_retention
单位是秒．指定一个像前版本在commit后被保存的时间．（减少ORA-01555错误）
 undo_suppress_errors
FALSE，TRUE．指定是否抑制RBU中可用的命令。
 undo_tablespace
指定用于AUM的表空间名．
（同一时间，只能有一个undo tablespace在线，也必须有一个undo tablespace在线．
如果数据库未创建而undo_management=auto，则系统自动创建一个SYS_UNDOTBS表空间来使用）

创建的语法：
create undo tablespace undo_tbs
datafile ‘/u01/oradata/prod/undo01.dbf’ size 500M
autoextend on
next 5M maxsize 2000M;
（不能指定初始范围和下一个范围大小，因系统要自己指定）

估计undo tablespace大小的公式：
Undo space = (undo_retention * (undo blocks per second * db_block_size)) + db_block_size;

删除一个大的undo tbs：
８） 创建一个新的undo tbs　undo_tbs02．
９） SQL>alter system setundo_tablespace=undo_tbs02;
此时，新的事务会使用undo_tbs02，而以前的事务，依然会继续使用undo_tbs．
１０） 待Undo_tbs上的所有事务commit或rollback，且超过了undo_retention指定的时间后，drop tablespace删除该undo tbs．
(此时注意，如果drop 了undo_tbs，此时任何发生在undo_tbs的像前读都要报错，此时最好发生在alter命令前的所有事物都commit了或rollback了．)

SQL>select u.begin_time,u.end_time,
t.name "undo_tbs_name",
u.undoblks "blocks_used",
u.txncount "transactions",
u.maxquerylen "longest query",
u.expblkreucnt "expired blocks"
from v$undostat u,v$tablespace t
where u.undotsn = t.ts#;

查询统计时间内，被undo使用的Oracke块数，发生的事务数，最长的查询时间，在需要查一致性时有多少块已经被覆盖（出现>0的数表示ORA-01555就很可能发生）．

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

转载于:http://blog.itpub.net/24032200/viewspace-673986/