Oracle如何分析执行计划

执行计划：

首先在分析的用户下执行rdbms\\admin\%utlxplan.sql 用sys用户登录：sqlplus\\admin\\plustrace.sql grant sqlplus to user_name;

1. 找出耗费资源比较多的语句

SELECT ADDRESS,

substr(SQL_TEXT,1,20) Text, buffer_gets, executions,

buffer_gets/executions AVG FROM v$sqlarea

WHERE executions>0 AND buffer_gets > 100000 ORDER BY 5;

2. 如何分析执行计划：

SQL> SELECT * FROM LARGE_TABLE where USERNAME = ‘TEST’; Query Plan

-----------------------------------------

SELECT STATEMENT Optimizer=CHOOSE (Cost=1234 Card=1 Bytes=14) TABLE ACCESS FULL LARGE_TABLE [:Q65001] [ANALYZED]

TABLE ACCESS FULL large_table:在large_table上做全表扫描 [:Q65001] 表明该部分查询是以并行方式运行的。

[ANALYZED] 表明操作中引用的对象被分析过了，在数据字典中有该对象的统计信息可以供CBO使用。

3. 各个表之间是如何关联的

在执行计划中，需要知道哪个操作是先执行的，哪个操作是后执行的，这对于判断哪个表为驱动表有用处。判断之前，如果对表的访问是通过rowid，且该rowid的值是从索引扫描中得来得，则将该索引扫描先从执行计划中暂时去掉。然后在执行计划剩下的部分中，判断执行顺序的指导原则就是：最右、最上的操作先执行。

4. 在RBO中，以from 子句中从右到左的顺序选择驱动表，即最右边的表为第一个驱动表但是，在RBO中，也是有一套规则来决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑当前索引的情况，还可能会考虑where 中的限制条件，但是肯定是与where中限制条件的位置无关。

5. 在CBO中，如果没有统计信息，则在from子句中从左到右的顺序选择驱动表。如果用ordered它会按从左到右的顺序选择驱动表。但是如果对表或索引进行分析，则优化器会自动根据cost值决定采用哪种连接类型，这与where子句中各个限制条件的位置没有任何关系，如果想改变优化器选择的连接类型或驱动表，则要使用hints。

CBO与RBO总结：

在RBO中，以从右到左的顺序选择驱动表，即最右边的表为第一个驱动表，但是在RBO中也有一套规则来决定使用哪种连接类型和哪个表作为驱动表，在选择时肯定会考虑到当前索引的情况，还可能会考虑到where中的限制条件，但是肯定是与where中限制条件的位置无关。

在CBO中，如果没有统计信息，则以从右到左的顺序选择驱动表，但是如果对表或索引进行分析，则优化器会自动根据cost值决定采用哪种连接类型，与where子句中各个限制的条件位置没有任何关系，如果想改变优化器选择类型或驱动表，刚要使用hints.如果使用ordered它也会按从左到右的顺序选择驱动表。

6. 下面我们来干预执行计划：使用hints提示 我们可以用hints来实现： 1） 使用优化器的类型

2） 基于代价的优化器的优化目标，是all_rows还是first_rows

3） 表的访问路径，是全表扫描还是索引扫描，还是直接利用rowid 4） 表之间的连接类型 5） 表之间的连接顺序 6） 语句的并行程序

如何使用hints:

Hints只应用在它们所在sql语句块(statement block，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。

{DELETE | INSERT | SELECT | UPDATE } /*+ hint [text] [hint[text]]..*/ or

{DELETE | INSERT | SELECT | UPDATE} --+ hint [text] [hint[text]]...

注解：

1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。

2) “+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。 3) hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。

4) text 是其它说明hint的注释性文本

如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。

下面是使用hints的例子：

ORDERED提示指出了连接的顺序，也为不同的表指定了连接方法 SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)

USE_NL (glcc glf) USE_MERGE (gp gsb) */ b.application_id, b.set_of_books_id , b.personnel_id, p.vendor_id Personnel,

p.segment1 PersonnelNumber, p.vendor_name Name FROM jl_br_journals j, jl_br_balances b,

gl_code_combinations glcc, fnd_flex_values_vl glf, gl_periods gp, gl_sets_of_books gsb, po_vendors p WHERE ...

6.1 指示优化器的方法与目标的hints：

ALL_ROWS -- 基于代价的优化器，以吞吐量为目标

FIRST_ROWS(n) -- 基于代价的优化器，以响应时间为目标 CHOOSE -- 根据是否有统计信息，选择不同的优化器 RULE -- 使用基于规则的优化器

SELECT /*+ FIRST_ROWS(19) */ employ_id,empname FROM employees

WHERE department_id = 20;

6.2 指示存储路径的hints： FULL /*+ FULL ( table ) */ 指定该表使用全表扫描

ROWID /*+ ROWID ( table ) */

指定对该表使用rowid存取方法，该提示用的较少 INDEX /*+ INDEX ( table [index]) */

使用该表上指定的索引对表进行索引扫描 INDEX_FFS /*+ INDEX_FFS ( table [index]) */ 使用快速全表扫描

NO_INDEX /*+ NO_INDEX ( table [index]) */

不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描

SELECT /*+ FULL(e) */ emp_id,empname FROM employees e;

SELECT /*+ INDEX(A sex_index) use sex_index because there are few male patients */ A.name, A.height, A.weight FROM patients A WHERE A.sex = 'm';

6.3 指示连接顺序的hints: ORDERED /*+ ORDERED */

按from 字句中表的顺序从左到右的连接

STAR /*+ STAR */

指示优化器使用星型查询

SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity FROM customers c, order_items l, orders o WHERE c.cust_last_name = :b1 AND o.customer_id = c.customer_id AND o.order_id = l.order_id;

6.4 指示连接类型的hints：

USE_NL /*+ USE_NL ( table [,table, ...] ) */ 使用嵌套连接

USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */ 使用排序- -合并连接

USE_HASH /*+ USE_HASH ( table [,table, ...]) */ 使用HASH连接

注意：如果表有alias(别名)，则上面的table指的是表的别名，而不是真实的表名

对对象进行分析： analyze table a compute statistices; analyze index inx_col12A compute statistics;

2) 当CBO选择了一个次优化的执行计划时, 不要同CBO过意不去, 先采取如下措施: a) 检查是否在表与索引上又最新的统计数据

b) 对所有的数据进行分析，而不是只分析一部分数据

c) 检查是否引用的数据字典表，在oracle 10G之前，缺省情况下是不对数据字典表进行分析的。

d) 试试RBO优化器，看语句执行的效率如何，有时RBO能比CBO产生的更好的执行计划 e) 如果还不行，跟踪该语句的执行，生成trace信息，然后用tkprof格式化trace信息，这样可以得到全面的供优化的信息。

5) 如果一个row source 超过10000行数据，则可以被认为大row source

6) 有(+)的表不是driving table，注意：如果有外联接，而且order hint指定的顺序与外联结决定的顺序冲突，则忽略order hint

7. 如何通过跟踪一个客户端程序发出的sql的方法来优化SQL

1) 识别要跟踪的客户端程序到数据库的连接(后面都用session代替)，主要找出能唯一识别一个session的sid与serial#.

2) 设定相应的参数，如打开时间开关(可以知道一个sql执行了多长时间)，存放跟踪数据的文件的位置、最大值。 3) 启动跟踪功能

4) 让系统运行一段时间，以便可以收集到跟踪数据 5) 关闭跟踪功能