一次使用 Extended Statistics 优化SQL案例
一次使用 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行数据,www.bkjia.com 那么问题可能出在这里了 于是创建如下索引
- 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)');
评论暂时关闭