一次使用 Extended Statistics 優化SQL案例

從Oracle 11g開始，Oracle提供了 Extended Statistics 新特征，本案例就恰好利用了這個新特征。

1.OBIEE終端用戶發來郵件說某某報表慢(跑了30分鐘還不出結果)，請求DBA調查。通過和OBIEE的人合作，找到報表的SQL如下： 
2. 
3.select sum(T2083114.MANUL_COST_OVRRD_AMT) as c1,  4.sum(nvl(T2083114.REVSD_VAR_ESTMT_COST_AMT , 0)) as c2,  5.T2084525.ACCT_LONG_NAME as c3,  6.T2084525.NAME as c4,  7.T2083424.PRMTN_NAME as c5,  8.T2083424.PRMTN_ID as c6,  9.case  when case  when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account'   10.then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end  is null   11.then 'Private' else case  when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account'   12.then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end  end  as c7,  13.T2083424.PRMTN_STTUS_CODE as c8,  14.T2083424.APPRV_BY_DESC as c9,  15.T2083424.APPRV_STTUS_CODE as c10,  16.T2083424.AUTO_UPDT_GTIN_IND as c11,  17.T2083424.CREAT_DATE as c12,  18.T2083424.PGM_START_DATE as c13,  19.T2083424.PGM_END_DATE as c14,  20.nvl(case  when T2083424.PRMTN_STTUS_CODE = 'Confirmed'   21.then cast(( TRUNC( TO_DATE('2011-06-07' , 'YYYY-MM-DD') ) - TRUNC( T2083424.PGM_END_DATE ) ) as  VARCHAR ( 10 ) ) end  , '') as c15,  22.T2083424.PRMTN_STOP_DATE as c16,  23.T2083424.SHPMT_START_DATE as c17,  24.T2083424.SHPMT_END_DATE as c18,  25.T2083424.CNBLN_WK_CNT as c19,  26.T2083424.ACTVY_DETL_POP as c20,  27.T2083424.CMMNT_DESC as c21,  28.T2083424.PRMTN_AVG_POP as c22,  29.T2084525.CHANL_TYPE_DESC as c23,  30.T2083424.PRMTN_SKID as c24  31.from  
32.ADWG_OPTIMA_LA11.OPT_ACCT_FDIM T2084525 /* OPT_ACCT_PRMTN_FDIM */ ,  33.ADWG_OPTIMA_LA11.OPT_BUS_UNIT_FDIM T2083056, 
34.ADWG_OPTIMA_LA11.OPT_CAL_MASTR_DIM T2083357 /* OPT_CAL_MASTR_DIM01 */ ,  35.ADWG_OPTIMA_LA11.OPT_PRMTN_FDIM T2083424, 
36.ADWG_OPTIMA_LA11.OPT_ACTVY_FCT T2083114 
37.where  ( T2083056.BUS_UNIT_SKID = T2083114.BUS_UNIT_SKID and T2083114.BUS_UNIT_SKID = T2084525.BUS_UNIT_SKID  
38.and T2083114.DATE_SKID = T2083357.CAL_MASTR_SKID and T2083114.BUS_UNIT_SKID = T2083424.BUS_UNIT_SKID  
39.and T2083114.PRMTN_SKID = T2083424.PRMTN_SKID and T2083056.BUS_UNIT_NAME = 'Chile'   40.and T2083114.ACCT_PRMTN_SKID = T2084525.ACCT_SKID and T2083357.FISC_YR_ABBR_NAME = 'FY10/11'   41.and T2084525.ACCT_LONG_NAME is not null and (case  when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account'   42.then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end  in ('Alternate BDF', 'Corporate', 'Private'))   43.and (T2084525.ACCT_LONG_NAME in ('ADELCO - CHILE - 0066009018', 'ALIMENTOS FRUNA - CHILE - 0066009049',   44.'CENCOSUD - CHILE - 0066009007', 'COMERCIAL ALVI - CHILE - 0066009070', 'D&S - CHILE - 0066009008',   45.'DIPAC - CHILE - 0066009024', 'DIST. COMERCIAL - CHILE - 0066009087', 'DISTRIBUCION LAGOS S.A. - CHILE - 2001146505',   46.'ECOMMERCE ESCALA 1 - 1900001746', 'EMILIO SANDOVAL - CHILE - 2000402293', 'F. AHUMADA - CHILE - 0066009023',  47.'FALABELLA - CHILE - 2000406971', 'FRANCISCO LEYTON - CHILE - 0066009142', 'MAICAO - CHILE - 0066009135',   48.'MARGARITA UAUY - CHILE - 0066009146', 'PREUNIC - CHILE - 0066009032', 'PRISA DISTRIBUCION - CHILE - 2001419970',  49.'RABIE - CHILE - 0066009015', 'S Y B FARMACEUTICA S.A. - CHILE - 2000432938',   50.'SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967', 'SOCOFAR - CHILE - 0066009028',   51.'SODIMAC - CHILE - 2000402358', 'SOUTHERN CROSS - CHILE - 2002135799',   52.'SUPERM. MONSERRAT - CHILE - 0066009120', 'TELEMERCADOS EUROPA - CHILE - 0066009044'))   53.and T2083424.PRMTN_LONG_NAME in (select distinct T2083424.PRMTN_LONG_NAME as c1  54.from  
55.ADWG_OPTIMA_LA11.OPT_ACCT_FDIM T2084525 /* OPT_ACCT_PRMTN_FDIM */ ,  56.ADWG_OPTIMA_LA11.OPT_BUS_UNIT_FDIM T2083056, 
57.ADWG_OPTIMA_LA11.OPT_CAL_MASTR_DIM T2083357 /* OPT_CAL_MASTR_DIM01 */ ,  58.ADWG_OPTIMA_LA11.OPT_PRMTN_FDIM T2083424, 
59.ADWG_OPTIMA_LA11.OPT_PRMTN_PROD_FLTR_LKP T2083698 
60.where  ( T2083056.BUS_UNIT_SKID = T2083698.BUS_UNIT_SKID and T2083357.CAL_MASTR_SKID = T2083698.DATE_SKID  
61.and T2083698.ACCT_PRMTN_SKID = T2084525.ACCT_SKID and T2083424.PRMTN_SKID = T2083698.PRMTN_SKID  
62.and T2083424.BUS_UNIT_SKID = T2083698.BUS_UNIT_SKID and T2083056.BUS_UNIT_NAME = 'Chile'   63.and T2083357.FISC_YR_ABBR_NAME = 'FY10/11' and T2083698.BUS_UNIT_SKID = T2084525.BUS_UNIT_SKID   64.and (case  when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'   65.else T2083424.CORP_PRMTN_TYPE_CODE end  in ('Alternate BDF', 'Corporate', 'Private'))   66.and (T2084525.ACCT_LONG_NAME in ('ADELCO - CHILE - 0066009018',   67.'ALIMENTOS FRUNA - CHILE - 0066009049', 'CENCOSUD - CHILE - 0066009007',   68.'COMERCIAL ALVI - CHILE - 0066009070', 'D&S - CHILE - 0066009008',   69.'DIPAC - CHILE - 0066009024', 'DIST. COMERCIAL - CHILE - 0066009087',   70.'DISTRIBUCION LAGOS S.A. - CHILE - 2001146505', 'ECOMMERCE ESCALA 1 - 1900001746',   71.'EMILIO SANDOVAL - CHILE - 2000402293', 'F. AHUMADA - CHILE - 0066009023',   72.'FALABELLA - CHILE - 2000406971', 'FRANCISCO LEYTON - CHILE - 0066009142',   73.'MAICAO - CHILE - 0066009135', 'MARGARITA UAUY - CHILE - 0066009146',  74.'PREUNIC - CHILE - 0066009032', 'PRISA DISTRIBUCION - CHILE - 2001419970',   75.'RABIE - CHILE - 0066009015', 'S Y B FARMACEUTICA S.A. - CHILE - 2000432938',   76.'SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967', 'SOCOFAR - CHILE - 0066009028',   77.'SODIMAC - CHILE - 2000402358', 'SOUTHERN CROSS - CHILE - 2002135799',   78.'SUPERM. MONSERRAT - CHILE - 0066009120', 'TELEMERCADOS EUROPA - CHILE - 0066009044')) ) ) )   79.group by T2083424.PRMTN_SKID, T2083424.PRMTN_ID, T2083424.PRMTN_NAME, T2083424.SHPMT_END_DATE,  
80.T2083424.SHPMT_START_DATE, T2083424.PRMTN_STTUS_CODE, T2083424.APPRV_STTUS_CODE, T2083424.CMMNT_DESC, 
81.T2083424.PGM_START_DATE, T2083424.PGM_END_DATE, T2083424.CREAT_DATE, T2083424.APPRV_BY_DESC,  
82.T2083424.AUTO_UPDT_GTIN_IND, T2083424.PRMTN_STOP_DATE, T2083424.ACTVY_DETL_POP, T2083424.CNBLN_WK_CNT,  
83.T2083424.PRMTN_AVG_POP, T2084525.NAME, T2084525.CHANL_TYPE_DESC, T2084525.ACCT_LONG_NAME,  
84.case  when case  when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'  85.else T2083424.CORP_PRMTN_TYPE_CODE end  is null then 'Private' else case   86.when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate'  87.else T2083424.CORP_PRMTN_TYPE_CODE end  end ,   88.nvl(case  when T2083424.PRMTN_STTUS_CODE = 'Confirmed'  89.then cast(( TRUNC( TO_DATE('2011-06-07' , 'YYYY-MM-DD') ) - TRUNC( T2083424.PGM_END_DATE ) ) as  VARCHAR ( 10 ) ) end  , '')  90.order by c24, c3; 
91. 
92.這個SQL要用到的表信息如下 
93. 
94.OWNER                TABLE_NAME                       Size(Mb) PARTITIONED          DEGREE          NUM_ROWS 
95.-------------------- ------------------------------ ---------- -------------------- ---------- ------------- 
96.ADWG_OPTIMA_LA11     *OPT_BUS_UNIT_FDIM             .001037598 NO                            1         16 
97.ADWG_OPTIMA_LA11     *OPT_CAL_MASTR_DIM             38.1284523 NO                            1         37435 
98.ADWG_OPTIMA_LA11     OPT_CAL_MASTR_DIM              38.1284523 NO                            1         37435 
99.ADWG_OPTIMA_LA11     *OPT_PRMTN_FDIM                74.6365929 YES                           1         52140 
100.ADWG_OPTIMA_LA11     OPT_PRMTN_FDIM                 74.6365929 YES                           1         52140 
101.ADWG_OPTIMA_LA11     OPT_ACTVY_FCT                  19.3430614 YES                           1        157230 
102.ADWG_OPTIMA_LA11     *OPT_ACCT_FDIM                 36.6709185 YES                           2         95415 
103.ADWG_OPTIMA_LA11     OPT_ACCT_FDIM                  36.6709185 YES                           2         95415 
104.ADWG_OPTIMA_LA11     OPT_PRMTN_PROD_FLTR_LKP        1523.87207 YES                           2      30148975 
105. 
106.帶*表示它用到了索引 那么這里 只有表OPT_PRMTN_PROD_FLTR_LKP是大表，它有3千多萬數據，1.5G 現在來看看這個SQL的執行計劃： 
107. 
108.SQL> select * from table(dbms_xplan.display); 
109. 
110.PLAN_TABLE_OUTPUT 
111.--------------------------------------------------------------------------------------------------------------------------------------------------- 
112.Plan hash value: 3566115627 
113. 
114.------------------------------------------------------------------------------------------------------------------------------------------ 
115.| Id  | Operation                                      | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
116.------------------------------------------------------------------------------------------------------------------------------------------ 
117.|   0 | SELECT STATEMENT                               |                         |     1 |   352 |  1551  (17)| 00:00:07 |       |       | 
118.|   1 |  SORT GROUP BY                                 |                         |     1 |   352 |  1551  (17)| 00:00:07 |       |       | 
119.|   2 |   VIEW                                         | VM_NWVW_2               |     1 |   352 |  1550  (17)| 00:00:07 |       |       | 
120.|   3 |    HASH UNIQUE                                 |                         |     1 |   652 |  1550  (17)| 00:00:07 |       |       | 
121.|   4 |     NESTED LOOPS                               |                         |       |       |         |     |       |       | 
122.|   5 |      NESTED LOOPS                              |                         |     1 |   652 |  1549  (17)| 00:00:07 |       |       | 
123.|   6 |       NESTED LOOPS                             |                         |     1 |   639 |  1548  (17)| 00:00:07 |       |       | 
124.|   7 |        NESTED LOOPS                            |                         |     2 |  1180 |  1546  (17)| 00:00:07 |       |       | 
125.|   8 |         NESTED LOOPS                           |                         |     1 |   568 |   130   (5)| 00:00:01 |       |       | 
126.|   9 |          NESTED LOOPS                          |                         |     1 |   509 |   109   (6)| 00:00:01 |       |       | 
127.|  10 |           NESTED LOOPS                         |                         |     1 |   484 |   108   (6)| 00:00:01 |       |       | 
128.|* 11 |            HASH JOIN                           |                         |     5 |   830 |   103   (6)| 00:00:01 |       |       | 
129.|  12 |             PARTITION LIST SUBQUERY            |                         |    47 |  4089 |    82   (3)| 00:00:01 |KEY(SQ)|KEY(SQ)| 
130.|  13 |              INLIST ITERATOR                   |                         |       |       |         |     |       |       | 
131.|  14 |               TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM           |    47 |  4089 |    82   (3)| 00:00:01 |KEY(SQ)|KEY(SQ)| 
132.|* 15 |                INDEX RANGE SCAN                | OPT_ACCT_FDIM_NX2       |    47 |       |    43   (5)| 00:00:01 |KEY(SQ)|KEY(SQ)| 
133.|  16 |             NESTED LOOPS                       |                         | 10482 |   808K|    20  (15)| 00:00:01 |       |       | 
134.|  17 |              NESTED LOOPS                      |                         |     1 |    40 |     2   (0)| 00:00:01 |       |       | 
135.|* 18 |               INDEX RANGE SCAN                 | OPT_BUS_UNIT_FDIM_UX2   |     1 |    26 |     1   (0)| 00:00:01 |       |       | 
136.|* 19 |               INDEX RANGE SCAN                 | OPT_BUS_UNIT_FDIM_UX2   |     1 |    14 |     1   (0)| 00:00:01 |       |       | 
137.|  20 |              PARTITION LIST ITERATOR           |                         | 10482 |  1699K|    18  (17)| 00:00:01 |   KEY |   KEY | 
138.|* 21 |               TABLE ACCESS FULL                | OPT_ACTVY_FCT           | 10482 |  1699K|    18  (17)| 00:00:01 |   KEY |   KEY | 
139.|* 22 |            TABLE ACCESS BY GLOBAL INDEX ROWID  | OPT_PRMTN_FDIM          |     1 |   318 |     1   (0)| 00:00:01 | ROWID | ROWID | 
140.|* 23 |             INDEX UNIQUE SCAN                  | OPT_PRMTN_FDIM_PK       |     1 |       |     0   (0)| 00:00:01 |       |       | 
141.|* 24 |           TABLE ACCESS BY INDEX ROWID          | OPT_CAL_MASTR_DIM       |     1 |    25 |     1   (0)| 00:00:01 |       |       | 
142.|* 25 |            INDEX UNIQUE SCAN                   | OPT_CAL_MASTR_DIM_PK    |     1 |       |     0   (0)| 00:00:01 |       |       | 
143.|  26 |          PARTITION LIST ALL                    |                         |     1 |    59 |    21   (0)| 00:00:01 |     1 |    17 | 
144.|* 27 |           TABLE ACCESS BY LOCAL INDEX ROWID    | OPT_PRMTN_FDIM          |     1 |    59 |    21   (0)| 00:00:01 |     1 |    17 | 
145.|* 28 |            INDEX RANGE SCAN                    | OPT_PRMTN_FDIM_NX3      |     4 |       |    17   (0)| 00:00:01 |     1 |    17 | 
146.|  29 |         PARTITION LIST ITERATOR                |                         |    39 |   858 |  1416  (18)| 00:00:07 |   KEY |   KEY | 
147.|* 30 |          TABLE ACCESS FULL                     | OPT_PRMTN_PROD_FLTR_LKP |    39 |   858 |  1416  (18)| 00:00:07 |   KEY |   KEY | 
148.|* 31 |        TABLE ACCESS BY GLOBAL INDEX ROWID      | OPT_ACCT_FDIM           |     1 |    49 |     1   (0)| 00:00:01 | ROWID | ROWID | 
149.|* 32 |         INDEX UNIQUE SCAN                      | OPT_ACCT_FDIM_PK        |     1 |       |     0   (0)| 00:00:01 |       |       | 
150.|* 33 |       INDEX UNIQUE SCAN                        | OPT_CAL_MASTR_DIM_PK    |     1 |       |     0   (0)| 00:00:01 |       |       | 
151.|* 34 |      TABLE ACCESS BY INDEX ROWID               | OPT_CAL_MASTR_DIM       |     1 |    13 |     1   (0)| 00:00:01 |       |       | 
152.------------------------------------------------------------------------------------------------------------------------------------------ 
153. 
154.Predicate Information (identified by operation id): 
155.--------------------------------------------------- 
156. 
157.  11 - access("T2083114"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID" AND "T2083114"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID")  158.  15 - access("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE -  159.              0066009049' OR "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE  160.              - 0066009070' OR "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE -  161.              0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION  162.              LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMMERCE ESCALA 1 - 1900001746' OR  163.              "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE -  164.              0066009023' OR "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON -  165.              CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY -  166.              CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA  167.              DISTRIBUCION - CHILE - 2001419970' OR "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S  168.              Y B FARMACEUTICA S.A. - CHILE - 2000432938' OR "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR  169.              "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR  170.              "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE -  171.              0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EUROPA - CHILE - 0066009044')  172.       filter("T2084525"."ACCT_LONG_NAME" IS NOT NULL)  173.  18 - access("T2083056"."BUS_UNIT_NAME"='Chile')  174.  19 - access("T2083056"."BUS_UNIT_NAME"='Chile')  175.  21 - filter("T2083056"."BUS_UNIT_SKID"="T2083114"."BUS_UNIT_SKID")  176.  22 - filter(CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE  177.              "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Alternate BDF' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN  178.              'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Corporate' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target  179.              Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private')  180.  23 - access("T2083114"."PRMTN_SKID"="T2083424"."PRMTN_SKID" AND "T2083114"."BUS_UNIT_SKID"="T2083424"."BUS_UNIT_SKID")  181.  24 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11')  182.  25 - access("T2083114"."DATE_SKID"="T2083357"."CAL_MASTR_SKID")  183.  27 - filter(CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE  184.              "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Alternate BDF' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN  185.              'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Corporate' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target  186.              Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private')  187.  28 - access("T2083424"."PRMTN_LONG_NAME"="T2083424"."PRMTN_LONG_NAME")  188.  30 - filter("T2083056"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID" AND "T2083424"."PRMTN_SKID"="T2083698"."PRMTN_SKID" AND  189.              "T2083424"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID")  190.  31 - filter("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE -  191.              0066009049' OR "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE  192.              - 0066009070' OR "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE -  193.              0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION  194.              LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMMERCE ESCALA 1 - 1900001746' OR  195.              "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE -  196.              0066009023' OR "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON -  197.              CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY -  198.              CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA  199.              DISTRIBUCION - CHILE - 2001419970' OR "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S  200.              Y B FARMACEUTICA S.A. - CHILE - 2000432938' OR "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR  201.              "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR  202.              "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE -  203.              0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EUROPA - CHILE - 0066009044')  204.  32 - access("T2083698"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID" AND "T2083698"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID")  205.  33 - access("T2083357"."CAL_MASTR_SKID"="T2083698"."DATE_SKID")  206.  34 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11')  207. 
208.95 rows selected. 
209. 
210.注意觀察 ID=30 它走的是全表掃描 并且優化器認為它只返回39行數據，www.linuxidc.com 那么問題可能出在這里了 于是創建如下索引 
211. 
212.SQL> create index OPT_PRMTN_PROD_FLTR_LKP_NX1 ON OPT_PRMTN_PROD_FLTR_LKP(BUS_UNIT_SKID,PRMTN_SKID) nologging parallel ; 
213. 
214.Index created. 
215. 
216.Elapsed: 00:00:33.04 
217. 
218.關于為什么我要這樣創建索引，這里就不說了，如果不明白的請看我前面博客，創建索引之后 SQL能在4分鐘以內跑完，下面是這個SQL的特殊執行計劃 
219. 
220.Plan hash value: 1310530159 
221. 
222.------------------------------------------------------------------------------------------------------------------------------ 
223.| Id  | Operation                                      | Name                        | Starts | E-Rows | A-Rows |   A-Time   | 
224.------------------------------------------------------------------------------------------------------------------------------ 
225.|   0 | SELECT STATEMENT                               |                             |      1 |        |   1324 |00:02:42.23 | 
226.|   1 |  SORT GROUP BY                                 |                             |      1 |      1 |   1324 |00:02:42.23 | 
227.|   2 |   VIEW                                         | VM_NWVW_2                   |      1 |      1 |   6808 |00:02:42.18 | 
228.|   3 |    HASH UNIQUE                                 |                             |      1 |      1 |   6808 |00:02:42.18 | 
229.|   4 |     NESTED LOOPS                               |                             |      1 |        |   5220K|00:02:21.06 | 
230.|   5 |      NESTED LOOPS                              |                             |      1 |      1 |   5220K|00:02:00.18 | 
231.|   6 |       NESTED LOOPS                             |                             |      1 |      1 |   5220K|00:01:49.74 | 
232.|   7 |        NESTED LOOPS                            |                             |      1 |      2 |   5220K|00:01:18.42 | 
233.|   8 |         NESTED LOOPS                           |                             |      1 |      1 |   6808 |00:00:01.62 | 
234.|   9 |          NESTED LOOPS                          |                             |      1 |      1 |   6808 |00:00:00.54 | 
235.|  10 |           NESTED LOOPS                         |                             |      1 |      1 |  11248 |00:00:00.40 | 
236.|* 11 |            HASH JOIN                           |                             |      1 |      5 |  11248 |00:00:00.07 | 
237.|  12 |             PARTITION LIST SUBQUERY            |                             |      1 |     47 |     25 |00:00:00.01 | 
238.|  13 |              INLIST ITERATOR                   |                             |      1 |        |     25 |00:00:00.01 | 
239.|  14 |               TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM               |     25 |     47 |     25 |00:00:00.01 | 
240.|* 15 |                INDEX RANGE SCAN                | OPT_ACCT_FDIM_NX2           |     25 |     47 |     25 |00:00:00.01 | 
241.|  16 |             NESTED LOOPS                       |                             |      1 |  10482 |  12788 |00:00:00.03 | 
242.|  17 |              NESTED LOOPS                      |                             |      1 |      1 |      1 |00:00:00.01 | 
243.|* 18 |               INDEX RANGE SCAN                 | OPT_BUS_UNIT_FDIM_UX2       |      1 |      1 |      1 |00:00:00.01 | 
244.|* 19 |               INDEX RANGE SCAN                 | OPT_BUS_UNIT_FDIM_UX2       |      1 |      1 |      1 |00:00:00.01 | 
245.|  20 |              PARTITION LIST ITERATOR           |                             |      1 |  10482 |  12788 |00:00:00.03 | 
246.|* 21 |               TABLE ACCESS FULL                | OPT_ACTVY_FCT               |      1 |  10482 |  12788 |00:00:00.03 | 
247.|* 22 |            TABLE ACCESS BY GLOBAL INDEX ROWID  | OPT_PRMTN_FDIM              |  11248 |      1 |  11248 |00:00:00.31 | 
248.|* 23 |             INDEX UNIQUE SCAN                  | OPT_PRMTN_FDIM_PK           |  11248 |      1 |  11248 |00:00:00.12 | 
249.|* 24 |           TABLE ACCESS BY INDEX ROWID          | OPT_CAL_MASTR_DIM           |  11248 |      1 |   6808 |00:00:00.14 | 
250.|* 25 |            INDEX UNIQUE SCAN                   | OPT_CAL_MASTR_DIM_PK        |  11248 |      1 |  11248 |00:00:00.05 | 
251.|  26 |          PARTITION LIST ALL                    |                             |   6808 |      1 |   6808 |00:00:01.08 | 
252.|* 27 |           TABLE ACCESS BY LOCAL INDEX ROWID    | OPT_PRMTN_FDIM              |    115K|      1 |   6808 |00:00:01.05 | 
253.|* 28 |            INDEX RANGE SCAN                    | OPT_PRMTN_FDIM_NX3          |    115K|      4 |   6808 |00:00:00.78 | 
254.|  29 |         TABLE ACCESS BY GLOBAL INDEX ROWID     | OPT_PRMTN_PROD_FLTR_LKP     |   6808 |     39 |   5220K|00:01:19.79 | 
255.|* 30 |          INDEX RANGE SCAN                      | OPT_PRMTN_PROD_FLTR_LKP_NX1 |   6808 |      3 |   5220K|00:00:43.96 | 
256.|* 31 |        TABLE ACCESS BY GLOBAL INDEX ROWID      | OPT_ACCT_FDIM               |   5220K|      1 |   5220K|00:00:23.79 | 
257.|* 32 |         INDEX UNIQUE SCAN                      | OPT_ACCT_FDIM_PK            |   5220K|      1 |   5220K|00:00:08.38 | 
258.|* 33 |       INDEX UNIQUE SCAN                        | OPT_CAL_MASTR_DIM_PK        |   5220K|      1 |   5220K|00:00:07.58 | 
259.|* 34 |      TABLE ACCESS BY INDEX ROWID               | OPT_CAL_MASTR_DIM           |   5220K|      1 |   5220K|00:00:17.28 | 
260.------------------------------------------------------------------------------------------------------------------------------ 
261. 
262.Predicate Information (identified by operation id): 
263.--------------------------------------------------- 
264. 
265.  11 - access("T2083114"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID" AND "T2083114"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID")  266.  15 - access(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHI  267.              "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHI  268.              0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. CO  269.              "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMM  270.              SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE - 0066009023' OR "T2084525"."A  271.              "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHI  272.              CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"  273.              "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A.  274.              MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"."  275.              "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSER  276.              EUROPA - CHILE - 0066009044')) 
277.       filter("T2084525"."ACCT_LONG_NAME" IS NOT NULL)  278.  18 - access("T2083056"."BUS_UNIT_NAME"='Chile')  279.  19 - access("T2083056"."BUS_UNIT_NAME"='Chile')  280.  21 - filter("T2083056"."BUS_UNIT_SKID"="T2083114"."BUS_UNIT_SKID")  281.  22 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_  282.              "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE"  283.              'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private'))  284.  23 - access("T2083114"."PRMTN_SKID"="T2083424"."PRMTN_SKID" AND "T2083114"."BUS_UNIT_SKID"="T2083424"."BUS_UNIT_SKID")  285.  24 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11')  286.  25 - access("T2083114"."DATE_SKID"="T2083357"."CAL_MASTR_SKID")  287.  27 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_  288.              "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE"  289.              'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private'))  290.  28 - access("T2083424"."PRMTN_LONG_NAME"="T2083424"."PRMTN_LONG_NAME")  291.  30 - access("T2083424"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID" AND "T2083424"."PRMTN_SKID"="T2083698"."PRMTN_SKID")  292.       filter("T2083056"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID")  293.  31 - filter(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHI  294.              "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHI  295.              0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. CO  296.              "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMM  297.              SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE - 0066009023' OR "T2084525"."A  298.              "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHI  299.              CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"  300.              "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A.  301.              MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"."  302.              "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSER  303.              EUROPA - CHILE - 0066009044')) 
304.  32 - access("T2083698"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID" AND "T2083698"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID")  305.  33 - access("T2083357"."CAL_MASTR_SKID"="T2083698"."DATE_SKID")  306.  34 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11')  307. 
308. 
309.107 rows selected. 
310. 
311.雖然這個SQL能在4分以內跑完，但是大家注意觀察上面執行計劃，ID=11這一步，CBO認為它只返回5行數據，但是實際卻是它會返回11248行數據，正是由于 
312.這里CBO計算出錯，導致后面的索引掃描高達上千萬次，所以有必要糾正這一步。因為這個SQL是OBIEE的，我不能更改SQL，也不能(至少很難)加HINT 
313.所以我選擇用11g 新特征----EXTENDED STATISTICS.  
314. 
315.11步驟是做HASH JOIN，而且這一步CBO選擇了2列作為HASH KEY，所以分別對2個表的2個HASH KEY收集EXTENDED STATISTICS 
316. 
317.SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'OPT_ACCT_FDIM', '(BUS_UNIT_SKID, ACCT_SKID)') FROM DUAL;  318. 
319.DBMS_STATS.CREATE_EXTENDED_STATS(USER,'OPT_ACCT_FDIM','(BUS_UNIT_SKID,ACCT_SKID)')  320.------------------------------------------------------------------------------------------------------------------ 
321.SYS_STUJ8OD#X2IPA_B9_CH00B046T 
322. 
323.SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'OPT_ACTVY_FCT', '(BUS_UNIT_SKID, ACCT_PRMTN_SKID)') FROM DUAL;  324. 
325.DBMS_STATS.CREATE_EXTENDED_STATS(USER,'OPT_ACTVY_FCT','(BUS_UNIT_SKID,ACCT_PRMTN_SKID)')  326.------------------------------------------------------------------------------------------------------------------ 
327.SYS_STU#CVQNKK5CCM0W2XEQWSRXSM 
328. 
329.SQL> BEGIN 
330.  2  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWG_OPTIMA_LA11',  331.  3  tabname => 'OPT_ACCT_FDIM',  332.  4  estimate_percent => 20, 
333.  5  method_opt => 'for all columns size auto',  334.  6  degree => 6, 
335.  7  granularity => 'ALL',  336.  8  cascade=>TRUE 
337.  9  ); 
338. 10  END; 
339. 11  / 
340. 
341.PL/SQL procedure successfully completed. 
342. 
343.Elapsed: 00:00:57.76 
344. 
345.SQL> BEGIN 
346.  2  DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWG_OPTIMA_LA11',  347.  3  tabname => 'OPT_ACTVY_FCT',  348.  4  estimate_percent => 20, 
349.  5  method_opt => 'for all columns size auto',  350.  6  degree => 6, 
351.  7  granularity => 'ALL',  352.  8  cascade=>TRUE 
353.  9  ); 
354. 10  END; 
355. 11  / 
356. 
357.PL/SQL procedure successfully completed. 
358. 
359.Elapsed: 00:01:15.10 
360. 
361.收集EXTENDED STATISTICS之后，SQL的的執行計劃更改如下： 
362. 
363.------------------------------------------------------------------------------------------------------------------------------------ 
364.| Id  | Operation                                 | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
365.------------------------------------------------------------------------------------------------------------------------------------ 
366.|   0 | SELECT STATEMENT                          |                             |      1 |        |   1324 |00:00:01.85 |     210K| 
367.|   1 |  SORT GROUP BY                            |                             |      1 |      1 |   1324 |00:00:01.85 |     210K| 
368.|*  2 |   FILTER                                  |                             |      1 |        |   6808 |00:00:01.84 |     210K| 
369.|   3 |    NESTED LOOPS                           |                             |      1 |        |   6808 |00:00:00.04 |   52722 | 
370.|   4 |     NESTED LOOPS                          |                             |      1 |      4 |  11248 |00:00:00.03 |   41474 | 
371.|   5 |      NESTED LOOPS                         |                             |      1 |     12 |  11248 |00:00:00.02 |   30247 | 
372.|*  6 |       HASH JOIN                           |                             |      1 |    403 |  11248 |00:00:00.01 |     172 | 
373.|   7 |        PARTITION LIST SUBQUERY            |                             |      1 |     47 |  25 |00:00:00.01 |      50 | 
374.|   8 |         INLIST ITERATOR                   |                             |      1 |        |  25 |00:00:00.01 |      47 | 
375.|   9 |          TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM               |     25 |     47 |  25 |00:00:00.01 |      47 | 
376.|* 10 |           INDEX RANGE SCAN                | OPT_ACCT_FDIM_NX2           |     25 |     47 |  25 |00:00:00.01 |      27 | 
377.|  11 |        NESTED LOOPS                       |                             |      1 |  10508 |  12788 |00:00:00.01 |     122 | 
378.|* 12 |         INDEX RANGE SCAN                  | OPT_BUS_UNIT_FDIM_UX2       |      1 |      1 |   1 |00:00:00.01 |  1 |      0 | 
379.|  13 |         PARTITION LIST ITERATOR           |                             |      1 |  10508 |  12788 |00:00:00.01 |     121 | 
380.|* 14 |          TABLE ACCESS FULL                | OPT_ACTVY_FCT               |      1 |  10508 |  12788 |00:00:00.01 |     121 | 
381.|* 15 |       TABLE ACCESS BY GLOBAL INDEX ROWID  | OPT_PRMTN_FDIM              |  11248 |      1 |  11248 |00:00:00.01 |   30075 | 
382.|* 16 |        INDEX UNIQUE SCAN                  | OPT_PRMTN_FDIM_PK           |  11248 |      1 |  11248 |00:00:00.01 |   11250 | 
383.|* 17 |      INDEX UNIQUE SCAN                    | OPT_CAL_MASTR_DIM_PK        |  11248 |      1 |  11248 |00:00:00.01 |   11227 | 
384.|* 18 |     TABLE ACCESS BY INDEX ROWID           | OPT_CAL_MASTR_DIM           |  11248 |      1 |   6808 |00:00:00.01 |   11248 | 
385.|  19 |    NESTED LOOPS                           |                             |   6206 |        |   6206 |00:00:01.79 |     158K| 
386.|  20 |     NESTED LOOPS                          |                             |   6206 |      1 |   6206 |00:00:01.79 |     151K| 
387.|  21 |      NESTED LOOPS                         |                             |   6206 |      1 |   6206 |00:00:01.79 |     145K| 
388.|  22 |       NESTED LOOPS                        |                             |   6206 |      5 |   6206 |00:00:01.79 |     128K| 
389.|  23 |        NESTED LOOPS                       |                             |   6206 |      1 |   6206 |00:00:00.09 |     103K| 
390.|* 24 |         INDEX RANGE SCAN                  | OPT_BUS_UNIT_FDIM_UX2       |   6206 |      1 |   6206 |00:00:00.01 |    6206 | 
391.|  25 |         PARTITION LIST ALL                |                             |   6206 |      1 |   6206 |00:00:00.09 |   97324 | 
392.|* 26 |          TABLE ACCESS BY LOCAL INDEX ROWID| OPT_PRMTN_FDIM              |  49648 |      1 |   6206 |00:00:00.09 |   97324 | 
393.|* 27 |           INDEX RANGE SCAN                | OPT_PRMTN_FDIM_NX3          |  49648 |      4 |   6206 |00:00:00.08 |   86887 | 
394.|  28 |        TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_PROD_FLTR_LKP     |   6206 |     39 |   6206 |00:00:01.69 |   24825 | 
395.|* 29 |         INDEX RANGE SCAN                  | OPT_PRMTN_PROD_FLTR_LKP_NX1 |   6206 |      3 |   6206 |00:00:01.53 |   18618 | 
396.|* 30 |       TABLE ACCESS BY GLOBAL INDEX ROWID  | OPT_ACCT_FDIM               |   6206 |      1 |   6206 |00:00:00.01 |   17241 | 
397.|* 31 |        INDEX UNIQUE SCAN                  | OPT_ACCT_FDIM_PK            |   6206 |      1 |   6206 |00:00:00.01 |   11035 | 
398.|* 32 |      INDEX UNIQUE SCAN                    | OPT_CAL_MASTR_DIM_PK        |   6206 |      1 |   6206 |00:00:00.01 |    6211 | 
399.|* 33 |     TABLE ACCESS BY INDEX ROWID           | OPT_CAL_MASTR_DIM           |   6206 |      1 |   6206 |00:00:00.01 |    6206 | 
400.------------------------------------------------------------------------------------------------------------------------------------ 
401. 
402.Predicate Information (identified by operation id): 
403.--------------------------------------------------- 
404. 
405.   2 - filter( IS NOT NULL) 
406.   6 - access("T2083114"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID" AND "T2083114"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID")  407.  10 - access(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE - 0  408.              "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE - 0  409.              "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR  410.              COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T208  411.              1 - 1900001746' OR "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='  412.              "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE  413.              "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY - CHILE - 006  414.              "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA DISTRIBUCION - CHILE  415.              "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. - CHI  416.              "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR -  417.              "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 20  418.              "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE - 0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EURO  419.       filter("T2084525"."ACCT_LONG_NAME" IS NOT NULL)  420.  12 - access("T2083056"."BUS_UNIT_NAME"='Chile')  421.  14 - filter("T2083056"."BUS_UNIT_SKID"="T2083114"."BUS_UNIT_SKID")  422.  15 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE"  423.              CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" E  424.              "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='  425.  16 - access("T2083114"."PRMTN_SKID"="T2083424"."PRMTN_SKID" AND "T2083114"."BUS_UNIT_SKID"="T2083424"."BUS_UNIT_SKID")  426.  17 - access("T2083114"."DATE_SKID"="T2083357"."CAL_MASTR_SKID")  427.  18 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11')  428.  24 - access("T2083056"."BUS_UNIT_NAME"='Chile')  429.  26 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE"  430.              CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" E  431.              "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='  432.  27 - access("T2083424"."PRMTN_LONG_NAME"=:B1)  433.  29 - access("T2083424"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID" AND "T2083424"."PRMTN_SKID"="T2083698"."PRMTN_SKID")  434.       filter("T2083056"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID")  435.  30 - filter(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE - 0  436.              "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE - 0  437.              "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR  438.              COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T208  439.              1 - 1900001746' OR "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='  440.              "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE  441.              "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY - CHILE - 006  442.              "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA DISTRIBUCION - CHILE  443.              "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. - CHI  444.              "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR -  445.              "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 20  446.              "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE - 0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EURO  447.  31 - access("T2083698"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID" AND "T2083698"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID")  448.  32 - access("T2083357"."CAL_MASTR_SKID"="T2083698"."DATE_SKID")  449.  33 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11')  450. 
451. 
452.109 rows selected. 
453. 
454.正如你可以從執行計劃中看到的那樣，這個SQL能立馬返回數據，返回1324行數據不到20秒就可以完成。 
455. 
456.希望本案例能對你有所幫助 
457. 
458.刪除 EXTENDED STATISTICS  
459. 
460.exec DBMS_STATS.DROP_EXTENDED_STATS (USER,'OPT_ACTVY_FCT','(BUS_UNIT_SKID, ACCT_PRMTN_SKID)');

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]