今天突然想起前段時間學習的一篇部落格，是oaktable的Charles Hooper所寫，連結為：

自己也趁機消化了一下。對於執行計劃中的列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是怎麼回事了。

關於執行計劃中的%CPU的含義

相關文章