10053事件分析案例一則

測試庫兩張表，資料一致，(表有複合主鍵A+B)，但同樣執行DELETE TABLE FROM T1/T2 WHERE A='1' AND ROWNUM<100;時，T1表刪除時間非常長，T2表刪除時間很快。在PLSQL中或sqlplus中查看執行計畫都是一樣的，表示都用到了索引範圍掃描。

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
| Id  | Operation            |  Name          | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | DELETE STATEMENT     |                |  1000 | 12000 |  3217 |
|   1 |  DELETE              | T1   |       |       |       |
|*  2 |   COUNT STOPKEY      |                |       |       |       |
|*  3 |    INDEX RANGE SCAN  | IDX_T1  |   420K|  4931K|  3217 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - filter(ROWNUM<=1000)
   3 - access("T1"."A"='1')
Note: cpu costing is off

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | DELETE STATEMENT     |             |  1000 | 12000 |  2965 |
|   1 |  DELETE              | T2   |       |       |       |
|*  2 |   COUNT STOPKEY      |             |       |       |       |
|*  3 |    INDEX RANGE SCAN  | IDX_T2  |   393K|  4607K|  2965 |
--------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - filter(ROWNUM<=1000)
   3 - access("T2"."A"='1')
Note: cpu costing is off

顯然感覺這兩個表的實際操作和執行計畫不太相符，這時10053事件就起到了作用。

10053介紹：

10053 事件是oracle 提供的用於跟蹤sql 語句成本計算的內部事件，它能記載CBO 模式下oracle 最佳化器如何計算sql 成本，產生相應的執行計畫。 用來描述oracle如何選擇執行計畫的過程，然後輸出到trace檔案裡，因為我們經常看執行計畫怎麼執行的消耗了哪些資源，而不是常看執行計畫怎麼選擇出來了的。

10053特點：

(1) 只可以瞭解oracle執行計畫的選擇過程

(2) 無法獲知代價的計算公式，因為這是oracle內部的商業機密，而且每個oracle版本的最佳化器計算公式都不相同差距還是蠻大的，不同版本的同一個語句的代價也不一樣，最佳化器現在還不是很成熟，還有待完善。

(3) 在這個裡面我們重點要瞭解的是“代價”是如何計算出來的，然後我們才能瞭解執行計畫是如何選擇的。

(4) 在10053中可以瞭解哪些因素影響sql的執行代價

(5) oracle 8i cost等價IO資源消耗   9i以後cost等價IO+CPU+網路+等待事件+其他代價

T1表的10053事件資訊：

***************************************

BASE STATISTICAL INFORMATION
***********************
Table stats    Table: T1   Alias: T1 來自user_tables視圖
  TOTAL ::  CDN: 2341358  NBLKS:  13921  AVG_ROW_LEN:  40
-- Index stats 來自user_indexes視圖
  INDEX NAME: IDX_STAROTHER  COL#: 2 3 
    TOTAL ::  LVLS: 2   #LB: 13609  #DK: 2156054  LB/K: 1  DB/K: 1  CLUF: 165252
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO  Col#: 2      Table: T1Alias: T1
    NDV: 7         NULLS: 0         DENS: 1.4286e-01
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: STAROTHERPRF     ORIG CDN: 2341358  ROUNDED CDN: 334480  CMPTD CDN: 334480
  Access path: tsc  Resc:  1340  Resp:  1340   全表掃描代價(1340)，這裡tsc我想應該是TableScan的縮寫
  Skip scan: ss-sel 0  andv 308008  
    ss cost 308008  索引跳躍掃描的代價(1945)
    index io scan cost 1945 
  Access path: index (index-only) 索引(範圍)掃描代價(1947)
      Index: IDX_T1
  TABLE: T1
      RSC_CPU: 0   RSC_IO: 1947
  IX_SEL:  1.4286e-01  TB_SEL:  1.4286e-01

  BEST_CST: 1340.00  PATH: 2  Degree:  1最佳代價是1340，即全表掃描

對應的執行計畫：

***************************************
GENERAL PLANS
***********************
Join order[1]:  STAROTHERPRF[STAROTHERPRF]#0
Best so far: TABLE#: 0  CST:       1340  CDN:     334480  BYTES:    4348240
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 1340  CDN: 334480  RSC: 1340  RSP: 1340  BYTES: 4348240
  IO-RSC: 1340  IO-RSP: 1340  CPU-RSC: 0  CPU-RSP: 0
QUERY
explain plan for delete from starotherprf WHERE AIRLINE_CODE = 'US' AND ROWNUM <= 1000
PLAN
Cost of plan:  1340
Operation...........Object name.....Options.........Id...Pid..
DELETE STATEMENT                                        0
DELETE              STAROTHERPRF                        1
COUNT                               STOPKEY             2    1
TABLE ACCESS        T1    FULL                3    2
QUERY

顯示用的就是全表掃描

T2表的10053事件資訊：

***************************************

SINGLE TABLE ACCESS PATH
Column: AIRLINE_CO  Col#: 1      Table: T2 Alias:T2
    NDV: 19        NULLS: 0         DENS: 5.2632e-02
    NO HISTOGRAM: #BKT: 1 #VAL: 2
  TABLE: CASTARPRF     ORIG CDN: 6665065  ROUNDED CDN: 350793  CMPTD CDN: 350793
  Access path: tsc  Resc:  4275  Resp:  4275 全表掃描代價(4275)
  Skip scan: ss-sel 0  andv 413617   索引跳躍掃描代價(413617)
    ss cost 413617 
    index io scan cost 1973 
  Access path: index (index-only) 索引(範圍)掃描代價(1975)
      Index: IDX_T2
  TABLE: T2
      RSC_CPU: 0   RSC_IO: 1975
  IX_SEL:  5.2632e-02  TB_SEL:  5.2632e-02
  BEST_CST: 1975.00  PATH: 4  Degree:  1 最佳代價是1975，即索引掃描

對應的執行計畫：

***************************************
GENERAL PLANS
***********************
Join order[1]:  CASTARPRF[CASTARPRF]#0
Best so far: TABLE#: 0  CST:       1975  CDN:     350793  BYTES:    4911102
prefetching is on for IDX_CASTAR
Final - All Rows Plan:
  JOIN ORDER: 1
  CST: 1975  CDN: 350793  RSC: 1975  RSP: 1975  BYTES: 4911102
  IO-RSC: 1975  IO-RSP: 1975  CPU-RSC: 0  CPU-RSP: 0
QUERY
explain plan for delete from castarprf WHERE AIRLINE_CODE = 'US' AND ROWNUM <= 1000
PLAN
Cost of plan:  1975
Operation...........Object name.....Options.........Id...Pid..
DELETE STATEMENT                                        0
DELETE              CASTARPRF                           1
COUNT                               STOPKEY             2    1
INDEX               IDX_T2      RANGE SCAN          3    2
QUERY

顯示用的就是索引掃描

現在就可以知道為什麼這兩張表刪除時間不同了，原因就是T1表CBO選擇了錯誤的執行計畫，導致全表掃描，因此百萬級的資料就會耗費更長的時間。

總結：當感覺SQL語句執行時走的是錯誤的執行計畫，而又找不到原因時，這時請用10053來分析一下原因。這就是10053的適用情境。