oracle数据库优化(16页).doc

《oracle数据库优化(16页).doc》由会员分享，可在线阅读，更多相关《oracle数据库优化(16页).doc（16页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、-oracle数据库优化-第 16 页目录目录1数据库的优化2概述2监控数据库的性能：2优化数据库磁盘I/O2检查系统的I/O问题2使用分布I/O减少磁盘竞争2避免动态空间管理2调整排序3调整Checkpoints3调整LGWR和DBWn I/O4建立和优化数据库文件的方针：6监控磁盘I/O的方法：6优化回滚段7检测回滚段争用：7通过以下公式计算等待比率：7若任何一个的比率大于1则建议再创一个回滚段：7避免动态分配空间7优化Redo日志8检测Redo日志缓冲区锁存：8减少Redo日志转储I/O争用：81.优化系统全局区（SGA）8调整操作系统的内存需求8调整redo log缓冲区8调整私有sq

2、l和pl/sql区8调整共享池9调整快速缓存10调整多缓冲池12调整排序区13重新分配内存13减少整个内存使用13优化数据字典高速缓存：13优化游标：13注释：以上增加SGA分配的调整以SGA不被换出实存为限，否则SGA部分换出实存反而降低Oracle性能。14优化数据库对象：14管理表的动态分配。14优化索引存储参数。14数据库的优化概述影响数据库性能的因素包括：系统、数据库、网络。数据库的优化包括：优化数据库磁盘I/O、优化回滚段、优化Rrdo日志、优化系统全局区、优化数据库对象。监控数据库的性能：在init.ora参数文件中设置TIMED_STATISTICS=TRUE 和在你的会话层设

3、置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系统中工具sard能检查整个系统的iostat（IO statistics），在NT系统上则使用性能监视器（P

4、erformance Monitor）.反映oracle文件I/O的进程文件进程LGWRDBWNARCHSMONPMONCKPTFore_groundPQ Slave数据库文件YYYYYYLog文件Y归档文件Y控制文件YYYYYYYY使用V$FILESTAT确定oracle数据文件I/OSELECT NAME,PHYRDS,PHYWRTS FROM V$DATAFILE DF,V$FILESTAT FS WHERE DF.FILE#=FS.FILE# ;使用分布I/O减少磁盘竞争l 将数据文件和redo log文件分开l Striping 表数据l 分开表和索引l 减少与oracle无关的磁盘

5、I/O避免动态空间管理在创建如表或回滚段的数据库实体时，在数据库中会为这些数据分配空间，该空间被称为段。如果数据库操作引起数据增加并超出了分配的表空间，oracle会扩展该段，动态扩展会降低系统性能。l 确定动态扩展select name,value from v$sysstat where name=recursive calls ;l 分配分区确定实体的最大大小；选择存储参数值，使oracle分配足够大的分区，在创建实体时可以装入所有数据l 避免回滚段的动态空间管理回滚段大小由其存储参数所决定，回滚段必须能保存所有交易的回滚入口；使用set transaction 命令可以为回滚段赋予交易

6、的合适的大小；对长的查询的修改数据，应赋予大的回滚段，以保持所有的回滚入口；对OLTP交易，由于频繁交易，每个交易只修改小量的数据，因此赋予小的回滚段。l 减少迁移和链接行1. 使用ANALYZE 收集迁移和链接行的信息；2. 查询输出表：chained_rows;3. 如果有许多迁移和链接行，就需要消除迁移行，方法如下：A. 创建与原表相同列的中间表，以保存迁移和链接行；B. 从原表中删除迁移和链接行；C. 将中间表中的行插入到原表中；D. 删除中间表4. 删除第一步收集的信息；5. 重新使用ANALYZE命令查询输出表6. 在输出表中出现的行都是链接行，只能通过增加数据块的大小来清除。调整

7、排序内存中排序使用动态表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一个ch

8、eckpoint是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，可以创建多

9、个数据库写进程。调整竞争由多个进程同时请求使用相同的资源时，就产生了竞争确定竞争问题视图V$RESOURCE_LIMIT提供了一些系统资源的使用限制。如果系统存在无反应的现象，检查V$SYSTEM_EVENT，检查最大平均等待时间的事件；如果存在过量的缓冲区等待，检查V$WAITSTAT，确定哪个类型的块有最多的等待次数和最长的等待时间，再查询V$SESSION_WAIT得到每个缓冲区的等待时间。减少回滚段的竞争通过检查V$WAITSTAT可以确定回滚段的竞争：SELECT CLASS,COUNT FROM V$WAITSTAT WHERE CLASS IN (SYSTEM UODO HEAD

10、ER,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

11、 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,N

12、o 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入口从red

13、o 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

14、竞争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

15、-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.

16、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.

17、检查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:

18、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日志和

19、回滚段放在不同的磁盘上。将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/Or

20、aHome/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 27

21、 rows selected.优化回滚段检测回滚段争用：select class,count from v$waitstatwhere class in (undo header,undo block,system undo header,system undo bolck)CLASS COUNTsystem undo header 0undo header 0undo block 0select sum(value) from v$sysstat where name in (consistent gets,db block gets) SUM(VALUE) 20589 通过以下公式计算等待

22、比率：system header waits = system undo header / total reads system block waits = system block / totalreadsrollback header waits = undo header / total reads rollback block waits = undo block / total reads若任何一个的比率大于1则建议再创一个回滚段：create rollback segment rbs21 tablespace rbs storage (inittial 10k optimal 20

23、k next 10k maxextents 8) ;避免动态分配空间用以下语句检查回滚段的动态分配：select name,shrinks from v$rollstat,v$rollname where v$rollstat.usn=v$roll ;NAME SHRINKSSYSTEM 0RBS0 100RBS1 1若动态分配次数较多可增大回滚段的初始容量。ALTER ROLLBACK SEGMENT RBS0 STORAGE (inittial 20k optimal 40k next 10k maxextents 8) ;优化Redo日志检测Redo日志缓冲区锁存：select name

24、,value from v$sysstatwhere name = rado log space requests ;value值应接近0若较大则应加大INITXXX.ORA 中的LOG_BUFFER项的值。减少Redo日志转储I/O争用：将Redo日志分布在不同磁盘中。1. 优化系统全局区（SGA）调整操作系统的内存需求l 减少页的换入换出；l 将SGA置于主存之中 使用SGA的主要目的是为了在内存中存储数据，以利于快速访问。通过设置初始化参数PRE_PAGE_SGA=YES，在数据库启动时，可以将整个SGA读入内存，这样会减少在启动后ORACLE达到全部性能的总的时间。使用如下命令可以查看

25、SGA所分配的内存以及其内部的结构：SVRMGR SHOW SGATotal System Global Area 107720688 bytesFixed Size 69616 bytesVariable Size 90701824 bytesDatabase Buffers 16777216 bytesRedo Buffers 172032 bytesl 为单个用户分配足够的内存调整redo log缓冲区 参数LOG_BUFFER指定了REDO LOG的缓冲区的保留大小。LOG写进程（LGWR）在该缓冲区被填充时总是运行的，在新的LOG进入缓冲区时，原来的LOG应已经写入磁盘。调整私有sq

26、l和pl/sql区l 标识不必要的语法分析调用1. 在sql跟踪工具有效时运行应用2. 查看视图V$SQLAREA viewSVRMGR select sql_text,parse_calls,executions from v$sqlarea ;如果parse_calls值接近execution值，可能就是不断地对sql语句进行语法分析3. 执行如下查询：select *from v$statname where name in (parse_count(hard),execute_count);其结果类似于:statistic#, name100 parse_count90 execute

27、_count然后执行如下查询：select * from v$sesstat where statistics# in(90,100) order by value , sid ;l 减少不必要的语法分析调用初始化参数open_cursors的最大值依赖于操作系统，最少值为5调整共享池数据字典或库快存的没有命中，其开销大大多于缓冲快存的没有命中，因此，首先应该为共享池分配足够的空间。使用如下语句可以确定库快存和数据字典快存的命中率：select（sum（pins-reloads）/sum（pins）“Lib Cache”from v$librarycache ；select （sum（gets

28、-getmisses-usage-fixed）/sum（gets）“Row Cache”from v$librarycache;共享池中的自由内存可以查看：select * from v$sgastat where name=free memory;当然，如果共享池满了并不一定存在问题，只要上面所说的比率接近于1，就不需要增加共享池大小；如果自由内存接近于0而且库快存或数据字典快存的命中率小于0.95，那么需要增加共享池的大小。l 调整Library Cache1 检查库快存的活动select sum(pins) “Executions”,sum(reloads)”Cache Misses w

29、hile Executing” from v$librarycache ;“Executions”列指明sql语句，pl/sql块和实体定义被访问执行了的次数，”Cache Misses while Executing”指明其中没有命中的次数。2 减少库快存的非命中1、 分配更多的库快存（可以增加初始化参数shared pool size的值；为了利用增加的共享sql区，增加初始化参数open cursors的值）；2、 尽可能使用标准的sql语句（sql语句或pl/sql块必须一致，包括字符和空格；sql语句或pl/sql块中对schema实体的引用必须解析到同一schema的同一对象；sq

30、l语句中试用的变量的名字和数据类型必须匹配；sql语句必须使用相同的优化方法和优化目标）；尽可能使用标准的sql语句，策略：l 语句中尽量使用变量而不要使用常量l 确保应用用户不会改变优化方法和目标l 标准化的变量命名和空格转换l 尽可能使用存储过程3、 使用cursor_space_for_time加速共享sql区的访问：该参数指定是否共享sql区可以释放，如果为false（默认值），一个共享sql区就可以被释放；如果为true，一个共享的sql区只存在所有与其相关的游标关闭后才可以被释放。如果库快存在语句执行时有非命中，不要将其设置为true，否则对新的sql语句将没有空间。l 调整Dat

31、a Dictionary Cache1、 监视数据字典快存的活动select sum(gets) “Data Dictionary Gets”, sum(getmisses) “Data Dictionary Cache Get Misses” from v$rowcache ;2、 减少数据字典快存的非命中对频繁访问的数据字典快存没命中和命中比应少于1015。要增加数据字典快存可用的内存数，需要增加初始参数shared_pool_size的值。l 调整多线程服务器的共享池查询动态表v$sesstat可以确定会话信息的大小：select sum(value)|bytes”Total memor

32、y for all sessions” from v$sesstat, v$statname where name=session uga memort and v$sesstat statistic#=v$statname.statistic#;显示信息如下：Total memory for all sessions157125 bytes 结果指明当前分配给所有会话的内存。l 调整共享池的保留空间shared_pool_reserved_size为大的存储保留的shared_pool_size总量；shared_pool_reserved_min_alloc控制保留内存的分配；要创建一个保

33、留列表，shared_pool_reserved_size必须大于shared_pool_reserved_min_alloc。优化共享池：用以下语句检测命中率：select sum(pins)pinned,sum(reloads)reloaded from v$librarycache pinned reloaded 2474 17命中率pinned/ reloaded若命中率低可增大INIT.ORA中SHARED_POOL_SIZE的值。调整快速缓存l 利用缓存的命中率评价快速缓存的活动select name,value from v$sysstat where name in (db b

34、lock gets,consistent_gets,physical reads);db block gets, consistent_gets其和为数据请求总数，physical reads为数据请求导致访问磁盘数据文件的总数。增加db_block_buffers应该遵循如下规则： 缓存命中率低于0.9； 前面增加的db_block_buffers有效l 提高缓冲区命中率如果缓冲命中率低，如小于60或70，那么需要增加缓冲区中的缓存数。方法是增大db_block_buffers的值。表v$recent_bucket的每一行反映在缓存中增加一个缓冲区的相应性能值，其中rownum列比可能增加到

35、缓存的缓冲区数小1；count列通过增加缓冲区数rownum1到缓存，增加的缓冲区命中。查询v$recent_bucket视图：select sum(count) ach from v$recent_bucket where rownum=90 ;计算命中率（ACM为增加的非命中次数）：Hit Ratio=1-(physical reads + ACM/(db block gets+consistent gets);也可以将100个缓冲区分为4个25个缓冲区段：select 25*TRUNC(ROWNUM/25)+1|to|25*(TRUNC(ROWNUM/25+1)”Interval”,su

36、m(count)”Buffer Cache Hits” from v$CURRENT_BUCKET where rownum0 group by TRUNC(ROWNUM/25);其中INTERVAL为缓冲区段，BUFFER CACHE HITS为对应段的缓存命中率。优化数据库缓冲区高速缓存:select name,value from v$sysstat where name in (db block gets,consistent gets,physical reads) ;NAME VALUEdb block gets 3700consistent gets 17603physical

37、reads 523计算公式：Hit Ratio = 1-(physical reads / ( db block gets + consisten gets) 若命中率低可以加大INITXXX.ORA中DB_BLOCK_BUFFER的值。调整多缓冲池l 多缓冲池特性概述可以使用keep缓冲池来维护缓存中的实体，使用recycle缓冲池来防止实体占用缓存中不必要的空间；可以将大的数据段置于独立的recycle缓存中，将小的数据段置于独立的keep缓存中。l 何时使用多缓冲池1 查找oracle内部实体数据段的数目：select data_object_id,object_type from us

38、er_objects where object_name= ;2 查找对应该段名的缓存中的缓冲区数：select count(*) buffers from x$bh where obj= ;3 查找实例中的缓冲区总数：select value “total buffers ” from v$parameter where name=db_block_buffers ;4 计算当前有指定段使用的缓存所占的比率:%cache used by segment_name=buffers(step 2)/buffers(step 3);l 使用多缓冲池调节缓存减少I/O操作；隔离缓存中的实体；限制实体

39、在缓存中的一个部分l 使多缓冲池生效定义新缓冲池：使用初始参数BUFFER_POOL_NAME来定义缓冲池，每个缓冲池可以指定两个属性：缓冲池中的缓冲区数；分配该缓冲池的LRU latches数。用于定义缓冲池的初始参数有：BUFFER_POOL_KEEP定义keep缓冲池BUFFER_POOL_RECYCLE定义recycle缓冲池DB_BLOCK_BUFFERS定义数据库实例的缓冲区数DB_BLOCK_LRU_LATCHES定义数据库实例的LRU latches数Oracle8定义了三个缓冲池：keep，recycle和default。其中default缓冲池是已经存在的。l 使用多缓冲池

40、为一个实体建立一个默认缓冲池，语法为：BUFFER_POOLKEEP | RECYCLE | DEFAULTl 如何为各缓冲池设置大小1. KEEP缓冲池使用keep缓冲池的目的是将实体保留在内存，避免I/O操作select physical_reads,block_gets,consistent_gets from v$buffer_pool statistics where name=KEEP;计算缓冲池命中率的公式：Hit Ratio=1-physical_reads/(block_gets+consistent_gets)2. Recycle缓冲池使用recycle缓冲池的目的是为了清

41、除内存中的不再使用的数据块，如果“free buffer waits”统计数总是很高，可以确定recycle缓冲池过小：确定recycle缓冲池大小的办法：使recycle缓冲池失效运行系通到稳定状态，查看default缓冲池中由本来需要置于recycle缓冲池中数据段的缓冲区数，除以4，该结果就可以用作recycle缓冲池的大小。3. 确定数据段置于keep还是recycle缓冲池对于至少两倍DEFAULT缓冲池大小，并且占用系统整个I/O的一定百分比的数据段，那么最好放置于recycle缓冲池；对于大小小于DEFAULT缓冲池的10，并且占用至少系统整个I/O的1的数据段，那么最好放置于k

42、eep缓冲池；如果对表空间超过一个段时，可以通过查询V$SESSION_WAIT确定每个段的I/O操作。l 如何识别和减少LRU Latch竞争 LRU latches规划了那些缓存中最近最少使用的缓冲区列表，使用DB_BLOCK_LRU_LATCHES参数可以设置数据库实例中的latches总数。可以通过如下语句确定系统中是否有latch竞争：select child#,sleeps/gets ratio from v$latch_children where name=cache buffers lru chain ;每个LRU latch的非命中率应少于1，任何大于1的latch说明存在竞争，通过如下语句查找出来：select name from v$buffer_pool_statistics where lo_setid=child_latch_numb