第十二章-CSDN博客

第十二章使用执行计划

一、理解执行计划
explain plan语句能够展示select、update、delete、insert语句经过优化的执行计划。
一条语句的执行计划就是数据库运行该语句的一系列动作。

行源数（row source tree）是执行计划的核心。显示如下信息：
语句指定的表执行顺序
语句中每个表的访问方式
语句中连接操作的方法
数据的filter、sort、aggregation

除了行源数，计划表中还包含
优化器，如成本、每个操作的cardinality
分区
并行执行

执行计划告诉我们优化的结果，知道查询的性能

1.执行计划如何修改
由于实际的执行环境与计划的环境不同，执行计划与实际的执行结果是有差异的。主要由于两个原因：
用户schemas不同：执行者不同，执行计划有可能不同
成本不同：数据量和统计信息、绑定变量的类型和值、全局以及会话级初始化参数的设置导致成本可能不同

2.最小化throw-away
Examining an explain plan lets you look for throw-away in cases such as the following:
■Full scans
■Unselective range scans
■Late predicate filters
■Wrong join order
■Late filter operations

3.查看执行计划
估计的查看一个执行计划并不能说明问题。比如：执行计划中显示使用了索引，这并不能说明语句高效。
索引有时候并不高效，这时候需要通过如下方式检查：
索引检索的行数
他们的可选择性

最好用explain plan来判断路径计划（access plan），然后通过实验证明这是好的计划。

（1）使用v$sql_plan视图
除了explain plan外，可以直接到v$sql_plan中查看执行计划。只要是还在shared sql area中的语句，都能看到。其定义类似plan_table

使用explain plan方法需要安装同样的环境来得到相同的计划，而v$sql_plan不需要。

使用v$sql_plan_statistics视图为计划中的每个操作都提供了实际的执行统计数据，包括输出行数量、跳出时间等。这些数据都是累加的，除了输出行。
初始化参数statistics_level设置为all时，v$sql_plan_Statistics对于游标才可用。

v$sql_plan_statistics_all视图将每个游标的v$sql_plan和v$sql_plan_statistics视图合并起来，比较优化器提供的行数和花费时间

4.explain plan的限制
对于含有绑定变量的语句，explain plan输出的可能不是真实的执行计划。
tkprof无法判断sql语句中绑定变量的类型，就假设是字符型，如果不是的话就会给出报错信息。可以通过适当的数据转化来避免此问题。

二、plan_table输出
plan_table作为全局临时表的公共同义词自动创建。
这个临时表为所有用户存储explain_plan语句。
plan_table是explain_plan语句默认插入数据的地方。

plan_table是自动安装的，也可以使用catplan.sql手工安装。此脚本默认linux路径为$oracle_home/rdbms/admin目录下。

oracle建议在升级后重建计划表plan_table。因为其列可能有变化，可能造成脚本错误或tkprof失败。

三、运行explain plan
1.为explain plan指定语句
SQL> explain plan
2 set statement_id='st1' for
3 select ename from emp;

2.将计划放入其他表中
explain plan [set statement_id='ssss']
into my_plan_table
for select * from dept;

四.显示plan_table输出
执行explain plan语句后，可以通过脚本或者pl/sql包来显示最新的plan table输出：
utlxpls.sql
utlxplp.sql 该脚本包含了并行执行的列
dbms_xplan.display
该函数可以指定未在plan_table中的表名；
如果设置了语句id，可以指定id；
决定细节级别的参数：basic、serial、typical、all

实例：
SQL> explain plan
2 set statement_id='st1' for
3 select ename from emp;

Explained.

SQL> @?/rdbms/admin/utlxpls.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |    15 |    90 |     3   (0)| 00:00:01 |
|   1 | TABLE ACCESS FULL| EMP |    15 |    90 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8 rows selected.

SQL> @?/rdbms/admin/utlxplp.sql

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3956160932

8 rows selected.

SQL> select * from table(dbms_xplan.display('EMP','st1','TYPICAL'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ERROR: an uncaught error in function display has happened; please contact Oracle
support

Please provide also a DMP file of the used plan table EMP
ORA-00904: "STATEMENT_ID": invalid identifier

这块应该是有问题的，正常应该也是显示上面类似的信息

1.定制plan_table输出
如果为语句制定了ID，就可以自己创建脚本来查询plan_table了。如：
（1）以0开始，并赋予statement_id
（2）使用connect by在树的父子节点上遍历，
Use the CONNECT BY clause to walk the tree from parent to child, the join keys being STATEMENT_ID = PRIOR STATEMENT_ID and PARENT_ID = PRIOR ID.
（3）Use the pseudo-column LEVEL (associated with CONNECT BY) to indent the children.

SQL> SELECT cardinality "Rows",
lpad(' ',level-1)||operation||' '||options||' '||object_name "Plan"
FROM PLAN_TABLE
CONNECT BY prior id = parent_id
AND prior statement_id = statement_id
START WITH id = 0
AND statement_id = 'st1'
ORDER BY id; 2 3 4 5 6 7 8

      Rows Plan
---------- --------------------------------------------------------------------------------
        15 SELECT STATEMENT
        15 TABLE ACCESS FULL EMP

五、explain plan的读取

通过类似以下语句读取执行计划：
SELECT PLAN_TABLE_OUTPUT
FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'statement_id','BASIC'));

六、用explain plan查看并行执行
对并行查询的调优，首先需要像串行查询那样选择驱动表。
但是选取依据是不一样的。

比如有表customer、account、transaction，其中customer最小、transaction最大。
在oltp系统中查询特定customer的交易信息，查询将把顾客表作为驱动，目标是用逻辑i/o最小，一般也会使物理i/o、cpu时间较小。

对于并行查询，由于可以并行查询，一般选择大表作为驱动表。由于最终只有一部分数据被检索，因此这种情况下并行不适用。

然而，如果需要查看所有顾客在一段时间内的交易信息，用交易信息表作为驱动就会很高效。因为在顾客表上没有任何限制，数据库将交易表与账户表合并，最后与顾客表合并。
这种情况下，账户和顾客表上的索引是高选择性的。由于交易表很大，列的选择性不高，利用交易表作为驱动表进行并行查询是高效的。

并行操作包括：
（1）parallel_to_parallel
（2）parallel_to_serial
A PARALLEL_TO_SERIAL operation which is always the step that occurs when rows from a parallel operation are consumed by the query coordinator. Another type of operation that does not occur in this query is a SERIAL operation. If these types of operations occur, then consider making them parallel operations to improve performance because they too are potential bottlenecks.
（3）parallel_from_serial
（4）parallel_to_parallel
通常来说是高效的，只要每一步的负载相对平均
（5）PARALLEL_COMBINED_WITH_CHILD
（6）PARALLEL_COMBINED_WITH_PARENT

七、查看并行查询的explain plan
When using EXPLAIN PLAN with parallel queries, the database compiles and executes one parallel plan. This plan is derived from the serial plan by allocating row sources specific to the parallel support in the QC plan. The table queue row sources (PX Send and PX Receive), the granule iterator, and buffer sorts, required by the two slave set PQ model, are directly inserted into the parallel plan. This plan is the exact same plan for all the slaves if executed in parallel or for the QC if executed in serial.

SQL> create table empp as select * from emp;

Table created.

SQL> alter table empp parallel 2;

Table altered.

SQL> explain plan for select sum(sal) from empp group by deptno;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2396956

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 15 | 390 | 3 (34)| 00:00
:01 | | | |

| 1 | PX COORDINATOR | | | | |
| | | |

| 2 | PX SEND QC (RANDOM) | :TQ10001 | 15 | 390 | 3 (34)| 00:00
:01 | Q1,01 | P->S | QC (RAND) |

| 3 | HASH GROUP BY | | 15 | 390 | 3 (34)| 00:00
:01 | Q1,01 | PCWP | |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

| 4 | PX RECEIVE | | 15 | 390 | 3 (34)| 00:00
:01 | Q1,01 | PCWP | |

| 5 | PX SEND HASH | :TQ10000 | 15 | 390 | 3 (34)| 00:00
:01 | Q1,00 | P->P | HASH |

| 6 | HASH GROUP BY | | 15 | 390 | 3 (34)| 00:00
:01 | Q1,00 | PCWP | |

| 7 | PX BLOCK ITERATOR | | 15 | 390 | 2 (0)| 00:00

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
:01 | Q1,00 | PCWC | |

| 8 | TABLE ACCESS FULL| EMPP | 15 | 390 | 2 (0)| 00:00
:01 | Q1,00 | PCWP | |

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

Note
-----

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)

19 rows selected.

The table EMP2 is scanned in parallel by one set of slaves while the aggregation for the GROUP BY is done by the second set. The PX BLOCK ITERATOR row source represents the splitting up of the table EMP2 into pieces so as to divide the scan workload between the parallel scan slaves. The PX SEND and PX RECEIVE row sources represent the pipe that connects the two slave sets as rows flow up from the parallel scan, get repartitioned through the HASH table queue, and then read by and aggregated on the top slave set. The PX SEND QC row source represents the aggregated values being sent to the QC in random (RAND) order. The PX COORDINATOR row source represents the QC or Query Coordinator which controls and schedules the parallel plan appearing below it in the plan tree.

八、查看位图索引的explain plan
位图索引列在执行计划表中用BITMAP提示索引类型。

九、查看explain plan的结果缓存
查询中包含result_cache 这个hint时，结果缓存被插入到执行计划。如：
SQL> select /*+result_cache*/deptno,avg(sal) from emp group by deptno;

SQL> explain plan for select /*+result_cache*/deptno,avg(sal) from emp group by deptno;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4067220884

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

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 21 | 4
(25)| 00:00:01 |

| 2 | HASH GROUP BY | | 3 | 21 | 4
(25)| 00:00:01 |

| 3 | TABLE ACCESS FULL| EMP | 15 | 105 | 3
(0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

Result Cache Information (identified by operation id):
------------------------------------------------------

1 - column-count=2; dependencies=(SCOTT.EMP); name="select /*+result_cache*/d
eptno,avg(sal) from emp group by deptno"

15 rows selected.

这样，就可以在v$result_cache_objects视图中使用显示出来的cacheid进行查询了

十、用explain plan查看分区对象

在partition start和partition stop列之间会显示分区的访问。
The row source name for the range partition is PARTITION RANGE. For hash partitions, the row source name is PARTITION HASH.

A join is implemented using partial partition-wise join if the DISTRIBUTION column of the plan table of one of the joined tables contains PARTITION(KEY). Partial partition-wise join is possible if one of the joined tables is partitioned on its join column and the table is parallelized.
A join is implemented using full partition-wise join if the partition row source appears before the join row source in the EXPLAIN PLAN output. Full partition-wise joins are possible only if both joined tables are equi-partitioned on their respective join columns. Examples of execution plans for several types of partitioning follow.

例子：
CREATE TABLE emp_range
PARTITION BY RANGE(hire_date)
(
PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1992','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1994','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1996','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1998','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY'))
)
AS SELECT * FROM employees;
For the first example, consider the following statement:
EXPLAIN PLAN FOR
SELECT * FROM emp_range;
Oracle Database displays something similar to the following:
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 105 | 13965 | 2 | | |
| 1 | PARTITION RANGE ALL| | 105 | 13965 | 2 | 1 | 5 |
| 2 | TABLE ACCESS FULL | EMP_RANGE | 105 | 13965 | 2 | 1 | 5 |
---------------------------------------------------------------------------------

这个例子中，由于谓词部分没有排除数据，所有的分区都被访问了。partition_start和partition_stop列分别是1和5 （Pstart、Pstop）

EXPLAIN PLAN FOR
SELECT * FROM emp_range
WHERE hire_date >= TO_DATE('1-JAN-1996','DD-MON-YYYY');

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 399 | 2 | | |
| 1 | PARTITION RANGE ITERATOR| | 3 | 399 | 2 | 4 | 5 |
|* 2 | TABLE ACCESS FULL | EMP_RANGE | 3 | 399 | 2 | 4 | 5 |
--------------------------------------------------------------------------------------
以上语句是4、5两个分区。
operation列也有变化。如果只有一个分区被检索，将是PARTITION RANGE SINGLE

对于hash 分区，显示的信息基本一样，但operation列时partition hash，而不是partition range

对于复合分区，显示也是类似的：
CREATE TABLE emp_comp PARTITION BY RANGE(hire_date)
SUBPARTITION BY HASH(department_id) SUBPARTITIONS 3
(
PARTITION emp_p1 VALUES LESS THAN (TO_DATE('1-JAN-1992','DD-MON-YYYY')),
PARTITION emp_p2 VALUES LESS THAN (TO_DATE('1-JAN-1994','DD-MON-YYYY')),
PARTITION emp_p3 VALUES LESS THAN (TO_DATE('1-JAN-1996','DD-MON-YYYY')),
PARTITION emp_p4 VALUES LESS THAN (TO_DATE('1-JAN-1998','DD-MON-YYYY')),
PARTITION emp_p5 VALUES LESS THAN (TO_DATE('1-JAN-2001','DD-MON-YYYY'))
)
AS SELECT * FROM employees;

EXPLAIN PLAN FOR
SELECT * FROM emp_comp;

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10120 | 1314K| 78 | | |
| 1 | PARTITION RANGE ALL| | 10120 | 1314K| 78 | 1 | 5 |
| 2 | PARTITION HASH ALL| | 10120 | 1314K| 78 | 1 | 3 |
| 3 | TABLE ACCESS FULL| EMP_COMP | 10120 | 1314K| 78 | 1 | 15 |
--------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT * FROM emp_comp
WHERE hire_date = TO_DATE('15-FEB-1998', 'DD-MON-YYYY');
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 2660 | 17 | | |
| 1 | PARTITION RANGE SINGLE| | 20 | 2660 | 17 | 5 | 5 |
| 2 | PARTITION HASH ALL | | 20 | 2660 | 17 | 1 | 3 |
|* 3 | TABLE ACCESS FULL | EMP_COMP | 20 | 2660 | 17 | 13 | 15 |
-----------------------------------------------------------------------------------
检索的开始分区和结束分区有变化

EXPLAIN PLAN FOR
SELECT * FROM emp_comp WHERE department_id = 20;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 13433 | 78 | | |
| 1 | PARTITION RANGE ALL | | 101 | 13433 | 78 | 1 | 5 |
| 2 | PARTITION HASH SINGLE| | 101 | 13433 | 78 | 3 | 3 |
|* 3 | TABLE ACCESS FULL | EMP_COMP | 101 | 13433 | 78 | | |
-----------------------------------------------------------------------------------
以上情况下只需要检索一个子分区，在编译时就知道子分区的号码，因此hash paratition row source不需要。

VARIABLE dno NUMBER;
EXPLAIN PLAN FOR
SELECT * FROM emp_comp WHERE department_id = :dno;
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 13433 | 78 | | |
| 1 | PARTITION RANGE ALL | | 101 | 13433 | 78 | 1 | 5 |
| 2 | PARTITION HASH SINGLE| | 101 | 13433 | 78 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | EMP_COMP | 101 | 13433 | 78 | | |
-----------------------------------------------------------------------------------
这种情况下，oracle编译时不知道子分区number，在运行时才能确定起止分区号

plan_table中列的内容列表见331页

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26451536/viewspace-751387/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-751387/