ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10205分区表global stats不精准导致big sql选择bad plan

10205分区表global stats不精准导致big sql选择bad plan

原创 Linux操作系统 作者:myownstars 时间:2013-09-06 17:17:54 0 删除 编辑

某生产库的siebel应用,版本10205
SELECT   obj.row_wid, obj.x_objective_name AS promo_name, fact.priority_wid,
         SUM (fact.x_cust_ind) AS x_actual
--SUM CALLS PER CUSTOMER
FROM     w_objective_f obj,
         w_act_prod_f fact,
         w_lov_d lov_sta,
         w_lov_d lov_typ,
         wc_prod_rank_aux,
         w_product_d,
         w_person_d,
         w_position_d
   WHERE obj.x_objective_name = 'Calls In Target'
     AND obj.x_sales_force_name = w_position_d.divn_name
     AND obj.obj_start_day_wid <= fact.period_day_wid
     AND obj.obj_end_day_wid >= fact.period_day_wid                    --MONTH
     AND obj.prod_wid = fact.prod_wid                                  --BRAND
     AND fact.prod_wid = w_product_d.row_wid                       -- brand --
     AND w_product_d.integration_id = wc_prod_rank_aux.prod_id
     AND w_person_d.integration_id = wc_prod_rank_aux.con_id
     AND obj.x_sales_cycle_wid = wc_prod_rank_aux.x_sales_cycle_wid
     AND fact.contact_wid = w_person_d.row_wid                   -- contact --
     AND fact.owner_postn_wid = w_position_d.row_wid                 -- rep --
     AND fact.x_act_sta_wid = lov_sta.row_wid
     AND lov_sta.NAME = 'Submitted'
     AND fact.act_type_wid = lov_typ.row_wid
     AND lov_typ.NAME LIKE '%Call%'
     AND w_position_d.divn_name <> 'NLD'
     AND w_person_d.con_active_flg = 'Y'
     AND obj.obj_start_day_wid >= 20130131
GROUP BY obj.row_wid, obj.x_objective_name, fact.priority_wid

客户抱怨跑了几个小时还没完。
登陆先查看现有计划
初看一下有点晕,先从带常量的谓词过滤看起,第20行明明实际返回850多万行,执行计划中却只显示26852;

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                          | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                   |                     |       |       |   216 (100)|          |       |       |        |      |            |
|   1 |  PX COORDINATOR                                    |                     |       |       |            |          |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                              | :TQ10003            |     1 |   205 |   216  (15)| 00:00:01 |       |       |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                                   |                     |     1 |   205 |   216  (15)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                                     |                     |     1 |   205 |   215  (14)| 00:00:01 |       |       |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH                                  | :TQ10002            |     1 |   205 |   215  (14)| 00:00:01 |       |       |  Q1,02 | P->P | HASH       |
|   6 |       BUFFER SORT                                  |                     |     1 |   205 |   216  (15)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|   7 |        NESTED LOOPS                                |                     |     1 |   205 |   215  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|   8 |         NESTED LOOPS                               |                     |     1 |   186 |   215  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|   9 |          NESTED LOOPS                              |                     |     3 |   489 |   215  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  10 |           NESTED LOOPS                             |                     |     7 |   980 |   215  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  11 |            NESTED LOOPS                            |                     |     7 |   868 |   215  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|* 12 |             HASH JOIN                              |                     |    56 |  5880 |   215  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  13 |              BUFFER SORT                           |                     |       |       |            |          |       |       |  Q1,02 | PCWC |            |
|  14 |               PX RECEIVE                           |                     | 27118 |  1244K|    52  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  15 |                PX SEND HASH                        | :TQ10000            | 27118 |  1244K|    52  (14)| 00:00:01 |       |       |        | S->P | HASH       |
|* 16 |                 HASH JOIN                          |                     | 27118 |  1244K|    52  (14)| 00:00:01 |       |       |        |      |            |
|* 17 |                  INDEX FULL SCAN                   | W_POSITION_D_M3     | 21901 |   213K|     1   (0)| 00:00:01 |       |       |        |      |            |
|  18 |                  PARTITION RANGE ITERATOR          |                     | 27118 |   979K|    45   (3)| 00:00:01 |    86 |    97 |        |      |            |
|  19 |                   TABLE ACCESS BY LOCAL INDEX ROWID| W_ACT_PROD_F        | 27118 |   979K|    45   (3)| 00:00:01 |    86 |    97 |        |      |            |
|* 20 |                    INDEX RANGE SCAN                | W_ACT_PROD_F_F9     |  3088 |       |     1   (0)| 00:00:01 |    86 |    97 |        |      |            |
|  21 |              PX RECEIVE                            |                     | 68968 |  3906K|   161  (14)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  22 |               PX SEND HASH                         | :TQ10001            | 68968 |  3906K|   161  (14)| 00:00:01 |       |       |  Q1,01 | P->P | HASH       |
|  23 |                PX PARTITION HASH SINGLE            |                     | 68968 |  3906K|   161  (14)| 00:00:01 |     2 |     2 |  Q1,01 | PCWC |            |
|* 24 |                 TABLE ACCESS BY LOCAL INDEX ROWID  | W_OBJECTIVE_F       | 68968 |  3906K|   161  (14)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 25 |                  INDEX RANGE SCAN                  | W_OBJECTIVE_F_U3    |  2136K|       |    11  (28)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
|* 26 |             INDEX RANGE SCAN                       | W_LOV_D_M2          |     1 |    19 |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|  27 |            TABLE ACCESS BY INDEX ROWID             | W_PRODUCT_D         |     1 |    16 |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|* 28 |             INDEX UNIQUE SCAN                      | W_PRODUCT_D_P1      |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|* 29 |           INDEX RANGE SCAN                         | WC_PROD_RANK_AUX_M1 |     1 |    23 |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|* 30 |          TABLE ACCESS BY INDEX ROWID               | W_PERSON_D          |     1 |    23 |     0   (0)|          |       |       |  Q1,02 | PCWP |            |
|* 31 |           INDEX RANGE SCAN                         | W_PERSON_D_M58      |     1 |       |     0   (0)|          |       |       |  Q1,02 | PCWP |            |
|* 32 |         TABLE ACCESS BY INDEX ROWID                | W_LOV_D             |     1 |    19 |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
|* 33 |          INDEX UNIQUE SCAN                         | W_LOV_D_PK          |     1 |       |     1   (0)| 00:00:01 |       |       |  Q1,02 | PCWP |            |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  12 - access("OBJ"."X_SALES_FORCE_NAME"="W_POSITION_D"."DIVN_NAME" AND "OBJ"."PROD_WID"="FACT"."PROD_WID")
       filter(("OBJ"."OBJ_START_DAY_WID"<="FACT"."PERIOD_DAY_WID" AND "OBJ"."OBJ_END_DAY_WID">="FACT"."PERIOD_DAY_WID"))
  16 - access("FACT"."OWNER_POSTN_WID"="W_POSITION_D"."ROW_WID")
  17 - filter("W_POSITION_D"."DIVN_NAME"<>'NLD')
  20 - access("FACT"."PERIOD_DAY_WID">=20130130)
  24 - filter(("OBJ"."X_OBJECTIVE_NAME"='Calls In Target' AND "OBJ"."OBJ_START_DAY_WID">=20130130 AND "OBJ"."X_SALES_FORCE_NAME"<>'NLD'))
  25 - access("OBJ"."OBJ_END_DAY_WID">=20130130)
  26 - access("LOV_STA"."NAME"='Submitted' AND "FACT"."X_ACT_STA_WID"="LOV_STA"."ROW_WID")
  28 - access("FACT"."PROD_WID"="W_PRODUCT_D"."ROW_WID")
  29 - access("OBJ"."X_SALES_CYCLE_WID"="WC_PROD_RANK_AUX"."X_SALES_CYCLE_WID" AND "W_PRODUCT_D"."INTEGRATION_ID"="WC_PROD_RANK_AUX"."PROD_ID")
       filter("W_PRODUCT_D"."INTEGRATION_ID"="WC_PROD_RANK_AUX"."PROD_ID")
  30 - filter(("W_PERSON_D"."CON_ACTIVE_FLG"='Y' AND "FACT"."CONTACT_WID"="W_PERSON_D"."ROW_WID"))
  31 - access("W_PERSON_D"."INTEGRATION_ID"="WC_PROD_RANK_AUX"."CON_ID")
  32 - filter("LOV_TYP"."NAME" LIKE '%Call%')
  33 - access("FACT"."ACT_TYPE_WID"="LOV_TYP"."ROW_WID")


该表按月分区,字段PERIOD_DAY_WID建有本地分区索引

SQL> select count(*) from siebel.W_ACT_PROD_F t where t.PERIOD_DAY_WID>=20130130;
  COUNT(*)
----------
   8544334

SQL> explain plan for select /*+ index(t,W_ACT_PROD_F_F9) */ count(*) from siebel.W_ACT_PROD_F partition (ACTPROD_P201302) t where t.PERIOD_DAY_WID>=20130130;
Explained.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2890614487
-----------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |     1 |     6 |    14  (22)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE         |                 |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE SINGLE|                 |  1403K|  8224K|    14  (22)| 00:00:01 |    87 |    87 |
|*  3 |    INDEX RANGE SCAN     | W_ACT_PROD_F_F9 |  1403K|  8224K|    14  (22)| 00:00:01 |    87 |    87 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."PERIOD_DAY_WID">=20130130)
15 rows selected.

SQL> explain plan for select count(*) from siebel.W_ACT_PROD_F t where t.PERIOD_DAY_WID>=20130130;
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3837642358
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                 |     1 |     6 |     1   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE           |                 |     1 |     6 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|                 | 27118 |   158K|     1   (0)| 00:00:01 |    86 |    97 |
|*  3 |    INDEX RANGE SCAN       | W_ACT_PROD_F_F9 | 27118 |   158K|     1   (0)| 00:00:01 |    86 |    97 |
-------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T"."PERIOD_DAY_WID">=20130130)
15 rows selected.


10053跟踪一下第2个sql
Table Stats::
  Table: W_ACT_PROD_F  Alias: W_ACT_PROD_F  (Using composite stats)
  (making adjustments for partition skews)
  ORIGINAL VALUES::    #Rows: 82284683  #Blks:  800304  AvgRowLen:  314.00
  PARTITIONS::
  PRUNED: 12
  ANALYZED: 12  UNANALYZED: 0
    #Rows: 82284683  #Blks:  91124  AvgRowLen:  314.00
Index Stats::
...
  Index: W_ACT_PROD_F_F9  Col#: 13
    USING COMPOSITE STATS
    LVLS: 1  #LB: 61800  #DK: 3638  LB/K: 16.00  DB/K: 3631.00  CLUF: 13210700.00
...
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#13): PERIOD_DAY_WID(NUMBER)
    AvgLen: 6.00 NDV: 3638 Nulls: 0 Density: 2.7488e-04 Min: 0 Max: 20131231
  Table: W_ACT_PROD_F  Alias: W_ACT_PROD_F    
    Card: Original: 82284683  Rounded: 27118  Computed: 27118.35  Non Adjusted: 27118.35  --特别不理解,为什么card一rounded就缩水成27118了??
  -----------------------------------------

查看统计信息,global stats没有直方图,但每个分区都有;
SQL> select NUM_BUCKETS,DENSITY from DBA_tab_columns where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID';

NUM_BUCKETS    DENSITY
----------- ----------
          1 .000274876
SQL> select NUM_BUCKETS,PARTITION_NAME,DENSITY from DBA_PART_COL_STATISTICS where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID' and PARTITION_NAME='ACTPROD_P201301';

NUM_BUCKETS PARTITION_NAME                    DENSITY
----------- ------------------------------ ----------
         31 ACTPROD_P201301                4.1336E-07

SQL> select NUM_BUCKETS,PARTITION_NAME,DENSITY from DBA_PART_COL_STATISTICS where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID' and PARTITION_NAME='&1';
Enter value for 1: ACTPROD_P201302
old   1: select NUM_BUCKETS,PARTITION_NAME,DENSITY from DBA_PART_COL_STATISTICS where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID' and PARTITION_NAME='&1'
new   1: select NUM_BUCKETS,PARTITION_NAME,DENSITY from DBA_PART_COL_STATISTICS where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID' and PARTITION_NAME='ACTPROD_P201302'

NUM_BUCKETS PARTITION_NAME                    DENSITY
----------- ------------------------------ ----------
         28 ACTPROD_P201302                3.5591E-07

现在怀疑是global stats缺失直方图所致,收集直方图;
SQL> select NUM_BUCKETS,DENSITY,sample_size,last_analyzed from DBA_tab_columns where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID';
NUM_BUCKETS    DENSITY SAMPLE_SIZE LAST_ANALYZED
----------- ---------- ----------- -------------------
        254 .000915751        3494 2013-08-30 11:03:33

执行计划变为
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                       | Name                | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                |                     |     1 |   205 |  4081  (10)|       |       |        |      |            |
|   1 |  PX COORDINATOR                                 |                     |       |       |            |       |       |        |      |            |
|   2 |   PX SEND QC (RANDOM)                           | :TQ10003            |     1 |   205 |  4081  (10)|       |       |  Q1,03 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY                                |                     |     1 |   205 |  4081  (10)|       |       |  Q1,03 | PCWP |            |
|   4 |     PX RECEIVE                                  |                     |     1 |   205 |  4079  (10)|       |       |  Q1,03 | PCWP |            |
|   5 |      PX SEND HASH                               | :TQ10002            |     1 |   205 |  4079  (10)|       |       |  Q1,02 | P->P | HASH       |
|   6 |       NESTED LOOPS                              |                     |     1 |   205 |  4079  (10)|       |       |  Q1,02 | PCWP |            |
|   7 |        NESTED LOOPS                             |                     |     1 |   186 |  4079  (10)|       |       |  Q1,02 | PCWP |            |
|   8 |         NESTED LOOPS                            |                     |    13 |  2210 |  4079  (10)|       |       |  Q1,02 | PCWP |            |
|   9 |          NESTED LOOPS                           |                     |  2480 |   356K|  4075  (10)|       |       |  Q1,02 | PCWP |            |
|* 10 |           HASH JOIN                             |                     |  2480 |   300K|  4072  (10)|       |       |  Q1,02 | PCWP |            |
|  11 |            PX RECEIVE                           |                     | 68968 |  3906K|   161  (14)|       |       |  Q1,02 | PCWP |            |
|  12 |             PX SEND HASH                        | :TQ10001            | 68968 |  3906K|   161  (14)|       |       |  Q1,01 | P->P | HASH       |
|  13 |              PX PARTITION HASH SINGLE           |                     | 68968 |  3906K|   161  (14)|     2 |     2 |  Q1,01 | PCWC |            |
|* 14 |               TABLE ACCESS BY LOCAL INDEX ROWID | W_OBJECTIVE_F       | 68968 |  3906K|   161  (14)|       |       |  Q1,01 | PCWP |            |
|* 15 |                INDEX RANGE SCAN                 | W_OBJECTIVE_F_U3    |  2136K|       |    11  (28)|       |       |  Q1,01 | PCWP |            |
|  16 |            BUFFER SORT                          |                     |       |       |            |       |       |  Q1,02 | PCWC |            |
|  17 |             PX RECEIVE                          |                     |  1210K|    76M|  3900  (10)|       |       |  Q1,02 | PCWP |            |
|  18 |              PX SEND HASH                       | :TQ10000            |  1210K|    76M|  3900  (10)|       |       |        | S->P | HASH       |
|* 19 |               HASH JOIN                         |                     |  1210K|    76M|  3900  (10)|       |       |        |      |            |
|* 20 |                INDEX FULL SCAN                  | W_POSITION_D_M3     | 21901 |   213K|     1   (0)|       |       |        |      |            |
|* 21 |                TABLE ACCESS BY LOCAL INDEX ROWID| W_ACT_PROD_F        |   755K|    26M|  1890   (7)|     1 |     1 |        |      |            |
|  22 |                 NESTED LOOPS                    |                     |  1210K|    64M|  3781   (7)|       |       |        |      |            |
|* 23 |                  INDEX RANGE SCAN               | W_LOV_D_M2          |     2 |    38 |     1   (0)|       |       |        |      |            |
|  24 |                  PARTITION RANGE ITERATOR       |                     |  6860K|       |    75  (19)|    86 |    97 |        |      |            |
|* 25 |                   INDEX RANGE SCAN              | W_ACT_PROD_F_XF13   |  6860K|       |    75  (19)|    86 |    97 |        |      |            |
|* 26 |           TABLE ACCESS BY INDEX ROWID           | W_PERSON_D          |     1 |    23 |     1   (0)|       |       |  Q1,02 | PCWP |            |
|* 27 |            INDEX UNIQUE SCAN                    | W_PERSON_D_P1       |     1 |       |     1   (0)|       |       |  Q1,02 | PCWP |            |
|* 28 |          INDEX RANGE SCAN                       | WC_PROD_RANK_AUX_M1 |     1 |    23 |     1   (0)|       |       |  Q1,02 | PCWP |            |
|* 29 |         TABLE ACCESS BY INDEX ROWID             | W_PRODUCT_D         |     1 |    16 |     1   (0)|       |       |  Q1,02 | PCWP |            |
|* 30 |          INDEX UNIQUE SCAN                      | W_PRODUCT_D_P1      |     1 |       |     1   (0)|       |       |  Q1,02 | PCWP |            |
|* 31 |        TABLE ACCESS BY INDEX ROWID              | W_LOV_D             |     1 |    19 |     1   (0)|       |       |  Q1,02 | PCWP |            |
|* 32 |         INDEX UNIQUE SCAN                       | W_LOV_D_PK          |     1 |       |     1   (0)|       |       |  Q1,02 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
  10 - access("OBJ"."X_SALES_FORCE_NAME"="W_POSITION_D"."DIVN_NAME" AND "OBJ"."PROD_WID"="FACT"."PROD_WID")
       filter("OBJ"."OBJ_START_DAY_WID"<="FACT"."PERIOD_DAY_WID" AND "OBJ"."OBJ_END_DAY_WID">="FACT"."PERIOD_DAY_WID")
  14 - filter("OBJ"."X_OBJECTIVE_NAME"='Calls In Target' AND "OBJ"."OBJ_START_DAY_WID">=20130130 AND "OBJ"."X_SALES_FORCE_NAME"<>'NLD')
  15 - access("OBJ"."OBJ_END_DAY_WID">=20130130)
  19 - access("FACT"."OWNER_POSTN_WID"="W_POSITION_D"."ROW_WID")
  20 - filter("W_POSITION_D"."DIVN_NAME"<>'NLD')
  21 - filter("FACT"."PERIOD_DAY_WID">=20130130)
  23 - access("LOV_STA"."NAME"='Submitted')
  25 - access("FACT"."X_ACT_STA_WID"="LOV_STA"."ROW_WID")
  26 - filter("W_PERSON_D"."CON_ACTIVE_FLG"='Y')
  27 - access("FACT"."CONTACT_WID"="W_PERSON_D"."ROW_WID")
  28 - access("OBJ"."X_SALES_CYCLE_WID"="WC_PROD_RANK_AUX"."X_SALES_CYCLE_WID" AND "W_PERSON_D"."INTEGRATION_ID"="WC_PROD_RANK_AUX"."CON_ID")
  29 - filter("W_PRODUCT_D"."INTEGRATION_ID"="WC_PROD_RANK_AUX"."PROD_ID")
  30 - access("FACT"."PROD_WID"="W_PRODUCT_D"."ROW_WID")
  31 - filter("LOV_TYP"."NAME" LIKE '%Call%')
  32 - access("FACT"."ACT_TYPE_WID"="LOV_TYP"."ROW_WID")
 
W_ACT_PROD_F返回的评估行数比较精确了,sql在几分钟之内执行完毕,buffer gets从原来的11位将为8位;


经anlinew证实,此乃10204一bug,原贴地址可参考,http://www.itpub.net/thread-1813319-1-1.html

[Bug 8971829: WRONG GLOBAL INDEX SELECTIVITY/COST ON SMALL PARTITIONS WITH FILTER ON PART KEY        
________________________________________
Bug Attributes
________________________________________
Type        B - Defect        Fixed in Product Version        -
Severity        2 - Severe Loss of Service        Product Version        10.2.0.4
Status        11 - Code Bug (Response/Resolution)        Platform        212 - IBM AIX on POWER Systems (64-bit)
Created        29-Sep-2009        Platform. Version        5.3
Updated        21-Oct-2009        Base Bug        -
Database Version        10.2.0.4               
Affects Platforms        Generic               
Product Source        Oracle               
Related Products
________________________________________
Line        Oracle Database Products        Family        Oracle Database
Area        Oracle Database        Product        5 - Oracle Server - Enterprise Edition
Hdr: 8971829 10.2.0.4 RDBMS 10.2.0.4 QRY OPTIMIZER PRODID-5 PORTID-212
Abstract: WRONG GLOBAL INDEX SELECTIVITY/COST ON SMALL PARTITIONS WITH FILTER ON PART KEY

*** 09/29/09 07:24 am ***

PROBLEM:
--------
For small partitions, the computed selectivity and cost of global index
access path are wrong - when a filter on partitioning key is present.


This causes CBO to choose a full scan of an unsuitable index over a very
    selective range scan of the correct index which results in performance
degradation.

Consider the following simplified example:

create table t(cl varchar2(8), r  integer)
    partition by list(cl) (
       partition big values('big'),
       partition small values ('small'),
       partition empty values (default)
    );

insert /*+ append */ into t(cl,r)
    select case when level between 1 and 4 then 'small' else 'big' end,
             dbms_random.value(1,9000)
    from dual connect by level<=10000;

commit;

create index i_tr on t(r);
   
begin dbms_stats.gather_table_stats(user,'T', cascade=>true,
granularity=>'all',
estimate_percent=>100); end;
/

select count(*) from T where cl='big';
   
      COUNT(*)
    ----------
          9996

select count(*) from T where cl='small';
   
      COUNT(*)
    ----------
             4

BAD case
========
select /*+index(t)*/ * from t where cl='small' and r=1452;
   
    CL                R
    -------- ----------
    small          1452

------------------------------------------------+---------------------------+
| Operation                           | Name    | Rows  |Cost  |Pstart|Pstop|
------------------------------------------------+---------------------------+
| SELECT STATEMENT                    |         |       | 2364 |      |     |
|  TABLE ACCESS BY GLOBAL INDEX ROWID | T       |     1 | 2364 |2     |2    |
|   INDEX RANGE SCAN                  | I_TR    |  2500 |    7        |     |
------------------------------------------------+---------------------------+
Predicate Information:
----------------------
1 - filter("CL"='small')
2 - access("R"=1452)

GOOD case
=========
select /*+index(t)*/ * from t where  r=1452;
   
    CL                R
    -------- ----------
    small          1452
   
-----------------------------------------------+----------------------------+
|Operation                           | Name    | Rows  | Cost  |Pstart|Pstop|
-----------------------------------------------+----------------------------+
|SELECT STATEMENT                    |         |       |     3 |      |     |
| TABLE ACCESS BY GLOBAL INDEX ROWID | T       |     2 |     3 |ROW LOCATION|
|  INDEX RANGE SCAN                  | I_TR    |     2 |     1 |      |     |
-----------------------------------------------------+----------------------+
Predicate Information:
----------------------
2 - access("R"=1452)

DIAGNOSTIC ANALYSIS:
--------------------
BAD Case
==========
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#1): CL(VARCHAR2)  Part#: 1
    AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 1
  Column (#1): CL(VARCHAR2)
    AvgLen: 6.00 NDV: 1 Nulls: 0 Density: 1
  Column (#2): R(NUMBER)  Part#: 1
    AvgLen: 4.00 NDV: 4 Nulls: 0 Density: 0.25 Min: 1452 Max: 8571
  Column (#2): R(NUMBER)
    AvgLen: 4.00 NDV: 4 Nulls: 0 Density: 0.25 Min: 1452 Max: 8571
  Table:  T  Alias: T    
    Card: Original: 4  Rounded: 1  Computed: 1.00  Non Adjusted: 1.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: index (AllEqRange)
    Index: I_TR
    resc_io: 2363.00  resc_cpu: 11702700
    ix_sel: 0.25  ix_sel_with_filters: 0.25
    Cost: 2363.79  Resp: 2363.79  Degree: 1
  Best:: AccessPath: IndexRange  Index: I_TR
         Cost: 2363.79  Degree: 1  Resp: 2363.79  Card: 1.00  Bytes: 0


GOOD case
==========
***************************************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Column (#2): R(NUMBER)
    AvgLen: 4.00 NDV: 6087 Nulls: 0 Density: 1.6428e-04 Min: 3 Max: 8999
  Table:  T  Alias: T    
    Card: Original: 10000  Rounded: 2  Computed: 1.64  Non Adjusted: 1.64
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: index (AllEqRange)
    Index: I_TR
    resc_io: 3.00  resc_cpu: 22954
    ix_sel: 1.6428e-04  ix_sel_with_filters: 1.6428e-04
    Cost: 3.00  Resp: 3.00  Degree: 1
  Best:: AccessPath: IndexRange  Index: I_TR
         Cost: 3.00  Degree: 1  Resp: 3.00  Card: 1.64  Bytes: 0

WORKAROUND:
-----------
set optimizer_features_enable='9.2.0'
or
delete statistics on base table and use dynamic sampling

RELATED BUGS:
-------------

REPRODUCIBILITY:
----------------
reproduced in-house on 10.2.0.4 and 11.1.0.7

TEST CASE:
----------
also available in testcase.sql

STACK TRACE:
------------

SUPPORTING INFORMATION:
-----------------------

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
----------------------------------------

DIAL-IN INFORMATION:
--------------------

IMPACT DATE:
------------

*** 09/29/09 07:30 am *** (CHG: Sta->16)
*** 09/29/09 07:30 am ***
*** 09/29/09 07:46 am *** (CHG: Customer-> DAIMLER AG)
*** 09/30/09 12:05 pm *** (ADD: Impact/Symptom->QUERY PERFORMANCE )
*** 09/30/09 12:05 pm *** (CHG: Support Contact->DSARGU->XMA)
*** 09/30/09 12:05 pm *** (CHG: Asg->NEW OWNER OWNER)
*** 10/01/09 02:28 pm *** (CHG: Sta->10)
*** 10/01/09 02:28 pm ***
*** 10/02/09 01:52 am *** (CHG: Sta->16)
*** 10/02/09 01:52 am ***
*** 10/06/09 05:09 pm ***
*** 10/06/09 05:14 pm *** (CHG: Sta->11)
*** 10/06/09 05:14 pm ***
*** 10/06/09 05:15 pm *** (CHG: Asg->NEW OWNER OWNER)
*** 10/06/09 05:15 pm ***
*** 10/14/09 10:21 pm *** (CHG: DevPri->2)
*** 10/14/09 10:21 pm *** (CHG: Confirmed Flag->Y)
*** 10/14/09 10:21 pm *** (CHG: Asg->NEW OWNER OWNER)
*** 10/14/09 10:21 pm ***
*** 10/14/09 10:21 pm *** (CHG: Asg->NEW OWNER OWNER)
*** 10/21/09 02:52 am *** (CHG: Asg->NEW OWNER OWNER)


 

 

 


 

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

上一篇: innodb事务锁小结
请登录后发表评论 登录
全部评论

注册时间:2010-03-18

  • 博文量
    375
  • 访问量
    3086021