一次使用 Extended Statistics 优化SQL案例

从Oracle 11g开始，Oracle提供了 Extended Statistics 新特征，本案例就恰好利用了这个新特征。

OBIEE终端用户发来邮件说某某报表慢(跑了30分钟还不出结果)，请求DBA调查。通过和OBIEE的人合作，找到报表的SQL如下： select sum(T2083114.MANUL_COST_OVRRD_AMT) as c1, sum(nvl(T2083114.REVSD_VAR_ESTMT_COST_AMT , 0)) as c2, T2084525.ACCT_LONG_NAME as c3, T2084525.NAME as c4, T2083424.PRMTN_NAME as c5, T2083424.PRMTN_ID as c6, case when case when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end is null then 'Private' else case when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end end as c7, T2083424.PRMTN_STTUS_CODE as c8, T2083424.APPRV_BY_DESC as c9, T2083424.APPRV_STTUS_CODE as c10, T2083424.AUTO_UPDT_GTIN_IND as c11, T2083424.CREAT_DATE as c12, T2083424.PGM_START_DATE as c13, T2083424.PGM_END_DATE as c14, nvl(case when T2083424.PRMTN_STTUS_CODE = 'Confirmed' then cast(( TRUNC( TO_DATE('2011-06-07' , 'YYYY-MM-DD') ) - TRUNC( T2083424.PGM_END_DATE ) ) as VARCHAR ( 10 ) ) end , '') as c15, T2083424.PRMTN_STOP_DATE as c16, T2083424.SHPMT_START_DATE as c17, T2083424.SHPMT_END_DATE as c18, T2083424.CNBLN_WK_CNT as c19, T2083424.ACTVY_DETL_POP as c20, T2083424.CMMNT_DESC as c21, T2083424.PRMTN_AVG_POP as c22, T2084525.CHANL_TYPE_DESC as c23, T2083424.PRMTN_SKID as c24 from ADWG_OPTIMA_LA11.OPT_ACCT_FDIM T2084525 /* OPT_ACCT_PRMTN_FDIM */ , ADWG_OPTIMA_LA11.OPT_BUS_UNIT_FDIM T2083056, ADWG_OPTIMA_LA11.OPT_CAL_MASTR_DIM T2083357 /* OPT_CAL_MASTR_DIM01 */ , ADWG_OPTIMA_LA11.OPT_PRMTN_FDIM T2083424, ADWG_OPTIMA_LA11.OPT_ACTVY_FCT T2083114 where ( T2083056.BUS_UNIT_SKID = T2083114.BUS_UNIT_SKID and T2083114.BUS_UNIT_SKID = T2084525.BUS_UNIT_SKID and T2083114.DATE_SKID = T2083357.CAL_MASTR_SKID and T2083114.BUS_UNIT_SKID = T2083424.BUS_UNIT_SKID and T2083114.PRMTN_SKID = T2083424.PRMTN_SKID and T2083056.BUS_UNIT_NAME = 'Chile' and T2083114.ACCT_PRMTN_SKID = T2084525.ACCT_SKID and T2083357.FISC_YR_ABBR_NAME = 'FY10/11' and T2084525.ACCT_LONG_NAME is not null and (case when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end in ('Alternate BDF', 'Corporate', 'Private')) and (T2084525.ACCT_LONG_NAME in ('ADELCO - CHILE - 0066009018', 'ALIMENTOS FRUNA - CHILE - 0066009049', 'CENCOSUD - CHILE - 0066009007', 'COMERCIAL ALVI - CHILE - 0066009070', 'D&S - CHILE - 0066009008', 'DIPAC - CHILE - 0066009024', 'DIST. COMERCIAL - CHILE - 0066009087', 'DISTRIBUCION LAGOS S.A. - CHILE - 2001146505', 'ECOMMERCE ESCALA 1 - 1900001746', 'EMILIO SANDOVAL - CHILE - 2000402293', 'F. AHUMADA - CHILE - 0066009023', 'FALABELLA - CHILE - 2000406971', 'FRANCISCO LEYTON - CHILE - 0066009142', 'MAICAO - CHILE - 0066009135', 'MARGARITA UAUY - CHILE - 0066009146', 'PREUNIC - CHILE - 0066009032', 'PRISA DISTRIBUCION - CHILE - 2001419970', 'RABIE - CHILE - 0066009015', 'S Y B FARMACEUTICA S.A. - CHILE - 2000432938', 'SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967', 'SOCOFAR - CHILE - 0066009028', 'SODIMAC - CHILE - 2000402358', 'SOUTHERN CROSS - CHILE - 2002135799', 'SUPERM. MONSERRAT - CHILE - 0066009120', 'TELEMERCADOS EUROPA - CHILE - 0066009044')) and T2083424.PRMTN_LONG_NAME in (select distinct T2083424.PRMTN_LONG_NAME as c1 from ADWG_OPTIMA_LA11.OPT_ACCT_FDIM T2084525 /* OPT_ACCT_PRMTN_FDIM */ , ADWG_OPTIMA_LA11.OPT_BUS_UNIT_FDIM T2083056, ADWG_OPTIMA_LA11.OPT_CAL_MASTR_DIM T2083357 /* OPT_CAL_MASTR_DIM01 */ , ADWG_OPTIMA_LA11.OPT_PRMTN_FDIM T2083424, ADWG_OPTIMA_LA11.OPT_PRMTN_PROD_FLTR_LKP T2083698 where ( T2083056.BUS_UNIT_SKID = T2083698.BUS_UNIT_SKID and T2083357.CAL_MASTR_SKID = T2083698.DATE_SKID and T2083698.ACCT_PRMTN_SKID = T2084525.ACCT_SKID and T2083424.PRMTN_SKID = T2083698.PRMTN_SKID and T2083424.BUS_UNIT_SKID = T2083698.BUS_UNIT_SKID and T2083056.BUS_UNIT_NAME = 'Chile' and T2083357.FISC_YR_ABBR_NAME = 'FY10/11' and T2083698.BUS_UNIT_SKID = T2084525.BUS_UNIT_SKID and (case when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end in ('Alternate BDF', 'Corporate', 'Private')) and (T2084525.ACCT_LONG_NAME in ('ADELCO - CHILE - 0066009018', 'ALIMENTOS FRUNA - CHILE - 0066009049', 'CENCOSUD - CHILE - 0066009007', 'COMERCIAL ALVI - CHILE - 0066009070', 'D&S - CHILE - 0066009008', 'DIPAC - CHILE - 0066009024', 'DIST. COMERCIAL - CHILE - 0066009087', 'DISTRIBUCION LAGOS S.A. - CHILE - 2001146505', 'ECOMMERCE ESCALA 1 - 1900001746', 'EMILIO SANDOVAL - CHILE - 2000402293', 'F. AHUMADA - CHILE - 0066009023', 'FALABELLA - CHILE - 2000406971', 'FRANCISCO LEYTON - CHILE - 0066009142', 'MAICAO - CHILE - 0066009135', 'MARGARITA UAUY - CHILE - 0066009146', 'PREUNIC - CHILE - 0066009032', 'PRISA DISTRIBUCION - CHILE - 2001419970', 'RABIE - CHILE - 0066009015', 'S Y B FARMACEUTICA S.A. - CHILE - 2000432938', 'SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967', 'SOCOFAR - CHILE - 0066009028', 'SODIMAC - CHILE - 2000402358', 'SOUTHERN CROSS - CHILE - 2002135799', 'SUPERM. MONSERRAT - CHILE - 0066009120', 'TELEMERCADOS EUROPA - CHILE - 0066009044')) ) ) ) group by T2083424.PRMTN_SKID, T2083424.PRMTN_ID, T2083424.PRMTN_NAME, T2083424.SHPMT_END_DATE, T2083424.SHPMT_START_DATE, T2083424.PRMTN_STTUS_CODE, T2083424.APPRV_STTUS_CODE, T2083424.CMMNT_DESC, T2083424.PGM_START_DATE, T2083424.PGM_END_DATE, T2083424.CREAT_DATE, T2083424.APPRV_BY_DESC, T2083424.AUTO_UPDT_GTIN_IND, T2083424.PRMTN_STOP_DATE, T2083424.ACTVY_DETL_POP, T2083424.CNBLN_WK_CNT, T2083424.PRMTN_AVG_POP, T2084525.NAME, T2084525.CHANL_TYPE_DESC, T2084525.ACCT_LONG_NAME, case when case when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end is null then 'Private' else case when T2083424.CORP_PRMTN_TYPE_CODE = 'Target Account' then 'Corporate' else T2083424.CORP_PRMTN_TYPE_CODE end end , nvl(case when T2083424.PRMTN_STTUS_CODE = 'Confirmed' then cast(( TRUNC( TO_DATE('2011-06-07' , 'YYYY-MM-DD') ) - TRUNC( T2083424.PGM_END_DATE ) ) as VARCHAR ( 10 ) ) end , '') order by c24, c3; 这个SQL要用到的表信息如下 OWNER TABLE_NAME Size(Mb) PARTITIONED DEGREE NUM_ROWS -------------------- ------------------------------ ---------- -------------------- ---------- ------------- ADWG_OPTIMA_LA11 *OPT_BUS_UNIT_FDIM .001037598 NO 1 16 ADWG_OPTIMA_LA11 *OPT_CAL_MASTR_DIM 38.1284523 NO 1 37435 ADWG_OPTIMA_LA11 OPT_CAL_MASTR_DIM 38.1284523 NO 1 37435 ADWG_OPTIMA_LA11 *OPT_PRMTN_FDIM 74.6365929 YES 1 52140 ADWG_OPTIMA_LA11 OPT_PRMTN_FDIM 74.6365929 YES 1 52140 ADWG_OPTIMA_LA11 OPT_ACTVY_FCT 19.3430614 YES 1 157230 ADWG_OPTIMA_LA11 *OPT_ACCT_FDIM 36.6709185 YES 2 95415 ADWG_OPTIMA_LA11 OPT_ACCT_FDIM 36.6709185 YES 2 95415 ADWG_OPTIMA_LA11 OPT_PRMTN_PROD_FLTR_LKP 1523.87207 YES 2 30148975 带*表示它用到了索引 那么这里 只有表OPT_PRMTN_PROD_FLTR_LKP是大表，它有3千多万数据，1.5G 现在来看看这个SQL的执行计划： SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT --------------------------------------------------------------------------------------------------------------------------------------------------- Plan hash value: 3566115627 ------------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 352 | 1551 (17)| 00:00:07 | | | | 1 | SORT GROUP BY | | 1 | 352 | 1551 (17)| 00:00:07 | | | | 2 | VIEW | VM_NWVW_2 | 1 | 352 | 1550 (17)| 00:00:07 | | | | 3 | HASH UNIQUE | | 1 | 652 | 1550 (17)| 00:00:07 | | | | 4 | NESTED LOOPS | | | | | | | | | 5 | NESTED LOOPS | | 1 | 652 | 1549 (17)| 00:00:07 | | | | 6 | NESTED LOOPS | | 1 | 639 | 1548 (17)| 00:00:07 | | | | 7 | NESTED LOOPS | | 2 | 1180 | 1546 (17)| 00:00:07 | | | | 8 | NESTED LOOPS | | 1 | 568 | 130 (5)| 00:00:01 | | | | 9 | NESTED LOOPS | | 1 | 509 | 109 (6)| 00:00:01 | | | | 10 | NESTED LOOPS | | 1 | 484 | 108 (6)| 00:00:01 | | | |* 11 | HASH JOIN | | 5 | 830 | 103 (6)| 00:00:01 | | | | 12 | PARTITION LIST SUBQUERY | | 47 | 4089 | 82 (3)| 00:00:01 |KEY(SQ)|KEY(SQ)| | 13 | INLIST ITERATOR | | | | | | | | | 14 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM | 47 | 4089 | 82 (3)| 00:00:01 |KEY(SQ)|KEY(SQ)| |* 15 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 47 | | 43 (5)| 00:00:01 |KEY(SQ)|KEY(SQ)| | 16 | NESTED LOOPS | | 10482 | 808K| 20 (15)| 00:00:01 | | | | 17 | NESTED LOOPS | | 1 | 40 | 2 (0)| 00:00:01 | | | |* 18 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 26 | 1 (0)| 00:00:01 | | | |* 19 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 14 | 1 (0)| 00:00:01 | | | | 20 | PARTITION LIST ITERATOR | | 10482 | 1699K| 18 (17)| 00:00:01 | KEY | KEY | |* 21 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 10482 | 1699K| 18 (17)| 00:00:01 | KEY | KEY | |* 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 318 | 1 (0)| 00:00:01 | ROWID | ROWID | |* 23 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | | |* 24 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 25 | 1 (0)| 00:00:01 | | | |* 25 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | | | 26 | PARTITION LIST ALL | | 1 | 59 | 21 (0)| 00:00:01 | 1 | 17 | |* 27 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_PRMTN_FDIM | 1 | 59 | 21 (0)| 00:00:01 | 1 | 17 | |* 28 | INDEX RANGE SCAN | OPT_PRMTN_FDIM_NX3 | 4 | | 17 (0)| 00:00:01 | 1 | 17 | | 29 | PARTITION LIST ITERATOR | | 39 | 858 | 1416 (18)| 00:00:07 | KEY | KEY | |* 30 | TABLE ACCESS FULL | OPT_PRMTN_PROD_FLTR_LKP | 39 | 858 | 1416 (18)| 00:00:07 | KEY | KEY | |* 31 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 49 | 1 (0)| 00:00:01 | ROWID | ROWID | |* 32 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | | |* 33 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | | |* 34 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 (0)| 00:00:01 | | | ------------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 11 - access("T2083114"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID" AND "T2083114"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID") 15 - access("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE - 0066009049' OR "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE - 0066009070' OR "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMMERCE ESCALA 1 - 1900001746' OR "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE - 0066009023' OR "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY - CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA DISTRIBUCION - CHILE - 2001419970' OR "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. - CHILE - 2000432938' OR "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE - 0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EUROPA - CHILE - 0066009044') filter("T2084525"."ACCT_LONG_NAME" IS NOT NULL) 18 - access("T2083056"."BUS_UNIT_NAME"='Chile') 19 - access("T2083056"."BUS_UNIT_NAME"='Chile') 21 - filter("T2083056"."BUS_UNIT_SKID"="T2083114"."BUS_UNIT_SKID") 22 - filter(CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Alternate BDF' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Corporate' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private') 23 - access("T2083114"."PRMTN_SKID"="T2083424"."PRMTN_SKID" AND "T2083114"."BUS_UNIT_SKID"="T2083424"."BUS_UNIT_SKID") 24 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11') 25 - access("T2083114"."DATE_SKID"="T2083357"."CAL_MASTR_SKID") 27 - filter(CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Alternate BDF' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Corporate' OR CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private') 28 - access("T2083424"."PRMTN_LONG_NAME"="T2083424"."PRMTN_LONG_NAME") 30 - filter("T2083056"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID" AND "T2083424"."PRMTN_SKID"="T2083698"."PRMTN_SKID" AND "T2083424"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID") 31 - filter("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE - 0066009049' OR "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE - 0066009070' OR "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMMERCE ESCALA 1 - 1900001746' OR "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE - 0066009023' OR "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY - CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA DISTRIBUCION - CHILE - 2001419970' OR "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. - CHILE - 2000432938' OR "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE - 0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EUROPA - CHILE - 0066009044') 32 - access("T2083698"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID" AND "T2083698"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID") 33 - access("T2083357"."CAL_MASTR_SKID"="T2083698"."DATE_SKID") 34 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11') 95 rows selected. 注意观察 ID=30 它走的是全表扫描 并且优化器认为它只返回39行数据，那么问题可能出在这里了 于是创建如下索引 SQL> create index OPT_PRMTN_PROD_FLTR_LKP_NX1 ON OPT_PRMTN_PROD_FLTR_LKP(BUS_UNIT_SKID,PRMTN_SKID) nologging parallel ; Index created. Elapsed: 00:00:33.04 关于为什么我要这样创建索引，这里就不说了，如果不明白的请看我前面博客，创建索引之后 SQL能在4分钟以内跑完，下面是这个SQL的特殊执行计划 Plan hash value: 1310530159 ------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | ------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1324 |00:02:42.23 | | 1 | SORT GROUP BY | | 1 | 1 | 1324 |00:02:42.23 | | 2 | VIEW | VM_NWVW_2 | 1 | 1 | 6808 |00:02:42.18 | | 3 | HASH UNIQUE | | 1 | 1 | 6808 |00:02:42.18 | | 4 | NESTED LOOPS | | 1 | | 5220K|00:02:21.06 | | 5 | NESTED LOOPS | | 1 | 1 | 5220K|00:02:00.18 | | 6 | NESTED LOOPS | | 1 | 1 | 5220K|00:01:49.74 | | 7 | NESTED LOOPS | | 1 | 2 | 5220K|00:01:18.42 | | 8 | NESTED LOOPS | | 1 | 1 | 6808 |00:00:01.62 | | 9 | NESTED LOOPS | | 1 | 1 | 6808 |00:00:00.54 | | 10 | NESTED LOOPS | | 1 | 1 | 11248 |00:00:00.40 | |* 11 | HASH JOIN | | 1 | 5 | 11248 |00:00:00.07 | | 12 | PARTITION LIST SUBQUERY | | 1 | 47 | 25 |00:00:00.01 | | 13 | INLIST ITERATOR | | 1 | | 25 |00:00:00.01 | | 14 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM | 25 | 47 | 25 |00:00:00.01 | |* 15 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 25 | 47 | 25 |00:00:00.01 | | 16 | NESTED LOOPS | | 1 | 10482 | 12788 |00:00:00.03 | | 17 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | |* 18 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 1 | 1 |00:00:00.01 | |* 19 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 1 | 1 |00:00:00.01 | | 20 | PARTITION LIST ITERATOR | | 1 | 10482 | 12788 |00:00:00.03 | |* 21 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 1 | 10482 | 12788 |00:00:00.03 | |* 22 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 11248 | 1 | 11248 |00:00:00.31 | |* 23 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 11248 | 1 | 11248 |00:00:00.12 | |* 24 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 11248 | 1 | 6808 |00:00:00.14 | |* 25 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 11248 | 1 | 11248 |00:00:00.05 | | 26 | PARTITION LIST ALL | | 6808 | 1 | 6808 |00:00:01.08 | |* 27 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_PRMTN_FDIM | 115K| 1 | 6808 |00:00:01.05 | |* 28 | INDEX RANGE SCAN | OPT_PRMTN_FDIM_NX3 | 115K| 4 | 6808 |00:00:00.78 | | 29 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_PROD_FLTR_LKP | 6808 | 39 | 5220K|00:01:19.79 | |* 30 | INDEX RANGE SCAN | OPT_PRMTN_PROD_FLTR_LKP_NX1 | 6808 | 3 | 5220K|00:00:43.96 | |* 31 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 5220K| 1 | 5220K|00:00:23.79 | |* 32 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 5220K| 1 | 5220K|00:00:08.38 | |* 33 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 5220K| 1 | 5220K|00:00:07.58 | |* 34 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 5220K| 1 | 5220K|00:00:17.28 | ------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 11 - access("T2083114"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID" AND "T2083114"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID") 15 - access(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHI "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHI 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. CO "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMM SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE - 0066009023' OR "T2084525"."A "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHI CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME" "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"." "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSER EUROPA - CHILE - 0066009044')) filter("T2084525"."ACCT_LONG_NAME" IS NOT NULL) 18 - access("T2083056"."BUS_UNIT_NAME"='Chile') 19 - access("T2083056"."BUS_UNIT_NAME"='Chile') 21 - filter("T2083056"."BUS_UNIT_SKID"="T2083114"."BUS_UNIT_SKID") 22 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_ "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private')) 23 - access("T2083114"."PRMTN_SKID"="T2083424"."PRMTN_SKID" AND "T2083114"."BUS_UNIT_SKID"="T2083424"."BUS_UNIT_SKID") 24 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11') 25 - access("T2083114"."DATE_SKID"="T2083357"."CAL_MASTR_SKID") 27 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_ "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END ='Private')) 28 - access("T2083424"."PRMTN_LONG_NAME"="T2083424"."PRMTN_LONG_NAME") 30 - access("T2083424"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID" AND "T2083424"."PRMTN_SKID"="T2083698"."PRMTN_SKID") filter("T2083056"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID") 31 - filter(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHI "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHI 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR "T2084525"."ACCT_LONG_NAME"='DIST. CO "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T2084525"."ACCT_LONG_NAME"='ECOMM SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"='F. AHUMADA - CHILE - 0066009023' OR "T2084525"."A "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE - 0066009142' OR "T2084525"."ACCT_LONG_NAME"='MAICAO - CHI CHILE - 0066009146' OR "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME" "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - CHILE - 0066009028' OR "T2084525"." "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 2002135799' OR "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSER EUROPA - CHILE - 0066009044')) 32 - access("T2083698"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID" AND "T2083698"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID") 33 - access("T2083357"."CAL_MASTR_SKID"="T2083698"."DATE_SKID") 34 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11') 107 rows selected. 虽然这个SQL能在4分以内跑完，但是大家注意观察上面执行计划，ID=11这一步，CBO认为它只返回5行数据，但是实际却是它会返回11248行数据，正是由于 这里CBO计算出错，导致后面的索引扫描高达上千万次，所以有必要纠正这一步。因为这个SQL是OBIEE的，我不能更改SQL，也不能(至少很难)加HINT 所以我选择用11g 新特征----EXTENDED STATISTICS. 11步骤是做HASH JOIN，而且这一步CBO选择了2列作为HASH KEY，所以分别对2个表的2个HASH KEY收集EXTENDED STATISTICS SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'OPT_ACCT_FDIM', '(BUS_UNIT_SKID, ACCT_SKID)') FROM DUAL; DBMS_STATS.CREATE_EXTENDED_STATS(USER,'OPT_ACCT_FDIM','(BUS_UNIT_SKID,ACCT_SKID)') ------------------------------------------------------------------------------------------------------------------ SYS_STUJ8OD#X2IPA_B9_CH00B046T SQL> SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'OPT_ACTVY_FCT', '(BUS_UNIT_SKID, ACCT_PRMTN_SKID)') FROM DUAL; DBMS_STATS.CREATE_EXTENDED_STATS(USER,'OPT_ACTVY_FCT','(BUS_UNIT_SKID,ACCT_PRMTN_SKID)') ------------------------------------------------------------------------------------------------------------------ SYS_STU#CVQNKK5CCM0W2XEQWSRXSM SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWG_OPTIMA_LA11', 3 tabname => 'OPT_ACCT_FDIM', 4 estimate_percent => 20, 5 method_opt => 'for all columns size auto', 6 degree => 6, 7 granularity => 'ALL', 8 cascade=>TRUE 9 ); 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:00:57.76 SQL> BEGIN 2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ADWG_OPTIMA_LA11', 3 tabname => 'OPT_ACTVY_FCT', 4 estimate_percent => 20, 5 method_opt => 'for all columns size auto', 6 degree => 6, 7 granularity => 'ALL', 8 cascade=>TRUE 9 ); 10 END; 11 / PL/SQL procedure successfully completed. Elapsed: 00:01:15.10 收集EXTENDED STATISTICS之后，SQL的的执行计划更改如下： ------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1324 |00:00:01.85 | 210K| | 1 | SORT GROUP BY | | 1 | 1 | 1324 |00:00:01.85 | 210K| |* 2 | FILTER | | 1 | | 6808 |00:00:01.84 | 210K| | 3 | NESTED LOOPS | | 1 | | 6808 |00:00:00.04 | 52722 | | 4 | NESTED LOOPS | | 1 | 4 | 11248 |00:00:00.03 | 41474 | | 5 | NESTED LOOPS | | 1 | 12 | 11248 |00:00:00.02 | 30247 | |* 6 | HASH JOIN | | 1 | 403 | 11248 |00:00:00.01 | 172 | | 7 | PARTITION LIST SUBQUERY | | 1 | 47 | 25 |00:00:00.01 | 50 | | 8 | INLIST ITERATOR | | 1 | | 25 |00:00:00.01 | 47 | | 9 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_ACCT_FDIM | 25 | 47 | 25 |00:00:00.01 | 47 | |* 10 | INDEX RANGE SCAN | OPT_ACCT_FDIM_NX2 | 25 | 47 | 25 |00:00:00.01 | 27 | | 11 | NESTED LOOPS | | 1 | 10508 | 12788 |00:00:00.01 | 122 | |* 12 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 1 | 1 | 1 |00:00:00.01 | 1 | 0 | | 13 | PARTITION LIST ITERATOR | | 1 | 10508 | 12788 |00:00:00.01 | 121 | |* 14 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 1 | 10508 | 12788 |00:00:00.01 | 121 | |* 15 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_FDIM | 11248 | 1 | 11248 |00:00:00.01 | 30075 | |* 16 | INDEX UNIQUE SCAN | OPT_PRMTN_FDIM_PK | 11248 | 1 | 11248 |00:00:00.01 | 11250 | |* 17 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 11248 | 1 | 11248 |00:00:00.01 | 11227 | |* 18 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 11248 | 1 | 6808 |00:00:00.01 | 11248 | | 19 | NESTED LOOPS | | 6206 | | 6206 |00:00:01.79 | 158K| | 20 | NESTED LOOPS | | 6206 | 1 | 6206 |00:00:01.79 | 151K| | 21 | NESTED LOOPS | | 6206 | 1 | 6206 |00:00:01.79 | 145K| | 22 | NESTED LOOPS | | 6206 | 5 | 6206 |00:00:01.79 | 128K| | 23 | NESTED LOOPS | | 6206 | 1 | 6206 |00:00:00.09 | 103K| |* 24 | INDEX RANGE SCAN | OPT_BUS_UNIT_FDIM_UX2 | 6206 | 1 | 6206 |00:00:00.01 | 6206 | | 25 | PARTITION LIST ALL | | 6206 | 1 | 6206 |00:00:00.09 | 97324 | |* 26 | TABLE ACCESS BY LOCAL INDEX ROWID| OPT_PRMTN_FDIM | 49648 | 1 | 6206 |00:00:00.09 | 97324 | |* 27 | INDEX RANGE SCAN | OPT_PRMTN_FDIM_NX3 | 49648 | 4 | 6206 |00:00:00.08 | 86887 | | 28 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_PROD_FLTR_LKP | 6206 | 39 | 6206 |00:00:01.69 | 24825 | |* 29 | INDEX RANGE SCAN | OPT_PRMTN_PROD_FLTR_LKP_NX1 | 6206 | 3 | 6206 |00:00:01.53 | 18618 | |* 30 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 6206 | 1 | 6206 |00:00:00.01 | 17241 | |* 31 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 6206 | 1 | 6206 |00:00:00.01 | 11035 | |* 32 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 6206 | 1 | 6206 |00:00:00.01 | 6211 | |* 33 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 6206 | 1 | 6206 |00:00:00.01 | 6206 | ------------------------------------------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter( IS NOT NULL) 6 - access("T2083114"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID" AND "T2083114"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID") 10 - access(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE - 0 "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE - 0 "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T208 1 - 1900001746' OR "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"=' "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY - CHILE - 006 "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA DISTRIBUCION - CHILE "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. - CHI "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 20 "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE - 0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EURO filter("T2084525"."ACCT_LONG_NAME" IS NOT NULL) 12 - access("T2083056"."BUS_UNIT_NAME"='Chile') 14 - filter("T2083056"."BUS_UNIT_SKID"="T2083114"."BUS_UNIT_SKID") 15 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" E "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END =' 16 - access("T2083114"."PRMTN_SKID"="T2083424"."PRMTN_SKID" AND "T2083114"."BUS_UNIT_SKID"="T2083424"."BUS_UNIT_SKID") 17 - access("T2083114"."DATE_SKID"="T2083357"."CAL_MASTR_SKID") 18 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11') 24 - access("T2083056"."BUS_UNIT_NAME"='Chile') 26 - filter((CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" CASE "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" E "T2083424"."CORP_PRMTN_TYPE_CODE" WHEN 'Target Account' THEN 'Corporate' ELSE "T2083424"."CORP_PRMTN_TYPE_CODE" END =' 27 - access("T2083424"."PRMTN_LONG_NAME"=:B1) 29 - access("T2083424"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID" AND "T2083424"."PRMTN_SKID"="T2083698"."PRMTN_SKID") filter("T2083056"."BUS_UNIT_SKID"="T2083698"."BUS_UNIT_SKID") 30 - filter(("T2084525"."ACCT_LONG_NAME"='ADELCO - CHILE - 0066009018' OR "T2084525"."ACCT_LONG_NAME"='ALIMENTOS FRUNA - CHILE - 0 "T2084525"."ACCT_LONG_NAME"='CENCOSUD - CHILE - 0066009007' OR "T2084525"."ACCT_LONG_NAME"='COMERCIAL ALVI - CHILE - 0 "T2084525"."ACCT_LONG_NAME"='D&S - CHILE - 0066009008' OR "T2084525"."ACCT_LONG_NAME"='DIPAC - CHILE - 0066009024' OR COMERCIAL - CHILE - 0066009087' OR "T2084525"."ACCT_LONG_NAME"='DISTRIBUCION LAGOS S.A. - CHILE - 2001146505' OR "T208 1 - 1900001746' OR "T2084525"."ACCT_LONG_NAME"='EMILIO SANDOVAL - CHILE - 2000402293' OR "T2084525"."ACCT_LONG_NAME"=' "T2084525"."ACCT_LONG_NAME"='FALABELLA - CHILE - 2000406971' OR "T2084525"."ACCT_LONG_NAME"='FRANCISCO LEYTON - CHILE "T2084525"."ACCT_LONG_NAME"='MAICAO - CHILE - 0066009135' OR "T2084525"."ACCT_LONG_NAME"='MARGARITA UAUY - CHILE - 006 "T2084525"."ACCT_LONG_NAME"='PREUNIC - CHILE - 0066009032' OR "T2084525"."ACCT_LONG_NAME"='PRISA DISTRIBUCION - CHILE "T2084525"."ACCT_LONG_NAME"='RABIE - CHILE - 0066009015' OR "T2084525"."ACCT_LONG_NAME"='S Y B FARMACEUTICA S.A. - CHI "T2084525"."ACCT_LONG_NAME"='SOC. INV. LA MUNDIAL LTDA - CHILE - 2001270967' OR "T2084525"."ACCT_LONG_NAME"='SOCOFAR - "T2084525"."ACCT_LONG_NAME"='SODIMAC - CHILE - 2000402358' OR "T2084525"."ACCT_LONG_NAME"='SOUTHERN CROSS - CHILE - 20 "T2084525"."ACCT_LONG_NAME"='SUPERM. MONSERRAT - CHILE - 0066009120' OR "T2084525"."ACCT_LONG_NAME"='TELEMERCADOS EURO 31 - access("T2083698"."ACCT_PRMTN_SKID"="T2084525"."ACCT_SKID" AND "T2083698"."BUS_UNIT_SKID"="T2084525"."BUS_UNIT_SKID") 32 - access("T2083357"."CAL_MASTR_SKID"="T2083698"."DATE_SKID") 33 - filter("T2083357"."FISC_YR_ABBR_NAME"='FY10/11') 109 rows selected. 正如你可以从执行计划中看到的那样，这个SQL能立马返回数据，返回1324行数据不到20秒就可以完成。 希望本案例能对你有所帮助 删除 EXTENDED STATISTICS exec DBMS_STATS.DROP_EXTENDED_STATS (USER,'OPT_ACTVY_FCT','(BUS_UNIT_SKID, ACCT_PRMTN_SKID)');

最后

以上就是深情金鱼为你收集整理的一次使用 Extended Statistics 优化SQL案例的全部内容，希望文章能够帮你解决一次使用 Extended Statistics 优化SQL案例所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。