《oracle数据库磁盘io优化》由会员分享，可在线阅读，更多相关《oracle数据库磁盘io优化（9页珍藏版）》请在金锄头文库上搜索。
1、详解 Oracle数据库磁盘 I/O优化2009-05-14 10:16 佚名 IT168 字号：T | T影响数据库性能的因素包括：系统、数据库、网络。本文将从磁盘 I/O优化的角度，讲讲如何通过磁盘优化提高 Oracle数据库性能。AD： 网+线下沙龙 | 移动 APP模式创新：给你一个做 APP的理由 监控数据库的性能 在 init.ora参数文件中设置 TIMED_STATISTICS=TRUE 和在你的会话层设置 ALTER SESSION SET STATISTICS=TRUE 。运行 svrmgrl 用 connect internal 注册，在你的应用系统正常活动期间，运行ut
2、lbstat.sql 开始统计系统活动，达到一定的时间后，执行 utlestat.sql 停止统计。统计结果将产生在 report.txt 文件中。(utlbstat.sql utlestat.sql 一般存放在$ORACLE_HOME/RDBMS/ADMIN 子目录下)优化数据库磁盘 I/O检查系统的 I/O问题在 UNIX系统中工具 sar-d能检查整个系统的 iostat(IO statistics)，在 NT系统上则使用性能监视器(Performance Monitor).反映 Oracle文件 I/O的进程使用 V$FILESTAT确定 Oracle数据文件 I/OSELECT NA
3、ME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHERE DF.FILE#=FS.FILE# ;使用分布 I/O减少磁盘竞争将数据文件和 redo log文件分开Striping 表数据分开表和索引减少与 Oracle无关的磁盘 I/O避免动态空间管理在创建如表或回滚段的数据库实体时，在数据库中会为这些数据分配空间，该空间被称为段。如果数据库操作引起数据增加并超出了分配的表空间，oracle 会扩展该段，动态扩展会降低系统性能。确定动态扩展select name,value from v$sysstat where name=recurs
4、ive calls ;分配分区确定实体的最大大小;选择存储参数值，使 Oracle分配足够大的分区，在创建实体时可以装入所有数据避免回滚段的动态空间管理回滚段大小由其存储参数所决定，回滚段必须能保存所有交易的回滚入口;使用 set transaction 命令可以为回滚段赋予交易的合适的大小;对长的查询的修改数据，应赋予大的回滚段，以保持所有的回滚入口;对 OLTP交易，由于频繁交易，每个交易只修改小量的数据，因此赋予小的回滚段。减少迁移和链接行1. 使用 ANALYZE 收集迁移和链接行的信息;2. 查询输出表：chained_rows;3. 如果有许多迁移和链接行，就需要消除迁移行，方法如
5、下：A. 创建与原表相同列的中间表，以保存迁移和链接行;B. 从原表中删除迁移和链接行;C. 将中间表中的行插入到原表中;D. 删除中间表4. 删除第一步收集的信息;5. 重新使用 ANALYZE命令查询输出表6. 在输出表中出现的行都是链接行，只能通过增加数据块的大小来清除。调整排序内存中排序使用动态表 V$SYSSTAT的信息反映排序SELECT NAME , VALUE FROM V$SYSSTAT WHERE NAME IN (SORTS(MEMORY),SORTS(DISK) ;SORTS(MEMORY)-不需要使用 I/O操作而完全在内存完成的排序数;SORTS(DISK)-需要使
6、用 I/O操作与磁盘临时段才能完成数据的排序数目。增大 SORT_AREA_SIZE以避免磁盘排序使用 NOSORT创建非排序的索引CREATE INDEX INDEX_NAME ON TABLE TABLE_NAME(COLUMN_NAME) NOSORT ;调整 Checkpoints一个 checkpoint是 oracle自动执行的一种操作，当检查点操作时，数据库中的所有缓冲区会写回磁盘，所有数据库的控制文件被更新。Checkpoint 频繁发生会加快数据库的恢复，但是增加了 I/O次数，会降低系统的性能。调整 LGWR和 DBWn I/O调整 LGWR I/O每次 I/O写的大小依赖
7、于 LOG缓冲区的大小，该大小由 LOG BUFFER 所设置，缓冲区太大会延迟写操作，太小可能导致频繁的小的 I/O操作。如果 I/O操作的平均大小很大，那么 LOG文件就会成为瓶颈，可以使用 STRIPE REDO LOG文件避免这个问题。调整 DBWN I/O使用初始参数 DB_WRITER_PROCESSES，可以创建多个数据库写进程。调整竞争由多个进程同时请求使用相同的资源时，就产生了竞争确定竞争问题视图 V$RESOURCE_LIMIT提供了一些系统资源的使用限制。如果系统存在无反应的现象，检查 V$SYSTEM_EVENT，检查最大平均等待时间的事件;如果存在过量的缓冲区等待，检
8、查 V$WAITSTAT，确定哪个类型的块有最多的等待次数和最长的等待时间，再查询 V$SESSION_WAIT得到每个缓冲区的等待时间。减少回滚段的竞争通过检查 V$WAITSTAT可以确定回滚段的竞争：SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS IN (SYSTEM UODO HEADER,SYSTEM UODO BLOCK,UODO HEADER,UODO BLOCK) ;减少调度进程的竞争检查调度进程的 busy率SELECT NETWORK”PROTOCOL”, SUM(BUSY)/(SUM(BUSY)+SUM(IDLE) “TOTA
9、L BUSY RATE” FROM V$DISPATCHER GROUP BY NETWORK ;如果指定协议的调度进程忙的时间超过 50%的有效工作时间，那么，增加调度进程可以提高使用该协议连接到 oracle的性能。检查调度进程相应队列的等待时间SELECT NETWORK “PROTOCOL” DECODE(SUM(TOTALQ),0.NO RESPONSES,SUM(WAIT)/SUM(TOTALQ)|HUNDREDTHS OF SECONDS) “AVERAGE WAIT TIME PER RESPONSE” FROM V$QUEUE Q,V$DISPATCHER D WHERE Q
10、.TYPE=DISPATCHER AND Q.PADDR=D.PADDR GROUP BY NETWORK ;增加调度进程：使用 MTS_DISPATCHERS参数和 ALTER_SYSTEM命令可以增加调度进程减少共享服务器进程的竞争共享服务器进程竞争可以由不断增加的请求等待时间所反映，使用如下查询：select decode(totalq,0,No Requests, wait/totalq|hundredths of seconds) “Average Wait Time Per Requests” from v$queue where type=COMMON ;使用如下查询可以得到当前
11、运行的共享服务进程数：select count(*) “Shared Server Processes” from V$shared_servers where status!=QUIT;oracle能自动增加共享服务进程，但是 MTS_MAX_SERVERS的值可以更改。减少 redo log缓冲区 latches竞争在 LGWR进程将 redo入口从 redo log缓冲区写入 redo log文件后，该入口就会被新入口覆盖，供其他log的使用。V$SYSSTAT中 redo buffer allocation retries 反映用户进程等待 redo log空间的次数：Select n
12、ame,value from v$sysstat where name=redo buffer allocation retries ;redo buffer allocation retries的值应该接近 0，如果该值持续增加，那么，说明进程需要等待缓冲区的空间。增大参数 LOG_BUFFER的值可以增大 redo log的大小。确定 redo log缓冲区 latches竞争redo分配 latch;redo复制 latches。一次只能有一个用户分配缓冲区中的空间，在分配了 redo入口的空间后，用户进程将入口复制到缓冲区，其最大大小是由 LOG_SMALL_ENTRY_MAX_SIZ
13、E指定。Redo 复制 latches的数目由参数LOG_SIMULTANEOUS_COPIES指定。检查 redo log活动对 redo log缓冲区的频繁访问可能导致 redo log缓冲区 latches竞争，降低系统性能。Oracle 在动态表 V$LATCH中收集了所有 LATCH的统计信息。其中：表 v$latch反映 willing-to-wait 请求的列gets-成功的 willing-to-wait请求数;misses-初始不成功的 willing-to-wait请求数;sleeps-请求不成功的等待时间;表 v$latch反映 immediate请求的列：immedia
14、te gets-成功的 immediate请求数immediate misses-不成功的 immediate请求数使用如下查询：select ln.name,gets,misses,immediate_gets,immediate_misses from v$latch l,v$latchname ln where ln.name in (redo allocation ,redo copy) and ln.latch#=l.latch# ;可以计算出各类请求的等待率。减少 latch竞争要减少 redo allocation latch竞争，必须减少单个进程占用 latch的时间。要减少这
15、个时间，可以减少该 redo allocation latch的复制。减少 LOG_SMALL_ENTRY_MAX_SIZE初始参数可以减少在 redo allocation latch的 redo入口的复制次数和大小。减少 redo copy Latches竞争可以用增加 LOG_SIMULTANEOUS_COPIES的值来增加 LATCH数，最多可以达到CPU的两倍。减少 Free List竞争确定 Free List竞争，可以使用以下几步：1. 检查 V$WAITSTAT，确定 DATA BLOCKS的竞争;2. 检查 V$SYSTEM_EVENT,确定 BUFFER BUSY WAIT
16、S，如果数值高，表明存在竞争;3. 在这种情况下，检查 V$SESSION_WAIT查询每个缓冲区的忙等待、FILE、BLOCK 及 ID;4. 使用如下查询得到实体和 FREE LIST的名称：SELECT SEGMENT_NAME,SEGMENT_TYPE FROM DBA_EXTENTS WHERE FILE_ID=file AND BLOCK BETWEEN block_id AND block_id+blocks ;5. 使用如下查询找到 FREE LIST:SELECT SEGMENT_NAME,FREELISTS FROM DBA_SEGMENTS WHERE SEGMENT_NAME=segment AND SEGMENT_TYPE=type ;增加 Free List要减少表的 Free list的竞争， 可以使用 freelists参数重新创建表，方法有：1. 删除旧表，重新创建表;2. 通过从旧表选择数据插入到新表，
