oracle执行占资源语句和程序,Oracle执行计划SQL语句执行效率问题查找与解决方法...

《Oracle执行计划SQL语句执行效率问题查找与解决方法》由会员分享，可在线阅读，更多相关《Oracle执行计划SQL语句执行效率问题查找与解决方法(9页珍藏版)》请在人人文库网上搜索。

1、Oracle的SQL语句执行效率问题查找与解决方法一、 识别占用资源较多的语句的方法(4种方法)1. 测试组和最终用户反馈的与反应缓慢有关的问题。2. 利用V_$SQLAREA视图提供了执行的细节。(执行、读取磁盘和读取缓冲区的次数) 数据列EXECUTIONS：执行次数DISK_READS：读盘次数COMMAND_TYPE：命令类型(3:select,2:insert;6:update;7delete;47:pl/sql程序单元)OPTIMIZER_MODE：优化方式SQL_TEXT：Sql语句SHARABLE_MEM：占用shared pool的内存多少BUFFER_GETS：读取缓冲区的。

2、次数 用途1、帮忙找出性能较差的SQL语句2、帮忙找出最高频率的SQL 3、帮忙分析是否需要索引或改善联接3. 监控当前Oracle的session，如出现时钟的标志，表示此进程中的sql运行时间较长。4. Trace工具：a) 查看数据库服务的初始参数：timed_statistics、user_dump_dest和max_dump_file_sizeb) Step 1: alter session set sql_trace=truec) Step 2: run sql；d) Step 3: alter session set sql_trace=falsee) Step 4:使用 “TK。

3、PROF”转换跟踪文件f) Parse，解析数量大通常表明需要增加数据库服务器的共享池大小，query或current提取数量大表明如果没有索引，语句可能会运行得更有效，disk提取数量表明索引有可能改进性能，library cache中多于一次的错过表明需要一个更大的共享池大小二、 如何管理语句处理和选项 基于成本(Cost Based) 和基于规则(Rule Based) 两种优化器， 简称为CBO 和RBO Optimizer Mode参数值:Choose：如果存在访问过的任何表的统计数据 ，则使用基于成本的Optimizer,目标是获得最优的通过量。如果一些表没有统计数据，则使用估计值。

4、。如果没有可用的统计数据，则将使用基于规则的OptimizerAll_rows：总是使用基于成本的Optimizer，目标是获得最优的通过量First_rows_n：总是使用基于成本的Optimizer，目标是对返回前N行(“n”可以是1，10，100或者1000)获得最优的响应时间First_rows：用于向后兼容。使用成本与试探性方法的结合，以便快速传递前几行RULE：总是使用基于规则的Optimizer三、 使用数据库特性来获得有助于查看性能的处理统计信息(解释计划和AUTOTRACE)No1: Explain PlanA) 使用Explain工具需要创建Explain_plan表，这必。

5、须先进入相关应用表、视图和索引的所有者的帐户内. (D:oracleora92rdbmsadminutlxplan)B) 表结构：STATEMENT_ID：为一条指定的SQL语句确定特定的执行计划名称。如果在EXPLAN PLAN语句中没有使用SET STATEMENT_ID，那么此值会被设为NULL。 OPERATION：在计划的某一步骤执行的操作名称，例如：Filters，Index，Table，Marge Joins and Table等。 OPTION：对OPERATION操作的补充，例如：对一个表的操作，OPERATION可能是TABLE ACCESS，但OPTION可能为by RO。

6、WID或FULL。 Object_Owner：拥有此database Object的Schema名或Oracle帐户名。 Object_name：Database Object名 Object_type：类型，例如：表、视图、索引等等 ID：指明某一步骤在执行计划中的位置。 PARENT_ID：指明从某一操作中取得信息的前一个操作。通过对与ID和PARENT_ID使用Connect By操作，我们可以查询整个执行计划树。C) EXPLAIN搜索路径解释 全表扫描(Full Table Scans)(无可用索引,大量数据,小表 ,全表扫描hints,HWM(High Water Mark), R。

7、owid扫描) 索引扫描 索引唯一扫描(Index Unique Scans)索引范围扫描(Index Range Scans)索引降序范围扫描(Index Range Scans Descending)索引跳跃扫描(Index Skip Scans)全索引扫描(Full Scans)快速全索引扫描(Fast Full Index Scans)索引连接(Index Joins)位图连接(Bitmap Joins) 如何选择访问路径： CBO首先检查WHERE子句中的条件以及FROM子句，确定有哪些访问路径是可用的。然后CBO使用这个访问路径产生一组可能的执行计划，再通过索引、表的统计信息评估每。

8、个计划的成本，最后优化器选择成本最低的一个。 表的连接方式：Nested Loops会循环外表(驱动表)，逐个比对和内表的连接是否符合条件。在驱动表比较小，内表比较大，而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候，Oracle也会选择使用NL。基于Cost的Oracle优化器(CBO)会自动选择较小的表做外表。(优点：嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。缺点：如果内部行源表(读取的第二张表(内表)已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中。

9、提取的记录)非常庞大时,其他的连接方法可能更加有效。)SORT- merge JOIN，将两表的连接列各自排序然后合并，只能用于连接列相等的情况，适合两表大小相若的情况(在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的5%)时,排序合并连接将比嵌套循环连更加高效。但是,排列合并连接只能用于等价连接(WHERE D.deptno=E.dejptno,而不是WHERE D.deptno=E.deptno)。排列合并连接需要临时的内存块,以用于排序(如果SORT_AREA_SIZE设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O。)HASH JOIN在其中一表。

10、的连接列上作散列，因此只有另外一个表做排序合并，理论上比SORT JOIN会快些，需要有足够的内存，而且打开了SORT_JOIN_ENABLE参数。(当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索B_树索引更加迅速。和排序合并连接、群集连接一样，哈希连接只能用于等价连接。和排序合并连接一样，哈希连接使用内存资源，并且当用于排序内存不足时，会增加临时表空间的I/O(这将使这种连接方法速度变得极慢)。最后，只有基于代价的优化器才可以使用哈希连接。) 索引连接:N。

11、o2: AUTOTRACE set autotrace 使用步骤：1、以system登录2、创建plustrace角色; sqlplusadminplustrce.sql3、向常规用户授予权限：grant plustrace to 4、如果没有plan_table也要创建: rdbmsadminutlxplan.sql set autotrace 选项on 显示查询结果，执行计划，统计数据on statistics 显示查询结果，统计数据，不显示执行计划on explain 显示查询结果，执行计划，不显示统计数据traceonly 显示执行计划和统计结果，但不包括查询结果traceonly s。

12、tatistics 仅显示统计数据recursive calls在用户级别和系统级别上生成的递归调用的数量。Oracle维护了一些用于内部处理的表。当oracle需要对这些表进行更改时，它就会在内部生成一个SQL语句，然后这个语句再生成一个递归调用。db block gets请求一个CURRENT块的次数consistent gets为一块请求consistent read的次数physical reads从磁盘读取得数据块总数。这个数量等于“直接物理读取”的值加上读入缓冲区的所有数据块redo size生成的重做的总数量(以字节为单位)bytes sent via SQL * Net to 。

13、client从前台进程发送给客户的总字节数bytes received via SQL * Net from client通过Oracle Net从客户接收的总字节数SQL*Net roundtrips to/from client发送给客户和从客户接收的Oracle Net消息的总数sorts (memory)完全在内存中执行并且不需要任何磁盘写入的排序操作的数量sorts (disk)至少需要一个磁盘写入的排序操作的数量rows processed在操作过程中处理的行数四、最后，使用计时特性来测量和比较处理时间Set timing onV$session_event应观注一下event这列。

14、，这是我们调优的关键一列，下面对常出现的event做以简要的说明： a、buffer busy waits，free buffer waits这两个参数所标识是dbwr是否够用的问题，与IO很大相关的，当v$session_wait中的free buffer wait的条目很小或没有的时侯，说明你的系统的dbwr进程决对够用，不用调整；free buffer wait的条目很多，你的系统感觉起来一定很慢，这时说明你的dbwr已经不够用了，它产生的wio已经成为你的数据库性能的瓶颈，这时的解决办法如下： a.1增加写进程，同时要调整db_block_lru_latches参数 示例：修改或添加如。

15、下两个参数 db_writer_processes=4 db_block_lru_latches=8 a、2开异步IO，IBM这方面简单得多，hp则麻烦一些，可以与Hp工程师联系。 b、db file sequential read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql调优，同时可以增大db_file_multiblock_read_count这个参数。 c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count这个参数来提高性能。 d、latch f。

16、ree,与栓相关的了，需要专门调节。 e、其他参数可以不特别观注。本文的目的： 1、说一说Oracle的Optimizer及其相关的一些知识。 2、回答一下为什么有时一个表的某个字段明明有索引,当观察一些SQL的执行计划时,发现确不走索引的问题。 3、如果你对 FIRST_ROWS、 ALL_ROWS这两种模式有疑惑时也可以看一下这篇文章。 开始吧: Oracle在执行一个SQL之前,首先要分析一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时。

17、间是最少的。相信你一定会用Pl/sql Developer、Toad等工具去看一个语句的执行计划,不过你可能对Rule、Choose、First rows、All rows这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了? 1、优化器的优化方式 Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。 A、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当。

18、一个where子句中的一列有索引时去走索引。 B、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。 我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次IO就可以完成全表的检索,而此时走索引时则需要两次IO,这时对这个表做全。

19、表扫描(full table scan)是最好的。 2、优化器的优化模式(Optermizer Mode) 优化模式包括Rule,Choose,First rows,All rows这四种方式,也就是我们以上所提及的。如下我解释一下： Rule:不用多说,即走基于规则的方式。 Choolse:这是我们应观注的,默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息,则走CBO的方式,如果表或索引没统计信息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走RBO的方式。 First Rows:它与Choose方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的。

20、方式返回查询的最先的几行,从总体上减少了响应时间。 All Rows:也就是我们所说的Cost的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。 3、如何设定选用哪种优化模式 a、Instance级别 我们可以通过在init.ora文件中设定OPTIMIZER_MODE=RULE、OPTIMIZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS去选用3所提的四种方式,如果你没设定OPTIMIZER_MODE参数则默认用的是Choose这种方式。 。

21、B、Sessions级别 通过SQL ALTER SESSION SET OPTIMIZER_MODE=;来设定。 C、语句级别 这些需要用到Hint,比如: SQL SELECT /*+ RULE */ a.userid, 2 b.name, 3 b.depart_name 4 FROM tf_f_yhda a, 5 tf_f_depart b 6 WHERE a.userid=b.userid; 4、为什么有时一个表的某个字段明明有索引,当观察一些语的执行计划确不走索引呢？如何解决呢 ？ A、不走索引大体有以下几个原因 你在Instance级别所用的是all_rows的方式 你的表的统计信。

22、息(最可能的原因) 你的表很小,上文提到过的,Oracle的优化器认为不值得走索引。 B、解决方法 可以修改init.ora中的OPTIMIZER_MODE这个参数,把它改为Rule或Choose,重起数据库。也可以使用4中所提的Hint. 删除统计信息 SQLanalyze table table_name delete statistics; 表小不走索引是对的,不用调的。 5、其它相关 A、如何看一个表或索引是否是统计信息 SQLSELECT * FROM user_tables 2 WHERE table_name= 3 AND num_rows is not null; SQLSEL。

23、ECT * FROM user_indexes 2 WHERE table_name= 3 AND num_rows is not null; b、如果我们先用CBO的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划。 SQL ANALYZE TABLE table_name COMPUTE STATISTICS; SQL ANALYZE INDEX index_name ESTIMATE STATISTICS; 具体的ANALYZE语句请参照Oracle8i/9i 的refrence文档。. 确保最优的索引使用 ：对于改善查询的速度，这是特别重要的。有时 Oracle 可。

24、以选择多个索引来进行查询，调优专家必须检查每个索引并且确保 Oracle 使用正确的索引。它还包括 bitmap 和基于函数的索引的使用。 . 确保最优的 JOIN 操作：有些查询使用 NESTED LOOP join 快一些，有些则是 HASH join 快一些，另外一些则是 sort-merge join 更快。 这些规则看来简单，不过它们占 SQL 调优任务的 90% ，并且它们也无需完全懂得 Oracle SQL 的内部运作。以下我们来简单概览以下 Oracle SQL 的优化。 我们首先简要查看 Oracle 的排序，并且看一看排序操作是如何影响性能的。 调整 Oracle 的排序操。

25、作 排序是 SQL 语法中一个小的方面，但很重要，在 Oracle 的调整中，它常常被忽略。当使用 create index 、 ORDER BY 或者 GROUP BY 的语句时， Oracle 数据库将会自动执行排序的操作。通常，在以下的情况下 Oracle 会进行排序的操作： 使用 Order by 的 SQL 语句 使用 Group by 的 SQL 语句 在创建索引的时候 进行 table join 时，由于现有索引的不足而导致 SQL 优化器调用 MERGE SORT 当与 Oracle 建立起一个 session 时，在内存中就会为该 session 分配一个私有的排序区域。如果。

26、该连接是一个专用的连接 (dedicated connection) ，那么就会根据 init.ora 中 sort_area_size 参数的大小在内存中分配一个 Program Global Area (PGA) 。如果连接是通过多线程服务器建立的，那么排序的空间就在 large_pool 中分配。不幸的是，对于所有的 session ，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序( disk sorts )的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。。

27、当然，当排序的空间需求超出了 sort_area_size 的大小时，这时将会在 TEMP 表空间中分页进行磁盘排序。磁盘排序要比内存排序大概慢 14,000 倍。 上面我们已经提到，私有排序区域的大小是有 init.ora 中的 sort_area_size 参数决定的。每个排序所占用的大小由 init.ora 中的 sort_area_retained_size 参数决定。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在 Oracle 实例中的临时表空间中进行。 磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时表空间中的资源。。

28、 Oracle 还必须分配缓冲池块来保持临时表空间中的块。无论什么时候，内存排序都比磁盘排序好，磁盘排序将会令任务变慢，并且会影响 Oracle 实例的当前任务的执行。还有，过多的磁盘排序将会令 free buffer waits 的值变高，从而令其它任务的数据块由缓冲中移走。 接着，让我们看一下 Oracle 的竞争，并且看一下表的存储参数的设置是如何影响 SQL UPDATE 和 INSERT 语句的性能的。 调整 Oracle 的竞争 Oracle 的其中一个优点时它可以管理每个表空间中的自由空间。 Oracle 负责处理表和索引的空间管理，这样就可以让我们无需懂得 Oracle 的表和。

29、索引的内部运作。不过，对于有经验的 Oracle 调优专家来说，他需要懂得 Oracle 是如何管理表的 extent 和空闲的数据块。对于调整拥有高的 insert 或者 update 的系统来说，这是非常重要的。 要精通对象的调整，你需要懂得 freelists 和 freelist 组的行为，它们和 pctfree 及 pctused 参数的值有关。这些知识对于企业资源计划( ERP )的应用是特别重要的，因为在这些应用中，不正确的表设置通常是 DML 语句执行慢的原因。 对于初学者来说，最常见的错误是认为默认的 Oracle 参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否。

30、则在设置表的 pctfree 和 pctused 参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到 freelists 中。当这些设置不正确时，那些得到的 freelists 也是 dead 块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。 Freelists 对于有效地重新使用 Oracle 表空间中的空间是很重要的，它和 pctfree 及 pctused 这两个存储参数的设置直接相关。通过将 pctused 设置为一个高的值，这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整 Oracle 的表格和索引时，需要认真。

31、考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。以下我们来看一下这些 freelists 是如何影响 Oracle 的性能的。 当有一个请求需要插入一行到表格中时， Oracle 就会到 freelist 中寻找一个有足够的空间来容纳一行的块。你也许知道， freelist 串是放在表格或者索引的第一个块中，这个块也被称为段头( segment header )。 pctfree 和 pctused 参数的唯一目的就是为了控制块如何在 freelists 中进出。虽然 freelist link 和 unlink 是简单的 Oracle 功能，不过设置 freelist link。

32、 (pctused) 和 unlink (pctfree) 对 Oracle 的性能确实有影响。 由 DBA 的基本知识知道， pctfree 参数是控制 freelist un-links 的(即将块由 freelists 中移除)。设置 pctfree=10 意味着每个块都保留 10% 的空间用作行扩展。 pctused 参数是控制 freelist re-links 的。设置 pctused=40 意味着只有在块的使用低于 40% 时才会回到表格的 freelists 中。 许多新手对于一个块重新回到 freelists 后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到。

33、 freelist 中，它将会一直保留在 freelist 中即使空间的使用超过了 60% ，只有在到达 pctfree 时才会将数据块由 freelist 中移走。 表格和索引存储参数设置的要求总结 以下的一些规则是用来设置 freelists, freelist groups, pctfree 和 pctused 存储参数的。你也知道， pctused 和 pctfree 的值是可以很容易地通过 alter table 命令修改的，一个好的 DBA 应该知道如何设置这些参数的最佳值。 有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾： . 对于需要有效地重新使用。

34、空间，可以设置一个高的 pctused 值，不过副作用是需要额外的 I/O 。一个高的 pctused 值意味着相对满的块都会放到 freelist 中。因此，这些块在再次满之前只可以接受几行记录，从而导致更多的 I/O 。 . 追求高性能的话，可以将 pctused 设置为一个低的值，这意味着 Oracle 不会将数据块放到 freelists 中直到它几乎是空的。那么块将可以在满之前接收更多的行，因此可以减少插入操作的 I/O 。要记住 Oracle 扩展新块的性能要比重新使用现有的块高。对于 Oracle 来说，扩展一个表比管理 freelists 消耗更少的资源。 让我们来回顾一下设置。

35、对象存储参数的一些常见规则： 经常将 pctused 设置为可以接收一条新行。对于不能接受一行的 free blocks 对于我们来说是没有用的。如果这样做，将会令 Oracle 的性能变慢，因为 Oracle 将在扩展表来得到一个空的块之前，企图读取 5 个 dead 的 free block 。 表格中 chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少。在很多情况下， RAW 和 LONG RAW 列都很巨大，以至超过了 Oracle 的最大块的大小，这时 chained rows 是不可以避免的。 如果一个表有同时插入的 SQL 语句，。

36、那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个 freelist 中，而没有其它包含有任何空闲块的 freelists 出现。 freelist 参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有 20 个用户执行插入的操作，那么该表的参数应该设置为 freelists=20 。 应记住的是 freelist groups 参数的值只是对于 Oracle Parallel Server 和 Real Application Clusters 才是有用的。对于这类 Oracle ， freelist groups 应该设置为访问该表格的 Oracle Parallel Server 实例的数目。