文章內(nèi)容

一次使用 Extended Statistics 優(yōu)化SQL案例

發(fā)布時間: 2012/8/24 17:31:10

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

1.OBIEE終端用戶發(fā)來郵件說某某報表慢(跑了30分鐘還不出結(jié)果)，請求DBA調(diào)查。通過和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千多萬數(shù)據(jù)，1.5G 現(xiàn)在來看看這個SQL的執(zhí)行計劃：
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 它走的是全表掃描并且優(yōu)化器認(rèn)為它只返回39行數(shù)據(jù)，www.linuxidc.com 那么問題可能出在這里了于是創(chuàng)建如下索引
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.關(guān)于為什么我要這樣創(chuàng)建索引，這里就不說了，如果不明白的請看我前面博客，創(chuàng)建索引之后 SQL能在4分鐘以內(nèi)跑完，下面是這個SQL的特殊執(zhí)行計劃
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分以內(nèi)跑完，但是大家注意觀察上面執(zhí)行計劃，ID=11這一步，CBO認(rèn)為它只返回5行數(shù)據(jù)，但是實際卻是它會返回11248行數(shù)據(jù)，正是由于
312.這里CBO計算出錯，導(dǎo)致后面的索引掃描高達(dá)上千萬次，所以有必要糾正這一步。因為這個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的的執(zhí)行計劃更改如下：
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.正如你可以從執(zhí)行計劃中看到的那樣，這個SQL能立馬返回數(shù)據(jù)，返回1324行數(shù)據(jù)不到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)');