文章內容

一次使用 Extended Statistics 優化SQL案例

發布時間: 2012/8/23 17:33:49

從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.linuxidc.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)');

本文出自：億恩科技【www.endtimedelusion.com】

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

上一篇 >> SQL調優之關注filter信息
下一篇 >> 在WPS表格中輸入平方和立方符號

服務器租用

服務器托管

機柜批發

云服務器

建站俠

空間/域名

安全保姆

幫助類別

幫助中心

文章內容

一次使用 Extended Statistics 優化SQL案例

同類文章

億恩公告

在線客服