深入浅出Oracle性能优化-Oracle EBS技术文档整理

DocRef:REFDocRefNumber<DocumentReferenceNumber>深入浅出丛书系列REFLastDateMarch9,2011STYLEREFHD2执行计划IfSection2>1“DateAuthorVersionChangeReferenceCREATEDATE\@"d-MMM-yy"4-Sep-15<李义>Draft1aNoPreviousDocumentReviewersNamePositionDistributionCopyNo.NameLocationLibraryMasterProjectLibraryProjectManagerNoteToHolders:Ifyoureceiveanelectroniccopyofthisdocumentandprintitout,pleasewriteyournameontheequivalentofthecoverpage,fordocumentcontrolpurposes.Ifyoureceiveahardcopyofthisdocument,pleasewriteyournameonthefrontcover,fordocumentcontrolpurposes.ContentsTOC\o"2-3"DocumentControl ii执行计划 2优化器 2执行计划 2性能优化实践 12通过优化数据库对象提升性能 12程序优化 24通过oracle提供的诊断工具提升性能 271. OpenandClosedIssuesforthisDeliverable 31OpenIssues 31ClosedIssues 31PAGE10执行计划优化器Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(7Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-asedOptimization,简称为RBO)和基于成本的优化方式(Cost-BasedOptimization,简称为CBO)。基于规则的优化器RBO优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时就一定去走索引。由于这种优化器过于默守陈规，比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(fulltablescan)是最好的。因此在10g以后Oracle已不在使用RBO。CBO依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。执行计划执行计划是Oracle执行查询时，用于执行的访问路径的描述。查询进程可以被分解为7个阶段：句法——检查查询的句式。语义——对查询相关对象的存在性及可访问性进行检查。视图合并——对查询进行重写，利用基表进行连接以替代初始查询中的视图。声明转换——对查询进行重写，将某些复杂的结构转换成更为合适更为简单的结构（比如，嵌套子查询，in/or转换）。有些转换是基于一定规则，而其他的则是基于统计成本信息。优化——决定了查询所采用的访问路径。基于成本的优化器（CostBasedOptimizer，CBO）使用统计信息来分析访问对象的相关成本。生成查询评估计划（QueryEvaluationPlan，QEP）QEP执行步骤【1】-【6】隶属于“解析”阶段。

步骤【7】隶属于声明的执行阶段。执行计划就是步骤【6】中访问路径的描述。一旦执行计划被制定，它就与查询声明一并被存储在缓存库中。查询声明以哈希格式保存于缓存库当中。当在缓存库中检索声明时，首先会为当前的声明应用一个哈希算法，然后在缓存库中查找算法返回的哈希值。直到查询被重新解析之前这个访问路径一直会被保持。相关术语行来源（RowSource）——行来源是一种软件功能，它可以实施具体的操作（诸如表扫描或哈希连接）并返回一个行记录的集合。谓词（Predicates）——查询的where从句。元组（Tuples）——行，记录。驱动表（DrivingTable）——用来启动查询的行来源。如果它返回了记录太多则会对后续的所有操作带来负面影响。探测表（ProbedTable）——当从驱动表中获取键值数据后我们从探测表中进行数据检索。获取执行计划Oracle中可从多种渠道获取执行计划，分为以下几类阐述：无需运行sql的情况下可采用：plsqldeveloper中使用F5;sqlplus中使用explainplanforyoursqltext，具体操作步骤如下sqlplusscott/tiggerexplainplanforselect*fromemp;select*fromtable(dbms_xplan.display);注意，此时如果出现：Note

-'PLAN_TABLE'isoldversionNote

-'PLAN_TABLE'isoldversiondroptablePLAN_TABLE;@?/rdbms/admin/utlxplandroptablePLAN_TABLE;@?/rdbms/admin/utlxplan已知sql_id使用dbms_xplan包获取执行计划：selectdbms_xplan.display_cursor('a2dk8bdn0ujx7')fromdual;需要运行完sqlAutotracesetautotraceon;SP2-0618:CannotfindtheSessionIdentifier.CheckPLUSTRACEroleisenabledSP2-0611:ErrorenablingSTATISTICSreportSP2-0618:CannotfindtheSessionIdentifier.CheckPLUSTRACEroleisenabledSP2-0611:ErrorenablingSTATISTICSreport 则执行如下操作：Sqlplus/assysdba;Sqlplus/assysdba;@?/rdbms/admin/utlxplan.sql;@?/sqlplus/admin/plustrce.sqlgrantplustracetopublic; statistics_levelaltersessionsetstatistics_level=all;select*fromemp;select*fromtable(dbms_xplan.display_cursor(null,null,'allstatslast'));注意：此时如果出现：UserhasnoSELECTprivilegeonV$SESSION UserhasnoSELECTprivilegeonV$SESSION则执行如下操作：sqlplus/assysdbagrantselectonv_$sql_plantoscott;sqlplus/assysdbagrantselectonv_$sql_plantoscott;grantselectonv_$sessiontoscott;grantselectonv_$sql_plan_statistics_alltoscott;grantselectonv_$sqltoscott; 10046事件与tkprof（同EBSForm中帮助->诊断->跟踪）altersessionsetevents'10046tracenamecontextforever,level12';select*fromemp;altersessionsetevents'10046tracenamecontextoff';showparameteruser_dump_dest;查看生成的文件路径tkproftrc文件目标文件sys=nosort=prsela,exeela,fchelaORA-01031:insufficientprivileges注意：如果altersession时出现权限不足：ORA-01031:insufficientprivileges则赋予其altersession权限即可sqlplus/assysdbasqlplus/assysdbagrantaltersessiontoscott;AWR（AutomaticWorkloadRepository）AWR实质上是一个Oracle的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。使用方式如下:sqplusscott/tiger@?/rdbms/admin/awrrpt.sql输出方式一般选择html，然后选择开始，结束快照即可。快照默认每小时创建一次，当然你也可以通过dbms_workload_repository.modify_snapshot_settings来配置你想要的频率，并且也可以通过dbms_workload_repository.create_snapshot();手工创建快照。读懂执行计划为了方便起见，本文示例中的解析计划输出全部通过SQL*Plus的自动追踪特性创建。Statistics_level被设置为“典型”。执行计划内容：ExecutionPlanPlanhashvalue:2872589290|Id|OperationExecutionPlanPlanhashvalue:2872589290|Id|Operation |Name |Rows |Bytes|Cost(%CPU) |Time||0|SELECTSTATEMENT | |14 |532|3(0) |00:00:01||1|TABLEACCESSFULL |EMP |14 |532|3(0) |00:00:01| 如果查询具有where从句，那么这个谓词（从句）将会输出：PredicateInformation(identifiedbyoperationid):PredicateInformation(identifiedbyoperationid):2-filter("DEPT"."DNAME"='ACCOUNTING'OR"DEPT"."DNAME"='OPERATIONS'OR"DEPT"."DNAME"='RESEARCH'OR"DEPT"."DNAME"='SALES')4-access("EMP"."DEPTNO"="DEPT"."DEPTNO")filter("EMP"."DEPTNO"="DEPT"."DEPTNO")谓词既包括访问谓词（被用于在数据对象中进行数据访问）也包括过滤谓词（被用于限制在操作中返回的记录数并重新定义结果）。需要注意，声明转换可能会使谓词看起来与原始查询中的谓词不同。Note-SQLplanbaselineSYS_SQL_PLAN_fcc170b0a62d0f4dusedforthisstatementNote-dynamicstatisticsused:dynamicsampling(level=2)NoteNote-SQLplanbaselineSYS_SQL_PLAN_fcc170b0a62d0f4dusedforthisstatementNote-dynamicstatisticsused:dynamicsampling(level=2)Note-statisticsfeedbackusedforthisstatementNote-thisisanadaptiveplanNote-fullyremotestatement如果一个查询是部分远程的那么还会有额外的一个段用来显示被发送到远程的查询信息：RemoteSQLInformation(identifiedbyoperationid):RemoteSQLInformation(identifiedbyoperationid):4-SELECT"DEPTNO","DNAME"FROM"DEPT""A"(accessing'LOOP_LINK')执行计划层次结构SQL>setautottraceonlyexplainSQL>setautottraceonlyexplainSQL>select*fromemp;ExecutionPlanPlanhashvalue:2872589290|Id|Operation |Name |Rows |Bytes |Cost(%CPU) |Time||0|SELECTSTATEMENT | |14 |532 |3(0) |00:00:01||1|TABLEACCESSFULL |EMP |14 |532 |3(0) |00:00:01|在执行计划中的步骤通过缩进的显示方式来揭示操作的层次结构与步骤之间的依赖关系。当查看这个缩紧过的计划时，为了找到被首先执行的操作，请查看“操作（Operation）”列。在此列中，最右边（被缩进最多的）最上边的操作就是被首先执行的。换句话说，在操作列上从上向下检索直到找到了缩进最多的那行。它就是最先被执行的。在本例中，被首先执行的操作的Id=1。有关这个简单例子的更加明细的解释如下。在本例中，TABLEACCESSFULLEMP是第一个发生的操作。这句声明意味着要对数据表EMP进行一次全表扫描。当这个操作结束后，结果行源被传递给查询的下一个级别进行处理。在本例中，下一级别的操作就是查询最顶的SELECTSTATEMENT。执行计划中的其他列提供了各种有益于计划选择决策的信息片段：行数（Rows）—它告诉我们优化器预估此条执行计划将返回的记录行数。字节数（Bytes）—它告诉我们优化器预估此条执行计划将返回的字节数。成本（Cost，%CPU）—它是优化器对查询所需的“成本”与CPU占用半分比的估算。正是这个成本支持着优化器得以（预估）比较不同计划之间的性能。时间（Time）—它是优化器对查询中每一步执行时间的估算。执行顺序PARENTFIRSTCHILDSECONDCHILD为了理解计划与执行顺序，很有必要理解PATENTPARENTFIRSTCHILDSECONDCHILD在本例中，FIRST首先被执行，其次是SECONDCHILD，然后PARENT以某种方式收集前两个步骤的执行结果。一种更加复杂的情况如下：PARENT1PARENT1FIRSTCHILDFIRSTGRANDCHILDSECONDCHILD本例中之前说过的原则同样适用，FIRSTGRANDCHILD是最初始操作，然后FIRSTCHILD与SECONDCHILD紧接着执行，最终PARENT收集结果。下面将此原则应用到实际例子中。示例1.setautotracetraceonlyexplainsetautotracetraceonlyexplainselectename,dnamefromemp,deptwhereemp.deptno=dept.deptnoanddept.dnamein('ACCOUNTING','RESEARCH','SALES','OPERATIONS');ExecutionPlanPlanhashvalue:2865896559|Id|Operation |Name |Rows |Bytes |Cost(%CPU)Time||0|SELECTSTATEMENT | |14 |308 |5(0) |00:00:01||1|MERGEJOIN | |14 |308 |5(0) |00:00:01||*2|TABLEACCESSBYINDEXROWID |DEPT |4 |52 |2(0) |00:00:01||3|INDEXFULLSCAN |PK_DEPT |4 | |1(0) |00:00:01||*4|SORTJOIN | |14 |126 |3(0) |00:00:01||5|TABLEACCESSFULL |EMP |14 |126 |3(0) |00:00:01|PredicateInformation(identifiedbyoperationid):2-filter("DEPT"."DNAME"='ACCOUNTING'OR"DEPT"."DNAME"='OPERATIONS'OR"DEPT"."DNAME"='RESEARCH'OR"DEPT"."DNAME"='SALES')4-access("EMP"."DEPTNO"="DEPT"."DEPTNO")filter("EMP"."DEPTNO"="DEPT"."DEPTNO")上述执行计划的路线图如下：执行起始于：ID=3。那么，我们首先查询ID=0：|Id|Operation |Name ||0|SELECTSTATEMENT | ||1|MERGEJOIN | ||*2|TABLEACCESSBYINDEXROWID |DEPT ||3|INDEXFULLSCAN |PK_DEPT ||*4|SORTJOIN | ||5|TABLEACCESSFULL |EMP |ID=0的上层没有其他操作，所以它没有父操作，但是它拥有1个子操作。ID=0是ID=1的父操作，它依赖于ID=1返回的记录行。可以通过子操作的缩进来判断其父子关系。所以ID=1必须先于ID=0被执行。将焦点转向ID=1：|0|SELECTSTATEMENT|0|SELECTSTATEMENT | ||1|MERGEJOIN | ||*2|TABLEACCESSBYINDEXROWID |DEPT ||*4|SORTJOIN | |已经确认，ID=1是ID=0的子操作。通过缩进判断，ID=2与ID=2都是是ID=1的分支并且缩进级别相同。因此ID=1是ID=2与ID=2的父操作并依赖于他们返回的行记录。所以ID=2与ID=4必须先于ID=1被执行。将焦点转向ID=2|1|MERGEJOIN|1|MERGEJOIN | ||*2|TABLEACCESSBYINDEXROWID |DEPT ||3|INDEXFULLSCAN |PK_DEPT |ID=2是ID=1的第一个子操作。通过缩进判断，ID=2是ID=3的父操作并依赖于他行记录。所以ID=3必须先于ID=2执行。焦点转向ID=3。|1|MERGEJOIN|1|MERGEJOIN | ||*2|TABLEACCESSBYINDEXROWID |DEPT ||3|INDEXFULLSCAN |PK_DEPT |ID=3是ID=2（唯一）的子操作。ID=3没有子操作。这意味着ID=3是整个查询中第一个被执行的步骤。它直接结束后行记录被返回给ID=2。ID=1与ID=0也依赖于ID=3。一旦ID=3输出了行记录，他们就被传递给ID=2以使用。然后行记录就在这个操作树中从下而上低传递。这意味着ID=2是第二个被执行的步骤。ID=1并不会紧接着ID=2被执行，因为它有2个输入。必须所有的输入到位后，它才可以开始操作，因此，让我们将焦点转移到ID=1的第二个子操作，ID=4：|1|MERGEJOIN|1|MERGEJOIN | ||*4|SORTJOIN | ||5|TABLEACCESSFULL |EMP |ID=4是ID=1的第二个子操作。ID=4是ID=5的父操作，它依赖于ID=5的行记录。ID=5必须先于ID=4执行。这意味着ID=5是紧接着ID=4的第三个被执行的操作。|1|MERGEJOIN|1|MERGEJOIN | ||*4|SORTJOIN | ||5|TABLEACCESSFULL |EMP |一旦ID=1集齐所有来自于子操作的输入，它就可以开始执行。ID=1处理接收自其依赖步骤（ID=2&ID=4）的行记录并将他们返回给父操作ID=0。ID=0将行记录返回给用户。上述过程的简短汇总如下：寻找执行顺序：从ID=0开始：SELECTSTATEMENT。依赖于子对象。查看它的第一个子步骤：ID=1MERGEJOIN。它依赖于子对象。查看它的第一个子步骤：ID=2TABLEACCESSBYINDEXROWIDDEPT。它依赖于子对象。查看它唯一的子步骤：ID=3INDEXFULLSCANPK_DEPT。它无子对象所以被执行。行记录被ID=3反馈给ID=2。行记录被ID=2反馈给ID=1。但是它仍然有两个未执行的子对象，因此ID=4需要展开。查看它的第二个子对象：ID=4SORTJOIN。它依赖于子对象。查看它唯一的子对象：ID=5TABLEACCESSFULLEMP。它无子对象所以被执行。行记录被ID=5反馈给ID=4。行记录被ID=4返回给ID=1。因为两个子操作都已经提供了行记录，ID=1可以被执行。行记录被ID=1反馈给ID=0。行记录被ID=0反馈给客户端。行记录被子步骤返回给父步骤直至完成。执行步骤是3，2，4，5，1，0。示例2.select/*+orderedUSE_NL(dept)*/ename,dnameselect/*+orderedUSE_NL(dept)*/ename,dnamefromemp,dept[anddept.dnamein('ACCOUNTING','RESEARCH','SALES','OPERATIONS');ExecutionPlanPlanhashvalue:196120631|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time||0|SELECTSTATEMENT||14|308|17(0)|00:00:01||1|NESTEDLOOPS|||||||2|NESTEDLOOPS||14|308|17(0)|00:00:01||3|TABLEACCESSFULL|EMP|14|126|3(0)|00:00:01||*4|INDEXUNIQUESCAN|PK_DEPT|1||0(0)|00:00:01||*5|TABLEACCESSBYINDEXROWID|DEPT|1|13|1(0)|00:00:01|PredicateInformation(identifiedbyoperationid):4-access("EMP"."DEPTNO"="DEPT"."DEPTNO")5-filter("DEPT"."DNAME"='ACCOUNTING'OR"DEPT"."DNAME"='OPERATIONS'OR"DEPT"."DNAME"='RESEARCH'OR"DEPT"."DNAME"='SALES')在本例中，执行起始于ID=3。寻找执行顺序：从ID=0开始：SELECTSTATEMENT。它依赖于子对象。查看它的第一个子步骤：ID=1NESTEDLOOPS。它依赖于子对象。执行它的第一个子步骤：ID=2NESTEDLOOPS。它依赖于子对象。执行它的第一个子步骤：ID=3TABLEACCESS(FULL)OF‘EMP’。它无子对象因此被执行。行记录被ID=3反馈给ID=2。由于ID=2是NESTEDLOOPS连接，所以第一个输入用来驱动连接。ID=2使用行记录来驱动它的第二个子步骤ID=4：INDEXUNIQUESCANPK_DEPT。行记录被ID=4反馈给父步骤ID=2，然后他们一并被反馈给父步骤ID=1。由于ID=1是NESTEDLOOPS连接，所以第一个输入用来驱动连接（NESTEDLOOPS的ID=1说明了之前取出的行记录被NESTEDLOOPS的ID=2使用）。ID=1使用行记录来驱动它的第二个子步骤ID=5：TABLEACCESSBYINDEXROWID|DEPT。行记录被ID=5反馈给ID=1。行记录被ID=1反馈给ID=0。行记录被ID=0反馈给客户端这个过程持续重复直至所有的行记录都从ID=2中取出了行记录。执行顺序是3，4，2，5，1，0。有很多种方法来描述如何判断一个计划中的操作，一旦熟悉了其中的一种那么其他的就很自然就熟悉了。有一种说法是说“解析计划是从最右-最上的操作开始执行的”，尽管经过一些练习，这不失成为一种直观的描述方式，它仍然会为一些读者造成困惑。如果仍然困惑，请按照id与父id的层次来理顺。访问方式细节FullTableScan(全表扫描) 执行计划中的“FullTableScans”是指全表扫描。全表扫描的工作机理是这样的：首先，Oracle的I/O是针对数据块的,通常一个数据块中存储着多条记录，被请求的记录要么聚集在少数几个块中，要么分散在大量的数据块中。而oracle对某个表进行全表扫描时，究竟应该读哪些数据块是根据全表扫描范围的标记-HWM(HighWaterMark)进行的。全表扫描将读取HWM之下的所有数据块，访问表中的所有行，每一行都要经WHERE子句判断是否满足检索条件。当Oracle执行全表扫描时会按顺序读取每个块且只读一次，因此如果能够一次读取多个数据块，可以提高扫描效率，初始化参数DB_FILE_MULTIBLOCK_READ_COUNT用来设置在一次I/O中可以读取数据块的最大数量。TABLEACCESSBYINDEXROWID(ROWID扫描)Rowid就是一个记录在数据块中的位置，由于指定了记录在数据库中的精确位置，因此rowid是检索单条记录的最快方式。如果通过rowid来访问表，Oracle首先需要获得被检索记录的rowid，Oracle可以在WHERE子句中得到rowid，但更多的是通过扫描索引来获得，然后Oracle基于rowid来定位被检索的每条记录。INDEXFULLSCAN(索引全扫描)全索引扫描就是对整个索引进行一次逐条扫描，只需要一次I/O。进行全索引扫描时因为有些查询条件必须对整个索引进行一次逐条扫描。INDEXUNIQUESCAN(索引唯一扫描)这种扫描通常发生在对一个主键字段或含有唯一约束的字段指定相等条件时，只有单行记录被访问。INDEXRANGESCAN(索引范围扫描)索引范围扫描通常发生在对一个索引字段指定范围条件时，有多行记录被访问。是检索数据的常用方式，返回的数据返照索引字段升序排列，字段值相同的则按照rowid升序排列。如果在语句中指定了orderby字句，而且排序字段是索引字段时Oracle将忽略orderby子句。INDEXFASTFULLSCAN(索引快速全扫描)索引快速全扫描只访问索引本身，而不去访问表，因此只有查询涉及的字段都包含在索引中时才会使用快速全索引扫描FILTER当Where语句中有In（Sql子查询）,Exists（Sql子查询）等条件时，执行计划中会有FILTER操作PARTITIONRANGEALL如果表是分区表，则对这个表查询的Sql语句的执行计划可能会出现PATITIONRANGEALL.NESTEDLOOP(嵌套连接)从执行计划的角度上看，表与表连接方法共有三种，嵌套循环是其中的一种，是执行计划中看到的最常见的一种连接。在嵌套循环中，内表被外表驱动，外表返回的每一行都要在内表中检索找到与它匹配的行。嵌套循环在小表驱动大表，并且返回结果小的情况下是最快的一种连接方式。对于嵌套循环来说，整个查询返回的结果集不能太大（大于1万不适合），要把返回子集较小表的作为外表（CBO默认外表是驱动表），而且在内表的连接字段上一定要有索引。HASHJOIN(散列连接)从执行计划的角度上看，表与表连接方法共有三种，散列连接是其中的一种。散列连接，又称哈希连接，是CBO做大数据集连接时的常用方式，优化器使用两个表中较小的表（或数据源）利用连接键在内存中建立散列表，然后扫描较大的表并探测散列表，找出与散列表匹配的行。这种方式适用于较小的表完全可以放于内存中的情况，这样总成本就是访问两个表的成本之和。但是在表很大的情况下并不能完全放入内存，这时优化器会将它分割成若干不同的分区，不能放入内存的部分就把该分区写入磁盘的临时段，此时要有较大的临时段从而尽量提高I/O的性能。MERGEJOIN&SORTJOIN(排序合并)从执行计划的角度上看，表与表连接方法共有三种，排序合并是其中的一种。一般我们称排序合并为SORTMERGE，在执行计划中表现为两个表分别作SortJoin然后再在一起做个MergeJion。sortmergejoin的操作通常分三步：对连接的每个表做tableaccessfull；对tableaccessfull的结果进行排序；进行mergejoin对排序结果进行合并。sortmergejoin性能开销几乎都在前两步。一般是在没有索引的情况下，9i开始已经很少出现了，因为其排序成本高，大多为hashjoin替代了。通常情况下hashjoin的效果都比sortmergejoin要好，然而如果行源已经被排过序，在执行sortmergejoin时不需要再排序了，这时sortmergejoin的性能会优于hashjoin。在全表扫描比索引范围扫描再通过rowid进行表访问更可取的情况下，sortmergejoin会比nestedloops性能更佳。性能优化实践通过优化数据库对象提升性能数据数据库对象的信息统计对于CBO,数据库根据搜集的表和索引的数据的统计信息综合来决定选取一个数据库认为最优的执行计划,统计信息给出表的大小、有多少行、每行的长度等信息。统计信息需要经过收集之后才存在，并且需要不断的更新，没有统计信息或者过期的统计信息都会使优化得出低效的执行计划。Oracle需要系统定期分析统计表/索引，只有这样CBO才能使用正确的SQL访问路径，提高查询效率。在11g数据库中，Oracle有自己默认的调度任务去定时收集统计信息SQL>selecta.window_name,a.repeat_interval,a.durationSQL>selecta.window_name,a.repeat_interval,a.duration2fromdba_scheduler_windowsa,dba_scheduler_wingroup_membersb3wherea.window_name=b.window_name4andb.window_group_name='MAINTENANCE_WINDOW_GROUP';WINDOW_NAMEREPEAT_INTERVALDURATIONMONDAY_WINDOWfreq=daily;byday=MON;byhour=22;byminute=0;bysecond=0+00004:00:00TUESDAY_WINDOWfreq=daily;byday=TUE;byhour=22;byminute=0;bysecond=0+00004:00:00WEDNESDAY_WINDOWfreq=daily;byday=WED;byhour=22;byminute=0;bysecond=0+00004:00:00THURSDAY_WINDOWfreq=daily;byday=THU;byhour=22;byminute=0;bysecond=0+00004:00:00FRIDAY_WINDOWfreq=daily;byday=FRI;byhour=22;byminute=0;bysecond=0+00004:00:00SATURDAY_WINDOWfreq=daily;byday=SAT;byhour=6;byminute=0;bysecond=0+00020:00:00SUNDAY_WINDOWfreq=daily;byday=SUN;byhour=6;byminute=0;bysecond=0+00020:00:007rowsselected当然，Oracle在默认调度是在工作日的晚上十点运行，数据统计收集也是需要消耗资源的，为了避开高峰期，我们也可以按照系统的特点设置调度任务beginbegindbms_scheduler.set_attribute(name=>'SYS.MONDAY_WINDOW',attribute=>'repeat_interval',value=>'freq=daily;byday=MON;byhour=1;byminute=0;bysecond=0');dbms_scheduler.set_attribute(name=>'SYS.MONDAY_WINDOW',attribute=>'duration',value=>'001:00:00');end; 查看任务是否启用selectclient_name,statusselectclient_name,statusfromdba_autotask_clientwhereclient_name='autooptimizerstatscollection';启用，禁用任务 beginbegindbms_auto_task_admin.enable(client_name=>'autooptimizerstatscollection',operation=>null,window_name=>null);end;begindbms_auto_task_admin.disable(client_name=>'autooptimizerstatscollection',operation=>null,window_name=>null);end;EBS中，任务默认是不启用的，并且我们也不会去启用这个任务，EBS提供了如下并发程序进行数据统计收集：统计数据收集表，收集所有列的统计数据，分析所有索引列的统计数据，收集列的统计数据，统计数据收集模式。通过查看可执行，这些程序均调用FND_STATS来进行数据统计收集，我们最常用的是统计数据收集模式。Oracle建议当我们系统安装完成或者升级完成之后，进行一次比较全面的数据统计收集，然后每段时间，根据需求定期进行收集。数据统计收集模式几个比较重要的参数的意义是：模式名：就是EBS中注册的应用名，可以选择某个应用或者选择全部估计百分比：预估行的百分比，如果为空的话，Oracle将会默认置为10%，合法的值为0-99，值越大，收集到的数据信息越准确，CBO将更能评估出最高效的执行计划，不过收集的时间也会更长。程度：即为并行度的程度，如果保持为空，则默认为cpu_count与parallel_max_servers的较小者备份标志：如果选择为NOBACKU则不备份当前收集信息，这样会跑得相对快一些，如果选择为BACKUP则在收集前会导出当前收集信息其他参数默认即可。注意：数据统计收集模式后台调用gather_table_stats时，参数cascade设置为了true，因此表相关的索引信息也将会收集，无需单独运行索引的数据收集。建议：当新安装系统或者完成系统升级之后，运行一次模式名为全部，估计百分比为99的数据统计收集模式，后续根据需要选择系统负载较小的时段，运行需要收集的模块，估计百分比设置为30即可。当然如果对表有大数据量操作，则百分比应调高，对表操作频率较高的时段，收集频率应该调高。高水位(HWM)的理解与优化首先要理解一个概念，即oracle数据块（Block）的组织方式：任何一个需要占用磁盘空间的数据库对象（比如表，索引等），从磁盘空间角度上看都体现为一个段（Segment），一个段由多个扩展（Extent）组成,Oracle的Extent是逻辑上的存储单位。一个Extent包含N个连续的BLOCK.N的数量取决于建表时指定的NextExtentS的大小。比如NextExtents是64K.而一个BLOCK是8K，则一个Extend包含８个连续的BLOCK.一个Extend一旦分配给某个Table就不能再分配给其他Table.每个段（Segment）的第一个扩展（Extent）的第一个数据块Block）是被oracle系统保留的，不能用于存储用户数据。这个块称之为段头（Segmentheader）段头（Segmentheader）中包含如下信息：1、段中的扩展信息表（ExtentsTable）2、剩余空间列表描述信息（freelistdescription）3、高水位标记（HighwaterMark(HWM)）一个Extend中已经使用了哪些BLOCK,还有哪些没有使用？这是靠HighWaterMark来标记的。HighwaterMark(HWM)顾名思义，非常形象，他把一个Segment看作一个桶。而把存储于其中的数据比作水。水平面就形成了一个高水位标记。当一个表被创建，还未有数据插入时，SEGMENT中所有的BLOCK都处于未使用未格式化状态，HWM在Segment的开始位置，且SEGMENT的第一个BLOCK将会记录HWM的位置当有事物插入数据到此SEGMENT中，数据库必须分配一组BLOCK来存储这些行，当高水位以下的BLOCK不足以存储需要插入的数据时，高水位将会往上移，当数据被删除时，高水位并不会下降。HWM是否合理直接影响到查询时全表扫面的性能，全表扫面会扫面HWM以下的所有BLOCK。经过大量Delete操作的表，则有必要通过技术手段降低其高水位。下面show_space程序可查看BLOCK使用信息。createorreplaceprocedureshow_space(p_segnameinvarchar2,createorreplaceprocedureshow_space(p_segnameinvarchar2,p_ownerinvarchar2defaultuser,p_typeinvarchar2default'TABLE',p_partitioninvarchar2defaultnull)asl_free_blksnumber;l_total_blocksnumber;l_total_bytesnumber;l_unused_blocksnumber;l_unused_bytesnumber;l_lastusedextfileidnumber;l_lastusedextblockidnumber;l_last_used_blocknumber;l_segment_space_mgmtvarchar2(255);l_unformatted_blocksnumber;l_unformatted_bytesnumber;l_unformatted_bytesnumber;l_fs1_blocksnumber;l_fs1_bytesnumber;l_fs2_blocksnumber;l_fs2_bytesnumber;l_fs3_blocksnumber;l_fs3_bytesnumber;l_fs4_blocksnumber;l_fs4_bytesnumber;l_full_blocksnumber;l_full_bytesnumber;procedurep(p_labelinvarchar2,p_numinnumber)isbegindbms_output.put_line(rpad(p_label,40,'.')||to_char(p_num,'999,999,999,999'));end;beginexecuteimmediate'selectts.segment_space_managementfromdba_segmentsseg,dba_tablespacestswhereseg.segment_name=:p_segnameand(:p_partitionisnullorseg.partition_name=:p_partition)andseg.owner=:p_ownerandseg.tablespace_name=ts.tablespace_name'intol_segment_space_mgmtusingp_segname,p_partition,p_partition,p_owner;l_segment_space_mgmt:='AUTO';ifl_segment_space_mgmt='AUTO'thendbms_space.space_usage(p_owner,p_segname,p_type,l_unformatted_blocks,l_unformatted_bytes,l_fs1_blocks,l_fs1_bytes,l_fs2_blocks,l_fs2_bytes,l_fs3_blocks,l_fs3_bytes,l_fs4_blocks,l_fs4_bytes,l_full_blocks,l_full_bytes,p_partition);p('UnformattedBlocks',l_unformatted_blocks);p('FS1Blocks(0-25)',l_fs1_blocks);p('FS2Blocks(25-50)',l_fs2_blocks);p('FS3Blocks(50-75)',l_fs3_blocks);p('FS4Blocks(75-100)',l_fs4_blocks);p('FullBlocks',l_full_blocks);elsedbms_space.free_blocks(segment_owner=>p_owner,segment_name=>p_segname,segment_type=>p_type,freelist_group_id=>0,free_blks=>l_free_blks);endif;dbms_space.unused_space(segment_owner=>p_owner,segment_name=>p_segname,segment_type=>p_type,partition_name=>p_partition,total_blocks=>l_total_blocks,total_bytes=>l_total_bytes,unused_blocks=>l_unused_blocks,unused_bytes=>l_unused_bytes,last_used_extent_file_id=>l_lastusedextfileid,;last_used_extent_block_id=>l_lastusedextblockid,last_used_extent_block_id=>l_lastusedextblockid,last_used_block=>l_last_used_block);p('TotalBlocks',l_total_blocks);p('TotalBytes',l_total_bytes);p('TotalMBytes',trunc(l_total_bytes/1024/1024));p('UnusedBlocks',l_unused_blocks);p('UnusedBytes',l_unused_bytes);p('LastUsedExtFileId',l_lastusedextfileid);p('LastUsedExtBlockId',l_lastusedextblockid);p('LastUsedBlock',l_last_used_block);end;Plsql中，使用示例：beginbeginshow_space(p_owner=>'INV',p_segname=>'MTL_MATERIAL_TRANSACTIONS');end;结果UnformattedBlocks0UnformattedBlocks0FS1Blocks(0-25)253,222FS2Blocks(25-50)376,842FS3Blocks(50-75)178,734FS4Blocks(75-100)192,301FullBlocks6,682,340TotalBlocks7,714,656TotalBytes63,198,461,952TotalMBytes60,270UnusedBlocks0UnusedBytes0LastUsedExtFileId44LastUsedExtBlockId710,921LastUsedBlock16MOVE方式：对于普通的表，通过Altertabletable_owner.table_namemove来进行碎片的整理与高水位的降低。AltertableAltertableINV.MTL_MATERIAL_TRANSACTIONSmove;再次执行show_spaceUnformattedBlocks0UnformattedBlocks0FS1Blocks(0-25)0FS2Blocks(25-50)0FS3Blocks(50-75)0FS4Blocks(75-100)0FullBlocks7,449,123TotalBlocks7,479,392TotalBytes61,271,179,264TotalMBytes58,432UnusedBlocks0UnusedBytes0LastUsedExtFileId415LastUsedExtBlockId418,640LastUsedBlock16当BLOCK被使用，然后数据又被DELETE之后，高水位并不会下降。而可以看到，已使用的TotalBlock，以及Block的分布都已经有了改变。HWM已降了下来。注意，使用move降低高水位时，容易导致索引失效，往往我们还需查出无效的索引rebuilt1declaredeclarel_statementvarchar2(1000);l_tab_ownervarchar2(100):='&tab_owner';l_tab_namevarchar2(100):='&tab_name';beginFORrecIN(SELECTindex_name,tablespace_name,ownerFROMdba_indexestWHEREt.table_name=l_tab_nameANDstatus<>'VALID'ANDt.table_owner=l_tab_owner)LOOPl_statement:='ALTERINDEX"'||rec.owner||'"."'||rec.index_name||'"REBUILDTABLESPACE'||rec.tablespace_name;EXECUTEIMMEDIATEl_statement;ENDLOOP;end;对于分区表，MOVE时，需要指明具体分区，Rebuilt时，也需要指明索引分区declaredeclarel_statementvarchar2(1000);l_tab_ownervarchar2(100):='&tab_owner';l_tab_namevarchar2(100):='&tab_name';beginFORrecIN(SELECTdtp.partition_name,dtp.tablespace_nameFROMdba_tab_partitionsdtpWHEREdtp.table_name=l_tab_nameANDdtp.table_owner=l_tab_owner)LOOPl_statement:='ALTERTABLE"'||l_tab_owner||'"."'||l_tab_name||'"MOVEPARTITION"'||rec.partition_name||'"TABLESPACE'||rec.tablespace_name;EXECUTEIMMEDIATEl_statement;ENDLOOP;end;declaredeclarel_statementvarchar2(1000);l_tab_ownervarchar2(100):='&tab_owner';l_tab_namevarchar2(100):='&tab_name';beginFORrecIN(SELECTindex_name,tablespace_name,ownerFROMdba_indexestWHEREt.table_name=l_tab_nameANDstatus<>'VALID'ANDt.table_owner=l_tab_owner)LOOPFORrec1IN(SELECTt.index_owner,t.index_name,t.partition_name,t.status,t.tablespace_nameFROMdba_ind_partitionstWHEREt.index_owner=rec.ownerANDt.index_name=rec.index_nameANDt.status<>'USABLE')LOOPl_statement:='ALTERINDEX"'||rec1.index_owner||'"."'||rec1.index_name||'"REBUILDPARTITION"'||rec1.partition