《Oracle公司的调优实例.ppt》由会员分享，可在线阅读，更多相关《Oracle公司的调优实例.ppt（116页珍藏版）》请在三一办公上搜索。
1、性能调整,计划任务模块调整,计划任务(现状),现在的解决方法小表：采用了snapshot的方法大表：KHTKHXL0和KHTXLJG0采用了通过dblink插入的方式db_link_xx分别是不同的服务器，每个服务器的连接有2M带宽的限制。目前串行运行所有的表没有进行Statistics Collection,南平地区,县市1,县市2,县市n,计划任务(现状),1,2,xx,KHTKHXL0的索引,KHTKHXL0_GSKHNYJYGSDM00,KHDM00,NYUE00,JYDM00GSDM00只有一个值NYUE00只有一个值KHTKHXL0_KHDMKHDM00,表KHTKHXL0及索引大
2、小,SEGMENT_NAME SUM(BYTES)/1048576-KHTKHXL0 13.9453125KHTKHXL0_GSKHNYJY 13.4765625KHTKHXL0_KHDM 6.2890625,KHTXLJG0的索引,KHTXLJG_KH_SP_INDEXGSDM00,KHDM00,SPDM00GSDM00只有一个值KHTXLJG0_KHDM_INDEXKHDM00KHTXLJG0_SPDM_INDEXSPDM00,表KHTXLJG0及索引大小,SEGMENT_NAME SUM(BYTES)/1048576-KHTXLJG0 143.554688KHTXLJG0_KHDM_IN
3、DEX 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的速度建议用分区表和分区索引。部分索引需要调整,建db link,在crm用户下建好指向各个地市的db
4、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),PARTITION P902 VALUES LESS THAN(903),PART
5、ITION 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 P929 VALUES LESS THAN(930)TABLESPAC
6、E CRM_DATA_TEST;,KHTXLJG0,INSERT/*+append*/INTO CRM.khtxljg0NOLOGGING a.yzgxl0,a.yzdxl0,a.jycljg,a.zzs000FROM CRM.khtxljg0db_link_01 aUNION ALL a.yzgxl0,a.yzdxl0,a.jycljg,a.zzs000FROM CRM.khtxljg0db_link_02 aUNION ALL,KHTKHXL0,INSERT/*+append*/INTO CRM.KHTKHXL0NOLOGGING WHERE NYUE00=TO_CHAR(SYSDATE,
7、YYYYMM)UNION ALL FROM CRM.KHTKHXL0DB_LINK_10 a WHERE NYUE00=TO_CHAR(SYSDATE,YYYYMM);UNION ALL,调整索引,KHTKHXL0KHTKHXL0_KHJYDM(KHDM00,JYDM00)KHTXLJG0KHTXLJG0_KHSPDM(KHDM00,SPDM00)采用分区(Local Prefixed)方式采用索引组织表,创建索引组织表,Create table name(col1 varchar2(10)not null,)Organization indexTablespace.,时间测试,使用SELEC
8、T SYSDATE FROM DUAL;SET ECHO ONSET TIMING ONSPOOL 文件名.log结果南平地区：一般9分钟主要是取KHTXLJG0时节约了约3分钟,调整总结,调整难点(在v$system_event中查询)事件 SQL*Net more data from dblinkSELECT EVENT,TOTAL_WAITS,TIME_WAITEDFROM V$SESSION_EVENTWHERE TOTAL_WAITS0 AND SID=(SELECT DISTINCT SID FROM V$MYSTAT)用crm,crm1,crm01,crm02,crm03,crm
9、04,crm05,crm06,crm07,crm08,crm09,crm10定期进行5%的Statistics Collection建议使用LOCAL表空间，以降低Recursive SQL的调用，采用分区表，合理选择索引的列和顺序,服务器调整,调整工具,Windows性能管理器，任务管理器Unixtopas，vmstat，sar,服务器端调优,Page IN/OUT的情况CPU的使用情况DISK I/O信息Alert.log中的信息$ORACLE_HOME/admin/bdumpSTATSPACK,内存(南平),总共2G物理内存Oracle的SGA为1.1G，使用pre_page_sga参数
10、常驻内存总连接数为30左右，峰值时内存为1.5G总结：内存方面不存在问题,CPU(南平),显示有4个CPU一般利用率在20%左右总结：CPU方面不存在问题,DISK I/O(南平),只有一个60G的硬盘操作系统、Oracle程序、数据库所有的文件在一个物理磁盘上总结：DISK I/O方面不够理想，应当选择多个小硬盘。,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.P
11、HYWRTS)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中显示大量的checkpoint not completed信息log_buffer只有64k有三个日志组，每组一个成员，每个成员只有1M应当调整log_buffer的大小到1m，增加日志组的大小和数量，如(5组，每个20m)ORA-00600错误,STATSPACK的使用,STATSPACK,Instan
12、ce 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.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
13、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,859 3,123 4.97control file parallel write 1,225 2,755 4.38-,如何安装,创建一个120M左右的表空间,如perfstat在服务器端用sqlplus(登录到sys as sysdba用户)运行?/rdbms/admin/spcreate如何生
14、成report?/rdbms/admin/spreport如何清除过多的数据?/rdbms/admin/sppurge如何运行收集？exec statspack.snapdbms_job.submit(,begin statspack.snap;end;,.),效率 Buffer Nowait,数值大(95%)表示数据缓冲区足够大否则需要增加数据缓冲区的大小，或调整数据文件IO的速度,效率 Redo Nowait,数据大(99%)表示log_buffer足够否则调整log_buffer的大小,效率 Buffer Hit,足够大(95%)表示命中率较高否则可以调整数据缓冲区的大小,效率 In-m
15、emory Sort,数字大(95%)表示大部分数据在内存中进行排序否则调整sort_area_size的值，或pga_aggregate_target的值,效率 Library Hit,数据大(95%)表示SQL的命中率较高否则需要调整shared_pool_size的值，或者需要调整SQL，使用bind variable,效率 Soft Parse,数字大(95%)表示SQL的缓冲情况比较好否则需要增加shared_pool_size的值,效率 Execute to parse,=100*(1-Parses/Executions)数据大表示重新解释的次数较多，在snapshot较多的系统中
16、值可能很低，因为完全刷新后很多SQL语句执行时需要重新解释,动态的创建表或索引等也会增加Parse的次数.,效率 Latch Hit,数字大(99%)表示内部锁比较好否则需要查询Oracle有关版本的bug说明,效率 Parse CPU to Parse elapsed,做SQL解释时CPU的利用率数据越大表示解释的效率高,效率 Non-Parse CPU,表示用于SQL Parse的CPU的比重数值越大越好,事件 Buffer Busy Wait,访问的块正在读取中其他他进程正在将数据读到Cache中访问的块正在修改中其他进程正在修改Cache中的数据调整思路：将数据文件放在读取速度更快的设
17、备上,事件 Checkpoint Completed,等待Checkpoint操作结束调整思路：减小一些log buffer的大小增加Checkpoint的频率将log文件放在更快的磁带设备上,如RAID,事件 Control File Parallel Write,等待向所有的控制文件写数据调整思路：减少control file的个数将不同control file分布到不同的磁盘驱动器,事件 Control File Sequential Read,从Control File中读取信息调整思路：将Control File放在比较空闲的磁盘上将Control File放在速度更快的磁盘上,事件
18、 Control File Single Write,某一个进程需要向Control File写东西,如：创建数据文件等,事件 DB File Parallel Read,一般在并行恢复时发生调整思路：调整文件在不同磁盘驱动上的分布选用访问速度更快的磁盘,事件 DB File Parallel Write,DBWn进程将数据写入数据文件调整思路：调整文件在多个磁盘控制器之间的分布采用条带化技术，提高写速度。,事件 DB File Scattered Read,读取大量的数据块到Cache中调整思路：调整大表上的索引收集更集统计信息,事件 DB File Sequential Read,一般指读
19、取索引的数据调整思路：调整索引的设计Rebuild索引，提高索引效率,事件 Direct Path Read,指直接从文件中读取，主要发生在检查Direct Path Writer写是否完成调整思路：调整文件分布选用更快的磁盘设备,事件 Direct Path Write,直接向数据文件写数据，发生在Direct Insert中Insert/*+append*/sqlldr中设置direct=true调整思路：调整文件分布选用更快的磁盘设备,事件 Enqueue,Emqueue是Oracle内部的一种锁，用来进行串行操作调整思路：增大enqueue_resources参数,事件 Free Bu
20、ffer Wait,寻找可用Cache块，如大量的数据被修改，或没有可用的空闲块调整思路增加db_block_buffers或db_cache_size的值,事件 IO Done,等待写文件操作结束，在不支持异步I/O的操作系统中经常发生调整思路查询有关平台可否使用异步I/O调整文件分布采用更快的磁盘设备,事件 Log Buffer Space,生成日志的速度大于将日志写到磁盘的速度调整思路增加log_buffer的值将log文件放到空闲的磁盘设备上,事件 Log File Parallel Write,等待log写操作结束，如日志组有的成员在快的设备上，有的在慢的设备上调整思路将log的成员
21、分布到不同的磁盘上用更快的磁盘设备,事件 Log File Switch(),Archiving needed 等待归档完成调整思路：增加log_archive_processes的数量Checkpoint not completed 等待切换到下一个日志调整思路：增加日志组的数据，调整大小Completion 等待日志切换完成调整思路：将log放到更快的磁盘设备上,事件 Log File Sync,在用户commit时，等待将日志写入文件的过程调整思路将日志放到更快的磁盘设备上将各个成员放到不同的磁盘设备上,事件 SQL*NET more data from dblink,等待dblink远
22、程数据库的数据调整思路dblink的速度太慢,事件 SQL*NET more data to dblink,通过dblink向远程数据库插入数据调整思路dblink的速度太慢,事件 transaction,等待回滚死掉的或阻塞(如死锁)进程中的任务,事件 Undo Segment Extension,等待动态扩展回滚段调整思路增加回滚段的初始大小，增加optimal的值对于大事务指定使用大的回滚段DBMS_TRANSACTION.USE_ROLLBACK_SEGMENT(RBS99);,事件 Undo Segment Recovery,等待PMON处理死锁时的情况,事件 Undo Segmen
23、t tx slot,等待获得回滚段调整思路增加回滚段的个数增加回滚段的初始extent的数量,事件 Write Completed,用户在commit时等待保存修改过的block,STATSPACK中的其他信息,Logical read 大于一定块数的SQLPhysical read大于一定块数的SQL从v$sqltext中查询整个sqlselect sql_text from v$sqltext where hash_value=?Order by piece,STATSPACK实验,安装STATSPACK收集一天的数据，白天每小时一次，从8点到18点生成一份报告，并进行服务器性能分析找出几
24、个logical read和physical read比较多的语句进行分析和调整,Oracle的优化器,Oracle优化器的类型,Cost Based Optimizer(简称CBO)基于统计信息的优化Rule Based Optimizer(简称RBO)基于数据字典的优化，在以后的版本中将不支持,RBO,根据数据字典查询有无可用的索引，如果有则使用，否则不使用不同的访问方法有预定好的优先级，选择优先级高的执行方法,RBO中访问数据,Rowid-Cluster Join(SR)-Hash Cluster Key(SR)-UK或PK(SR)-Cluster Join-Hash Cluster K
25、ey-Index Cluster Key-Composite Index-Single Column Index-Bound Range Index Scan-Unbound Range Index Scan-Sort Merge Join-Max or Min on Indexed Column-Order on Indexed Column-Full Table Scan,CBO,需要收集统计信息表有多少行，占用多少数据块列有多少个Null值、不同值列的最大值和最小值，及值的分布情况索引的层次、结点数、叶结点数，及行的分布状况(Cluster)根据一定算法算出一个成本值，选择成本值最低的执
26、行方法，不一定使用索引。,CBO的新功能,Partition Table 和 Partition IndexIndex Organized TableReserve Key IndexFunction IndexBitmap Index 和 Bitmap Join IndexIndex Skip ScanParallel Query 和 Parallel DMLHash Join基于实体化视图的Query Rewrite,使用CBO,新的应用应当基于CBO开发用dbms_stats收集信息在语句中用hint指定rule choose first_rows all_rows在session中用a
27、lter session指定optimizer_mode参数在参数文件中指定optimizer_mode参数,CBO几个概念,Selective结果记录的比例Histograms列值的分布情况Cardinality结果记录数Cost执行成本,CBO:Selective,指结果记录的比例用于决定使用索引访问还是使用全表扫描例子：表T的COL1字段上有10个不同值，当执行select*from T where col1=?时，这个查询的selective=1/10*100=10%,CBO:Histograms,用于描述列值的分布情况在分布不平均的例上，需要用Histograms来记录列值的分布情况
28、例子:在表T的COL1字段上，有100个值，它分的分布如下 10 20 5 5 40 10 10,CBO:Cardinality,CBO对查询将返回的记录数的一个估计，在最简单的情况下：Cardinality=Table rows*Selective,CBO:Cost,CBO根据所收集的统计信息或猜测信息给某一个SQL语句算出的执行成本具体计算方法很复杂没有统计信息或过时的统计信息，会导至CBO产生和使用错误的执行方法CBO还不是十分完善，可以使用Hint影响CBO选择执行方法,全表扫描和索引扫描,索引扫描不一定是最快的(如小表)CBO中经常会选择全表扫描(如小表)当Selective 15%
29、时，选择索引扫描比较有利，否则使用全表扫描更好记录的Cluster情况,A B C D,A B C D,A B C D,A B C D,A A A A,B B B B,C C C C,D D D D,CBO中的访问方法,Full Table ScansRowid ScansIndex ScansCluster ScansHash ScansSample Table Scans,CBO中Index访问,Index Unique ScanIndex Range Scan DescendingIndex Skip ScanFull ScanFast Full Index ScanIndex Join
30、Bitmap Join,CBO中的JOIN方法,NEST LOOPMERGE JOINHASH JOIN,收集统计信息,定时收集在数据大量变更后收集使用Analyze命信收集或dbms_stats包收集在optimizer_mode=choose时，收集信息后会采用CBO进行优化，在dblink应用较多时，需要注意。,DBMS_STATS,Gather_database_stats收集数据库下所有对象的统计信息Gather_schema_stats收集某一用户下所有对象的统计信息Gather_table_stats收集某一个表或分区的统计信息Gather_index_stats收集某一个索引或
31、分区的统计信息,gather_database_stats,estimate_percent=比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度,gather_schema_stats,ownname=用户名estimate_percent=比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度,gather_table_stats,ownname=用户名tab
32、name=表名partname=分区名estimate_percent=比例比例越大，收集越耗时method_opt=选项for all indexed|hidden columns size 1-254 degree=并行度默认为表的并行度,gather_index_stats,ownname=用户名tabname=表名partname=分区名estimate_percent=比例比例越大，收集越耗时degree=并行度默认为表的并行度,查看表的统计信息,USER|ALL|DBA _TABLESnum_rows/blocks/avg_row_len/last_analyzed,查看索引的统计
33、信息,USER|ALL|DBA _INDEXESNUM_ROWS/DISTINCT/LEAF_BLOCKS/CF/ALFBKEYCF=一个索引叶块对应的数据块的数量，越小表示一索引越有效ALFBKEY=每一个值占据的叶块的数量,查看列的统计信息,USER|ALL|DBA _tab_col_statisticsnum_distinct/num_nulls/num_buckets/densitynum_buckets=histograms中组的数量Density=1/num_distinct,查看histograms的信息,user|all|dba_histogramsuser|all|dba_p
34、art_histogramsuser|all|dba_subpart_histogramsuser|all|dba_tab_col_statistics不适合在分布均匀的列或在where中用bind variable来查询的列在收集时需要在method_opt中指定size值,CBO使用实列,使用Function Index选择一个例值分布不平均的表学会收集统计信息使用dbms_job来定时收集统计信息查看统计信息,XSTJXCRB查询优化,思路使用实体化视图实现Query RewriteP200404分区记录数：88512条按RQ0000和SPDM00汇总：6899条利用CBO的Query
35、Rewrite技术，按rq0000和spdm00汇总好数据存放在中间表(实体化视图)中,理解实体化视图,SQL,Oracle,XSTJXCRB表,XSTJXCRB实体化视图,Query Rewrite,SELECT COL2,SUM(COL2)COL2 FROM GROUP BY COL1,SELECT SUM(COL2)FROM GROUP BY COL1,SELECT COL1,COL2 FROM,实体化视图的刷新,Complete可以不用实体化视图日志Fast需要实体化视图日志的支持，可以支持Join和Summary操作Fast refresh on commit需要实体化视图日志的支持
36、，可以支持Join和Summary操作,可以用于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 rowi
37、d_b,b.*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,rowid
38、(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)(PARTITION
39、 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 rq0000,spdm00,使用
40、实体化视图,修改启动参数：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的功能还不十分强大尽量让优化器选择，除非你很有自信例子：在使用dblink的环境中在使用bin
41、d variable时,Hint:优化器的选择,All_rowsFirst_rows(n)n=1,10,100,1000ChooseRule,Hint:选择访问方法,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不使用union all来替换or条件Rewrite/nore
42、write启用和禁用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_sj,Hint:控制并行,Parallel(表名 并行度)Noparallel(表名),Hint:其他,AppendCache,创建索引,索引能提高速度的关键就是索引所占的空间要比表小得多注意索引的大小，有一些表可以建成索引组织表索引的列不要太多，要选择一些selective比较低的列建B-tre
43、e索引，选择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并进行调整,
