ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 由于统计量失真造成SQL执行计划错误一例

由于统计量失真造成SQL执行计划错误一例

原创 Linux操作系统 作者:realkid4 时间:2012-11-18 21:46:22 0 删除 编辑

 

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7814733