您现在的位置: 破洛洛 >> 建站学院 >> 网站开发 >> Oracle教程 >> 正文

Oracle数据库的优化之数据库磁盘I/O

作者:佚名 来源:CNZZ 更新时间:2011-9-19
分享到
概述

  影响数据库性能的因素包括:系统、数据库、网络。

  数据库的优化包括:优化数据库磁盘I/O、优化回滚段、优化Rrdo日志、优化系统全局区、优化数据库对象。

  监控数据库的性能

  在init.ora参数文件中设置TIMED_STATISTICS=TRUE 和在你的会话层设置ALTER SESSION SET STATISTICS=TRUE 。运行svrmgrl 用 connect internal 注册,在你的应用系统正常活动期间,运行utlbstat.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/O

  SELECT NAME,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=’recursive calls’ ;

   分配分区

  确定实体的最大大小;

  选择存储参数值,使oracle分配足够大的分区,在创建实体时可以装入所有数据

   避免回滚段的动态空间管理

  回滚段大小由其存储参数所决定,回滚段必须能保存所有交易的回滚入口;

  使用set transaction 命令可以为回滚段赋予交易的合适的大小;

  对长的查询的修改数据,应赋予大的回滚段,以保持所有的回滚入口;

  对OLTP交易,由于频繁交易,每个交易只修改小量的数据,因此赋予小的回滚段。

   减少迁移和链接行

  1. 使用ANALYZE 收集迁移和链接行的信息;

  2. 查询输出表:chained_rows;

  3. 如果有许多迁移和链接行,就需要消除迁移行,方法如下:

  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)-需要使用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写的大小依赖于LOG缓冲区的大小,该大小由LOG BUFFER 所设置,缓冲区太大会延迟写操作,太小可能导致频繁的小的I/O操作。如果I/O操作的平均大小很大,那么LOG文件就会成为瓶颈,可以使用STRIPE REDO LOG文件避免这个问题。

  调整DBWN I/O

  使用初始参数DB_WRITER_PROCESSES,可以创建多个数据库写进程

调整竞争

  由多个进程同时请求使用相同的资源时,就产生了竞争

  确定竞争问题

  视图V$RESOURCE_LIMIT提供了一些系统资源的使用限制。

  如果系统存在无反应的现象,检查V$SYSTEM_EVENT,检查最大平均等待时间的事件;

  如果存在过量的缓冲区等待,检查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)) “TOTAL 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.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’ ;

  使用如下查询可以得到当前运行的共享服务进程数:

  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 name,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_SIZE指定。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请求的列:

  immediate 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的时间。要减少这个时间,可以减少该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 WAITS,如果数值高,表明存在竞争;

  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. 通过从旧表选择数据插入到新表,删除旧表,重命名,完成表的重新创建;

  3. 使用Export 和Import,export旧表,删除旧表,import该表。

  建立和优化数据库文件的方针:

  为表和索引创建不同的表空间。

  将表和索引的表空间放在不同的磁盘上。

  将REDO日志和回滚段放在不同的磁盘上。

  将Oracle可执行文件和数据库文件放在单独的磁盘上。

  确定最常用的表、索引,以及他们的表空间,并放在单独的磁盘上。

  不要在Oracle数据磁盘上安装其他第三方软件。

  监控磁盘I/O的方法:

  用以下查询语句可以得到各表空间读写次数,phyrds+phywrts 即是磁盘I/O量。应按前面讲的方针调整数据文件的分布方式。

  select name,phyrds,phywrts from v$datafile,v$filestat where v$datafile.file# = v$filestat.file#

  结果如下:

  NAME PHYRDS PHYWRTS

  -------------------------------------------------- ---------- ----------

  /u/oracle/OraHome/oradata/pb/system01.dbf 2511 8

  /u/oracle/OraHome/oradata/pb/tools01.dbf 5 2

  /u/oracle/OraHome/oradata/pb/rbs01.dbf 18 20

  /u/oracle/OraHome/oradata/pb/temp01.dbf 4 2

  /u/oracle/OraHome/oradata/pb/users01.dbf 45 5

  /u/oracle/OraHome/oradata/pb/indx01.dbf 4 2

  /u/oracle/OraHome/oradata/pb/drsys01.dbf 4 2

  7 rows selected.

转载请注明:破洛洛(谢谢合作)
网友评论: