10205分割槽表global stats不精準導致big sql選擇bad plan
某生產庫的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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- ddl 導致分割槽表全域性索引unusable索引
- Oracle 是分割槽表，但條件不帶分割槽條件的SQLOracleSQL
- 資料庫分割槽表分割槽未分配導致的一些問題資料庫
- sql中使用函式導致explain plan for和set autotrace得到執行計劃不準確SQL函式AI
- SQL Server表分割槽SQLServer
- SQL server 分割槽表SQLServer
- 分割槽表的bitmap索引不能是global的索引
- 分割槽表全域性索引與本地索引的選擇索引
- SQL SERVER之分割槽表SQLServer
- Linux精講——分割槽表fstabLinux
- split分割槽操作導致的librarycachelock
- 操作分割槽表對global和local索引的影響索引
- Sql Server系列：分割槽表操作SQLServer
- SQL Server表分割槽詳解SQLServer
- Oracle資料庫分割槽表SPLIT操作導致歸檔瘋漲Oracle資料庫
- oracle分割槽表的常規操作導致對索引的影響Oracle索引
- Oracle分割槽表全域性索引新增分割槽時不會失效Oracle索引
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- oracle實用sql(14)--查詢分割槽表的分割槽列和子分割槽列OracleSQL
- Oracle SQL調優之分割槽表OracleSQL
- sql server 小記——分割槽表（上）SQLServer
- SQL Server表分割槽操作詳解SQLServer
- 關於SQL Server的分割槽表SQLServer
- oracle分割槽表和分割槽表exchangeOracle
- 全面學習分割槽表及分割槽索引(13)--分隔表分割槽索引
- oracle分割槽表和非分割槽表exchangeOracle
- Oracle分割槽表及分割槽索引Oracle索引
- INTERVAL分割槽表鎖分割槽操作
- SQL Server 表分割槽注意事項HXSQLServer
- SQL SERVER 2005表分割槽功能SQLServer
- 裝win10分割槽時選擇什麼格式_win10系統安裝分割槽格式的選擇方法Win10
- 全面學習分割槽表及分割槽索引(9)--刪除表分割槽索引
- 全面學習分割槽表及分割槽索引(11)--合併表分割槽索引
- 全面學習分割槽表及分割槽索引(12)--修改list表分割槽索引
- 學習筆記】分割槽表和分割槽索引——新增表分割槽（二）筆記索引
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件
- 如何查詢分割槽表的分割槽及子分割槽
- 使用split對分割槽表再分割槽