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

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_GE

2、TS：读取缓冲区的次数 用途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=false

3、e) Step 4:使用 “TKPROF”转换跟踪文件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） 使用 E

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

6、RATION 可能是 TABLE ACCESS，但 OPTION 可能为by ROWID或 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

7、Scans）(无可用索引,大量数据,小表 ,全表扫描hints,HWM(High Water Mark), Rowid扫描) 索引扫描 索引唯一扫描（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 子句中的条件以及FRO

8、M 子句，确定有哪些访问路径是可用的。然后 CBO使用这个访问路径产生一组可能的执行计划，再通过索引、表的统计信息评估每个计划的成本，最后优化器选择成本最低的一个。 表的连接方式：Nested Loops会循环外表（驱动表） ，逐个比对和内表的连接是否符合条件。在驱动表比较小，内表比较大，而且内外表的连接列有索引的时候比较好。当SORT_AREA空间不足的时候，Oracle 也会选择使用 NL。基于 Cost 的 Oracle 优化器(CBO)会自动选择较小的表做外表。 （优点：嵌套循环连接比其他连接方法有优势,它可以快速地从结果集中提取第一批记录,而不用等待整个结果集完全确定下来。缺点：如果

9、内部行源表(读取的第二张表（内表）已连接的列上不包含索引,或者索引不是高度可选时, 嵌套循环连接效率是很低的。如果驱动行源表(从驱动表中提取的记录)非常庞大时,其他的连接方法可能更加有效。 ）SORT- merge JOIN，将两表的连接列各自排序然后合并，只能用于连接列相等的情况，适合两表大小相若的情况（在缺乏数据的选择性或者可用的索引时,或者两个源表都过于庞大(超过记录数的 5%)时,排序合并连接将比嵌套循环连更加高效。但是,排列合并连接只能用于等价连接(WHERE D.deptno=E.dejptno,而不是 WHERE D.deptno=E.deptno)。排列合并连接需要临时的内存块

10、,以用于排序(如果SORT_AREA_SIZE 设置得太小的话)。这将导致在临时表空间占用更多的内存和磁盘I/O。 ）HASH JOIN 在其中一表的连接列上作散列，因此只有另外一个表做排序合并，理论上比 SORT JOIN 会快些，需要有足够的内存，而且打开了SORT_JOIN_ENABLE 参数。(当缺少有用的索引时，哈希连接比嵌套循环连接更加有效。哈希连接可能比排序合并连接更快，因为在这种情况下只有一张源表需要排序。哈希连接也可能比嵌套循环连接更快，因为处理内存中的哈希表比检索 B_树索引更加迅速。和排序合并连接、群集连接一样，哈希连接只能用于等价连接。和排序合并连接一样，哈希连接使用内

11、存资源，并且当用于排序内存不足时，会增加临时表空间的 I/O（这将使这种连接方法速度变得极慢） 。最后，只有基于代价的优化器才可以使用哈希连接。) 索引连接:No2: AUTOTRACE set autotrace 使用步骤：1、以 system 登录2、创建 plustrace 角色; sqlplusadminplustrce.sql3、向常规用户授予权限：grant plustrace to 4、如果没有 plan_table 也要创建: rdbmsadminutlxplan.sql set autotrace 选项on 显示查询结果，执行计划，统计数据on statistics 显示查询

12、结果，统计数据，不显示执行计划on explain 显示查询结果，执行计划，不显示统计数据traceonly 显示执行计划和统计结果，但不包括查询结果traceonly statistics 仅显示统计数据recursive calls在用户级别和系统级别上生成的递归调用的数量。Oracle 维护了一些用于内部处理的表。当 oracle需要对这些表进行更改时，它就会在内部生成一个SQL 语句，然后这个语句再生成一个递归调用。db block gets 请求一个 CURRENT 块的次数consistent gets 为一块请求consistent read 的次数physical reads从

13、磁盘读取得数据块总数。这个数量等于“直接物理读取”的值加上读入缓冲区的所有数据块redo size 生成的重做的总数量（以字节为单位）bytes sent via SQL * Net to client 从前台进程发送给客户的总字节数bytes received via SQL * Net from client 通过 Oracle Net 从客户接收的总字节数SQL*Net roundtrips to/from client发送给客户和从客户接收的 Oracle Net 消息的总数sorts (memory)完全在内存中执行并且不需要任何磁盘写入的排序操作的数量sorts (disk) 至少

14、需要一个磁盘写入的排序操作的数量rows processed 在操作过程中处理的行数四、最后，使用计时特性来测量和比较处理时间Set timing onV$session_event应观注一下event这列，这是我们调优的关键一列，下面对常出现的 event 做以简要的说明：a、buffer busy waits，free buffe r waits这两个参数所标识是 dbwr 是否够用的问题，与 IO 很大相关的，当 v$session_wait 中的 free buffer wait 的条目很小或没有的时侯，说明你的系统的 dbwr 进程决对够用，不用调整；free buffer wait

15、 的条目很多，你的系统感觉起来一定很慢，这时说明你的 dbwr已经不够用 了，它产生的 wio 已经成 为你的数据库性能的瓶颈，这时的解决办法如下： a.1 增加写进程，同时要调整db_block_lru_latches 参数 示例：修改或添加如下两个参数 db_wr iter_processes=4 db_block_l ru_latches=8 a、2 开异步 IO，IBM 这方 面简单得多，hp 则麻烦一些，可以与 Hp工程师联系。 b、db file seque ntial read，指的是顺序读，即全表扫描，这也是我们应该尽量减少的部分，解决方法就是使用索引、sql 调优，同时可以增

16、大 db_file_multiblock_read_count这个参数。 c、db file scattered read,这个参数指的是通过索引来读取，同样可以通过增加db_file_multiblock_read_count 这个参数来提高性能。 d、latch free, 与栓相关的了，需要专门调节。 e、其他参数可以不特别观注。本文的目的： 1、说一说 Oracle 的Optimizer 及其相关的一些知识。 2、回答一下为什么有时一 个表的某个字段明明有索引,当观察一些SQL 的执行计划时,发现确不走索引的问题。 3、如果你对 FIRST_ROWS、 ALL_ROWS 这两种模式有疑

17、惑时也可以看一下这篇文章。 开始吧: Oracle 在执行一个 SQL 之前,首先要分析 一下语句的执行计划,然后再按执行计划去执行。分析语句的执行计划的工作是由优化器(Optimizer)来完成的。不同的情况,一条 SQL可能有多种执行计划,但在某一时点,一定只有一种执行计划是最优的,花费时间是最少的。相信你一定会用 Pl/sql Developer、Toad 等工具去看一个语句的执行计划,不过你可能对 Rule、Choose、First rows、All rows 这几项有疑问,因为我当初也是这样的,那时我也疑惑为什么选了以上的不同的项,执行计划就变了? 1、优化器的优化方式 Oracle

18、 的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为 RBO)和基于代价的优化方式(Cost-Based Optimization,简称为 CBO)。 A、RBO 方式：优化器在分析 SQL 语句时,所遵循的是Oracle 内部预定的一些规则。比如我们常见的,当一个 where 子句中的一列有索引时去走索引。 B、CBO 方式：依词义可知,它是看语句的代价(Cost)了,这里的代价 主要指 Cpu 和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是

19、没有的,是你在做 analyze 后才出现 的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在 Oracle8 及以后的版本,Oracle 列推荐用CBO 的方式。 我们要明了,不一定走索引就是优的 ,比如一个表只有两行数据,一次 IO 就可以完成全表的检索,而此时走索引时则需要两次 IO,这时对这个表做全表扫描(full table scan)是最好的。 2、优化器的优化模式 (Optermizer Mode) 优化模式包括 Rule,Choose,First rows,All rows 这四种方式,也就是我们 以上所提及的。如下我解释一下： Rule

20、:不用多说 ,即走基于规 则的方式。 Choolse:这是我们应 观注的,默认的情况下 Oracle 用的便是这种方式。指的是当一个表或或索引有统计信息,则走 CBO的方式,如果表或索引没统计信 息,表又不是特别的小,而且相应的列有索引时,那么就走索引,走 RBO 的方式。 First Rows:它与 Choose 方式是类似的,所不同的是当一个表有统计信息时,它将是以最快的方式返回查询的最先的几行,从总体上减少了响应时间。 All Rows:也就是我们所说的 Cost 的方式,当一个表有统计信息时,它将以最快的方式返回表的所有的行,从总体上提高查询的吞吐量。没有统计信息则走基于规则的方式。

21、3、如何设定选用哪种优化 模式 a、Instance 级别 我们可以通过在 init.ora 文件 中设定OPTIMIZER_MODE=RULE、OPTIM IZER_MODE=CHOOSE、OPTIMIZER_MODE=FIRST_ROWS、OPTIMIZER_MODE=ALL_ROWS 去选用 3 所提的四种方式,如果你没设定 OPTIMIZER_MODE参数则默认用的是 Choose 这 种方式。 B、Sessions 级别 通过 SQL ALTER SESSION SET OPTIMIZER_MODE=;来设定。 C、语句级别 这些需要用到 Hint,比如: SQL SELECT /*

22、+ 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 的方式 你的表的统计信息(最可能的原 因) 你的表很小,上文提到过的,Oracle 的优化器认为不值得走索引。 B、解决方法 可以修改 init.ora 中的OPTIM IZER_MODE 这个参数

23、,把它改为 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; SQLSELECT * FROM user_indexes 2 WHERE table_name= 3 AND num_rows is not null; b、如果

24、我们先用 CBO 的方式,我们应及时去更新表和索引的统计信息,以免生形不切合实的执行计划。 SQL ANALYZE TABLE table_name COMPUTE STATISTICS; SQL ANALYZE INDEX index_name ESTIMATE STATISTICS; 具体的 ANALYZE语句请参照Oracle8i/9i 的 refrence 文档。. 确保最优的索引使用 ：对于改善查询的速度，这是特别重要的。有时 Oracle 可以选择多个索引来进行查询，调优专家必须检查每个索引并且确保 Oracle 使用正确的索引。它还包括 bitmap 和基于函数的索引的使用。 .

25、 确保最优的 JOIN 操作：有些查询使用 NESTED LOOP join 快一些，有些则是 HASH join 快一些，另外一些则是 sort-merge join 更快。 这些规则看来简单，不过它们占 SQL 调优任务的 90% ，并且它们也无需完全懂得 Oracle SQL 的内部运作。以下我们来简单概览以下 Oracle SQL 的优化。 我们首先简要查看 Oracle 的排序，并且看一看排序操作是如何影响性能的。 调整 Oracle 的排序操作 排序是 SQL 语法中一个小的方面，但很重要，在 Oracle 的调整中，它常常被忽略。当使用 create index 、 ORDER

26、BY 或者 GROUP BY 的语句时， Oracle 数据库将会自动执行排序的操作。通常，在以下的情况下 Oracle 会进行排序的操作： 使用 Order by 的 SQL 语句 使用 Group by 的 SQL 语句 在创建索引的时候 进行 table join 时，由于现有索引的不足而导致 SQL 优化器调用 MERGE SORT 当与 Oracle 建立起一个 session 时，在内存中就会为该 session 分配一个私有的排序区域。如果该连接是一个专用的连接 (dedicated connection) ，那么就会根据 init.ora 中 sort_area_size 参数

27、的大小在内存中分配一个 Program Global Area (PGA) 。如果连接是通过多线程服务器建立的，那么排序的空间就在 large_pool 中分配。不幸的是，对于所有的 session ，用做排序的内存量都必须是一样的，我们不能为需要更大排序的操作分配额外的排序区域。因此，设计者必须作出一个平衡，在分配足够的排序区域以避免发生大的排序任务时出现磁盘排序（ disk sorts ）的同时，对于那些并不需要进行很大排序的任务，就会出现一些浪费。当然，当排序的空间需求超出了 sort_area_size 的大小时，这时将会在 TEMP 表空间中分页进行磁盘排序。磁盘排序要比内存排序大概

28、慢 14,000 倍。 上面我们已经提到，私有排序区域的大小是有 init.ora 中的 sort_area_size 参数决定的。每个排序所占用的大小由 init.ora 中的 sort_area_retained_size 参数决定。当排序不能在分配的空间中完成时，就会使用磁盘排序的方式，即在 Oracle 实例中的临时表空间中进行。 磁盘排序的开销是很大的，有几个方面的原因。首先，和内存排序相比较，它们特别慢；而且磁盘排序会消耗临时表空间中的资源。 Oracle 还必须分配缓冲池块来保持临时表空间中的块。无论什么时候，内存排序都比磁盘排序好，磁盘排序将会令任务变慢，并且会影响 Oracl

29、e 实例的当前任务的执行。还有，过多的磁盘排序将会令 free buffer waits 的值变高，从而令其它任务的数据块由缓冲中移走。 接着，让我们看一下 Oracle 的竞争，并且看一下表的存储参数的设置是如何影响 SQL UPDATE 和 INSERT 语句的性能的。 调整 Oracle 的竞争 Oracle 的其中一个优点时它可以管理每个表空间中的自由空间。 Oracle 负责处理表和索引的空间管理，这样就可以让我们无需懂得 Oracle 的表和索引的内部运作。不过，对于有经验的 Oracle 调优专家来说，他需要懂得 Oracle 是如何管理表的 extent 和空闲的数据块。对于调

30、整拥有高的 insert 或者 update 的系统来说，这是非常重要的。 要精通对象的调整，你需要懂得 freelists 和 freelist 组的行为，它们和 pctfree 及 pctused 参数的值有关。这些知识对于企业资源计划（ ERP ）的应用是特别重要的，因为在这些应用中，不正确的表设置通常是 DML 语句执行慢的原因。 对于初学者来说，最常见的错误是认为默认的 Oracle 参数对于所有的对象都是最佳的。除非磁盘的消耗不是一个问题，否则在设置表的 pctfree 和 pctused 参数时，就必须考虑平均的行长和数据库的块大小，这样空的块才会被有效地放到 freelists

31、 中。当这些设置不正确时，那些得到的 freelists 也是 “dead“ 块，因为它们没有足够的空间来存储一行，这样将会导致明显的处理延迟。 Freelists 对于有效地重新使用 Oracle 表空间中的空间是很重要的，它和 pctfree 及 pctused 这两个存储参数的设置直接相关。通过将 pctused 设置为一个高的值，这时数据库就会尽快地重新使用块。不过，高性能和有效地重新使用表的块是对立的。在调整 Oracle 的表格和索引时，需要认真考虑究竟需要高性能还是有效的空间重用，并且据此来设置表的参数。以下我们来看一下这些 freelists 是如何影响 Oracle 的性能的

32、。 当有一个请求需要插入一行到表格中时， Oracle 就会到 freelist 中寻找一个有足够的空间来容纳一行的块。你也许知道， freelist 串是放在表格或者索引的第一个块中，这个块也被称为段头（ segment header ） 。 pctfree 和 pctused 参数的唯一目的就是为了控制块如何在 freelists 中进出。虽然 freelist link 和 unlink 是简单的 Oracle 功能，不过设置 freelist link (pctused) 和 unlink (pctfree) 对 Oracle 的性能确实有影响。 由 DBA 的基本知识知道， pctf

33、ree 参数是控制 freelist un-links 的（即将块由 freelists 中移除） 。设置 pctfree=10 意味着每个块都保留 10% 的空间用作行扩展。 pctused 参数是控制 freelist re-links 的。设置 pctused=40 意味着只有在块的使用低于 40% 时才会回到表格的 freelists 中。 许多新手对于一个块重新回到 freelists 后的处理都有些误解。其实，一旦由于一个删除的操作而令块被重新加入到 freelist 中，它将会一直保留在 freelist 中即使空间的使用超过了 60% ，只有在到达 pctfree 时才会将数据

34、块由 freelist 中移走。 表格和索引存储参数设置的要求总结 以下的一些规则是用来设置 freelists, freelist groups, pctfree 和 pctused 存储参数的。你也知道， pctused 和 pctfree 的值是可以很容易地通过 alter table 命令修改的，一个好的 DBA 应该知道如何设置这些参数的最佳值。 有效地使用空间和高性能之间是有矛盾的，而表格的存储参数就是控制这个方面的矛盾： . 对于需要有效地重新使用空间，可以设置一个高的 pctused 值，不过副作用是需要额外的 I/O 。一个高的 pctused 值意味着相对满的块都会放到 f

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

36、是没有用的。如果这样做，将会令 Oracle 的性能变慢，因为 Oracle 将在扩展表来得到一个空的块之前，企图读取 5 个 “dead“ 的 free block 。 表格中 chained rows 的出现意味着 pctfree 太低或者是 db_block_size 太少。在很多情况下，RAW 和 LONG RAW 列都很巨大，以至超过了 Oracle 的最大块的大小，这时 chained rows 是不可以避免的。 如果一个表有同时插入的 SQL 语句，那么它需要有同时删除的语句。运行单一个一个清除的工作将会把全部的空闲块放到一个 freelist 中，而没有其它包含有任何空闲块的 freelists 出现。 freelist 参数应该设置为表格同时更新的最大值。例如，如果在任何时候，某个表最多有 20 个用户执行插入的操作，那么该表的参数应该设置为 freelists=20 。 应记住的是 freelist groups 参数的值只是对于 Oracle Parallel Server 和 Real Application Clusters 才是有用的。对于这类 Oracle ， freelist groups 应该设置为访问该表格的 Oracle Parallel Server 实例的数目。