由於統計量失真造成SQL執行計劃錯誤一例

realkid4發表於2012-11-18

 

Oracle CBO時代,統計量(Statistic Data,包括系統統計量和物件統計量)是最優SQL執行計劃生成的基礎。在我們的實際工作中,大部分CBO執行計劃效率問題,都是與過期統計量或者異常統計量有關。可以說,把握好統計量,穩定統計量,是CBO時代DBA的重要工作之一。

 

本篇分析一例由於統計量錯誤引起的效能問題。

 

1、問題綜述

 

開發組同事反映了一個問題:相同的SQL語句,在前一天根本無法執行出結果,語句執行以報錯告終。資料表中使用了多表連結,最大資料量在幾萬條記錄。但是時隔一夜之後,第二天聯絡DB組協助除錯時候,Oracle竟然可以執行出結果,第一次耗時約5s,第二次耗時不到1s完成,結果集合大約2000餘條。

 

這個問題看似是“自愈”了,但是作為一個案例。筆者還是決定著手研究一下。資料庫版本為11g,對應SQL語句原文為如下(安全原因,部分內容進行處理)

 

 

select count(*)

 from comm.iraline

 inner join UX.air on trim(air.air_code) = iraline.air_code

 inner join common.ori_city on air.air_city = ori_city.city_code

 inner join comm.new_city on upper(new_city.city_name) =

                                    trim(ori_city.city_desc)

  left join UX.param p1 on (air.air_code = p1.sspa_air_c and

                                  p1.sspa_param_c = 'RPT')

  left join UX.param p2 on (air.air_code = p2.sspa_air_c and

                                  p2.sspa_param_c = 'DTA')

 where (AIR.AIR_EFF_EN_DT >=

       to_date('2010-1-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss') or

       AIR.AIR_EFF_EN_DT is null);

 

 

2、執行計劃分析

 

單從SQL形態上看,問題SQL本身沒有什麼過多值得關注的地方,重點在於多次的內連線和左連線操作。而where條件後面的內容,也沒有什麼過多關注的方面。

 

相對於RBOCBO具有很強的靈活性。其中一個代表feature就是“SQL Transformation”,Oracle最佳化器會根據自己對SQL的理解,將輸入SQL進行一定的改寫,便於生成更好的執行計劃。所以在CBO時代,SQL外型分析的作用比較有限,最直接的做法是確定SQL的執行計劃。

 

我們首先實際實行該SQL,看看效果。

 

 

SQL> select /*+ GATHER_PLAN_STATISTICS */ count(*)

(篇幅原因,有省略)

 24    from comm.iraline

 25   inner join UX.air on trim(air.air_code) = iraline.air_code

 26   inner join common.ori_city on air.air_city = ori_city.city_code

 27   inner join comm.new_city on upper(new_city.city_name) =

 28                                      trim(ori_city.city_desc)

 29    left join UX.param p1 on (air.air_code = p1.sspa_air_c and

 30                                    p1.sspa_param_c = 'RPT')

 31    left join UX.param p2 on (air.air_code = p2.sspa_air_c and

 32                                    p2.sspa_param_c = 'DTA')

 33   where (AIR.AIR_EFF_EN_DT >=

 34         to_date('2010-1-1 00:00:00', 'yyyy-mm-dd hh24:mi:ss') or

 35         AIR.AIR_EFF_EN_DT is null);

 

COUNT(*)/*AIR.AIR_EFF_ST_DTEFF

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

                          2945

Executed in 0.484 seconds

 

 

生成的執行計劃如下:

 

 

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

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 1966585548

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

| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU

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

|   0 | SELECT STATEMENT          |                |     1 |   116 |   535   (4

|   1 |  SORT AGGREGATE           |                |     1 |   116 |

|*  2 |   HASH JOIN               |                |  1236K|   136M|   535   (4

|*  3 |    HASH JOIN              |                |  1478 |   151K|   214   (2

|*  4 |     HASH JOIN RIGHT OUTER  |               |   165 | 16665 |   179   (3

|*  5 |      INDEX FAST FULL SCAN  | PARAMP1  |   161 |  3542 |    31   (4

|*  6 |      HASH JOIN            |                |   151 | 11929 |   148   (3

|*  7 |       HASH JOIN OUTER     |                |   151 |  6040 |    43   (3

|*  8 |        TABLE ACCESS FULL  | AIR            |   139 |  2502 |    12   (0

|*  9 |        INDEX FAST FULL SCAN| PARAMP1  |   161 |  3542 |    31   (4

|  10 |       TABLE ACCESS FULL   | ORI_CITY        | 30914 |  1177K|   104   (1

|  11 |     TABLE ACCESS FULL     | IRALINE |  5109 | 20436 |    35   (0

|  12 |    TABLE ACCESS FULL      | NEW_CITY       | 83678 |   898K|   310   (1

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

PLAN_TABLE_OUTPUT

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

Predicate Information (identified by operation id):

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

   2 - access(TRIM("ORI_CITY"."CITY_DESC")=UPPER("NEW_CITY"."CITY_NAME"))

   3 - access("IRALINE"."AIR_CODE"=TRIM("AIR"."AIR_CODE"))

   4 - access("AIR"."AIR_CODE"="P2"."SSPA_AIR_C"(+))

   5 - filter("P2"."SSPA_PARAM_C"(+)='DTA')

   6 - access("AIR"."AIR_CITY"="ORI_CITY"."CITY_CODE")

   7 - access("AIR"."AIR_CODE"="P1"."SSPA_AIR_C"(+))

   8 - filter("AIR"."AIR_EFF_EN_DT" IS NULL OR "AIR"."AIR_EFF_EN_DT">=TO_DATE('

              2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

   9 - filter("P1"."SSPA_PARAM_C"(+)='RPT')

32 rows selected

 

Executed in 0.281 seconds

 

 

從執行計劃看,大部分連線的資料來源(Row Source)評估值相對較大,所以選擇Hash Join。而且從實際執行結果看,效果也是可以接受的。那麼,此時統計量情況反映是否準確,也就是說估算計劃時的資料量和實際執行的效果有多大差異呢?

 

 

PLAN_TABLE_OUTPUT

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

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

| Id  | Operation                 | Name           | Starts | E-Rows | A-Rows |

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

|   0 | SELECT STATEMENT          |                |      1 |        |      1 |

|   1 |  SORT AGGREGATE           |                |      1 |      1 |      1 |

|*  2 |   HASH JOIN               |                |      1 |   1236K|   2945 |

|*  3 |    HASH JOIN              |                |      1 |   1478 |   1333 |

|*  4 |     HASH JOIN RIGHT OUTER  |               |      1 |    165 |    137 |

|*  5 |      INDEX FAST FULL SCAN  | PARAMP1  |      1 |    161 |    161 |

|*  6 |      HASH JOIN             |               |      1 |    151 |    137 |

|*  7 |       HASH JOIN OUTER      |               |      1 |    151 |    139 |

|*  8 |        TABLE ACCESS FULL   | AIR           |      1 |    139 |    139 |

|*  9 |        INDEX FAST FULL SCAN| PARAMP1  |      1 |    161 |    161 |

|  10 |       TABLE ACCESS FULL    | ORI_CITY       |      1 |  30914 |  30910 |

|  11 |     TABLE ACCESS FULL      | IRALINE|      1 |   5109 |   5109 |

|  12 |    TABLE ACCESS FULL       | NEW_CITY      |      1 |  83678 |  83678 |

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

Predicate Information (identified by operation id):

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

 

 

對比E-RowsA-Rows兩列,我們發現執行計劃估算在資料行的問題上是比較準確的。最高層Hash Join的巨大差異(1236K2945)可以接受,首先因為實際成本是一個降低趨勢,其次由於資料關聯性引起的實際行數不多也是合理的。

 

所以,我們認為在當前情況下,統計量和執行計劃是沒有問題的,起碼在沒有修改SQL的前提下。

 

那麼,頭一天究竟發生了什麼呢?

 

3、錯誤執行計劃獲取

 

雖然故障消失了,但是筆者還想探討一下問題根源。此時,問題是我們希望獲取到頭一天發生故障的SQL執行計劃。這裡,我們求助了AWR報告。既然當時無法執行出結果,那麼必然應該在AWR報告庫中有所體現。

 

經過分析解析,我們定位了sql_id=b5ub33jyunuhb’,從結構上看就是我們需要處理的SQL

 

 

  from comm.iraline

 inner join UX.air on trim(air.air_code) = iraline.air_code

 inner join common.ori_city on air.air_city = ori_city.city_code

 inner join comm.new_city on trim(ori_city.city_desc) =

                                    upper(new_city.city_name)

  left join UX.param p1 on (air.air_code = p1.sspa_air_c and

                                  p1.sspa_param_c = 'RPT')

  left join UX.param p2 on (air.air_code = p2.sspa_air_c and

                                  p2.sspa_param_c = 'DTA')

 where (AIR.AIR_EFF_EN_DT >= to_date('2010-1-1 00:00:00', 'yyyy-mm-ddhh24:mi:ss') or

       AIR.AIR_EFF_EN_DT is null)

 

 

使用dbms_xplan包方法,可以抽取出在AWR資料庫中儲存的快照執行計劃。

 

 

SQL> select * from table(dbms_xplan.display_awr(sql_id => 'b5ub33jyunuhb',format => 'advanced'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID b5ub33jyunuhb

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

select count(1)/*IRALINE.GLBAIRLINE_SEQ GLBAIRLINE_SEQ,

        AIR.AIR_1LOC_ADD ADDRESS1,                  AIR.AIR_2LOC_ADD

ADDRESS2,                  AIR.AIR_3LOC_ADD ADDRESS3,

NEW_CITY.CITY_SEQ CITY_SEQ,

decode(air.air_ia_da,'I','INTERNATIONAL','D','DOMESTIC','BOTH')

APPLICABLITY,                  AIR.AIR_EFF_ST_DT EFFECTIVE_FROM,

          AIR.AIR_EFF_EN_DT EFFECTIVE_TO,                  'ADD'

LAST_ACT_TYP,                  'MIGRATION' LAST_ACT_USER,

   sysdate LAST_ACT_DATE,                  new_city.State_Seq

STATE_SEQ,                  AIR.AIR_CTRY ISOC_CODE,

AIR.AIR_PCO_ZIP ZIP_CODE,                  AIR.AIR_NAME BSP_REG_NAME,

               air.air_code air_code*/             from

comm.iraline             inner join UX.air

 on trim(air.air_code) = iraline.air_code             inner join

common.ori_city               on air.air_city = ori_city.city_code

     inner join comm.new_city               on

trim(ori_city.city_desc) = upper(new_city.city_name)             left

join UX.param p1               on (air.air_code = p1.sspa_air_c

 

PLAN_TABLE_OUTPUT

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

and p1.sspa_param_c = 'RPT')             left join

UX.param p2               on (air.air_code = p2.sspa_air_c and

p2.sspa_param_c = 'DTA')             where (AIR.AIR_EFF_EN_DT

>=                   to_date('2010-1-1 00:00:00', 'yyyy-mm-dd

hh24:mi:ss') or                   AIR.AIR_EFF_EN_DT is null)

Plan hash value: 3696956085

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

| Id  | Operation               | Name           | Rows  | Bytes | Cost (%

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

|   0 | SELECT STATEMENT             |                |       |       |   361 (

|   1 |  SORT AGGREGATE              |                |     1 |   116 |

|   2 |   NESTED LOOPS                |                |       |       |

|   3 |    NESTED LOOPS               |                |     1 |   116 |   361

|   4 |     NESTED LOOPS OUTER        |                |     1 |    77 |   360

|   5 |      NESTED LOOPS OUTER       |                |     1 |    55 |   359

|   6 |       HASH JOIN               |                |     1 |    33 |   358

|   7 |        MERGE JOIN CARTESIAN   |                |     1 |    15 |   345

|   8 |         TABLE ACCESS FULL     | IRALINE |     1 |     4 |    35

|   9 |         BUFFER SORT           |                | 83678 |   898K|   310

|  10 |          TABLE ACCESS FULL    | NEW_CITY       | 83678 |   898K|   310

|  11 |        TABLE ACCESS FULL      | AIR            |   139 |  2502 |    12

|  12 |       INDEX RANGE SCAN        | PARAMI3   |     1 |    22 |     1

|  13 |      INDEX RANGE SCAN         | PARAMI3   |     1 |    22 |     1

|  14 |     INDEX UNIQUE SCAN         | ORI_CITYP1      |     1 |       |     0

|  15 |    TABLE ACCESS BY INDEX ROWID| ORI_CITY        |     1 |    39 |     1

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

 

 

從抽取出的執行計劃看,我們發現了頭一天執行計劃的不同,大量的Nested Loop,少見的Merge Join操作。究其根源,我們看到Rows列的問題,對IRALINE等關鍵資料表,返回的資料行只有1,這個和資料量實際顯然不匹配。

 

看來筆者懷疑問題出在統計量上。錯誤、過期的統計量生成了錯誤的執行計劃。

 

進一步探討,究竟是不是統計量變化呢?

 

 

SQL> select owner, table_name, STATS_UPDATE_TIME from dba_tab_stats_history where table_name in ('ORI_CITY','NEW_CITY','IRALINE','PARAM') and owner in ('NBSCMP','COMM','UX','COMMON');

 

OWNER                          TABLE_NAME                     STATS_UPDATE_TIME

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

UX                           PARAM                     16-10-12 02.28.49.189288 下午 +08:00

UX                           PARAM                     16-10-12 02.28.49.495411 下午 +08:00

COMMON                         ORI_CITY                        16-10-12 01.57.59.694213 下午 +08:00

COMMON                         ORI_CITY                        16-10-12 01.57.59.926715 下午 +08:00

COMM                    NEW_CITY                       15-10-12 10.01.06.353768 下午 +08:00

COMM                    NEW_CITY                       17-10-12 10.01.14.828447 下午 +08:00

COMM                    NEW_CITY                       25-10-12 10.01.08.381246 下午 +08:00

COMM                    NEW_CITY                       12-11-12 10.02.16.006723 下午 +08:00

COMM                    NEW_CITY                       13-11-12 10.03.07.035336 下午 +08:00

COMM                    IRALINE                 17-10-12 10.00.38.855801 下午 +08:00

COMM                    IRALINE                 26-10-12 10.01.05.350764 下午 +08:00

COMM                    IRALINE                 05-11-12 10.00.46.740167 下午 +08:00

COMM                    IRALINE                 12-11-12 10.02.08.259827 下午 +08:00

COMM                    IRALINE                 13-11-12 10.00.42.222598 下午 +08:00

 

14 rows selected

Executed in 0.468 seconds

 

 

顯然,至少兩個資料表IRALINENEW_CITY在頭一天晚上,透過自動統計量收集作業進行過收集。注意,Oracle自動作業收集物件是那些變化超過一定程度的資料表。這也就側面說明該資料表在頭天的時候變化劇烈。

 

那麼,我們就可以猜測,那個詭異的執行計劃是建立在錯誤的執行計劃基礎上。

 

4、舊統計量恢復,執行計劃重演

 

Oracle對於統計量管理維護是有專門的方法和手段的。我們可以恢復過去時間點的統計量,進而生成過去的執行計劃。

 

 

--當前統計量

SQL> select owner, table_name, last_analyzed,num_rows from dba_tables where table_name in ('ORI_CITY','NEW_CITY','IRALINE','PARAM') and owner in ('NBSCMP','COMM','UX','COMMON');

 

OWNER           TABLE_NAME      LAST_ANALYZED               NUM_ROWS

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

COMM     IRALINE  2012/11/13 22:00:42             5109

COMM     NEW_CITY        2012/11/13 22:03:07            83678

UX            PARAM      2012/7/8 8:19:15               16787

COMMON          ORI_CITY         2012/7/8 13:01:20              30914

 

Executed in 0.062 seconds

 

 

恢復到昨天下午,我們只需要對IRALINENEW_CITY進行處理,因為其他物件統計量沒有變化。

 

 

SQL> exec dbms_stats.restore_table_stats('COMM',tabname => 'NEW_CITY',as_of_timestamp => to_timestamp('2012-11-13 00:00:00','yyyy-mm-dd hh24:mi:ss'));

 

PL/SQL procedure successfully completed

Executed in 1.482 seconds

 

SQL> exec dbms_stats.restore_table_stats('COMM',tabname => 'IRALINE',as_of_timestamp => to_timestamp('2012-11-13 00:00:00','yyyy-mm-dd hh24:mi:ss'));

 

PL/SQL procedure successfully completed

Executed in 0.218 seconds

 

 

SQL> select owner, table_name, last_analyzed,num_rows from dba_tables where table_name in ('ORI_CITY','NEW_CITY','IRALINE','PARAM') and owner in ('NBSCMP','COMM','UX','COMMON');

 

OWNER           TABLE_NAME      LAST_ANALYZED               NUM_ROWS

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

COMM     IRALINE  2012/11/12 22:02:08                0

COMM     NEW_CITY        2012/11/12 22:02:15            83678

UX            PARAM      2012/7/8 8:19:15               16787

COMMON          ORI_CITY         2012/7/8 13:01:20              30914

 

 

注意,我們此處看到了變化。在前一天下午,IRALINE資料表統計行數為0。那麼,生成的執行計劃執行效果如何呢?

 

 

SQL> alter system flush shared_pool;

System altered

 

Executed in 24.415 seconds

 

SQL>  select count(1)

(篇幅原因,省略部分……)

 13    from comm.iraline

 14   inner join UX.air on trim(air.air_code) = iraline.air_code

 15   inner join common.ori_city on air.air_city = ori_city.city_code

 16   inner join comm.new_city on trim(ori_city.city_desc) =

 17                                      upper(new_city.city_name)

 18    left join UX.param p1 on (air.air_code = p1.sspa_air_c and

 19                                    p1.sspa_param_c = 'RPT')

 20    left join UX.param p2 on (air.air_code = p2.sspa_air_c and

 21                                    p2.sspa_param_c = 'DTA')

 22   where (AIR.AIR_EFF_EN_DT >= to_date('2010-1-1 00:00:00',

 23                                       'yyyy-mm-dd

 24  hh24:mi:ss') or AIR.AIR_EFF_EN_DT is null)

 25  ;

 

select count(1) /*IRALINE.GLBAIRLINE_SEQ GLBAIRLINE_SEQ,

        AIR.AIR_1LOC_ADD ADDRESS1,                  AIR.AIR_2LOC_ADD

ADDRESS2,                  AIR.AIR_3LOC_ADD ADDRESS3,

NEW_CITY.CITY_SEQ CITY_SEQ,

decode(air.air_ia_da,'I','INTERNATIONAL','D','DOMESTIC','BOTH')

APPLICABLITY,                  AIR.AIR_EFF_ST_DT EFFECTIVE_FROM,

          AIR.AIR_EFF_EN_DT EFFECTIVE_TO,                  'ADD'

LAST_ACT_TYP,                  'MIGRATION' LAST_ACT_USER,

   sysdate LAST_ACT_DATE,                  new_city.State_Seq

STATE_SEQ,                  AIR.AIR_CTRY ISOC_CODE,

AIR.AIR_PCO_ZIP ZIP_CODE,                  AIR.AIR_NAME BSP_REG_NAME,

               air.air_code air_code*/

  from comm.iraline

 inner join UX.air on trim(air.air_code) = iraline.air_code

 inner join common.ori_city on air.air_city = ori_city.city_code

 inner join comm.new_city on trim(ori_city.city_desc) =

                                    upper(new_city.city_name)

  left join UX.param p1 on (air.air_code = p1.sspa_air_c and

                                  p1.sspa_param_c = 'RPT')

  left join UX.param p2 on (air.air_code = p2.sspa_air_c and

                                  p2.sspa_param_c = 'DTA')

 where (AIR.AIR_EFF_EN_DT >= to_date('2010-1-1 00:00:00',

                                     'yyyy-mm-dd

hh24:mi:ss') or AIR.AIR_EFF_EN_DT is null)

 

ORA-01652: 無法透過 128 (在表空間 TEMP ) 擴充套件 temp

ORA-27072: 檔案 I/O 錯誤

Additional information: 2

 

 

報錯,根本無法執行出結果。對應執行計劃和我們從AWR從抽取出的一致。

 

 

PLAN_TABLE_OUTPUT

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

Plan hash value: 3696956085

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

| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%

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

|   0 | SELECT STATEMENT              |                |     1 |   116 |   361

|   1 |  SORT AGGREGATE               |                |     1 |   116 |

|   2 |   NESTED LOOPS                |                |       |       |

|   3 |    NESTED LOOPS               |                |     1 |   116 |   361

|   4 |     NESTED LOOPS OUTER        |                |     1 |    77 |   360

|   5 |      NESTED LOOPS OUTER       |                |     1 |    55 |   359

|*  6 |       HASH JOIN               |                |     1 |    33 |   358

|   7 |        MERGE JOIN CARTESIAN   |                |     1 |    15 |   345

|   8 |         TABLE ACCESS FULL     | IRALINE |     1 |     4 |    35

|   9 |         BUFFER SORT           |                | 83678 |   898K|   310

|  10 |          TABLE ACCESS FULL    | NEW_CITY       | 83678 |   898K|   310

|* 11 |        TABLE ACCESS FULL      | AIR            |   139 |  2502 |    12

|* 12 |       INDEX RANGE SCAN        | PARAMI3   |     1 |    22 |     1

|* 13 |      INDEX RANGE SCAN         | PARAMI3   |     1 |    22 |     1

|* 14 |     INDEX UNIQUE SCAN         | ORI_CITYP1      |     1 |       |     0

|* 15 |    TABLE ACCESS BY INDEX ROWID| ORI_CITY        |     1 |    39 |     1

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

Predicate Information (identified by operation id):

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

   6 - access("IRALINE"."AIR_CODE"=TRIM("AIR"."AIR_CODE"))

  11 - filter("AIR"."AIR_EFF_EN_DT" IS NULL OR "AIR"."AIR_EFF_EN_DT">=TO_DATE('

              2010-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

  12 - access("AIR"."AIR_CODE"="P1"."SSPA_AIR_C"(+) AND

              "P1"."SSPA_PARAM_C"(+)='RPT')

       filter("P1"."SSPA_PARAM_C"(+)='RPT')

  13 - access("AIR"."AIR_CODE"="P2"."SSPA_AIR_C"(+) AND

              "P2"."SSPA_PARAM_C"(+)='DTA')

       filter("P2"."SSPA_PARAM_C"(+)='DTA')

  14 - access("AIR"."AIR_CITY"="ORI_CITY"."CITY_CODE")

  15 - filter(TRIM("ORI_CITY"."CITY_DESC")=UPPER("NEW_CITY"."CITY_NAME"))

 

37 rows selected

 

 

從執行計劃上,我們可以理解執行報錯的原因在於Merge Join操作。由於IRALINE資料表量的意外增大,引起PGAsort area使用過多,不足的空間使用Temp進行彌補。但是,Temp使用到一定程度後,檔案自我膨脹到極限報錯。由於統計量的原因,Oracle最佳化器覺得該表資料量很少才會選擇這樣的路徑。

 

5、恢復正常

 

重新收集統計量,執行計劃恢復正常。

 

 

SQL> exec dbms_stats.gather_table_stats('COMM','NEW_CITY',cascade => true);

 

PL/SQL procedure successfully completed

 

Executed in 5.881 seconds

 

SQL> exec dbms_stats.gather_table_stats('COMM','IRALINE',cascade => true);

 

PL/SQL procedure successfully completed

 

Executed in 0.671 seconds

 

SQL> select owner, table_name, last_analyzed,num_rows from dba_tables where table_name in ('ORI_CITY','NEW_CITY','IRALINE','PARAM') and owner in ('NBSCMP','COMM','UX','COMMON');

 

OWNER           TABLE_NAME      LAST_ANALYZED               NUM_ROWS

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

COMM     IRALINE  2012/11/14 17:05:01             5109

COMM     NEW_CITY        2012/11/14 17:04:50            83678

UX            PARAM      2012/7/8 8:19:15               16787

COMMON          ORI_CITY         2012/7/8 13:01:20              30914

 

Executed in 0.125 seconds

 

SQL> alter system flush shared_pool;

 

System altered

 

Executed in 0.156 seconds

 

 

 

SQL>  select count(1)

 13    from comm.iraline

 14   inner join UX.air on trim(air.air_code) = iraline.air_code

 15   inner join common.ori_city on air.air_city = ori_city.city_code

 16   inner join comm.new_city on trim(ori_city.city_desc) =

 17                                      upper(new_city.city_name)

 18    left join UX.param p1 on (air.air_code = p1.sspa_air_c and

 19                                    p1.sspa_param_c = 'RPT')

 20    left join UX.param p2 on (air.air_code = p2.sspa_air_c and

 21                                    p2.sspa_param_c = 'DTA')

 22   where (AIR.AIR_EFF_EN_DT >= to_date('2010-1-1 00:00:00',

 23                                       'yyyy-mm-dd

 24  hh24:mi:ss') or AIR.AIR_EFF_EN_DT is null)

 25  ;

 

COUNT(1)

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

                          2945

 

Executed in 0.343 seconds

 

 

執行計劃恢復正常。

 

6、結論

 

這個案例很有代表性。在CBO時代,統計量是執行計劃的生命。儘管Oracle 10引入了週期性自動統計量收集、Dynamic Sampling等特性來彌補統計量和實際資料的差異,但是這種差異始終存在。

 

對於我們運維管理人員來說,穩定執行計劃在於系統資料分佈穩定和統計量穩定。基本上這兩個因素穩定後,執行計劃穩定不是難事。Oracle也提出瞭如SPMSQL ProfileStatistic Pending等手段來實現對統計量變化的控制。

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17203031/viewspace-749444/,如需轉載,請註明出處,否則將追究法律責任。

相關文章