本案例发生在 2010 年，当时作者罗老师在惠普担任开发 DBA，支撑宝洁公司的数据仓库项目。ETL 开发人员需要帮助调查一个 long running 的 JOB，该 JOB 执行了 7 个小时还没执行完。
数据库环境为 11.1.0.7（RAC，4 节点）。
select * from v$version;
BANNER-------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
数据块大小为 16k。
show parameter db_block_size
NAME TYPE VALUE------------------------------------ --------------------------------- ------db_block_size integer 16384
执行得慢的 JOB 是一个 insert into …select …语句。一般情况下，如果 select 语句跑得快，那么整个 JOB 也就跑得快，因此我们应该把主要精力放在 select 语句上面。select 部分的 SQL 语句如下，这是一个接近 400 行的 SQL（因为 SQL 实在太长，所以没有对 SQL 格式化）。
SELECT ACTVY_SKID,FUND_SKID,PRMTN_SKID,PROD_SKID,DATE_SKID,ACCT_SKID,BUS_UNIT_SKID,FY_DATE_SKID,ESTMT_VAR_COST_AMT,ESTMT_FIXED_COST_AMT,REVSD_ESTMT_VAR_COST_AMT,ACTL_VAR_COST_AMT,ACTL_FIXED_COST_AMT,COST_PLAN_AMT,COST_CMMT_AMT,COST_BOOK_AMT,ESTMT_COST_OVRRD_AMT,LA_TOT_BOOK_AMT,MANUL_COST_OVRRD_AMT,ACTL_COST_AMTFROM (SELECT ACTVY_SKID,FUND_SKID,PROD_SKID,PRMTN_SKID,DATE_SKID,ACCT_SKID,BUS_UNIT_SKID,FY_DATE_SKID,ESTMT_VAR_COST_AMT,ESTMT_FIXED_COST_AMT,REVSD_ESTMT_VAR_COST_AMT,0 as ACTL_COST_AMT,ACTL_VAR_COST_AMT,ACTL_FIXED_COST_AMT,MANUL_COST_OVRRD_AMT,ESTMT_COST_OVRRD_AMT,COST_BOOK_AMT,-- Updated by Luke for QC3369-- If the committed amount on Activity level <0 then return 0(CASE WHEN SUM(ESTMT_COST_OVRRD_AMT - ACTL_VAR_COST_AMT -ACTL_FIXED_COST_AMT) OVER(PARTITION BY ACTVY_SKID) < 0 THEN 0ELSE COST_CMMT_AMT END) AS COST_CMMT_AMT,-- Updated by Luke for QC3369(CASE WHEN SUM(ESTMT_COST_OVRRD_AMT - ACTL_VAR_COST_AMT -ACTL_FIXED_COST_AMT) OVER(PARTITION BY ACTVY_SKID) < 0 THEN 0ELSE COST_PLAN_AMT END) AS COST_PLAN_AMT,LA_TOT_BOOK_AMTFROM (SELECT ACTVY_SKID,FUND_SKID,PROD_SKID,PRMTN_SKID,DATE_SKID,ACCT_SKID,BUS_UNIT_SKID,FY_DATE_SKID,ESTMT_VAR_COST_AMT,ESTMT_FIXED_COST_AMT,REVSD_ESTMT_VAR_COST_AMT,ACTL_VAR_COST_AMT,ACTL_FIXED_COST_AMT,MANUL_COST_OVRRD_AMT,(CASE WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'E' THENESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN ESTMT_FIXED_COST_AMT + DECODE(REVSD_BPT_COST_AMT,0,REVSD_ESTMT_VAR_COST_AMT,--Ax Revised Estimated Variable Cost REVSD_BPT_COST_AMT) --BPT Revised CostWHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN MANUL_COST_OVRRD_AMTWHEN ESTMT_COST_IND IS NULL THEN DECODE(CORP_PRMTN_TYPE_CODE,'Annual Agreement',ESTMT_FIXED_COST_AMT + DECODE(REVSD_BPT_COST_AMT,0,REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable CostREVSD_BPT_COST_AMT), --BPT Revised CostESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT) END) AS ESTMT_COST_OVRRD_AMT,(ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT) AS COST_BOOK_AMT,DECODE(PRMTN_STTUS_CODE,'Confirmed',--Estimate Total Cost - Actual Cost--Add the logic of Activity Stop date and Pyment allow IND--For Defect 2913 Luke 2010-5-5(CASE WHEN (ACTVY_STOP_DATE IS NULL OR ACTVY_STOP_DATE > SYSDATE ORNVL(PYMT_ALLWD_STOP_IND, 'N') = 'Y') THEN (CASE WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'E' THEN ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN ESTMT_FIXED_COST_AMT + DECODE(REVSD_BPT_COST_AMT,0,REVSD_ESTMT_VAR_COST_AMT,--Ax Revised Estimated Variable CostREVSD_BPT_COST_AMT) --BPT Revised CostWHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN MANUL_COST_OVRRD_AMTWHEN ESTMT_COST_IND IS NULL THEN DECODE(CORP_PRMTN_TYPE_CODE,'Annual Agreement',ESTMT_FIXED_COST_AMT + DECODE(REVSD_BPT_COST_AMT,0,REVSD_ESTMT_VAR_COST_AMT,--Ax Revised Estimated Variable CostREVSD_BPT_COST_AMT), --BPT Revised CostESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT) END) - (ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT)ELSE 0 END), 0) AS COST_CMMT_AMT,(CASE WHEN (PRMTN_STTUS_CODE IN ('Planned', 'Revised') AND NVL(APPRV_STTUS_CODE, 'Nothing') <> 'Rejected' AND--Add the logic of Activity Stop date and Pyment allow IND--For Defect 2913 Luke 2010-5-5(ACTVY_STOP_DATE IS NULL OR ACTVY_STOP_DATE > SYSDATE OR NVL(PYMT_ALLWD_STOP_IND, 'N') = 'Y'))THEN (CASE WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'E' THEN ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMTWHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN ESTMT_FIXED_COST_AMT + DECODE(REVSD_BPT_COST_AMT,0, REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable Cost REVSD_BPT_COST_AMT) --BPT Revised CostWHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN MANUL_COST_OVRRD_AMT WHEN ESTMT_COST_IND IS NULL THEN DECODE(CORP_PRMTN_TYPE_CODE,'Annual Agreement',ESTMT_FIXED_COST_AMT +DECODE(REVSD_BPT_COST_AMT,0,REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable CostREVSD_BPT_COST_AMT), --BPT Revised CostESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT) END) - (ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT) ELSE 0 END) AS COST_PLAN_AMT,(CASE WHEN MTH_START_DATE > TRUNC(SYSDATE, 'MM') AND PRMTN_STTUS_CODE IN ('Planned', 'Confirmed', 'Revised') THEN (CASE WHEN SUBSTR(ESTMT_COST_IND, 1, 1)= 'E' THEN ESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT WHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'R' THEN ESTMT_FIXED_COST_AMT + DECODE(REVSD_BPT_COST_AMT,0,REVSD_ESTMT_VAR_COST_AMT,--Ax Revised Estimated Variable CostREVSD_BPT_COST_AMT) --BPT Revised CostWHEN SUBSTR(ESTMT_COST_IND, 1, 1) = 'M' THEN MANUL_COST_OVRRD_AMT WHEN ESTMT_COST_IND IS NULL THEN DECODE(CORP_PRMTN_TYPE_CODE,'Annual Agreement',ESTMT_FIXED_COST_AMT +DECODE(REVSD_BPT_COST_AMT,0,REVSD_ESTMT_VAR_COST_AMT, --Ax Revised Estimated Variable CostREVSD_BPT_COST_AMT), --BPT Revised CostESTMT_FIXED_COST_AMT + ESTMT_VAR_COST_AMT) END)WHEN MTH_START_DATE <= TRUNC(SYSDATE, 'MM') THEN (ACTL_VAR_COST_AMT + ACTL_FIXED_COST_AMT) ELSE 0 END) AS LA_TOT_BOOK_AMT FROM (SELECT ACTVY_MTH_GTIN.ACTVY_SKID,ACTVY_MTH_GTIN.FUND_SKID,ACTVY_MTH_GTIN.PROD_SKID,ACTVY_MTH_GTIN.PRMTN_SKID,ACTVY_MTH_GTIN.MTH_SKID AS DATE_SKID,ACTVY_MTH_GTIN.ACCT_SKID,ACTVY_MTH_GTIN.BUS_UNIT_SKID,ACTVY_MTH_GTIN.FY_DATE_SKID,PRMTN.PRMTN_STTUS_CODE,PRMTN.APPRV_STTUS_CODE,ACTVY.ESTMT_COST_IND,ACTVY.CORP_PRMTN_TYPE_CODE,ACTVY.ACTVY_STOP_DATE,ACTVY.PYMT_ALLWD_STOP_IND,CAL.MTH_START_DATE,ROUND(NVL(DECODE(ACTVY.COST_TYPE_CODE,'% Fund',(ACTVY_MTH_GTIN.ESTMT_VAR_COST * -- added by Rita for defect 3105 in R10ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE),DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'AnnualAgreement',AA.ESTMT_VAR_COST_AMT,ESTMT_VAR_COST.ESTMT_VAR_COST_AMT)),0),7) AS ESTMT_VAR_COST_AMT,-- Modified by Simon For CR389 in R10 on 2010-3-18ROUND(NVL(DECODE(ACTVY.COST_TYPE_CODE,-- % Fund'% Fund',ACTVY_MTH_GTIN.ESTMT_FIX_COST * ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,-- Fixed'Fixed',ACTVY_MTH_GTIN.ESTMT_FIX_COST * ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,-- Not % Fund or FixedDECODE(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'Annual Agreement',SUM(NVL(AA.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID),SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID)),0,ACTVY_MTH_GTIN.ESTMT_FIX_COST * BRAND_MTH_RATE,ACTVY_MTH_GTIN.ESTMT_FIX_COST * NVL(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'AnnualAgreement',AA.ESTMT_VAR_COST_AMT,ESTMT_VAR_COST.ESTMT_VAR_COST_AMT),0) / DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'Annual Agreement',SUM(NVL(AA.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID),SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID)))),0),7) AS ESTMT_FIXED_COST_AMT,-- Change in R10 for Revised Cost logicROUND(NVL(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'Annual Agreement',AA.REVSD_ESTMT_VAR_COST_AMT,REVSD_VAR_COST.REVSD_ESTMT_VAR_COST_AMT),0),7) AS REVSD_ESTMT_VAR_COST_AMT,ROUND(NVL(ESTMT_VAR_COST.REVSD_BPT_COST_AMT, 0), 7) AS REVSD_BPT_COST_AMT,ROUND(NVL((ACTVY_MTH_GTIN.ACTL_VAR_COST * ACTVY_MTH_GTIN.ACTVY_GTIN_ACTL_WGHT_RATE),0),7)AS ACTL_VAR_COST_AMT,ROUND(NVL((ACTVY_MTH_GTIN.ACTL_FIX_COST * ACTVY_MTH_GTIN.ACTVY_GTIN_ACTL_WGHT_RATE),0),7) AS ACTL_FIXED_COST_AMT,ROUND(NVL(DECODE(ACTVY.COST_TYPE_CODE,'% Fund',ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT * ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,'Fixed',ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT * ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE,DECODE(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'Annual Agreement',SUM(NVL(AA.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID),SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID)),0,ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT *BRAND_MTH_RATE,ACTVY_MTH_GTIN.MANUL_COST_OVRRD_AMT *NVL(DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'Annual Agreement',AA.ESTMT_VAR_COST_AMT,ESTMT_VAR_COST.ESTMT_VAR_COST_AMT),0) /DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,'Annual Agreement',SUM(NVL(AA.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID),SUM(NVL(ESTMT_VAR_COST.ESTMT_VAR_COST_AMT,0))OVER(PARTITION BY ACTVY_MTH_GTIN.ACTVY_SKID)))),0),7) AS MANUL_COST_OVRRD_AMTFROM OPT_ACTVY_DIM ACTVY,OPT_PRMTN_DIM PRMTN,OPT_CAL_MASTR_DIM CAL,(SELECT ACTVY.ACTVY_SKID,ACTVY_GTIN_BRAND.ACTVY_ID,ACTVY.FUND_SKID,ACTVY.ACCT_PRMTN_SKID AS ACCT_SKID,ACTVY_GTIN_BRAND.PROD_SKID,ACTVY_GTIN_BRAND.PROD_ID,ACTVY_GTIN_BRAND.PRMTN_SKID,ACTVY.BUS_UNIT_SKID,ACTVY_GTIN_BRAND.MTH_SKID,ACTVY_GTIN_BRAND.FY_DATE_SKID,ACTVY.VAR_COST_ESTMT_AMT AS ESTMT_VAR_COST,ACTVY.PRDCT_FIXED_COST_AMT AS ESTMT_FIX_COST,ACTVY.CALC_INDEX_NUM AS ACTL_FIX_COST,ACTVY.ACTL_VAR_COST_NUM AS ACTL_VAR_COST,ACTVY.ESTMT_COST_OVRRD_AMT,ACTVY.MANUL_COST_OVRRD_AMT,ACTVY_GTIN_BRAND.ACTVY_GTIN_ACTL_WGHT_RATE,ACTVY_GTIN_BRAND.ACTVY_GTIN_ESTMT_WGHT_RATE,ACTVY_GTIN_BRAND.BRAND_MTH_RATE FROM OPT_ACTVY_FCT ACTVY,OPT_ACTVY_GTIN_BRAND_SFCT ACTVY_GTIN_BRAND, OPT_ACCT_DIM ACCTWHERE ACTVY.ACTVY_SKID = ACTVY_GTIN_BRAND.ACTVY_SKID AND ACCT.ACCT_SKID = ACTVY.ACCT_PRMTN_SKID-- Optima11, B018, 9-Oct-2010, Kingham, filter out TSP accountAND ACCT.FUND_FRCST_MODEL_DESC not like 'TSP%') ACTVY_MTH_GTIN,--Estamate variable cost aggregated to brand level(SELECT ESTMT.ACTVY_ID AS ACTVY_ID,BRAND_HIER.BRAND_ID AS PROD_ID,ESTMT.DATE_SKID AS DATE_SKID,ESTMT.BUS_UNIT_SKID AS BUS_UNIT_SKID,SUM(ESTMT.ESTMT_VAR_COST_AMT) AS ESTMT_VAR_COST_AMT,SUM(ESTMT.REVSD_BPT_COST_AMT) AS REVSD_BPT_COST_AMTFROM OPT_ACTVY_GTIN_ESTMT_SFCT ESTMT, -- add by ritaOPT_PROD_BRAND_ASSOC_DIM BRAND_HIER,CAL_MASTR_DIM CALWHERE ESTMT.PROD_ID = BRAND_HIER.PROD_ID AND ESTMT.DATE_SKID = CAL.CAL_MASTR_SKIDAND CAL.FISC_YR_SKID = BRAND_HIER.FY_DATE_SKID GROUP BY ESTMT.ACTVY_ID,BRAND_HIER.BRAND_ID,ESTMT.DATE_SKID,ESTMT.BUS_UNIT_SKID) ESTMT_VAR_COST,--Revised variable cost aggregated to brand level(SELECT REVSD.ACTVY_ID AS ACTVY_ID,BRAND_HIER.BRAND_ID AS PROD_ID,REVSD.DATE_SKID AS DATE_SKID,REVSD.BUS_UNIT_SKID AS BUS_UNIT_SKID,SUM(REVSD.REVSD_ESTMT_VAR_COST_AMT) AS REVSD_ESTMT_VAR_COST_AMTFROM OPT_ACTVY_GTIN_REVSD_SFCT REVSD,OPT_PROD_BRAND_ASSOC_DIM BRAND_HIER,CAL_MASTR_DIM CAL WHERE REVSD.PROD_ID = BRAND_HIER.PROD_IDAND REVSD.DATE_SKID = CAL.CAL_MASTR_SKID AND CAL.FISC_YR_SKID = BRAND_HIER.FY_DATE_SKIDGROUP BY REVSD.ACTVY_ID,BRAND_HIER.BRAND_ID,REVSD.DATE_SKID,REVSD.BUS_UNIT_SKID) REVSD_VAR_COST,--AA Variable Cost aggregated to Brand Level(SELECT AA.ACTVY_ID AS ACTVY_ID,BRAND_HIER.BRAND_ID AS PROD_ID,AA.MTH_SKID AS DATE_SKID,AA.BUS_UNIT_SKID AS BUS_UNIT_SKID,SUM(AA.ESTMT_VAR_COST_AMT) AS ESTMT_VAR_COST_AMT,SUM(AA.REVSD_VAR_ESTMT_COST_AMT) AS REVSD_ESTMT_VAR_COST_AMT FROM OPT_ACTVY_BUOM_GTIN_COST_TFADS AA,OPT_PROD_BRAND_ASSOC_DIM BRAND_HIER WHERE AA.BUOM_GTIN_PROD_SKID = BRAND_HIER.PROD_SKIDAND BRAND_HIER.FY_DATE_SKID = AA.FY_DATE_SKID GROUP BY AA.ACTVY_ID,BRAND_HIER.BRAND_ID,AA.MTH_SKID,AA.BUS_UNIT_SKID) AAWHERE ACTVY_MTH_GTIN.ACTVY_ID = ESTMT_VAR_COST.ACTVY_ID(+)AND ACTVY_MTH_GTIN.MTH_SKID = ESTMT_VAR_COST.DATE_SKID(+)AND ACTVY_MTH_GTIN.PROD_ID = ESTMT_VAR_COST.PROD_ID(+)AND ACTVY_MTH_GTIN.ACTVY_ID = REVSD_VAR_COST.ACTVY_ID(+)AND ACTVY_MTH_GTIN.MTH_SKID = REVSD_VAR_COST.DATE_SKID(+)AND ACTVY_MTH_GTIN.PROD_ID = REVSD_VAR_COST.PROD_ID(+)AND ACTVY_MTH_GTIN.ACTVY_ID = AA.ACTVY_ID(+)AND ACTVY_MTH_GTIN.MTH_SKID = AA.DATE_SKID(+)AND ACTVY_MTH_GTIN.PROD_ID = AA.PROD_ID(+)AND ACTVY_MTH_GTIN.ACTVY_SKID = ACTVY.ACTVY_SKIDAND ACTVY_MTH_GTIN.PRMTN_SKID = PRMTN.PRMTN_SKIDAND ACTVY_MTH_GTIN.MTH_SKID = CAL.CAL_MASTR_SKID))
SQL 的执行计划如下（为了方便排版，我们删除了部分无关紧要的信息）。
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------Plan hash value: 2005223222-------------------------------------------------------------------------------------| Id |Operation |Name |Rows |-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 1|| 1 | VIEW | | 1|| 2 | WINDOW BUFFER | | 1|| 3 | VIEW | | 1|| 4 | WINDOW SORT | | 1|| 5 | NESTED LOOPS | | || 6 | NESTED LOOPS | | 1|| 7 | NESTED LOOPS | | 1||* 8 | HASH JOIN OUTER | | 1||* 9 | HASH JOIN OUTER | | 1||*10 | HASH JOIN OUTER | | 1||*11 | HASH JOIN | | 1|| 12 | NESTED LOOPS | | || 13 | NESTED LOOPS | | 1||*14 | HASH JOIN | | 1|| 15 | PARTITION LIST ALL | | 1||*16 | TABLE ACCESS FULL |OPT_ACCT_DIM | 1|| 17 | PARTITION LIST ALL | | 114K|| 18 | TABLE ACCESS FULL |OPT_ACTVY_FCT | 114K||*19 | INDEX RANGE SCAN |OPT_ACTVY_DIM_PK | 1|| 20 | TABLE ACCESS BY GLOBAL INDEX ROWID|OPT_ACTVY_DIM | 1|| 21 | PARTITION LIST ALL | | 19M|| 22 | TABLE ACCESS FULL |OPT_ACTVY_GTIN_BRAND_SFCT | 19M|| 23 | VIEW | | 1|| 24 | HASH GROUP BY | | 1|| 25 | NESTED LOOPS | | || 26 | NESTED LOOPS | | 1|| 27 | TABLE ACCESS FULL |OPT_ACTVY_BUOM_GTIN_COST_TFADS| 1||*28 | INDEX RANGE SCAN |OPT_PROD_BRAND_ASSOC_DIM_PK | 1|| 29 | TABLE ACCESS BY GLOBAL INDEX ROWID|OPT_PROD_BRAND_ASSOC_DIM | 1|| 30 | VIEW | | 718|| 31 | HASH GROUP BY | | 718||*32 | HASH JOIN | | 718||*33 | HASH JOIN | | 872|| 34 | PARTITION LIST ALL | | 872|| 35 | TABLE ACCESS FULL |OPT_ACTVY_GTIN_REVSD_SFCT | 872|| 36 | TABLE ACCESS FULL |OPT_CAL_MASTR_DIM |36826|| 37 | PARTITION LIST ALL | | 671K|| 38 | TABLE ACCESS FULL |OPT_PROD_BRAND_ASSOC_DIM | 671K|| 39 | VIEW | | 6174|| 40 | HASH GROUP BY | | 6174||*41 | HASH JOIN | | 6174||*42 | HASH JOIN | | 8998|| 43 | PARTITION LIST ALL | | 8998|| 44 | TABLE ACCESS FULL |OPT_ACTVY_GTIN_ESTMT_SFCT | 8998|| 45 | TABLE ACCESS FULL |OPT_CAL_MASTR_DIM |36826|| 46 | PARTITION LIST ALL | | 671K|| 47 | TABLE ACCESS FULL |OPT_PROD_BRAND_ASSOC_DIM | 671K|| 48 | TABLE ACCESS BY INDEX ROWID |OPT_CAL_MASTR_DIM | 1||*49 | INDEX UNIQUE SCAN |OPT_CAL_MASTR_DIM_PK | 1||*50 | INDEX RANGE SCAN |OPT_PRMTN_DIM_PK | 1|| 51 | TABLE ACCESS BY GLOBAL INDEX ROWID |OPT_PRMTN_DIM | 1|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------8 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="ESTMT_VAR_COST"."ACTVY_ID"(+) AND"ACTVY_GTIN_BRAND"."MTH_SKID"="ESTMT_VAR_COST"."DATE_SKID"(+) AND"ACTVY_GTIN_BRAND"."PROD_ID"="ESTMT_VAR_COST"."PROD_ID"(+))9 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="REVSD_VAR_COST"."ACTVY_ID"(+) AND"ACTVY_GTIN_BRAND"."MTH_SKID"="REVSD_VAR_COST"."DATE_SKID"(+) AND"ACTVY_GTIN_BRAND"."PROD_ID"="REVSD_VAR_COST"."PROD_ID"(+))10 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="AA"."ACTVY_ID"(+) AND"ACTVY_GTIN_BRAND"."MTH_SKID"="AA"."DATE_SKID"(+) AND"ACTVY_GTIN_BRAND"."PROD_ID"="AA"."PROD_ID"(+))11 - access("ACTVY"."ACTVY_SKID"="ACTVY_GTIN_BRAND"."ACTVY_SKID")14 - access("ACCT"."ACCT_SKID"="ACTVY"."ACCT_PRMTN_SKID")16 - filter("ACCT"."FUND_FRCST_MODEL_DESC" NOT LIKE 'TSP%')19 - access("ACTVY"."ACTVY_SKID"="ACTVY"."ACTVY_SKID")28 - access("AA"."BUOM_GTIN_PROD_SKID"="BRAND_HIER"."PROD_SKID" AND"BRAND_HIER"."FY_DATE_SKID"="AA"."FY_DATE_SKID")32 - access("REVSD"."PROD_ID"="BRAND_HIER"."PROD_ID" AND"CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")33 - access("REVSD"."DATE_SKID"="CAL"."CAL_MASTR_SKID")41 - access("ESTMT"."PROD_ID"="BRAND_HIER"."PROD_ID" AND"CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")42 - access("ESTMT"."DATE_SKID"="CAL"."CAL_MASTR_SKID")49 - access("ACTVY_GTIN_BRAND"."MTH_SKID"="CAL"."CAL_MASTR_SKID")50 - access("ACTVY_GTIN_BRAND"."PRMTN_SKID"="PRMTN"."PRMTN_SKID")
该 SQL 是用来做数据清洗的（ETL），需要处理大量数据。处理大量数据应该走 HASH 连接，因此该执行计划是错误的，因为执行计划中有大量的嵌套循环。
注意观察执行计划，执行计划中 Id=16 和 Id=27 优化器评估只返回 1 行数据，因此怀疑 OPT_ACCT_DIM 和 OPT_ACTVY_BUOM_GTIN_COST_TFADS 这两个表统计信息有问题。对这两个表收集完统计信息之后，我们再来看一下执行计划。
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT-------------------------------------------------------------------------------------Plan hash value: 183294992-------------------------------------------------------------------------------------| Id |Operation |Name |Rows |-------------------------------------------------------------------------------------| 0 |SELECT STATEMENT | | 19M|| 1 | VIEW | | 19M|| 2 | WINDOW BUFFER | | 19M|| 3 | VIEW | | 19M|| 4 | WINDOW SORT | | 19M||* 5 | HASH JOIN | | 19M|| 6 | PARTITION LIST ALL | |37880|| 7 | TABLE ACCESS FULL |OPT_PRMTN_DIM |37880||* 8 | HASH JOIN | | 19M|| 9 | TABLE ACCESS FULL |OPT_CAL_MASTR_DIM |36826||*10 | HASH JOIN RIGHT OUTER | | 19M|| 11 | VIEW | | 6174|| 12 | HASH GROUP BY | | 6174||*13 | HASH JOIN | | 6174||*14 | HASH JOIN | | 8998|| 15 | PARTITION LIST ALL | | 8998|| 16 | TABLE ACCESS FULL |OPT_ACTVY_GTIN_ESTMT_SFCT | 8998|| 17 | TABLE ACCESS FULL |OPT_CAL_MASTR_DIM |36826|| 18 | PARTITION LIST ALL | | 671K|| 19 | TABLE ACCESS FULL |OPT_PROD_BRAND_ASSOC_DIM | 671K||*20 | HASH JOIN RIGHT OUTER | | 19M|| 21 | VIEW | | 718|| 22 | HASH GROUP BY | | 718||*23 | HASH JOIN | | 718||*24 | HASH JOIN | | 872|| 25 | PARTITION LIST ALL | | 872|| 26 | TABLE ACCESS FULL |OPT_ACTVY_GTIN_REVSD_SFCT | 872|| 27 | TABLE ACCESS FULL |OPT_CAL_MASTR_DIM |36826|| 28 | PARTITION LIST ALL | | 671K|| 29 | TABLE ACCESS FULL |OPT_PROD_BRAND_ASSOC_DIM | 671K||*30 | HASH JOIN RIGHT OUTER | | 19M|| 31 | VIEW | | 1|| 32 | HASH GROUP BY | | 1|| 33 | NESTED LOOPS | | || 34 | NESTED LOOPS | | 1|| 35 | TABLE ACCESS FULL |OPT_ACTVY_BUOM_GTIN_COST_TFADS| 1||*36 | INDEX RANGE SCAN |OPT_PROD_BRAND_ASSOC_DIM_PK | 1|| 37 | TABLE ACCESS BY GLOBAL INDEX ROWID|OPT_PROD_BRAND_ASSOC_DIM | 1||*38 | HASH JOIN | | 19M||*39 | HASH JOIN | | 114K|| 40 | PARTITION LIST ALL | | 115K|| 41 | TABLE ACCESS FULL |OPT_ACTVY_DIM | 115K||*42 | HASH JOIN | | 114K|| 43 | PARTITION LIST ALL | |94478||*44 | TABLE ACCESS FULL |OPT_ACCT_DIM |94478|| 45 | PARTITION LIST ALL | | 114K|| 46 | TABLE ACCESS FULL |OPT_ACTVY_FCT | 114K|| 47 | PARTITION LIST ALL | | 19M|| 48 | TABLE ACCESS FULL |OPT_ACTVY_GTIN_BRAND_SFCT | 19M|-------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------5 - access("ACTVY_GTIN_BRAND"."PRMTN_SKID"="PRMTN"."PRMTN_SKID")8 - access("ACTVY_GTIN_BRAND"."MTH_SKID"="CAL"."CAL_MASTR_SKID")10 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="ESTMT_VAR_COST"."ACTVY_ID"(+)AND "ACTVY_GTIN_BRAND"."MTH_SKID"="ESTMT_VAR_COST"."DATE_SKID"(+) AND"ACTVY_GTIN_BRAND"."PROD_ID"="ESTMT_VAR_COST"."PROD_ID"(+))13 - access("ESTMT"."PROD_ID"="BRAND_HIER"."PROD_ID" AND"CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")14 - access("ESTMT"."DATE_SKID"="CAL"."CAL_MASTR_SKID")20 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="REVSD_VAR_COST"."ACTVY_ID"(+)AND "ACTVY_GTIN_BRAND"."MTH_SKID"="REVSD_VAR_COST"."DATE_SKID"(+) AND"ACTVY_GTIN_BRAND"."PROD_ID"="REVSD_VAR_COST"."PROD_ID"(+))23 - access("REVSD"."PROD_ID"="BRAND_HIER"."PROD_ID" AND"CAL"."FISC_YR_SKID"="BRAND_HIER"."FY_DATE_SKID")24 - access("REVSD"."DATE_SKID"="CAL"."CAL_MASTR_SKID")30 - access("ACTVY_GTIN_BRAND"."ACTVY_ID"="AA"."ACTVY_ID"(+) AND"ACTVY_GTIN_BRAND"."MTH_SKID"="AA"."DATE_SKID"(+) AND"ACTVY_GTIN_BRAND"."PROD_ID"="AA"."PROD_ID"(+))36 - access("AA"."BUOM_GTIN_PROD_SKID"="BRAND_HIER"."PROD_SKID" AND"BRAND_HIER"."FY_DATE_SKID"="AA"."FY_DATE_SKID")38 - access("ACTVY"."ACTVY_SKID"="ACTVY_GTIN_BRAND"."ACTVY_SKID")39 - access("ACTVY"."ACTVY_SKID"="ACTVY"."ACTVY_SKID")42 - access("ACCT"."ACCT_SKID"="ACTVY"."ACCT_PRMTN_SKID")44 - filter("ACCT"."FUND_FRCST_MODEL_DESC" NOT LIKE 'TSP%')
执行计划中，除了 Id=35 和 Id=37 两个表没有走 HASH 连接之外，其余表都走了 HASH 连接。Id=35 的表 OPT_ACTVY_BUOM_GTIN_COST_TFADS 之前已经收集过统计信息，因此 Id=35 和 Id=37 的表走嵌套循环没有问题，那么整个 SQL 的执行计划现在是正确的。纠正完执行计划之后，笔者将 SQL 放在后台运行了大概两小时，发现 SQL 还没执行完毕。起初，笔者认为 SQL 执行 7 个小时还没跑完是因为 SQL 执行计划错误导致的，但是现在纠正了 SQL 的执行计划，SQL 执行了两小时还是没有跑完，于是监控 SQL 的等待事件，看 SQL 究竟在等什么。
select inst_id,sid,serial#,event,p1,p2,p3from gv$session where osuser='luobi';
INST_ID SID SERIAL# EVENT P1 P2 P3--------- ---------- ---------- ---------------------- ------ ---------- ----------2 4754 10050 direct path write temp 20025 857328 7SQL> /INST_ID SID SERIAL# EVENT P1 P2 P3-------- ---------- ---------- ----------------------- ------ ---------- ----------2 4754 10050 direct path write temp 20025 406768 7SQL> /INST_ID SID SERIAL# EVENT P1 P2 P3-------- ---------- ---------- ----------------------- ------ ---------- ----------2 4754 10050 direct path write temp 20007 2849264 7SQL> /INST_ID SID SERIAL# EVENT P1 P2 P3-------- ---------- ---------- ----------------------- ------ ---------- ----------2 4754 10050 direct path write temp 20007 115341 7SQL> /INST_ID SID SERIAL# EVENT P1 P2 P3-------- ---------- ---------- ------------------------ ------ ---------- ---------2 4754 10050 direct path write temp 20007 81029 7
我们监控到该 SQL 的等待事件为 direct path write temp，该等待事件表示当前 SQL 正在进行排序或者正在进行 HASH 连接，但是因为 PGA 不够大，不能完全容纳需要排序或者需要 HASH 的数据，导致有部分数据被写入 temp 表空间。
为了追查究竟是因为排序还是因为 HASH 而引发的 direct path write temp 等待，使用以下脚本查看临时段数据类型。
select a.inst_id, a.sid, a.serial#, a.sql_id, b.tablespace, b.blocks*(select value from v$parameter where name='db_block_size')/1024/1024 "Size(M)",b.segtypefrom gv$session a, gv$tempseg_usage b where a.inst_id=b.inst_id and a.saddr = b.session_addrand a.inst_id=2 and a.sid=4754;
INST_ID SID SERIAL# SQL_ID TABLESPACE Size(M) SEGTYPE------- ---------- ------------- ------------- --------------------- ---------2 4754 10050 6qsuc8mafy20m TEMP 1 DATA2 4754 10050 6qsuc8mafy20m TEMP 1 LOB_DATA2 4754 10050 6qsuc8mafy20m TEMP 1 INDEX2 4754 10050 6qsuc8mafy20m TEMP 1 LOB_DATA2 4754 10050 6qsuc8mafy20m TEMP 3304 HASH
从 SQL 查询中我们看到，临时段数据类型为 HASH，耗费了 3 304MB 的 temp 表空间，这表示 SQL 是因为 HASH 连接引发的 direct path write temp 等待。
大家请仔细观察等待事件 P3，它的值一直为 7，这表示 Oracle 一次只写入 7 个块到 temp 表空间，而且是一直只写入 7 个块到 temp 表空间。笔者在第 4 章中讲到，绝大多数的操作系统，一次 I/O 最多只能读取或者写入 1MB 数据。这里的数据块大小为 16KB，正常情况下应该是每次 I/O 写入 64 个块到 temp 表空间，但是每次 I/O 只写了 7 个块。于是怀疑是 PGA 中 work area 不够导致出现了该问题。
PGA 在自动管理的情况下，单个 PGA 进程的 work area 不能超过 1GB（想要超过 1GB 需要修改隐含参数，但是本书主题是 SQL 优化，因此不想太多涉及到 Oracle 内部原理），如果 PGA 是手动管理，单个 PGA 进程的 work area 可以接近 2GB，但是不能超过 2GB。
alter session set workarea_size_policy = manual;
Session altered.
alter session set hash_area_size = 2147483648; ---2GBalter session set hash_area_size = 2147483648
ERROR at line 1:ORA-02017: integer value required
alter session set hash_area_size = 2147483647;
Session altered.
将 PGA 的 work area 设置为接近 2GB 之后，重新运行了 SQL 并且监控等待事件。
select inst_id,sid,serial#,event,p1,p2,p3from gv$session where osuser='luobi';
INST_ID SID SERIAL# EVENT P1 P2 P3-------- ---------- ---------- ------------------------------- ---------- ----------2 4885 11759 direct path write temp 20012 71053 64
将 PGA 的 work area 设置为接近 2GB 之后，笔者发现 P3 可以达到 64，相比之前一次只能写入 7 个块速度提升了 9 倍。
有 direct path write temp 等待必然会出现 direct path read temp 等待，在没修改 PGA 的 work area 之前，不仅仅是单次 I/O 只能写入 7 个块，单次 I/O 读取也是只能读取 7 个块，因此，将 PGA 的 work area 设置为接近 2GB 之后，整个 SQL 的性能应该提升了 18 倍。
最后，经过对比测试，手动设置 work area 的 SQL 只需要 56 分钟左右就能执行完毕。
6889440 rows selected.Elapsed: 00:56:36.08
而自动 work area 管理的 SQL 还在一直等待 direct path write temp，估计该 SQL 如果不手动设置 work area 可能跑一天一夜都跑不完。
优化完上述 SQL 之后，我们发现当时整个平台已经瘫痪，整个平台都出现了 P3=7 的问题，最后经过与 Oracle 确认，发现该问题是 11.1.0.7 版本在 HPUX 平台下的一个 bug。Oracle 开发补丁需要一定的时间，在此期间，使用本书给出的方法临时解决了项目中遇到的问题，确保项目不会因此延期。
若有收获，就点个赞吧
0 人点赞
