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

myownstars · 发表于 2013-8-31 18:36

本帖最后由 myownstars 于 2013-8-31 18:52 编辑

删除该列的histogram
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
SQL> exec dbms_stats.DELETE_COLUMN_STATS('SIEBEL','W_ACT_PROD_F','PERIOD_DAY_WID');
PL/SQL procedure successfully completed.
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
----------- ---------- ----------- -------------------

SQL> exec dbms_stats.gather_table_stats('SIEBEL','W_ACT_PROD_F',cascade=>true, degree=>8);

PL/SQL procedure successfully completed.
SQL> 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
----------- ---------- ----------- -------------------
      1 .000620732       2575 2013-08-31 12:30:48
重新生成执行计划，是不是跟原来的bad sql非常接近，但奇怪的是其逻辑读仍旧只是8位数，没有原先的bad plan这么夸张

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name             | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop | TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                                  |                   |    1 | 205 | 262  (14)|    |    |       |    |          |
| 1 |  PX COORDINATOR                                  |                   |    |    |          |    |    |       |    |          |
| 2 | PX SEND QC (RANDOM)                            | :TQ10003          |    1 | 205 | 262  (14)|    |    |  Q1,03 | P->S | QC (RAND)  |
| 3 | HASH GROUP BY                                  |                   |    1 | 205 | 262  (14)|    |    |  Q1,03 | PCWP |          |
| 4 |    PX RECEIVE                                  |                   |    1 | 205 | 260  (14)|    |    |  Q1,03 | PCWP |          |
| 5 |    PX SEND HASH                               | :TQ10002          |    1 | 205 | 260  (14)|    |    |  Q1,02 | P->P | HASH    |
| 6 |    BUFFER SORT                               |                   |    1 | 205 | 262  (14)|    |    |  Q1,02 | PCWP |          |
| 7 |       NESTED LOOPS                               |                   |    1 | 205 | 260  (14)|    |    |  Q1,02 | PCWP |          |
| 8 |       NESTED LOOPS                            |                   |    1 | 189 | 260  (14)|    |    |  Q1,02 | PCWP |          |
| 9 |       NESTED LOOPS                            |                   |    1 | 170 | 260  (14)|    |    |  Q1,02 | PCWP |          |
|  10 |          NESTED LOOPS                            |                   | 30 |  4410 | 260  (14)|    |    |  Q1,02 | PCWP |          |
|  11 |          NESTED LOOPS                         |                   | 30 |  3720 | 260  (14)|    |    |  Q1,02 | PCWP |          |
|* 12 |          HASH JOIN                            |                   | 114 | 11970 | 260  (14)|    |    |  Q1,02 | PCWP |          |
|  13 |             BUFFER SORT                         |                   |    |    |          |    |    |  Q1,02 | PCWC |          |
|  14 |             PX RECEIVE                         |                   | 54304 |  2492K| 97  (12)|    |    |  Q1,02 | PCWP |          |
|  15 |             PX SEND HASH                      | :TQ10000          | 54304 |  2492K| 97  (12)|    |    |       | S->P | HASH    |
|* 16 |                HASH JOIN                         |                   | 54304 |  2492K| 97  (12)|    |    |       |    |          |
|* 17 |                INDEX FULL SCAN                | W_POSITION_D_M3    | 21901 | 213K|    1 (0)|    |    |       |    |          |
|  18 |                PARTITION RANGE ITERATOR       |                   | 54304 |  1962K| 87 (3)| 86 | 97 |       |    |          |
|  19 |                TABLE ACCESS BY LOCAL INDEX ROWID| W_ACT_PROD_F       | 54304 |  1962K| 87 (3)| 86 | 97 |       |    |          |
|* 20 |                   INDEX RANGE SCAN             | W_ACT_PROD_F_F9    |  6214 |    |    1 (0)| 86 | 97 |       |    |          |
|  21 |             PX RECEIVE                         |                   | 68968 |  3906K| 161  (14)|    |    |  Q1,02 | PCWP |          |
|  22 |             PX SEND HASH                      | :TQ10001          | 68968 |  3906K| 161  (14)|    |    |  Q1,01 | P->P | HASH    |
|  23 |             PX PARTITION HASH SINGLE          |                   | 68968 |  3906K| 161  (14)|    2 |    2 |  Q1,01 | PCWC |          |
|* 24 |                TABLE ACCESS BY LOCAL INDEX ROWID  | W_OBJECTIVE_F    | 68968 |  3906K| 161  (14)|    |    |  Q1,01 | PCWP |          |
|* 25 |                INDEX RANGE SCAN                | W_OBJECTIVE_F_U3 |  2136K|    | 11  (28)|    |    |  Q1,01 | PCWP |          |
|* 26 |          INDEX RANGE SCAN                      | W_LOV_D_M2       |    1 | 19 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 27 |          TABLE ACCESS BY INDEX ROWID          | W_PERSON_D       |    1 | 23 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 28 |          INDEX UNIQUE SCAN                   | W_PERSON_D_P1    |    1 |    |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 29 |          INDEX RANGE SCAN                      | WC_PROD_RANK_AUX_M1 |    1 | 23 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 30 |       TABLE ACCESS BY INDEX ROWID             | W_LOV_D          |    1 | 19 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 31 |          INDEX UNIQUE SCAN                      | W_LOV_D_PK       |    1 |    |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 32 |       TABLE ACCESS BY INDEX ROWID             | W_PRODUCT_D       |    1 | 16 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 33 |       INDEX UNIQUE SCAN                      | W_PRODUCT_D_P1    |    1 |    |    1 (0)|    |    |  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")
  27 - filter("W_PERSON_D"."CON_ACTIVE_FLG"='Y')
  28 - access("FACT"."CONTACT_WID"="W_PERSON_D"."ROW_WID")
  29 - 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")
  30 - filter("LOV_TYP"."NAME" LIKE '%Call%')
  31 - access("FACT"."ACT_TYPE_WID"="LOV_TYP"."ROW_WID")
  32 - filter("W_PRODUCT_D"."INTEGRATION_ID"="WC_PROD_RANK_AUX"."PROD_ID")
  33 - access("FACT"."PROD_WID"="W_PRODUCT_D"."ROW_WID")
Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
      432  recursive calls
      6  db block gets
37800769  consistent gets
   267844  physical reads
      628  redo size
   48710  bytes sent via SQL*Net to client
   1724  bytes received via SQL*Net from client
      114  SQL*Net roundtrips to/from client
      134  sorts (memory)
      1  sorts (disk)
   1693  rows processed

myownstars · 发表于 2013-8-31 18:58

这是该sql的历史数据

Snap Id	Snap Time	Sessions	Cursors/Session
Begin Snap:	37161	30-Aug-13 03:00:55	58	2.5
End Snap:	37173	30-Aug-13 15:00:39	49	2.9
Elapsed:		719.73 (mins)
DB Time:		10,607.83 (mins)

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
15,609,197,947	2	7,804,598,973.50	26.51	134883.91	185928.75	1ubusf0k9g6b8	pmdtm.exe	SELECT OBJ.ROW_WID, OBJ.X_OBJE...

CPU Time (s)	Elapsed Time (s)	Executions	CPU per Exec (s)	% Total	% Total DB Time	SQL Id	SQL Module	SQL Text
134,884	185,929	2	67441.96	79.85	29.21	1ubusf0k9g6b8	pmdtm.exe	SELECT OBJ.ROW_WID, OBJ.X_OBJE...

SQL> select * from table(dbms_xplan.display_awr('1ubusf0k9g6b8'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1ubusf0k9g6b8
--------------------
--SUM CALLS PER CUSTOMERND) AS X_ACTUALME,
      W_PRODUCT_D,_AUX,,
AND OBJ.X_SALES_FORCE_NAME = W_POSITION_D.DIVN_NAME
AND OBJ.PROD_WID = FACT.PROD_WID --BRANDAY_WID --MONTH
AND W_PERSON_D.INTEGRATION_ID == WC_PROD_RANK_AUX.PROD_ID
AND FACT.CONTACT_WID = W_PERSON_D.ROW_WID -- contact -- WID
AND LOV_STA.NAME = 'Submitted'TA.ROW_WIDWID -- rep --
AND W_PERSON_D.CON_ACTIVE_FLG = 'Y'
  GROUP BY OBJ.ROW_WID, OBJ.X_OBJECTIVE_NAME, FACT.PRIORITY_WID
Plan hash value: 3470813107
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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 |          |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> select SNAP_ID,PLAN_HASH_VALUE,EXECUTIONS_TOTAL,EXECUTIONS_DELTA,BUFFER_GETS_TOTAl,BUFFER_GETS_DELTA ,ELAPSED_TIME_TOTAL,ELAPSED_TIME_DELTA from dba_hist_sqlstat where snap_id between 37150 and 37180 and sql_id='1ubusf0k9g6b8';
SNAP_ID PLAN_HASH_VALUE EXECUTIONS_TOTAL EXECUTIONS_DELTA BUFFER_GETS_TOTAL BUFFER_GETS_DELTA ELAPSED_TIME_TOTAL ELAPSED_TIME_DELTA
---------- --------------- ---------------- ---------------- ----------------- ----------------- ------------------ ------------------
   37163    3470813107             1             1       3571760058       3571760058       5.2789E+10       5.2789E+10
   37164    3470813107             1             0       899865851       1623073089       8.7347E+10       3.4558E+10
   37165    3470813107             1             0       426366972       3821468417       1.1414E+11       2.6792E+10
   37166    3470813107             1             0       3160291359       2733924387       1.3577E+11       2.1626E+10
   37167    3470813107             1             0       861858535       1996534472       1.5494E+11       1.9174E+10
   37168    3470813107             1             0       2523797757       1661939222       1.7272E+11       1.7782E+10
   37169    3470813107             1             0       2697965859       174168102       1.8553E+11       1.2808E+10
   37172    2672969965             1             1       26330200       26330200       398873347       398873347
8 rows selected.

Yong Huang · 发表于 2013-9-4 03:57

> 分区表的global stats中的bucket影响了列的选择率，最终左右执行计划中rows的评估数量？

Thanks for your feedback. I think you're saying you gathered table global level stats with histograms (i.e. buckets is no longer 1). And that solved the problem? Correct?

Your initial trace file actually shows that for SQL 1, you do have a histogram:

Histogram: Freq #Bkts: 28 UncompBkts: 421456 EndPtVals: 28

But for SQL 2, that line is not shown. I guess CBO ignores this histogram for SQL 2. So, instead of saying you created the histogram for the column to solve the problem, it's really that you re-created the histogram (changed from a 28-bucket frequency histogram to a 254-bucket histogram) and that solved the problem. Correct?

I wish you had checked global_stats column of dba_tab_columns before you made the change. The value in it tells us whether the column stats are collected for the table as a whole or summed on all partitions.

myownstars · 发表于 2013-9-4 09:45

>I guess CBO ignores this histogram for SQL 2
最开始每个分区都有frequency histogram(DBA_PART_COL_STATISTICS )，因为该字段PERIOD_DAY_WID记录格式为yyyymmdd，即按天记录而分区按月；而DBA_tab_columns 中histogram=1；重新收集的是DBA_tab_columns中的histogram
>I wish you had checked global_stats column of dba_tab_columns before you made the change
这个刚开始还真没留意，多谢提醒

Yong Huang · 发表于 2013-9-5 00:12

So it looks like when you try to access more than one partition, in Oracle 10g, if you don't have a table level histogram for a partitioned table, CBO may make a bad choice (calculating the index selectivity to be a very small number and giving a smaller cost than it should). Once the table level histogram is in place, cost is calculated more reasonably.

11g probably will correct the problem. In 11g, partition level stats are aggregated to form table level stats (except NDV, number of distinct values).

anlinew · 发表于 2013-9-5 10:35

[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)

[ 本帖最后由 anlinew 于 2009-11-26 17:35 编辑 ]

myownstars · 发表于 2013-9-5 15:41

问题今天又重现了
SQL> select num_buckets,density,GLOBAL_STATS,LAST_ANALYZED from DBA_tab_columns where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID';
NUM_BUCKETS DENSITY GLO LAST_ANALYZED
----------- ---------- --- -------------------
1 .000274574 YES 2013-09-04 18:31:25

Buffer Gets	Executions	Gets per Exec	%Total	CPU Time (s)	Elapsed Time (s)	SQL Id	SQL Module	SQL Text
14,386,364,372	2	7,193,182,186.00	27.24	125086.27	178513.42	0b92v2g8cwp82	pmdtm.exe	SELECT OBJ.ROW_WID, OBJ.X_OBJE...

------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                        | Name             | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop | TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                                  |                   |    1 | 205 | 217  (15)|    |    |       |    |          |
| 1 |  PX COORDINATOR                                  |                   |    |    |          |    |    |       |    |          |
| 2 | PX SEND QC (RANDOM)                            | :TQ10003          |    1 | 205 | 217  (15)|    |    |  Q1,03 | P->S | QC (RAND)  |
| 3 | HASH GROUP BY                                  |                   |    1 | 205 | 217  (15)|    |    |  Q1,03 | PCWP |          |
| 4 |    PX RECEIVE                                  |                   |    1 | 205 | 216  (14)|    |    |  Q1,03 | PCWP |          |
| 5 |    PX SEND HASH                               | :TQ10002          |    1 | 205 | 216  (14)|    |    |  Q1,02 | P->P | HASH    |
| 6 |    BUFFER SORT                               |                   |    1 | 205 | 217  (15)|    |    |  Q1,02 | PCWP |          |
| 7 |       NESTED LOOPS                               |                   |    1 | 205 | 216  (14)|    |    |  Q1,02 | PCWP |          |
| 8 |       NESTED LOOPS                            |                   |    1 | 189 | 216  (14)|    |    |  Q1,02 | PCWP |          |
| 9 |       NESTED LOOPS                            |                   |    1 | 170 | 216  (15)|    |    |  Q1,02 | PCWP |          |
|  10 |          NESTED LOOPS                            |                   | 22 |  3234 | 216  (15)|    |    |  Q1,02 | PCWP |          |
|  11 |          NESTED LOOPS                         |                   | 22 |  2728 | 216  (15)|    |    |  Q1,02 | PCWP |          |
|* 12 |          HASH JOIN                            |                   | 45 |  4725 | 216  (15)|    |    |  Q1,02 | PCWP |          |
|  13 |             BUFFER SORT                         |                   |    |    |          |    |    |  Q1,02 | PCWC |          |
|  14 |             PX RECEIVE                         |                   | 26852 |  1232K| 49  (15)|    |    |  Q1,02 | PCWP |          |
|  15 |             PX SEND HASH                      | :TQ10000          | 26852 |  1232K| 49  (15)|    |    |       | S->P | HASH    |
|* 16 |                HASH JOIN                         |                   | 26852 |  1232K| 49  (15)|    |    |       |    |          |
|* 17 |                INDEX FULL SCAN                | W_POSITION_D_M3    | 21903 | 213K|    1 (0)|    |    |       |    |          |
|  18 |                PARTITION RANGE ITERATOR       |                   | 26852 | 970K| 42 (3)| 87 | 97 |       |    |          |
|  19 |                TABLE ACCESS BY LOCAL INDEX ROWID| W_ACT_PROD_F       | 26852 | 970K| 42 (3)| 87 | 97 |       |    |          |
|* 20 |                   INDEX RANGE SCAN             | W_ACT_PROD_F_F9    |  2718 |    |    1 (0)| 87 | 97 |       |    |          |
|  21 |             PX RECEIVE                         |                   | 57672 |  3266K| 165  (14)|    |    |  Q1,02 | PCWP |          |
|  22 |             PX SEND HASH                      | :TQ10001          | 57672 |  3266K| 165  (14)|    |    |  Q1,01 | P->P | HASH    |
|  23 |             PX PARTITION HASH SINGLE          |                   | 57672 |  3266K| 165  (14)|    2 |    2 |  Q1,01 | PCWC |          |
|* 24 |                TABLE ACCESS BY LOCAL INDEX ROWID  | W_OBJECTIVE_F    | 57672 |  3266K| 165  (14)|    |    |  Q1,01 | PCWP |          |
|* 25 |                INDEX RANGE SCAN                | W_OBJECTIVE_F_U3 |  1671K|    | 11  (28)|    |    |  Q1,01 | PCWP |          |
|* 26 |          INDEX RANGE SCAN                      | W_LOV_D_M2       |    1 | 19 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 27 |          TABLE ACCESS BY INDEX ROWID          | W_PERSON_D       |    1 | 23 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 28 |          INDEX UNIQUE SCAN                   | W_PERSON_D_P1    |    1 |    |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 29 |          INDEX RANGE SCAN                      | WC_PROD_RANK_AUX_M1 |    1 | 23 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 30 |       TABLE ACCESS BY INDEX ROWID             | W_LOV_D          |    1 | 19 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 31 |          INDEX UNIQUE SCAN                      | W_LOV_D_PK       |    1 |    |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 32 |       TABLE ACCESS BY INDEX ROWID             | W_PRODUCT_D       |    1 | 16 |    1 (0)|    |    |  Q1,02 | PCWP |          |
|* 33 |       INDEX UNIQUE SCAN                      | W_PRODUCT_D_P1    |    1 |    |    1 (0)|    |    |  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">=20130204)
  24 - filter("OBJ"."X_OBJECTIVE_NAME"='Calls In Target' AND "OBJ"."OBJ_START_DAY_WID">=20130204 AND "OBJ"."X_SALES_FORCE_NAME"<>'NLD')
  25 - access("OBJ"."OBJ_END_DAY_WID">=20130204)
  26 - access("LOV_STA"."NAME"='Submitted' AND "FACT"."X_ACT_STA_WID"="LOV_STA"."ROW_WID")
  27 - filter("W_PERSON_D"."CON_ACTIVE_FLG"='Y')
  28 - access("FACT"."CONTACT_WID"="W_PERSON_D"."ROW_WID")
  29 - 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")
  30 - filter("LOV_TYP"."NAME" LIKE '%Call%')
  31 - access("FACT"."ACT_TYPE_WID"="LOV_TYP"."ROW_WID")
  32 - filter("W_PRODUCT_D"."INTEGRATION_ID"="WC_PROD_RANK_AUX"."PROD_ID")
  33 - access("FACT"."PROD_WID"="W_PRODUCT_D"."ROW_WID")

SQL> exec dbms_stats.RESTORE_TABLE_STATS('SIEBEL','W_ACT_PROD_F',to_timestamp(to_date('30-AUG-13 11.03.34','dd-mm-yy hh24:mi:ss')));
PL/SQL procedure successfully completed.
SQL>  select num_buckets,density,GLOBAL_STATS,LAST_ANALYZED from DBA_tab_columns where TABLE_NAME='W_ACT_PROD_F' and COLUMN_NAME='PERIOD_DAY_WID';
NUM_BUCKETS DENSITY GLO LAST_ANALYZED
----------- ---------- --- -------------------
      254 .000915751 YES 2013-08-30 10:58:26
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name             | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop | TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                               |                   |    1 | 205 |  9679  (11)|    |    |       |    |          |
| 1 |  PX COORDINATOR                               |                   |    |    |          |    |    |       |    |          |
| 2 | PX SEND QC (RANDOM)                         | :TQ10003          |    1 | 205 |  9679  (11)|    |    |  Q1,03 | P->S | QC (RAND)  |
| 3 | HASH GROUP BY                               |                   |    1 | 205 |  9679  (11)|    |    |  Q1,03 | PCWP |          |
| 4 |    PX RECEIVE                               |                   |    1 | 205 |  9677  (11)|    |    |  Q1,03 | PCWP |          |
| 5 |    PX SEND HASH                            | :TQ10002          |    1 | 205 |  9677  (11)|    |    |  Q1,02 | P->P | HASH    |
| 6 |    NESTED LOOPS                            |                   |    1 | 205 |  9677  (11)|    |    |  Q1,02 | PCWP |          |
| 7 |       NESTED LOOPS                            |                   |    1 | 186 |  9677  (11)|    |    |  Q1,02 | PCWP |          |
| 8 |       NESTED LOOPS                         |                   | 32 |  5440 |  9677  (11)|    |    |  Q1,02 | PCWP |          |
| 9 |       NESTED LOOPS                         |                   |  6049 | 868K|  9665  (11)|    |    |  Q1,02 | PCWP |          |
|* 10 |          HASH JOIN                            |                   |  6049 | 732K|  9659  (11)|    |    |  Q1,02 | PCWP |          |
|  11 |          PX RECEIVE                         |                   | 57672 |  3266K| 165  (14)|    |    |  Q1,02 | PCWP |          |
|  12 |          PX SEND HASH                      | :TQ10001          | 57672 |  3266K| 165  (14)|    |    |  Q1,01 | P->P | HASH    |
|  13 |             PX PARTITION HASH SINGLE          |                   | 57672 |  3266K| 165  (14)|    2 |    2 |  Q1,01 | PCWC |          |
|* 14 |             TABLE ACCESS BY LOCAL INDEX ROWID | W_OBJECTIVE_F    | 57672 |  3266K| 165  (14)|    |    |  Q1,01 | PCWP |          |
|* 15 |             INDEX RANGE SCAN                | W_OBJECTIVE_F_U3 |  1671K|    | 11  (28)|    |    |  Q1,01 | PCWP |          |
|  16 |          BUFFER SORT                         |                   |    |    |          |    |    |  Q1,02 | PCWC |          |
|  17 |          PX RECEIVE                         |                   |  3564K| 224M|  9462  (10)|    |    |  Q1,02 | PCWP |          |
|  18 |             PX SEND HASH                      | :TQ10000          |  3564K| 224M|  9462  (10)|    |    |       | S->P | HASH    |
|* 19 |             HASH JOIN                      |                   |  3564K| 224M|  9462  (10)|    |    |       |    |          |
|* 20 |             INDEX FULL SCAN                | W_POSITION_D_M3    | 21903 | 213K|    1 (0)|    |    |       |    |          |
|* 21 |             TABLE ACCESS BY LOCAL INDEX ROWID| W_ACT_PROD_F       | 677K| 23M|  1824 (7)|    1 |    1 |       |    |          |
|  22 |                NESTED LOOPS                   |                   |  3564K| 190M|  9121 (7)|    |    |       |    |          |
|* 23 |                INDEX RANGE SCAN             | W_LOV_D_M2       |    5 | 95 |    1 (0)|    |    |       |    |          |
|  24 |                PARTITION RANGE ITERATOR    |                   |  6876K|    | 58  (23)| 87 | 97 |       |    |          |
|* 25 |                INDEX RANGE SCAN             | W_ACT_PROD_F_XF13 |  6876K|    | 58  (23)| 87 | 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">=20130204 AND "OBJ"."X_SALES_FORCE_NAME"<>'NLD')
  15 - access("OBJ"."OBJ_END_DAY_WID">=20130204)
  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">=20130204)
  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")
Statistics
----------------------------------------------------------
      298  recursive calls
      2  db block gets
20847726  consistent gets
   363448  physical reads
      0  redo size
   43274  bytes sent via SQL*Net to client
   1592  bytes received via SQL*Net from client
      102  SQL*Net roundtrips to/from client
      11  sorts (memory)
      1  sorts (disk)
   1501  rows processed
这是10g，还没有dbms_stats.set_table_prefs，该表统计信息每周收集2次，而列的直方图很有可能会被覆盖；
看来目前唯一的方法是让开发修改存储过程，让其收集该表时添加选项method_opt=> 'for columns size 254 PERIOD_DAY_WID'

Yong Huang · 发表于 2013-9-7 05:19

anlinew found the bug that may be relevant. Please try the workaround in the bug report:

"set optimizer_features_enable='9.2.0'
or
delete statistics on base table and use dynamic sampling"

or

"Hint the query to enforce the index range scan."

Wait. You said your index is local? The bug is about a global index. So I don't know. But at least you can try alter session set optimizer_features_enable='9.2.0'.

myownstars · 发表于 2013-9-9 11:09

Yong Huang 发表于 2013-9-7 05:19
anlinew found the bug that may be relevant. Please try the workaround in the bug report:

"set opt ...

dynamic sampling确实可以解决该问题，生产库上的sql全是封装的，没法修改；
目前optimizer_features_enable = 10.2.0.5，有机会尝试一下9.2.0，不过即便能解决问题，也不大可能在生产库采用

[讨论] 10205分区global stat不精准导致big sql选择bad plan

浏览过的版块