Oracle执行计划SQL语句执行效率问题查找与解决方法

PAGE/NUMPAGESOracle的SQL语句执行效率问题查找与解决方法识别占用资源较多的语句的方法（4种方法）测试组和最终用户反馈的与反应缓慢有关的问题。利用V_$SQLAREA视图提供了执行的细节。（执行、读取磁盘和读取缓冲区的次数）数据列EXECUTIONS：执行次数DISK_READS：读盘次数COMMAND_TYPE：命令类型（3:select,2:insert;6:update;7delete;47:pl/sql程序单元）OPTIMIZER_MODE：优化方式SQL_TEXT：Sql语句SHARABLE_MEM：占用sharedpool的内存多少BUFFER_GETS：读取缓冲区的次数用途1、帮忙找出性能较差的SQL语句2、帮忙找出最高频率的SQL3、帮忙分析是否需要索引或改善联接监控当前Oracle的session，如出现时钟的标志，示此进程中的sql运行时间较长。Trace工具：查看数据库服务的初始参数：timed_statistics、user_dump_dest和max_dump_file_sizeStep1:altersessionsetsql_trace=trueStep2:runsql；Step3:altersessionsetsql_trace=falseStep4:使用“TKPROF”转换跟踪文件Parse，解析数量大通常表明需要增加数据库服务器的共享池大小，query或current提取数量大表明如果没有索引，语句可能会运行得更有效，disk提取数量表明索引有可能改进性能，librarycache中多于一次的错过表明需要一个更大的共享池大小如何管理语句处理和选项基于成本（CostBased）和基于规则（RuleBased）两种优化器，简称为CBO和RBOOptimizerMode参数值:Choose：如果存在访问过的任何表的统计数据，则使用基于成本的Optimizer,目标是获得最优的通过量。如果一些表没有统计数据，则使用估计值。如果没有可用的统计数据，则将使用基于规则的OptimizerAll_rows：总是使用基于成本的Optimizer，目标是获得最优的通过量First_rows_n：总是使用基于成本的Optimizer，目标是对返回前N行（“n”可以是1，10，100或者1000）获得最优的响应时间First_rows：用于向后兼容。使用成本与试探性方法的结合，以便快速传递前几行RULE：总是使用基于规则的Optimizer使用数据库特性来获得有助于查看性能的处理统计信息（解释和AUTOTRACE）No1:ExplainPlan使用Explain工具需要创建Explain_plan表，这必须先进入相关应用表、视图和索引的所有者的帐户内.(@D:\oracle\ora92\rdbms\admin\utlxplan)表结构：STATEMENT_ID：为一条指定的SQL语句确定特定的执行计划名称。如果在EXPLANPLAN语句中没有使用SETSTATEMENT_ID，那么此值会被设为NULL。OPERATION：在计划的某一步骤执行的操作名称，例如：Filters，Index，Table，MargeJoinsandTable等。OPTION：对OPERATION操作的补充，例如：对一个表的操作，OPERATION可能是TABLEACCESS，但OPTION可能为byROWID或FULL。Object_Owner：拥有此databaseObject的Schema名或Oracle帐户名。Object_name：DatabaseObject名Object_type：类型，例如：表、视图、索引等等ID：指明某一步骤在执行计划中的位置。PARENT_ID：指明从某一操作中取得信息的前一个操作。通过对与ID和PARENT_ID使用ConnectBy操作，我们可以查询整个执行计划树。EXPLAIN搜索路径解释全表扫描（FullTableScans）(无可用索引,大量数据,小表,全表扫描hints,HWM(HighWaterMark),Rowid扫描)索引扫描索引唯一扫描（IndexUniqueScans）索引范围扫描（IndexRangeScans）索引降序范围扫描（IndexRangeScansDescending）索引跳跃扫描（IndexSkipScans）全索引扫描（FullScans）快速全索引扫描（FastFullIndexScans）索引连接（IndexJoins）位图连接（BitmapJoins）如何选择访问路径：CBO首先检查WHERE子句中的条件以及FROM子句，确定有哪些访问路径是可用的。然后CBO使用这个访问路径产生一组可能的执行计划，再通过索引、表的统计信息评估每个计划的成本，最后优化器选择成本最低的一个。表的连接方式：NestedLoops会循环外表（驱动表），逐个比对和内表的连接是否符合条件。在驱动表比较小，内表比较大，而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候，Oracle也会选择使用NL。基于Cost的Oracle优化器(CBO)会自动选择较小的表做外表。（优点：嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批,而不用等待整个结果集完全确定下来。缺点：如果内部行源表(读取的第二张表（内表）已连接的列上不包含索引,或者索引不是高度可选时,嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中提取的记录)非常庞大时,其他的连接方法可能更加有效。）SORT-mergeJOIN，将两表的连接列各自排序然后合并，只能用于连接列相等的情况，适合两表大小相若的情况（在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。但是,排列合并连接只能用于等价连接(WHERED.deptno=E.dejptno,而不是WHERED.deptno>=E.deptno)。排列合并连接需要临时的内存块,以用于排序(如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O。）HASHJOIN在其中一表的连接列上作散列，因此只有另外一个表做排序合并，理论上比SORTJOIN会快些，需要有足够的内存，而且打开了SORT_JOIN_ENABLE参数。(当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。和排序合并连接、群集连接一样，哈希连接只能用于等价连接。和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O（这将使这种连接方法速度变得极慢）。最后，只有基于代价的优化器才可以使用哈希连接。)索引连接:No2:AUTOTRACEsetautotrace使用步骤：1、以system登录2、创建plustrace角色;\sqlplus\admin\plustrce.sql3、向常规用户授予权限：grantplustraceto4、如果没有plan_table也要创建:\rdbms\admin\utlxplan.sqlsetautotrace选项on            显示查询结果，执行计划，统计数据onstatistics        显示查询结果，统计数据，不显示执行计划onexplain        显示查询结果，执行计划，不显示统计数据traceonly          显示执行计划和统计结果，但不包括查询结果traceonlystatistics  仅显示统计数据recursivecalls在用户级别和系统级别上生成的递归调用的数量。Oracle维护了一些用于内部处理的表。当oracle需要对这些表进行更改时，它就会在内部生成一个SQL语句，然后这个语句再生成一个递归调用。dbblockgets请求一个CURRENT块的次数consistentgets为一块请求consistentread的次数physicalreads从磁盘读取得数据块总数。这个数量等于“直接物理读取”的值加上读入缓冲区的所有数据块redosize生成的重做的总数量（以字节为单位）bytessentviaSQL*Nettoclient从前台进程发送给客户的总字节数bytesreceivedviaSQL*Netfromclient通过OracleNet从客户接收的总字节数SQL*Netroundtripsto/fromclient发送给客户和从客户接收的OracleNet消息的总数sorts(memory)完全在内存中执行并且不需要任何磁盘写入的排序操作的数量sorts(disk)至少需要一个磁盘写入的排序操作的数量rowsprocessed在操作过程中处理的行数四、最后，使用计时特性来测量和比较处理时间SettimingonV$session_event应观注一下event这列，这是我们调优的关键一列，下面对常出现的event做以简要的说明：a、bufferbusywaits，freebufferwaits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session_wait中的freebufferwait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整；freebufferwait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下：　　a.1增加写进程，同时要调整db_block_lru_latches参数　　示例：修改或添加如下两个参数　　db_writer_processes=4　　db_block_lru_latches=8　　a、2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师了解。　　b、dbfilesequentialread，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db_file_multiblock_read_count这个参数。　　c、dbfilescatteredread,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。　　d、latchfree,与栓相关的了，需要专门调节。　　e、其他参数可以不特别观注。本文的目的：1、说一说Oracle的Optimizer及其相关的一些知识。2、回答一下为什么有时一个表的某个字段明明有索引,当观察一些SQL的执行计划时,发现确不走索引的问题。3、如果你对FIRST_ROWS、ALL_ROWS这两种模式有疑惑时也可以看一下这篇文章。开始吧:Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。相信你一定会用Pl/sqlDeveloper、Toad等工具去看一个语句的执行计划,不过你可能对Rule、Choose、Firstrows、Allrows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了?1、优化器的优化方式Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-BasedOptimization,简称为RBO)和基于代价的优化方式(Cost-BasedOptimization,简称为CBO)。A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。B、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。我们要明了,不一定走索引就是优的,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全表扫描(fulltablescan)是最好的。2、优化器的优化模式(OptermizerMode)优化模式包括Rule,Choose,Firstrows,Allrows这四种方式,也就是我们以上所提及的。如下我解释一下：Rule:不用多说,即走基于规则的方式。Choolse:这是我们应观注的,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。FirstRows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。AllRows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。3、如何设定选用哪种优化模式a、Instance级别我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。B、Sessions级别通过SQL>ALTERSESSIONSETOPTIMIZER_MODE=;来设定。C、语句级别这些需要用到Hint,比如:SQL>SELECT/*+RULE*/a.userid,2b.name,3b.depart_name4FROMtf_f_yhdaa,5tf_f_departb6WHEREa.userid=b.userid;4、为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢？如何解决呢？A、不走索引大体有以下几个原因♀你在Instance级别所用的是all_rows的方式♀你的表的统计信息(最可能的原因)♀你的表很小,上文提到过的,Oracle的优化器认为不值得走索引。B、解决方法♀可以修改init.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint.♀删除统计信息SQL>analyzetabletable_namedeletestatistics;♀表小不走索引是对的,不用调的。5、其它相关A、如何看一个表或索引是否是统计信息SQL>SELECT*FROMuser_tables2WHEREtable_name=3ANDnum_rowsisnotnull;SQL>SELECT*FROMuser_indexes2WHEREtable_name=3ANDnum_rowsisnotnull;b、如果我们先用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划。SQL>ANALYZETABLEtable_nameCOMPUTESTATISTICS;SQL>ANALYZEINDEXindex_nameESTIMATESTATISTICS;具体的ANALYZE语句请参照Oracle8i/9i的refrence文档。.确保最优的索引使用：对于改善查询的速度，这是特别重要的。有时Oracle可以选择多个索引来进行查询，调优专家必须检查每个索引并且确保Oracle使用正确的索引。它还包括bitmap和基于函数的索引的使用。　　.确保最优的JOIN操作：有些查询使用NESTEDLOOPjoin快一些，有些则是HASHjoin快一些，另外一些则是sort-mergejoin更快。　　这些规则看来简单，不过它们占SQL调优任务的90%，并且它们也无需完全懂得OracleSQL的内部运作。以下我们来简单概览以下OracleSQL的优化。　　我们首先简要查看Oracle的排序，并且看一看排序操作是如何影响性能的。　　调整Oracle的排序操作　　排序是SQL语法中一个小的方面，但很重要，在Oracle的调整中，它常常被忽略。当使用createindex、ORDERBY或者GROUPBY的语句时，Oracle数据库将会自动执行排序的操作。通常，在以下的情况下Oracle会进行排序的操作：　　使用Orderby的SQL语句　　使用Groupby的SQL语句　　在创建索引的时候　　进行tablejoin时，由于现有索引的不足而导致SQL优化器调用MERGESORT　　当与Oracle建立起一个session时，在内存中就会为该session分配一个私有的排序区域。如果该连接是一个专用的连接(dedicatedconnection)，那么就会根据init.ora中sort_area_size参数的大小在内存中分配一个ProgramGlobalArea(PGA)。如果连接是通过多线程服务器建立的，那么排序的空间就在large_pool中分配。不幸的是，对于所有的session，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，

设计

领导形象设计圆作业设计ao工艺污水处理厂设计附属工程施工组织设计清扫机器人结构设计

者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（disksorts）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。当然，当排序的空间需求超出了sort_area_size的大小时，这时将会在TEMP表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢14,000倍。　　上面我们已经提到，私有排序区域的大小是有init.ora中的sort_area_size参数决定的。每个排序所占用的大小由init.ora中的sort_area_retained_size参数决定。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在Oracle实例中的临时表空间中进行。　　磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时表空间中的资源。Oracle还必须分配缓冲池块来保持临时表空间中的块。无论什么时候，内存排序都比磁盘排序好，磁盘排序将会令任务变慢，并且会影响Oracle实例的当前任务的执行。还有，过多的磁盘排序将会令freebufferwaits的值变高，从而令其它任务的数据块由缓冲中移走。　　接着，让我们看一下Oracle的竞争，并且看一下表的存储参数的设置是如何影响SQLUPDATE和INSERT语句的性能的。调整Oracle的竞争　　Oracle的其中一个优点时它可以管理每个表空间中的自由空间。Oracle负责处理表和索引的空间管理，这样就可以让我们无需懂得Oracle的表和索引的内部运作。不过，对于有经验的Oracle调优专家来说，他需要懂得Oracle是如何管理表的extent和空闲的数据块。对于调整拥有高的insert或者update的系统来说，这是非常重要的。　　要精通对象的调整，你需要懂得freelists和freelist组的行为，它们和pctfree及pctused参数的值有关。这些知识对于企业资源计划（ERP）的应用是特别重要的，因为在这些应用中，不正确的表设置通常是DML语句执行慢的原因。　　对于初学者来说，最常见的错误是认为默认的Oracle参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的pctfree和pctused参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到freelists中。当这些设置不正确时，那些得到的freelists也是"dead"块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。Freelists对于有效地重新使用Oracle表空间中的空间是很重要的，它和pctfree及pctused这两个存储参数的设置直接相关。通过将pctused设置为一个高的值，这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整Oracle的表格和索引时，需要认真考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。以下我们来看一下这些freelists是如何影响Oracle的性能的。　　当有一个请求需要插入一行到表格中时，Oracle就会到freelist中寻找一个有足够的空间来容纳一行的块。你也许知道，freelist串是放在表格或者索引的第一个块中，这个块也被称为段头（segmentheader）。pctfree和pctused参数的唯一目的就是为了控制块如何在freelists中进出。虽然freelistlink和unlink是简单的Oracle功能，不过设置freelistlink(pctused)和unlink(pctfree)对Oracle的性能确实有影响。　　由DBA的基本知识知道，pctfree参数是控制freelistun-links的（即将块由freelists中移除）。设置pctfree=10意味着每个块都保留10%的空间用作行扩展。pctused参数是控制freelistre-links的。设置pctused=40意味着只有在块的使用低于40%时才会回到表格的freelists中。　　许多新手对于一个块重新回到freelists后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到freelist中，它将会一直保留在freelist中即使空间的使用超过了60%，只有在到达pctfree时才会将数据块由freelist中移走。　　表格和索引存储参数设置的要求总结　　以下的一些规则是用来设置freelists,freelistgroups,pctfree和pctused存储参数的。你也知道，pctused和pctfree的值是可以很容易地通过altertable命令修改的，一个好的DBA应该知道如何设置这些参数的最佳值。　　有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾：.对于需要有效地重新使用空间，可以设置一个高的pctused值，不过副作用是需要额外的I/O。一个高的pctused值意味着相对满的块都会放到freelist中。因此，这些块在再次满之前只可以接受几行记录，从而导致更多的I/O。.追求高性能的话，可以将pctused设置为一个低的值，这意味着Oracle不会将数据块放到freelists中直到它几乎是空的。那么块将可以在满之前接收更多的行，因此可以减少插入操作的I/O。要记住Oracle扩展新块的性能要比重新使用现有的块高。对于Oracle来说，扩展一个表比管理freelists消耗更少的资源。　　让我们来回顾一下设置对象存储参数的一些常见规则：　　．经常将pctused设置为可以接收一条新行。对于不能接受一行的freeblocks对于我们来说是没有用的。如果这样做，将会令Oracle的性能变慢，因为Oracle将在扩展表来得到一个空的块之前，企图读取5个"dead"的freeblock。　　．表格中chainedrows的出现意味着pctfree太低或者是db_block_size太少。在很多情况下，RAW和LONGRAW列都很巨大，以至超过了Oracle的最大块的大小，这时chainedrows是不可以避免的。　　．如果一个表有同时插入的SQL语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个freelist中，而没有其它包含有任何空闲块的freelists出现。　　．freelist参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有20个用户执行插入的操作，那么该表的参数应该设置为freelists=20。　　应记住的是freelistgroups参数的值只是对于OracleParallelServer和RealApplicationClusters才是有用的。对于这类Oracle，freelistgroups应该设置为访问该表格的OracleParallelServer实例的数目。友情提示：部分文档来自网络整理，供您参考！文档可复制、编制，期待您的好评与关注！