Oracle 9i 整体性能优化概述草稿之四:调整磁盘I/O (zt)-优快云博客

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
l 搜集至数据库启动以来所发生的
确定：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所花费的最长时间（毫秒）

l 搜集某个时间以来所发生的
$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信息。

定时搜集statspackl

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性能
l 系统视图检查至数据库启动以来的数据
１）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在底效的执行写操作。

STATSPACKl
Utlbstat.sql utlestat.sqll

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参考）。
l 块头区：每个块一般使用分配给他的50到200个字节来存储关于该块内容的一些头部信息。（包括：initrans产生在表创建时指定的transaction slot，该块内含有的行的一个目录和管理该块需要的其他一般性头部信息）
l 保留区和自由区：一个块，除了分配给块头区的外，剩下的都是保留区和自由区，其中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 范围大小的确定与性能
越大的范围比越小的范围提供更好一点的性能，因扩展的次数就少了；而且可以在初始范围头部由一个统一的块-----范围地图（extent 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会把该行完全迁移放到一个新块上，而在原来的块上保留一个指向新块的指针。
对性能的影响：操作一行，至少需要读两个块。

确定行连接或行迁移l
检查的两种办法：
１） 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，都会被分配一个指定大小的内存用来排序，如果要排序的内容小于该内存，则在内存排序；如果大于该内存，则内容会

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

转载于:http://blog.itpub.net/35489/viewspace-374867/