《ORACLE执行计划和SQL调优.ppt》由会员分享，可在线阅读，更多相关《ORACLE执行计划和SQL调优.ppt（150页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、ORACLE调优调优吴志忠2009.09面向对象n懂基本数据库常识n有ORACLE开发使用经验n乐于接受性能优化探讨内容安排n第一部分：背景知识n第二部分：SQL调优n第三部分：工具介绍n第四部分ROWID高级应用n第五部分附录调优方面n1商业逻辑n2优化数据设计n3优化应用程序设计n4优化数据库逻辑设计n5优化数据库操作优化数据库操作n6优化访问路径n7优化内存分配n8优化I/O和物理结构n9优化资源争用n10优化所采用的平台*越靠前越重要越靠前越重要第一部分背景知识nSQL处理流程n优化器n缓冲池n执行计划n变量绑定n索引n表连接SQL执行的步骤n解析：安全性检查，语法检查；n创建：评估多
2、个执行计划，并选择一个最优的执行计划；n执行：捆绑变量，执行已经创建的执行计划；n获取：获取结果集，进行转换，排序等；SQL处理流程第1步:CreateaCursor创建游标第2步:ParsetheStatement分析语句第5步:BindAnyVariables绑定变量第7步:RuntheStatement运行语句第9步:ClosetheCursor关闭游标如果使用了并行功能，还会包含下面这个阶段：第6步:ParallelizetheStatement并行执行语句如果是查询语句，则需要以下几个额外的步骤第3步:DescribeResultsofaQuery描述查询的结果集第4步:Define
3、OutputofaQuery定义查询的输出数据第8步:FetchRowsofaQuery取查询出来的行SQL优化器(Optimizer)n概念：是一个为所有的sql语句创建执行计划的工具。n目的：生成最快的，消耗资源最少的执行计划。n两种优化器a.RBO,Rule-BasedOptimizerb.CBO,Cost-BasedOptimizerRBO特性n总是使用索引（不识别位图索引或基于函数的索引）n总是从驱动表开始n只有在不可避免的情况下，才使用全表扫描n索引选择的随机特性*从从ORACLE10G开始，开始废弃开始，开始废弃RBO优化器。这句话并不是指在优化器。这句话并不是指在ORACLE1
4、0G中不中不能使用能使用RBO，而是从，而是从ORACLE10G开始开始，不再为开始开始，不再为RBO的的BUG提供修补服务提供修补服务CBO特性n前提条件：存在表和索引的统计资料；使用analyzetable和analyzeindex命令从表或索引中收集统计资料（表的记录平均长度，记录数等）；如果没有现存的统计资料，将在sql运行时收集资料，会大大降低性能；n影响CBO执行计划成本评估的初始化参数较多（optimizer_search_limit,optimizer_max_permutations,optimizer_index_caching,hash_area_size,hash_jo
5、in_enable,hash_multiblock_io_count,star_transformation_enable,optimizer_index_cost_adj等）SQL优化器模式n实例级通过对init.ora中OPTIMIZER_MODE参数,2会话级ALTERSESSIONSETOPTIMIZER_MODE=;3语句别hintnCHOOSE不是优化器,决定用什么优化器的参数:如果表有分析资料,便用CBO,否则用RBOn在缺省情况下,ORACLE采用CHOOSE优化器模式CHOOSERBOCBOFIRST_ROWS_n(OLTP系统)FIRST_ROWSALL_ROWS(DSS系
6、统)表分析n为了使用CBO,必须经常运行analyze命令,以增加数据库中的对象统计信息n多表连接时只要有一个表分析过，就用CBOn7i:ANALYZE和DBMS_UTILITY从815开始有DBMS_STATUS从10g以后可以自动分析表,有个系统job(GATHER_STATS_JOB)。SQL优化器处理体系结构n解析程序n优化程序n行源产生程序nSQL执行SQL优化器处理流程图-1SQL优化器处理流程图-2SQL优化器处理流程图-3缓冲池nPGAProgramGlobalArea是为每个连接到Oracledatabase的用户进程保留的内存nSGASystemGlobalArea是Ora
7、cleInstance的基本组成部分，在实例启动时分配;系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。缓冲池-SGAn共享池(Sharedpool)SQL语句缓冲(LibraryCache)也叫库缓冲区数据字典缓冲区(DataDictionaryCache)n块缓冲区高速缓存(DatabaseBufferCache)n重做日志缓冲区(Redologbuffer)nJava程序缓冲区(JavaPool)n大池(LargePool)执行计划n概念就是对一个查询任务，做出一份怎样去完成任务的详细方案.n工具变量绑定n为什么DBA要求我们变量绑定n预编译概念n怎样处理(?,?Pro
8、cedure等)变量绑定必须满足的条件n字符级的比较n两个SQL语句中必须使用相同的名字的绑定变量n两个语句所指的对象必须完全相同用户对象名如何访问Jacksal_limitprivatesynonymWork_citypublicsynonymPlant_detailpublicsynonymJillsal_limitprivatesynonymWork_citypublicsynonymPlant_detailtableownerSQL能否共能否共享享原因原因select max(sal_cap)from sal_limit;不能每个用户都有一个private synonym-sal_li
9、mit,它们是不同的对象select count(*0 from work_city where sdesc like NEW%;能两个用户访问相同的对象public synonym-work_city select a.sdesc,b.location from work_city a,plant_detail b where a.city_id=b.city_id不能用户jack 通过private synonym访问plant_detail 而jill 是表的所有者,对象不同.Statement和preparedStatementnPreparedStatement对象的开销比State
10、ment大，对于一次性操作并不会带来额外的好处npreparedstatement是预编译npreparedstatement支持批处理nSQL注入，安全,强制类型转换n不同数据库不同IBATIS预编译证据packagecom.ibatis.sqlmap.engine.execution;-SqlExecutorpublicintexecuteUpdate(RequestScoperequest,Connectionconn,Stringsql,Objectparameters)throwsSQLExceptionErrorContexterrorContext=request.getErro
11、rContext();errorContext.setActivity(executingupdate);errorContext.setObjectId(sql);PreparedStatementps=null;setupResultObjectFactory(request);introws=0;tryerrorContext.setMoreInfo(ChecktheSQLStatement(preparationfailed).);ps=prepareStatement(request.getSession(),conn,sql);setStatementTimeout(request
12、.getStatement(),ps);errorContext.setMoreInfo(Checktheparameters(setparametersfailed).);request.getParameterMap().setParameters(request,ps,parameters);errorContext.setMoreInfo(Checkthestatement(updatefailed).);ps.execute();rows=ps.getUpdateCount();finallycloseStatement(request.getSession(),ps);return
13、rows何时何时Oracle使用绑定变量性能更差使用绑定变量性能更差nhttp:/ 用集合表代替与其它表关联，参见tstr2numlist,str2varlist曾测过:fain(a,b)fa=aorfa=b编译成这样执行n用EXISTS替代INn用notexists或者（外联结+判断为空）来代替NOTINn用表连接替换EXISTSn用EXISTS替换DISTINCTn其它相同功能的操作运算代替;a0改为a0ora0a改为ana0或a代替aisnotnullnA=2代替A1nLikeaaa%可以利用索引like%aaa肯定不走索引，其它方案全文搜索或利用三方引擎技术如Lucene调整SQL（续
14、）nUNIONALL代替UNIONn查询表顺序的影响;RBOnWHERE后面的条件顺序影响RBOn禁止使用连接列,除非有相应该索引n禁止对列使用函数和表达式,除非有相应该索引n显视指定列类型，如fa=99禁止用fa=99来对应varchar字段n分页时先将当前页的记录rowid取出来，然后再关联取其它信息n禁止使用select*，因对*进行解析n用TRUNCATE替代DELETE(删除全表，必要时用)n在事物完整时尽量多提交事务。可以释放相当多的资源nWhere子句替换HAVING(后面有例)n减少对表的查询(后面有例)n使用表的别名计算记录条数计算记录条数n和一般的观点相反，count（*）
15、比count（1）稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如COUNT（EMPNO）n这个问题在各大论坛中，都有过相当热烈的讨论，我也并不十分准确，通过实际的测试，上述三种方法并没有显著的性能差别n按DBA要求做。count（*）用Where子句替换HAVING子句n避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤。这个处理需要排序，总计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。n例如：n低效：nSELECTREGION，AVG(LOG_SIZE)nFROMLOCATIONnGROUPBYREGIONnHAVI
16、NGREGIONREGION!=SYDNEYnANDREGION!=PERTHn高效nSELECTREGION，AVG(LOG_SIZE)nFROMLOCATIONnWHEREREGIONREGION!=SYDNEYnANDREGION!=PERTHnGROUPBYREGIONnHAVING中的条件一般用于对一些集合函数的比较，如中的条件一般用于对一些集合函数的比较，如COUNT（）（）等等。等等。除此而外，一般的条件应除此而外，一般的条件应该写在该写在WHERE子句中子句中减少对表的查询减少对表的查询n在含有子查询的SQL语句中，要特别注意减少对表的查询。n例如：n低效nSELECTTAB_
17、NAMEnFROMTABLESnWHERETAB_NAME=(SELECTTAB_NAMEnFROMTAB_COLUMNSnWHEREVERSION=604)nANDDB_VER=(SELECTDB_VERnFROMTAB_COLUMNSnWHEREVERSION=604)n高效nSELECTTAB_NAMEnFROMTABLESnWHERE(TAB_NAME,DB_VER)n=(SELECTTAB_NAME,DB_VER)nFROMTAB_COLUMNSnWHEREVERSION=604)n减少对表的查询减少对表的查询(2)nUpdate多个Column例子：n低效：nUPDATEEMPnS
18、ETEMP_CAT=(SELECTMAX(CATEGORY)FROMEMP_CATEGORIES),nSAL_RANGE=(SELECTMAX(SAL_RANGE)FROMEMP_CATEGORIES)nWHEREEMP_DEPT=0020;n高效：nUPDATEEMPSET(EMP_CAT,SAL_RANGE)=(SELECTMAX(CATEGORY),MAX(SAL_RANGE)FROMEMP_CATEGORIES)WHEREEMP_DEPT=0020;使用表的别名使用表的别名n当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减
19、少那些由Column歧义引起的语法错误。用用EXISTS替代替代INn在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用EXISTS（或NOTEXISTS）通常将提高查询的效率。n低效：nSELECT*nFROMEMP(基础表)nWHEREEMPNO0nANDDEPTNOIN(SELECTDEPTNOnFROMDEPTnWHERELOC=MELB)n高效：nSELECT*nFROMEMP(基础表)nWHEREEMPNO0nANDEXISTS(SELECTXnFROMDEPTnWHEREDEPT.DEPTNO=EMP.DEPTNOnANDLOC=MELB)
20、用用NOTEXISTS替代替代NOTINn在子查询中，NOTIN子句将执行一个内部的排序和合并。无论在哪种情况下，NOTIN都是最低效的（因为它对子查询中的表执行了一个全表遍历）。为了避免使用NOTIN，我们可以把它改写成外连接（OuterJoins）或NOTEXISTS.n例如：nSELECTnFROMEMPnWHEREDEPT_NONOTIN(SELECTDEPT_NOFROMDEPTWHEREDEPT_CAT=A);n为了提高效率。改写为：n（方法一：高效）nSELECT.nFROMEMPA,DEPTBnWHEREA.DEPT_NO=B.DEPT(+)nANDB.DEPT_NOISNUL
21、LnANDB.DEPT_CAT(+)=A（方法二：最高效）nSELECT.nFROMEMPEnWHERENOTEXISTS(SELECTXnFROMDEPTDnWHERED.DEPT_NO=E.DEPT_NOnANDDEPT_CAT=A);用表连接替换EXISTSn通常来说，采用表连接的方式比EXISTS更有效率nSELECTENAMEnFROMEMPEnWHEREEXISTS(SELECTXnFROMDEPTnWHEREDEPT_NO=E.DEPT_NOnANDDEPT_CAT=A);n（更高效）nSELECTENAMEnFROMDEPTD,EMPEnWHEREE.DEPT_NO=D.DEP
22、T_NOnANDDEPT_CAT=A;用EXISTS替换DISTINCTn当提交一个包含一对多表信息（比如部门表和雇员表）的查询时，避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换n例如：n低效：nSELECTDISTINCTDEPT_NO,DEPT_NAMEnFROMDEPTD,EMPEnWHERED.DEPT_NO=E.DEPT_NOn高效：nSELECTDEPT_NO,DEPT_NAMEnFROMDEPTDnWHEREEXISTS(SELECTXnFROMEMPEnWHEREE.DEPT_NO=D.DEPT_NO);nEXISTS使查询更为迅速，因为RDBMS核
23、心模块将在子查询的条件一旦满足后，立刻返回结果。基于RBO的调整原则n驱动表的设置：在RBO中，驱动表是from子句的最后一个表；驱动表应该是返回记录最少的那个表；nWhere子句设置：限制性最强的布尔表达式放在最底层；n添加基于成本的提示，来获得更快的执行计划；基于CBO的调整原则n优化器自动执行表连接调整原则nRBO只能调用nestedloop和mergesort连接；hashjoin和star连接只能在CBO中得到；n尽量不要使用notin反连接子查询，把它替换成标准等连接，用外连接和wherecolumnisnull子句删除多余的记录；或者尽量替换成notexists子查询，因为它将调
24、用相关联的子查询；n半连接子查询可以重新书写成标准等连接，用selectdistinct子句删除重复的记录；表连接调整原则（续）n如果驱动表较小，可以完全装入hash_area_size内存中，使用散列连接（hashjoin）速度比嵌套循环连接（nestedloop）快；n在两个表非常大的情况下，经常使用嵌套循环连接（nestedloop）；n生成大型结果集的查询、不使用where子句的大表连接或表中无可用索引的查询，经常使用排序合并连接（srotmerge)；n对于多个小的维表和一个大的事实表的情况下（数据仓库），经常使用星型连接(star)；SQL子查询调整子查询类型n标准子查询：in和e
25、xists；n反连接子查询：notin和notexists；n关联子查询：指在子查询内部引用外部数据表；fromtable1awhere(selectfromtable2bWherea.f1=b.f1)；对于外部数据集的每一条记录，都将重新执行一次内部子查询；n非关联子查询：指在子查询内部不会引用外部的数据表；fromtable1awhere(selectfromtable2b)；内部子查询只执行一次；子查询调整原则n只要可能的话，尽可能的避免使用子查询，而用标准的连接操作来代替，这样可以使用提示来更改执行计划；n先考虑子查询的合法性，再考虑进行改写；n使用一个关联子查询时，in与exists
26、子句的子查询的执行计划基本相同；n在外部查询返回相对较少的记录时，关联子查询比非关联子查询执行得更快；子查询调整原则（续）n在内部子查询只有少量的记录时，非关联子查询比关联子查询执行得更快；n关联子查询使用in子句是多余的；而非关联子查询使用exists子句是不恰当的。n使用in子句的非关联子查询可以转换为标准连接操作以及使用selectdistinct来删除重复的记录；n使用exists子句的关联子查询可以转换为标准连接，但子查询最好只能返回一个记录；子查询调整原则（续）n非关联子查询使用notexists子句是没有意义的；n使用notin子句的非关联子查询可以转sqlminus子句，性能相
27、对会高一些；n使用notin子句的关联子查询，可以使用带有selectdistinct子句的外部连接操作改写；各种子查询技术总结标准子查询反连接子查询inexistsNotinNotexists关联子查询多余的自动转换为嵌套的循环连接可以重写为selectdistinct外部连接可以重写为selectdistinct外部连接非关联子查询自动转换为嵌套的循环连接不适合可以重写为minus操作符的嵌套循环连接不适合例子：使用in子句的非关联子查询可以使用标准连接操作以及使用selectdistinct来删除重复的记录；原sql语句：SelectenameFromempWhereempnoin(se
28、lectempnofrombad_creditwherebad_credit_datesysdate-365);改写后：Selectdistinct/*+rule*/enameFromemp,bad_creditWherea.empno=b.empnoandbad_credit_datesysdate-365;使用提示进行调整提示简介n历史：第一次引入是在oracle7，用来弥补CBO的缺陷，oracle8i中工作得较好；n目的：用来更改SQL语句的执行计划；n格式：select(update,delete)/*+hints*/或select(update,delete)-+hint使用提示遵
29、循的原则n注意检查语法：select/*+hint*/，/*和+之间不能有空格，必须紧跟在select之后，否则无效n使用表别名：如果指定了表别名，就不能使用表名称；n不能使用模式名称：如果指定了模式所有者，那么提示将被忽略；n检验提示：如果指定不可用的访问路径，如：first_rows优化器模式与orderby子句不兼容，那么提示将被忽略；n警告：如果该提示语句书写不正确，则警告：如果该提示语句书写不正确，则Oracle就忽略就忽略掉该语句。掉该语句。使提示无效的条件nCluster，hash：与非簇表一起使用；nMerge_aj,push_subq,Hash_aj：不存在子查询；nInde
30、x：指定的索引不存在；nIndex_combine：不存在位图索引；nParellel：调用的不是全表扫描计划；nStar：事实表中存在不恰当索引；nUse_concat：在where子句中不存在多个索引；nUse_nl：表中不存在索引；常用索引提示n。rule。all_rows。first_rows。use_nl。use_hash。use_merge。index。index_asc。no_index。index_desc（常用于使用max内置函数）。index_combine(强制使用位图索引)。index_ffs（索引快速完全扫描）。use_concat(将查询中所有or条件使用union
31、all)。parallel。noparallel。full。ordered（基于成本）提示之一：优化器提示nRule：使oracle为查询应用基于规则的优化模式。在怀疑CBO使用了非优化的执行计划时，使用rule提示；它将忽略表和索引的统计资料；/*+rule*/nAll_rows：基于成本的优化方法。目的是提供最佳的吞吐量和最小的资源消耗。倾向于全表扫描，不适用于OLTP系统；依赖于表和索引的统计资料；/*+all_rows*/nFirst_rows:基于成本的优化方法。目的是提供最快的反映时间。依赖于表和索引的统计资料；/*+first_rows*/提示之二：表连接提示nUse_hash：
32、对指定的表执行一个散列连接；如果有一个表较小，通常快于嵌套循环连接；在两个表非常大的情况下，散列连接经常与并行查询连接结合使用；select/*+use_hash(a,b)parallel(a,4)parallel(b,4)*/;nUse_merge：强制执行一个排序合并操作；对表执行全表扫描；通常与并行查询结合使用；最适用于生成大型结果集的查询、不使用where子句的大表连接或表中无可用索引的查询；select/*+use_merge(a,b)parallel(a,4)parallel(b,4)*/表连接提示（续）nUse_nl：强制对目标表执行嵌套循环连接；对包含两个大表的连接通常最快；可
33、以不用更改from子句表的顺序来更改驱动表（使用CBO时，from子句的第一个表）；是CBO的默认行为，比较少用；/*+use_nl(a)*/nStar：强制使用星型查询计划；查询中至少存在三个表（一个事实表和几个维表），而且事实表（大表）存在恰当的索引（8i可以使用位图索引）；/*+star*/提示之三：反连接提示n反连接是在SQL语句中包含notin或notexist子句时执行的操作；如果子查询返回的任何一条记录包含空值，那么该查询将不会返回记录；应尽量避免使用。nHash_aj，Merge_aj：在notin子查询的字段中不存在空值的时候，根据连接的类型，考虑使用这两个提示之一，可以在很
34、大程度上提高notin子查询的性能；/*+hash_aj*/提示之四：索引提示nIndex：优化器将使用指定的索引；如果没有指定索引，优化器将使用表中最佳的索引；/*+index(table,index)*/nIndex_join：要求优化器使用索引连接作为访问路径；nAnd_equal：如果表拥有非唯一的单独字段索引，而且期望使用多个索引服务于该查询，那么使用该提示将合并这些索引；至少两个索引名，但不能超过五个；/*+and_equal(table,index1,index2,)*/索引提示（续）nIndex_asc：要求在范围扫描中使用升序索引；优化器的默认行为，不常用；nNo_index
35、：强制优化器忽略索引得存在。一般用在并行全表扫描性能高于索引范围扫描性能的情况下；等同于full提示；nIndex_desc：要求在范围扫描中使用降序索引；如在max（）计算字段的最大值的时候使用；/*+index_desc(table,index)*/索引提示（续）nIndex_combine：强制使用位图索引作为表的访问路径，对两个位图索引执行ROWID交集操作。如果没有指定索引作参数，优化器将自动选择最佳的位图索引；/*+table(bitmap1,bitmap2)*/nIndex_ffs：强制使用快速完全索引扫描；如果大表中不存在被查询字段的高层索引主键，比如需要选择复合索引中第二个字
36、段的值时，那么快速完全索引扫描总是比全表扫描速度更快；/*+index_ffs(table,comindex)*/索引提示（续）nUse_concat：要求为查询中所有or条件使用unionall执行计划；一般用在where子句中存在大量的or条件；提示之五：并行提示nParallel：要求表查询以并行模式执行；一般用在多个cpu的服务器上，与full提示一起使用；/*+full(table)parallel(table,8)*/nNoparallel：不希望对全表扫描使用并行机制，如对小表执行的全表扫描时，使用该提示；提示之六：表访问提示nFull：要求避开索引，调用全表扫描；读取表中大量的
37、数据块时；与parallel一起使用；nHash：选择散列扫描来访问指定的簇表；/*+hash*/nCluster：选择簇扫描来访问指定的簇表；/*+cluster*/nNocache：指定为keep池分配的表数据块放置在default池的中点；很少使用；表访问提示（续）nOrdered：要求表按照from子句指定的顺序进行连接；对连接多于4个表的查询非常有用，可以节省sql解析的时间；/*+ordered*/nOrdered_predicates：用来指定where子句中布尔条件评估的顺序；nPush_subq：要求查询数据块中的所有子查询在执行计划中尽可能早的被执行；用在子查询相对来说不很
38、昂贵，并且在很大程度上降低返回到记录数；如果子查询使用的是排序合并连接或调用远程表，提示将不起作用；调整索引索引调整n使用索引来消除一些不必要（如表中没有索引，排序合并连接）的排序操作；n通过增加索引来避免不合法的全表扫描（一般来说，在有序表中查询返回的记录数少于表记录数的40，或者在无序表中返回的记录数少于表中记录数的7）；n对于数据列的唯一值较少的字段，建立位图索引，以提高性能；n创建基于函数的索引来避免全表扫描；索引调整（续）n当数据列中数据值不均匀时，建立字段矩形图，以帮助系统选择恰当地访问方式来提高性能；n使用快速完全索引扫描来代替访问表的记录；/*+index_ffs*/分而治之n
39、对于那些在处理中需要连接多个表的应用，如果每个表的记录数都相当大时，不要想一次就完成所有的操作。这样会消耗大量的内存及回滚段等。从而影响速度。建议你采用分段处理的方法尽量将处理分为不同阶段来处理。n常见的统计信息，主要在设计阶段优化分区，分表，分库，日统计信息等设计思想，避免大数据量的操作。第三部分工具介绍n开发工具n测试语句性能工具n查看执行计划工具开发工具nSQL*PLUSnPL/SQLDEVELOPERnTOAD(强大，功能众多)n其它测试语句性能工具常用于测试语句性能的方法有nTKPROF实用程序nEXPLAINPLANnAUTOTRACE当设置SQL_Trace为有效，则系统对每条S
40、QL语句的执行情况提供EXPLAINPLANn解析、执行、取数据的计数；nCPU时间和占用时间；n物理读和逻辑读；n处理行数目；n所解析的用户名；n每次提交和回滚的情况。SQL_Trace实用工具常用于测试语句性能的方法有nALTERSYSTEMSETSQL_TRACE=TRUE;(实例内)nAltersessionSQL_TRACE=TRUE;(会话内)n警告：由于警告：由于SQL_TRACE实用程序会增加系统的开销，建议用完实用程序会增加系统的开销，建议用完后及时设置为后及时设置为FALSE。另外，如果将整个系统都进行跟踪的话，。另外，如果将整个系统都进行跟踪的话，则在则在INITsid.
41、ORA中设置中设置sql_trace=true。这样会使系统付出。这样会使系统付出更大的代价更大的代价。用TKPROF格式化跟踪文件nTKPROF将跟踪文件作为输入文件，在经过格式化后产生输出文件。由于跟踪文件是一系列的文件，在使用TKPROF时可以对单个文件进行格式化，也可以将所有的跟踪文件串在一切在进行格式化。n逐一对单个文件进行TKPROF，分别产生相应的输出文件。n对所有跟踪文件级联在一起在进行TKPROF处理，从而产生整个实例的格式化输出文件。nTKPROF命令语法：命令语法：nTKPROFfilename1,filename2SORT=opion,optionnPRINT=inte
42、gernAGGREGATE=YES|NOnINSERT=filename3nSYS=YES|NOnTABLE=schema.table|EXPLAIN=user/passwordnRECORD=filename用TKPROF格式化跟踪文件(续)经过tkprof处理输出的结构如nselectu.name,o.namefromobj$o,user$u,trigger$tnwheret.baseobject=:1andt.obj#=o.obj#nando.owner#=u.user#orderbyo.obj#ncallcountcpuelapseddiskquerycurrentrowsn-npar
43、se10.010.010000nexecute10.010.010000ngetch10.000.000100n-ntotal30.020.020100nInsertintoemployee(employee_id,last_name,first_name)nValues(295,Joe,Johnson);ncallcountcpuelapseddiskquerycurrentrowsn-nparse10.080.120000nexecute10.040.2053111ngetch00.000.000100n-ntotal20.120.3253111.用TKPROF格式化跟踪文件(续)nsel
44、ectlast_name,title,department_idnfromemployee;nncallcountcpuelapseddiskquerycurrentrowsn-nparse10.010.010000nexecute50.080.090000ngetch2500.090.105420264245n-ntotal2560.180.205420264245n分析1次；执行5次；取数250行；总CPU是0.18秒；处理用去0.20秒；从磁盘读54数据块；从SGA中得到266(202+64)；返回245行。n结论：花去0.18秒的CPU时间取到245行，还能接受。识别需要调整的语句识别
45、需要调整的语句n消耗超出CPU资源的能力；n分析、执行或取回的时间太长；n访问太多的数据块但只返回少量的行用用TKPROF生成解释计划生成解释计划n可以用EXPLAIN参数来产生SQL语句解释计划nselect*fromemp,deptnwhereemp.deptno=dept.deptno;ncallcountcpuelapseddiskquerycurrentrowsn-nparse10.160.2931300nexecute10.000.000000nfetch10.030.2622414nMissesinlibaraycacheduringparse:1nParseuserid(8)S
46、COTTnRowsExecutionPlann-nMEREGEJOINnSORTJOINnTABLEACCESS(FULL)OFDEPTnSORTJOINn14TABLEACCESS(FULL)OFEMPAUTOTRACE实用程序n以sys用户连接；n运行$ORACLE_HOME/sqlplus目录下的plustrace.sql脚本；ngrantplustracetopublic，对所有用户有效；n在sql*plus中运行setautoton命令，将自动跟踪sql的执行计划并提供sql统计资料；AUTOTRACE实用程序(续)nSQLselectd.dname,e.ename,e.salfro
47、mdeptd,empewhered.deptno=e.deptno;nExecutionPlann-n0SELECTSTATEMENTOptimizer=CHOOSEn10NESTEDLOOPSn21TABLEACCESS(FULL)OFEMPn31TABLEACCESS(BYINDEXROWID)OFDEPTn43INDEX(UNIQUESCAN)OFPK_DEPT(UNIQUE)nStatisticsn-n0recursivecallsn4dbblockgetsn28consistentgetsn0physicalreadsn0redosizen1207bytessentviaSQL*Ne
48、ttoclientn424bytesreceivedviaSQL*Netfromclientn2SQL*Netroundtripsto/fromclientn0sorts(memory)n0sorts(disk)n13rowsprocessedAUTOTRACE实用参数选件说明ON EXPALIN显示查询结果和执行路径，但没有统计数据TRACEONLY显示执行路径和统计，但没有查询结果TRACEONLY STATISTICS仅显示统计，没有执行路径和结果OFF设置autotrace 为无效STATSPACK介绍nStatspack是一款功能强大的，免费的，oracle自带的性能分析工具n当运行
49、脚本时，自动创建PERFSTAT用户。PERFSTAT用户拥有所有的对象，并被授权访问V$_视图。PERFSTAT变成快照的一个家族。专门搜集性能的数据。STATSPACK报告n1.数据库总体信息n含实例、版本、是否RAC、CPU、物理内存、oracle内存设置等等n2.每秒每事务的资源消耗情况n3.实例的各组件的命中率n4.共享池总体情况(SharedPoolStatistics)n5.等待时间最长的前5个等待事件(Top5TimedEvents)n含前5等待事件，两次采样间cpu占用，内存分配等信息。Oracle各版本等待事件并不完全相同，数量依版本升高而增加，关于各项等待事情的说明，三思
50、之前的学习动态性能表系列文章中有过介绍，有心的朋友可以去搜搜看。n6.DB所有等待事件(WaitEvents)nTotalwaittime=0.001的事件。n7.后台等待事件(BackgroundWaitEvents)nTotalwaittime=0.001的事件。n8.柱状显示的等待事件(WaitEventHistogram)n显示各等待事件不同响应时间的比例n9.根据CPU开销进行排序的SQL(SQLorderedbyCPU)n10.根据执行时间进行排序的SQL(SQLorderedbyElapsed)n11.根据BufferGets进行排序的SQL(SQLorderedbyGets)n