关于执行计划中的%CPU的含义

今天突然想起前段时间学习的一篇博客，是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中的问当描述就要详细的多。

	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

对于%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是怎么回事了。

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

转载于:http://blog.itpub.net/23718752/viewspace-1844005/