Oracle公司的调优实例.ppt

《Oracle公司的调优实例.ppt》由会员分享，可在线阅读，更多相关《Oracle公司的调优实例.ppt（116页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、性能性能调整整时间:2004.06.23计划任务模块调整计划任务模块调整计划任划任务(现状状)现在的解决方法小表：采用了snapshot的方法大表：KHTKHXL0和KHTXLJG0采用了通过dblink插入的方式db_link_xx分别是不同的服务器，每个服务器的连接有2M带宽的限制。目前串行运行所有的表没有进行Statistics Collection南平地区县市1县市2县市n计划任划任务(现状状)12xxKHTKHXL0的索引的索引KHTKHXL0_GSKHNYJYGSDM00,KHDM00,NYUE00,JYDM00GSDM00只有一个值NYUE00只有一个值KHTKHXL0_KHDM

2、KHDM00表表KHTKHXL0及索引大小及索引大小SEGMENT_NAME SUM(BYTES)/1048576-KHTKHXL0 13.9453125KHTKHXL0_GSKHNYJY 13.4765625KHTKHXL0_KHDM 6.2890625KHTXLJG0的索引的索引KHTXLJG_KH_SP_INDEXGSDM00,KHDM00,SPDM00GSDM00只有一个值KHTXLJG0_KHDM_INDEXKHDM00KHTXLJG0_SPDM_INDEXSPDM00表表KHTXLJG0及索引大小及索引大小SEGMENT_NAME SUM(BYTES)/1048576-KHTXLJ

3、G0 143.554688KHTXLJG0_KHDM_INDEX 69.1796875KHTXLJG0_SPDM_INDEX 57.5390625KHTXLJG_KH_SP_INDEX 130.195313时间测试使用SET ECHO ONSET TIMING ONSPOOL 文件名.log结果南平地区：一般12分钟计划任划任务(调整思路整思路)采用并行方式KHTKHXL0和KHTXLJG0使用并行方式(union all)进行插入将表空间变成Local Management方式，可以降低recursive sql 的调用试次，有利于提高insert的速度建议用分区表和分区索引。部分索引需要调

4、整建建db link在crm用户下建好指向各个地市的db link调整open_links的参数为16建建LOCAL表空间表空间CREATE TABLESPACE CRM_DATA_TESTDATAFILE EXTENT MANAGEMENT LOCALUNIFORM SIZE 2MCREATE TABLESPACE CRM_INDX_TESTDATAFILE EXTENT MANAGEMENT LOCALUNIFORM SIZE 1M建分区表建分区表PARTITION BY RANGE(KHDM00)(PARTITION P900 VALUES LESS THAN(901),PARTITIO

5、N P902 VALUES LESS THAN(903),PARTITION P903 VALUES LESS THAN(904),PARTITION P921 VALUES LESS THAN(922),PARTITION P922 VALUES LESS THAN(923),PARTITION P923 VALUES LESS THAN(924),PARTITION P924 VALUES LESS THAN(927),PARTITION P927 VALUES LESS THAN(928),PARTITION P928 VALUES LESS THAN(929),PARTITION P9

6、29 VALUES LESS THAN(930)TABLESPACE CRM_DATA_TEST;KHTXLJG0INSERT/*+append*/INTO CRM.khtxljg0NOLOGGINGSELECT/*+FULL(a)*/135021000000,900|SUBSTRB(a.khdm00,4,9),a.spdm00,a.zgxl00,a.zdxl00,a.yzgxl0,a.yzdxl0,a.jycljg,a.zzs000FROM CRM.khtxljg0db_link_01 aUNION ALLSELECT/*+FULL(a)*/135021000000,902|SUBSTRB(

7、a.khdm00,4,9),a.spdm00,a.zgxl00,a.zdxl00,a.yzgxl0,a.yzdxl0,a.jycljg,a.zzs000FROM CRM.khtxljg0db_link_02 aUNION ALLKHTKHXL0INSERT/*+append*/INTO CRM.KHTKHXL0NOLOGGING SELECT/*+FULL(a)*/135021000000,928|SUBSTRB(KHDM00,4,9),NYUE00,JYDM00,RXL000,DPZXL0,YXL000,SYYXL0,DPZYGL FROM CRM.KHTKHXL0DB_LINK_09 a

8、WHERE NYUE00=TO_CHAR(SYSDATE,YYYYMM)UNION ALLSELECT/*+FULL(a)*/135021000000,929|SUBSTRB(KHDM00,4,9),NYUE00,JYDM00,RXL000,DPZXL0,YXL000,SYYXL0,DPZYGL FROM CRM.KHTKHXL0DB_LINK_10 a WHERE NYUE00=TO_CHAR(SYSDATE,YYYYMM);UNION ALL调整索引整索引KHTKHXL0KHTKHXL0_KHJYDM(KHDM00,JYDM00)KHTXLJG0KHTXLJG0_KHSPDM(KHDM00

9、,SPDM00)采用分区(Local Prefixed)方式采用索引组织表创建索引组织表创建索引组织表Create table name(col1 varchar2(10)not null,)Organization indexTablespace.时间测试使用SELECT SYSDATE FROM DUAL;SET ECHO ONSET TIMING ONSPOOL 文件名.log结果南平地区：一般9分钟主要是取KHTXLJG0时节约了约3分钟调整总结调整总结调整难点(在v$system_event中查询)事件 SQL*Net more data from dblinkSELECT EVEN

10、T,TOTAL_WAITS,TIME_WAITEDFROM V$SESSION_EVENTWHERE TOTAL_WAITS0 AND SID=(SELECT DISTINCT SID FROM V$MYSTAT)用crm,crm1,crm01,crm02,crm03,crm04,crm05,crm06,crm07,crm08,crm09,crm10定期进行5%的Statistics Collection建议使用LOCAL表空间，以降低Recursive SQL的调用，采用分区表，合理选择索引的列和顺序服务器调整服务器调整调整工具调整工具Windows性能管理器，任务管理器Unixtopas，

11、vmstat，sar服服务器端器端调优Page IN/OUT的情况CPU的使用情况DISK I/O信息Alert.log中的信息$ORACLE_HOME/admin/bdumpSTATSPACK内存内存(南平南平)总共2G物理内存Oracle的SGA为1.1G，使用pre_page_sga参数常驻内存总连接数为30左右，峰值时内存为1.5G总结：内存方面不存在问题CPU(南平南平)显示有4个CPU一般利用率在20%左右总结：CPU方面不存在问题DISK I/O(南平南平)只有一个60G的硬盘操作系统、Oracle程序、数据库所有的文件在一个物理磁盘上总结：DISK I/O方面不够理想，应当选择

12、多个小硬盘。DISK I/O(福州福州)通过topas和sar观查disk2非常忙通过v$filestat和v$datafile进行关联，在/u02和/u03上的Oracle数据文件读写很高:SELECT SUBSTR(F.NAME,1,4)DISK,SUM(S.PHYRDS),SUM(S.PHYWRTS)FROM V$DATAFILE F,V$FILESTAT SWHERE F.FILE#=S.FILE#GROUP BY SUBSTR(F.NAME,1,4)可以进一步通过lslv,lsvg,lspv命令看/u02和/u03在那块盘上Alert.log文件文件在alert.log中显示大量的c

13、heckpoint not completed信息log_buffer只有64k有三个日志组，每组一个成员，每个成员只有1M应当调整log_buffer的大小到1m，增加日志组的大小和数量，如(5组，每个20m)ORA-00600错误STATSPACK的使用的使用STATSPACKInstance Efficiency Percentages(Target 100%)Buffer Nowait%:100.00 Redo NoWait%:100.00 Buffer Hit%:95.97 In-memory Sort%:99.74 Library Hit%:99.69 Soft Parse%:99

14、.31 Execute to Parse%:-3.23 Latch Hit%:99.99Parse CPU to Parse Elapsd%:39.59%Non-Parse CPU:98.65Top 5 Wait Events Wait%TotalEvent Waits Time(cs)Wt Time-SQL*Net more data from dblink 56,250 36,386 57.88direct path read 7,073 4,549 7.24db file parallel write 1,540 3,666 5.83db file scattered read 7,85

15、9 3,123 4.97control file parallel write 1,225 2,755 4.38 -如何安装如何安装创建一个120M左右的表空间,如perfstat在服务器端用sqlplus(登录到sys as sysdba用户)运行?/rdbms/admin/spcreate如何生成report?/rdbms/admin/spreport如何清除过多的数据?/rdbms/admin/sppurge如何运行收集？exec statspack.snapdbms_job.submit(,begin statspack.snap;end;,.)效率效率 Buffer Nowait数值

16、大(95%)表示数据缓冲区足够大否则需要增加数据缓冲区的大小，或调整数据文件IO的速度效率效率 Redo Nowait数据大(99%)表示log_buffer足够否则调整log_buffer的大小效率效率 Buffer Hit足够大(95%)表示命中率较高否则可以调整数据缓冲区的大小效率效率 In-memory Sort数字大(95%)表示大部分数据在内存中进行排序否则调整sort_area_size的值，或pga_aggregate_target的值效率效率 Library Hit数据大(95%)表示SQL的命中率较高否则需要调整shared_pool_size的值，或者需要调整SQL，使用

17、bind variable效率效率 Soft Parse数字大(95%)表示SQL的缓冲情况比较好否则需要增加shared_pool_size的值效率效率 Execute to parse=100*(1-Parses/Executions)数据大表示重新解释的次数较多，在snapshot较多的系统中值可能很低，因为完全刷新后很多SQL语句执行时需要重新解释,动态的创建表或索引等也会增加Parse的次数.效率效率 Latch Hit数字大(99%)表示内部锁比较好否则需要查询Oracle有关版本的bug说明效率效率 Parse CPU to Parse elapsed做SQL解释时CPU的利用率

18、数据越大表示解释的效率高效率效率 Non-Parse CPU表示用于SQL Parse的CPU的比重数值越大越好事件事件 Buffer Busy Wait访问的块正在读取中其他他进程正在将数据读到Cache中访问的块正在修改中其他进程正在修改Cache中的数据调整思路：将数据文件放在读取速度更快的设备上事件事件 Checkpoint Completed等待Checkpoint操作结束调整思路：减小一些log buffer的大小增加Checkpoint的频率将log文件放在更快的磁带设备上,如RAID事件事件 Control File Parallel Write等待向所有的控制文件写数据调整思

19、路：减少control file的个数将不同control file分布到不同的磁盘驱动器事件事件 Control File Sequential Read 从Control File中读取信息调整思路：将Control File放在比较空闲的磁盘上将Control File放在速度更快的磁盘上事件事件 Control File Single Write某一个进程需要向Control File写东西,如：创建数据文件等事件事件 DB File Parallel Read一般在并行恢复时发生调整思路：调整文件在不同磁盘驱动上的分布选用访问速度更快的磁盘事件事件 DB File Parallel

20、WriteDBWn进程将数据写入数据文件调整思路：调整文件在多个磁盘控制器之间的分布采用条带化技术，提高写速度。事件事件 DB File Scattered Read读取大量的数据块到Cache中调整思路：调整大表上的索引收集更集统计信息事件事件 DB File Sequential Read一般指读取索引的数据调整思路：调整索引的设计Rebuild索引，提高索引效率事件事件 Direct Path Read指直接从文件中读取，主要发生在检查Direct Path Writer写是否完成调整思路：调整文件分布选用更快的磁盘设备事件事件 Direct Path Write直接向数据文件写数据，发

21、生在Direct Insert中Insert/*+append*/sqlldr中设置direct=true调整思路：调整文件分布选用更快的磁盘设备事件事件 EnqueueEmqueue是Oracle内部的一种锁，用来进行串行操作调整思路：增大enqueue_resources参数事件事件 Free Buffer Wait寻找可用Cache块，如大量的数据被修改，或没有可用的空闲块调整思路增加db_block_buffers或db_cache_size的值事件事件 IO Done等待写文件操作结束，在不支持异步I/O的操作系统中经常发生调整思路查询有关平台可否使用异步I/O调整文件分布采用更快的

22、磁盘设备事件事件 Log Buffer Space生成日志的速度大于将日志写到磁盘的速度调整思路增加log_buffer的值将log文件放到空闲的磁盘设备上事件事件 Log File Parallel Write等待log写操作结束，如日志组有的成员在快的设备上，有的在慢的设备上调整思路将log的成员分布到不同的磁盘上用更快的磁盘设备事件事件 Log File Switch()Archiving needed 等待归档完成调整思路：增加log_archive_processes的数量Checkpoint not completed 等待切换到下一个日志调整思路：增加日志组的数据，调整大小Com

23、pletion 等待日志切换完成调整思路：将log放到更快的磁盘设备上事件事件 Log File Sync在用户commit时，等待将日志写入文件的过程调整思路将日志放到更快的磁盘设备上将各个成员放到不同的磁盘设备上事件事件 SQL*NET more data from dblink等待dblink远程数据库的数据调整思路dblink的速度太慢事件事件 SQL*NET more data to dblink通过dblink向远程数据库插入数据调整思路dblink的速度太慢事件事件 transaction等待回滚死掉的或阻塞(如死锁)进程中的任务事件事件 Undo Segment Extensi

24、on等待动态扩展回滚段调整思路增加回滚段的初始大小，增加optimal的值对于大事务指定使用大的回滚段DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(RBS99);事件事件 Undo Segment Recovery等待PMON处理死锁时的情况事件事件 Undo Segment tx slot等待获得回滚段调整思路增加回滚段的个数增加回滚段的初始extent的数量事件事件 Write Completed用户在commit时等待保存修改过的blockSTATSPACK中的其他信息中的其他信息Logical read 大于一定块数的SQLPhysical read大于一

25、定块数的SQL从v$sqltext中查询整个sqlselect sql_text from v$sqltext where hash_value=?Order by pieceSTATSPACK实验实验安装STATSPACK收集一天的数据，白天每小时一次，从8点到18点生成一份报告，并进行服务器性能分析找出几个logical read和physical read比较多的语句进行分析和调整Oracle的优化器的优化器Oracle优化器的类型优化器的类型Cost Based Optimizer(简称CBO)基于统计信息的优化Rule Based Optimizer(简称RBO)基于数据字典的优化，

26、在以后的版本中将不支持RBO根据数据字典查询有无可用的索引，如果有则使用，否则不使用不同的访问方法有预定好的优先级，选择优先级高的执行方法RBO中访问数据中访问数据Rowid-Cluster Join(SR)-Hash Cluster Key(SR)-UK或PK(SR)-Cluster Join-Hash Cluster Key-Index Cluster Key-Composite Index-Single Column Index-Bound Range Index Scan-Unbound Range Index Scan-Sort Merge Join-Max or Min on In

27、dexed Column-Order on Indexed Column-Full Table ScanCBO需要收集统计信息表有多少行，占用多少数据块列有多少个Null值、不同值列的最大值和最小值，及值的分布情况索引的层次、结点数、叶结点数，及行的分布状况(Cluster)根据一定算法算出一个成本值，选择成本值最低的执行方法，不一定使用索引。CBO的新功能的新功能Partition Table 和 Partition IndexIndex Organized TableReserve Key IndexFunction IndexBitmap Index 和 Bitmap Join Inde

28、xIndex Skip ScanParallel Query 和 Parallel DMLHash Join基于实体化视图的Query Rewrite 使用使用CBO新的应用应当基于CBO开发用dbms_stats收集信息在语句中用hint指定rule choose first_rows all_rows在session中用alter session指定optimizer_mode参数在参数文件中指定optimizer_mode参数CBO几个概念几个概念Selective结果记录的比例Histograms列值的分布情况Cardinality结果记录数Cost执行成本CBO:Selective指

29、结果记录的比例用于决定使用索引访问还是使用全表扫描例子：表T的COL1字段上有10个不同值，当执行select*from T where col1=?时，这个查询的selective=1/10*100=10%CBO:Histograms用于描述列值的分布情况在分布不平均的例上，需要用Histograms来记录列值的分布情况例子:在表T的COL1字段上，有100个值，它分的分布如下 10 20 5 5 40 10 10CBO:CardinalityCBO对查询将返回的记录数的一个估计，在最简单的情况下：Cardinality=Table rows*SelectiveCBO:CostCBO根据所收

30、集的统计信息或猜测信息给某一个SQL语句算出的执行成本具体计算方法很复杂没有统计信息或过时的统计信息，会导至CBO产生和使用错误的执行方法CBO还不是十分完善，可以使用Hint影响CBO选择执行方法全表扫描和索引扫描全表扫描和索引扫描索引扫描不一定是最快的(如小表)CBO中经常会选择全表扫描(如小表)当Selective 比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度gather_schema_statsownname=用户名estimate_percent=比例比例越大

31、，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度gather_table_statsownname=用户名tabname=表名partname=分区名estimate_percent=比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度gather_index_statsownname=用户名tabname=表名partname=分区名estimate_percent=

32、比例比例越大，收集越耗时degree=并行度默认为表的并行度查看表的统计信息查看表的统计信息 USER|ALL|DBA _TABLESnum_rows/blocks/avg_row_len/last_analyzed查看索引的统计信息查看索引的统计信息 USER|ALL|DBA _INDEXESNUM_ROWS/DISTINCT/LEAF_BLOCKS/CF/ALFBKEYCF=一个索引叶块对应的数据块的数量，越小表示一索引越有效ALFBKEY=每一个值占据的叶块的数量查看列的统计信息查看列的统计信息 USER|ALL|DBA _tab_col_statisticsnum_distinct/n

33、um_nulls/num_buckets/densitynum_buckets=histograms中组的数量Density=1/num_distinct 查看查看histograms的信息的信息user|all|dba_histogramsuser|all|dba_part_histogramsuser|all|dba_subpart_histogramsuser|all|dba_tab_col_statistics不适合在分布均匀的列或在where中用bind variable来查询的列在收集时需要在method_opt中指定size值CBO使用实列使用实列使用Function Index

34、选择一个例值分布不平均的表学会收集统计信息使用dbms_job来定时收集统计信息查看统计信息XSTJXCRB查询优化查询优化思路使用实体化视图实现Query RewriteP200404分区记录数：88512条按RQ0000和SPDM00汇总：6899条利用CBO的Query Rewrite技术，按rq0000和spdm00汇总好数据存放在中间表(实体化视图)中理解实体化视图理解实体化视图SQLSQLSQLSQLOracleOracleOracleOracleXSTJXCRBXSTJXCRBXSTJXCRBXSTJXCRB表表表表XSTJXCRBXSTJXCRBXSTJXCRBXSTJXCRB

35、实体化视图实体化视图实体化视图实体化视图Query RewriteQuery RewriteQuery RewriteQuery RewriteSELECT COL2,SUM(COL2)SELECT COL2,SUM(COL2)SELECT COL2,SUM(COL2)SELECT COL2,SUM(COL2)COL2 FROM COL2 FROM COL2 FROM COL2 FROM GROUP BY GROUP BY GROUP BY GROUP BY COL1 COL1 COL1 COL1 SELECT SELECT SELECT SELECT SUM(COL2)SUM(COL2)SU

36、M(COL2)SUM(COL2)FROM FROM FROM FROM GROUP BY GROUP BY GROUP BY GROUP BY COL1 COL1 COL1 COL1 SELECT COL1,COL2 FROM SELECT COL1,COL2 FROM SELECT COL1,COL2 FROM SELECT COL1,COL2 FROM 实体化视图的刷新实体化视图的刷新Complete可以不用实体化视图日志Fast需要实体化视图日志的支持，可以支持Join和Summary操作Fast refresh on commit需要实体化视图日志的支持，可以支持Join和Summar

37、y操作可以用于可以用于Query Rewrite的条件的条件不可以使用Rownum,sysdate等值变化不固定的函数不支持raw,long raw和ref object类型不支持union,minus,intersect等类型语句一个表不要引用两次不能在sys用户下不支持connect by语句刷新刷新:Fast(关联语句关联语句)给所有关联的表建实体化视图日志需要有with rowid选项在select的列中包括所有表的rowid列不能有group by语句在外连接的另一方必须有unique约束例子select a.rowid rowid_a,a.*,b.rowid rowid_b,b.*

38、from a,b where a.*=b.*刷新刷新:Fast(汇总语句汇总语句)所有的表必须有日志日志中被引用的所有列应有with rowid和including new values如果有insert,update,delete操作，则指定with sequence选项在select中应当有count(*),count(字段),sum(字段),sum(字段*字段)包括group by中的所有列使用cube或rollup时，包括grouping_id(字段)列创建实体化视图日志创建实体化视图日志语法create materialized view log on with sequence,r

39、owid(col1,col2,)including new values例子Create materialized view log on bbcx.xstjxcrb with sequence,rowid,(QNXS00,RJKC00,RQ0000,SNXS00,SPDM00,SWXS00)including new values创建实体化视图创建实体化视图CREATE MATERIALIZED VIEW BBCX.MVIEW_BBCX_XSTJXCRB (RQ0000,SPDM00,RJKC00,SWXS00,SNXS00,QNXS00)PARTITION BY RANGE(RQ0000)

40、(PARTITION P200401 values less than (TO_DATE(20040501,YYYYMMDD),)BUILD IMMEDIATE REFRESH FAST WITH ROWID ON COMMIT ENABLE QUERY REWRITE TABLESPACE IQS_BASICDATA AS select rq0000,spdm00,count(*)rowcnt,sum(rjkc00)rjkc00,SUM(SWXS00)swxs00,sum(SNXS00)snxs00,sum(QNXS00)qnxs00 from xstjxcrb group by rq000

41、0,spdm00使用实体化视图使用实体化视图修改启动参数：query_rewrite_enabled=truequery_rewrite_integrity=trustedGrant query rewrite to bbcx收集统计信息dbms_stats.gather_table_stats(bbcx,xstjxcrb)或使用rewrite提示符需要注意SQL的匹配性，可以用explain plan来看调整临时表调整临时表尽量减少临时表的使用可以适当使用嵌套的SQL语句Optimizer Hints为什么会有为什么会有Hints因为CBO的功能还不十分强大尽量让优化器选择，除非你很有自信例

42、子：在使用dblink的环境中在使用bind variable时Hint:优化器的选择优化器的选择All_rowsFirst_rows(n)n=1,10,100,1000ChooseRuleHint:选择访问方法选择访问方法Full(表名)Index(表名 索引)Index_asc(表名 索引)Index_desc(表名 索引)Index_combine(表名 bid1 bid2)Index_join(表名 ind1 ind2)Index_ffs(表名 索引)No_index(表名 索引)Hint:控制控制SQL转换转换Use_concat使用union all来替换or条件No_expand

43、不使用union all来替换or条件Rewrite/norewrite启用和禁用Query Rewrite技术Merge(视图)/no_merge(视图)是否合并视图Hint:控制控制Join类型类型 Use_nl(表名)Use_merge(表1 表2)Use_hash(表1 表2)Hash_aj/nl_aj/merge_ajHash_sj/nl_sj/merge_sjHint:控制并行控制并行Parallel(表名 并行度)Noparallel(表名)Hint:其他其他AppendCache创建索引创建索引索引能提高速度的关键就是索引所占的空间要比表小得多注意索引的大小，有一些表可以建成索

44、引组织表索引的列不要太多，要选择一些selective比较低的列建B-tree索引，选择selective高的列建bitmap索引(在更新比较多的表不不要建bitmap索引)将selective较低的列放在前面在更新不多的表上建索引时，可以考虑用compress选择，以节约索引的空间创建表创建表普通表索引组织表大部分字段是Primary Key的表分区表一般记录数在50万以上的可以考虑创建分区表其他问题其他问题在表空间使用时，最好用Local表空间，否则应当设置pctincrease为0以减少表空间的碎片不同数据量级的表应当有不同的initial和next或Uniform size设置许多表有maxextents的限值，需要注意unable to allocate extends的错误信息若使用CBO，应在晚上定时收集statistics培训总结培训总结认真思考理解CBO的部分原理查出目前数据库中各个索引与表的大小的比值，考虑索引存在的必须性，或索引列的选择是否正确了解和合理使用Optimizer Hint使用CBO，在CBO下调整优化SQL用STATPACK找出一些reads比较大的SQL并进行调整