今天突然想起前段时间学习的一篇博客，是oaktable的Charles Hooper所写，链接为：
https://hoopercharles.wordpress.com/2010/02/19/what-is-the-meaning-of-the-cpu-column-in-an-explain-plan/
自己也趁机消化了一下。对于执行计划中的 列Cost (%CPU)，其中的%CPU的含义很少有人能够说得清楚，于是Charles Hooper写了上面的文章来解释。
对于执行计划的信息都会放入plan_table,所以对于plan_table中存在的三个列，也是需要格外关心的。
我也顺便从官方文档中查看了cost,cpu_cost,io_cost在10g,11g中的解释，发现还是有很大的差别，10g版本中只是寥寥几笔带过，11g中的问当描述就要详细的多。
对于%CPU的计算方式，还是根据CBO模型估算的值，我就不按照这位大师的方式了。自己准备了一些数据也来简单模拟一下。
首先创建两个表，一个大表，一个小表。
create table test_big as select object_id,object_name from all_objects;
create table test_small as select object_id,object_name from all_objects where rownum<10;
收集统计信息
exec dbms_stats.gather_table_stats(OWNNAME=>null,tabname=>'TEST_BIG',cascade=>TRUE);
exec dbms_stats.gather_table_stats(OWNNAME=>null,tabname=>'TEST_SMALL',cascade=>TRUE);
然后开始得到执行计划的信息
explain plan for select big.object_id from test_big big,test_small small where big.object_id=small.object_id order by big.object_id;
查看执行计划信息如下：
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 714063251
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 72 | 104 ( 2)| 00:00:02 |
| 1 | SORT ORDER BY | | 9 | 72 | 104 (2)| 00:00:02 |
|* 2 | HASH JOIN | | 9 | 72 | 103 ( 1)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TEST_SMALL | 9 | 27 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST_BIG | 72872 | 355K| 99 (0)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BIG"."OBJECT_ID"="SMALL"."OBJECT_ID")
16 rows selected.
这个时候可以看到在有些行中显示%CPU为1，有些为2.
我们来看看plan_table中的结果。
SELECT
ID,
COST,
IO_COST,
CPU_COST
FROM
PLAN_TABLE;
结果如下：
ID COST IO_COST CPU_COST
---------- ---------- ---------- ----------
0 104 102 69336070
1 104 102 69336070
2 103 102 36982117
3 3 3 29836
4 99 99 13487397
至于%CPU的计算方式，可以参考下面的例子。
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
---------- ---------- ---------- ---------- ---------- ----------
0 104 102 2 2 69336070
1 104 102 2 2 69336070
2 103 102 1 1 36982117
3 3 3 0 0 29836
4 99 99 0 0 13487397
可以看到在id=0的行 %CPU为2，id=2的行，%CPU为1
这些也是完全和执行计划吻合的。
再来看一个例子，我们开启一个并行查询。
SQL> explain plan for select /*+parallel*/ *from test_big ;
Explained.
这个时候直接查看plan_table的结果，来猜猜执行计划的情况。
SQL> SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
---------- ---------- ---------- ---------- ---------- ----------
0 55 55 0 0 6882356
1
2 55 55 0 0 6882356
3 55 55 0 0 6882356
4 55 55 0 0 6882356
再次查看执行计划的情况。
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 2497108266
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72872 | 2063K| 55 ( 0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 72872 | 2063K| 55 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 72872 | 2063K| 55 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| TEST_BIG | 72872 | 2063K| 55 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
可以看到官方文档中对于cost的解释最后一句The value of this column is a function of the CPU_COST and IO_COST columns.
看来还是很有必要来分析分析这个function是怎么回事了。
https://hoopercharles.wordpress.com/2010/02/19/what-is-the-meaning-of-the-cpu-column-in-an-explain-plan/
自己也趁机消化了一下。对于执行计划中的 列Cost (%CPU)，其中的%CPU的含义很少有人能够说得清楚，于是Charles Hooper写了上面的文章来解释。
对于执行计划的信息都会放入plan_table,所以对于plan_table中存在的三个列，也是需要格外关心的。
我也顺便从官方文档中查看了cost,cpu_cost,io_cost在10g,11g中的解释，发现还是有很大的差别，10g版本中只是寥寥几笔带过，11g中的问当描述就要详细的多。
| |
11g | 10g |
| COST | Cost of the operation as estimated by the optimizer's query approach. Cost is not determined for table access operations. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans. The value of this column is a function of the CPU_COST and IO_COST columns. | Cost of the current operation estimated by the cost-based optimizer (CBO) |
| CPU_COST | CPU cost of the operation as estimated by the query optimizer's approach. The value of this column is proportional to the number of machine cycles required for the operation. For statements that use the rule-based approach, this column is NULL | User-defined CPU cost |
| IO_COST | I/O cost of the operation as estimated by the query optimizer's approach. The value of this column is proportional to the number of data blocks read by the operation. For statements that use the rule-based approach, this column is NULL. | User-defined CPU cost |
首先创建两个表，一个大表，一个小表。
create table test_big as select object_id,object_name from all_objects;
create table test_small as select object_id,object_name from all_objects where rownum<10;
收集统计信息
exec dbms_stats.gather_table_stats(OWNNAME=>null,tabname=>'TEST_BIG',cascade=>TRUE);
exec dbms_stats.gather_table_stats(OWNNAME=>null,tabname=>'TEST_SMALL',cascade=>TRUE);
然后开始得到执行计划的信息
explain plan for select big.object_id from test_big big,test_small small where big.object_id=small.object_id order by big.object_id;
查看执行计划信息如下：
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 714063251
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 72 | 104 ( 2)| 00:00:02 |
| 1 | SORT ORDER BY | | 9 | 72 | 104 (2)| 00:00:02 |
|* 2 | HASH JOIN | | 9 | 72 | 103 ( 1)| 00:00:02 |
| 3 | TABLE ACCESS FULL| TEST_SMALL | 9 | 27 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| TEST_BIG | 72872 | 355K| 99 (0)| 00:00:02 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BIG"."OBJECT_ID"="SMALL"."OBJECT_ID")
16 rows selected.
这个时候可以看到在有些行中显示%CPU为1，有些为2.
我们来看看plan_table中的结果。
SELECT
ID,
COST,
IO_COST,
CPU_COST
FROM
PLAN_TABLE;
结果如下：
ID COST IO_COST CPU_COST
---------- ---------- ---------- ----------
0 104 102 69336070
1 104 102 69336070
2 103 102 36982117
3 3 3 29836
4 99 99 13487397
至于%CPU的计算方式，可以参考下面的例子。
SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
---------- ---------- ---------- ---------- ---------- ----------
0 104 102 2 2 69336070
1 104 102 2 2 69336070
2 103 102 1 1 36982117
3 3 3 0 0 29836
4 99 99 0 0 13487397
可以看到在id=0的行 %CPU为2，id=2的行，%CPU为1
这些也是完全和执行计划吻合的。
再来看一个例子，我们开启一个并行查询。
SQL> explain plan for select /*+parallel*/ *from test_big ;
Explained.
这个时候直接查看plan_table的结果，来猜猜执行计划的情况。
SQL> SELECT
ID,
COST,
IO_COST,
COST-IO_COST DIFF,
CEIL(DECODE(COST,0,0,(COST-IO_COST)/COST)*100) PER_CPU,
CPU_COST
FROM
PLAN_TABLE;
ID COST IO_COST DIFF PER_CPU CPU_COST
---------- ---------- ---------- ---------- ---------- ----------
0 55 55 0 0 6882356
1
2 55 55 0 0 6882356
3 55 55 0 0 6882356
4 55 55 0 0 6882356
再次查看执行计划的情况。
SQL> select *from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 2497108266
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 72872 | 2063K| 55 ( 0)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 72872 | 2063K| 55 (0)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 72872 | 2063K| 55 (0)| 00:00:01 | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| TEST_BIG | 72872 | 2063K| 55 (0)| 00:00:01 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------------
Note
-----
- automatic DOP: Computed Degree of Parallelism is 2
可以看到官方文档中对于cost的解释最后一句The value of this column is a function of the CPU_COST and IO_COST columns.
看来还是很有必要来分析分析这个function是怎么回事了。
