Oracle性能优化调整--调整磁盘I/O-技术开发专区

Oracle性能优化调整--调整磁盘I/O

作者：小毛头编辑：阿雪 2007-07-19 15:48

【IT168技术文档】
一.虽然通过调整SGA组件可以最大限度地减少物理I/O的发生

但是我们在某些活动中还是不可避免的将会产生磁盘I/O。需要注意的是在内存中的I/O活动是逻辑的，在磁盘上的I/O活动是物理的。逻辑I/O比物理I/O快数千倍。

二.产生磁盘I/O的来源：

1.Database Writer(DBWO)--写Database Buffer Cache中的缓存区到数据库中的数据文件。

2.Database Writer(DBWO)--写数据到回退段以维护读一致性

3.用户Server Process--读取数据块到Database Buffer Cache中

4.Log Writer(LGWR)--从Redo Log Buffer中写事务恢复信息到Online Redo Log上

5.Archiver(ARC0)--读取重做日志的内容，并写到存档目标上

6.临时的大型排序写盘操作

三.调整物理I/O的2个目标：

1.适当的SGA大小

2.尽可能快地执行物理I/O

四.提高磁盘I/O需要调整的数据库物理结构

1.数据文件

2.DBWO

3.单个段数据块

4.排序操作和临时段

5.回退段

五.调整表空间与数据文件

select s.FILE#||'D' "FILE#+TYPE" , d.NAME "DATAFILE",
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#||'T' "FILE#+TYPE" , t.NAME "DATAFILE",
s.PHYRDS,s.PHYWRTS,s.AVGIOTIM,s.MINIOTIM,s.MAXIOWTM,s.MAXIORTM
from v$filestat s,v$tempfile t
where s.FILE#=t.FILE#
order by 1

PHYRDS--该数据文件上发生的物理读次数

PHYWRTS--该数据文件上发生的物理写次数

AVGIOTIM--平均花费的I/O时间

MINIOTIM--花费的最短时间

MAXIOWTM--写操作花费的最长时间

MAXIORTM--读操作花费的最长时间

我们能够做的工作就是

(1)不要将非Oracle文件放在与Oracle文件相同的磁盘上，避免引起对磁盘资源的潜在争用!

(2)创建局部管理表空间(LMT Locally Managed Tablespace)

(3)均衡数据文件I/O--不要把数据库的段集中放在某一个表空间，如:system.另外如果可能的话，将高频率的I/O数据文件放在不同的磁盘或者控制器上面

(4)调整init.ora参数的DB_FILE_MULTIBLOCK_READ_COUNT--指定一个用户Server Process在执行全表扫描的时候，读取的最多数据块个数，默认是8

select name,value from v$sysstat
where name='table scans (long tables)'

--如果统计数据很大，说明经常执行全表扫描，这样的话增大这个参数是有收获的

六.调整DBW0的性能

DBW0是负责写Database Buffer Cache中的缓存区到数据库中的数据文件。

由于数据文件可能驻留在一个速度比较慢的设备上或者DBW0写脏缓冲区的效率不高，早成了等待事件。

 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' )

--如果发现等待次数过多，可能就需要调整

(1)参数DBWR_IO_SLAVES --启动Database Writer从属进程默认0

(2)参数DB_WRITER_PROCESSES --启动额外Database Writer进程与DBWR_IO_SLAVES相比可以DBWO的全部功能默认1，最大10

七.调整段I/O

ORACLE存储体系结构

--数据库包含一个或多个表空间

--表空间使用数据文件来存储段

--段由一个或多个范围组成

--范围由连续的ORACLE块组成

--ORACLE块由连续的操作系统块组成

(1)块

SYSTEM和TEMP表空间使用的是主块,由DB_BLOCK_SIZE参数设定

其他表空间可以使用BLOCKSIZE关键字指定块的大小，不指定就默认为主块的大小

(2)范围

create table col_cust
(
cust_id varchar2(20),
cust_name varchar2(20)
)
PCTFREE 20 PCTUSED 30
INITTRANS 5
STORAGE (initial 250K
next 250K
pctincrease 0
freelists 1)
TABLESPACE aap1_data;

--上面那段SQL中,范围=32块*8KB/块=250K

PCTFREE 20--每个块预留20%的空间用于保存行更新信息

(3)不要将应用程序的表和索引放在system表空间中。

(4)设置合理的范围大小通过参数DB_FILE_MULTIBLOCK_READ_COUNT

大范围的缺点是表空间的空间可能被浪费，但是读取I/O的次数就可能会减少

(5)设置合理的块大小

八.调整排序I/O

会导致排序的操作

--ORDER BY

--GROUP BY

--SELECT DISTINCT

--UNION

--INTERSECT

--MINUS

--ANALYZE

--CREATE INDEX

(1)尽量在内存中完成排序

SORT_AREA_SIZE(不推荐更改默认值)

SORT_AREA_RETAINED_SIZE

PGA_AGGREGRATE_TAGET

WORKAREA_SIZE_POLICY

select mem.NAME,mem.VALUE /(disk.VALUE+mem.VALUE) "In-memory Sort Ratio"
from v$sysstat mem,v$sysstat disk
where mem.NAME='sorts (memory)'
and disk.NAME='sorts (disk)'

建议95%以上都在内存中排序

(2)减少或者避免磁盘排序

(a)UNION ALL取代UNION 避免INTERSECT/MINUS/DISTINCT

(b)对ORDER BY和GROUP BY所引用的列尽量使用index

(c)CREATE INDEX时增加NOSORT选项

(3)使用临时表空间

使用下面这条语句可以查询一个表空间是临时还是永久的

select tablespace_name,contents from dba_tablespaces

把一个临时表空间指定为所有用户的默认临时表空间

alter database default temporary tablespace temp;

用户默认的临时表空间是SYSTEM

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>200;

上述语句可以查询引起大排序的SQL语句，然后可以通过建立索引或者重写语句来消除大排序

九.调整回退段I/O

当用户启动一个DML的事务时，以修改数据的像前版本被缓存在SGA的Database Buffer Cache中。这些缓冲区的副本也被写到一个回退段上。ORACLE利用回退段(rollback segment)来存储应用程序用户执行DML操作所产生的以修改数据的像前版本。该回退段被存储在一个表空间中。回退段也叫做撤销段(undo segment).像前版本的目的：
1.ROLLBACK时恢复原始数据
2.COMMIT以前，提供其他用户访问原数据，提供以修改数据的读一致性
3.当实例发生故障时，撤销还未提交的事务

关注我们