以下是一位朋友的一次调优SQL的经历，有很多精彩的地方，仅仅为了记录，后面附上我的疑问，一并贴在下面：
主要环境如下:
SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
4节点 HPUX RAC OLAP 环境
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_block_size integer 16384
SQL> show parameter db_file
NAME TYPE VALUE
------------------------------------ --------------------------------- ------
db_file_multiblock_read_count integer
ETL开发人员找我调查一个long running的JOB，该JOB已经跑了7小时了还没跑完。
那个JOB 是一个insert into ... select ..... 语句。insert 肯定不会7小时还
未完成，所以，这里主要的调整应该关注 select 部分
select部分的SQL语句如下，这是一个接近400行的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_AMT
FROM (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
0
ELSE
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
0
ELSE
COST_PLAN_AMT
END) AS COST_PLAN_AMT,
LA_TOT_BOOK_AMT
FROM (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' 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 Cost
REVSD_BPT_COST_AMT) --BPT Revised Cost
WHEN 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 Cost
REVSD_BPT_COST_AMT), --BPT Revised Cost
ESTMT_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 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_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 Cost
WHEN 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 Cost
REVSD_BPT_COST_AMT), --BPT Revised Cost
ESTMT_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_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 Cost
WHEN 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 Cost
REVSD_BPT_COST_AMT), --BPT Revised Cost
ESTMT_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 Cost
REVSD_BPT_COST_AMT) --BPT Revised Cost
WHEN 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 Cost
REVSD_BPT_COST_AMT), --BPT Revised Cost
ESTMT_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 R10
ACTVY_MTH_GTIN.ACTVY_GTIN_ESTMT_WGHT_RATE),
DECODE(ACTVY.CORP_PRMTN_TYPE_CODE,
'Annual Agreement',
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-18
ROUND(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 Fixed
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.ESTMT_FIX_COST *
BRAND_MTH_RATE,
ACTVY_MTH_GTIN.ESTMT_FIX_COST *
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 ESTMT_FIXED_COST_AMT,
-- Change in R10 for Revised Cost logic
ROUND(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_AMT
FROM 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 ACCT
WHERE 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 account
AND 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_AMT
FROM OPT_ACTVY_GTIN_ESTMT_SFCT ESTMT, -- add by rita
OPT_PROD_BRAND_ASSOC_DIM BRAND_HIER,
CAL_MASTR_DIM CAL
WHERE ESTMT.PROD_ID = BRAND_HIER.PROD_ID
AND ESTMT.DATE_SKID = CAL.CAL_MASTR_SKID
AND 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_AMT
FROM OPT_ACTVY_GTIN_REVSD_SFCT REVSD,
OPT_PROD_BRAND_ASSOC_DIM BRAND_HIER,
CAL_MASTR_DIM CAL
WHERE REVSD.PROD_ID = BRAND_HIER.PROD_ID
AND REVSD.DATE_SKID = CAL.CAL_MASTR_SKID
AND CAL.FISC_YR_SKID = BRAND_HIER.FY_DATE_SKID
GROUP 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_SKID
AND 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) AA
WHERE 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_SKID
AND ACTVY_MTH_GTIN.PRMTN_SKID = PRMTN.PRMTN_SKID
AND ACTVY_MTH_GTIN.MTH_SKID = CAL.CAL_MASTR_SKID))
);
该SQL执行计划如下
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2005223222
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 249 | 3855 (39)| 00:00:27 | | |
| 1 | VIEW | | 1 | 249 | 3855 (39)| 00:00:27 | | |
| 2 | WINDOW BUFFER | | 1 | 308 | 3855 (39)| 00:00:27 | | |
| 3 | VIEW | | 1 | 308 | 3855 (39)| 00:00:27 | | |
| 4 | WINDOW SORT | | 1 | 376 | 3855 (39)| 00:00:27 | | |
| 5 | NESTED LOOPS | | | | | | | |
| 6 | NESTED LOOPS | | 1 | 376 | 3854 (39)| 00:00:27 | | |
| 7 | NESTED LOOPS | | 1 | 351 | 3852 (39)| 00:00:27 | | |
|* 8 | HASH JOIN OUTER | | 1 | 338 | 3851 (39)| 00:00:27 | | |
|* 9 | HASH JOIN OUTER | | 1 | 281 | 3536 (41)| 00:00:25 | | |
|* 10 | HASH JOIN OUTER | | 1 | 237 | 3223 (43)| 00:00:23 | | |
|* 11 | HASH JOIN | | 1 | 180 | 3218 (43)| 00:00:23 | | |
| 12 | NESTED LOOPS | | | | | | | |
| 13 | NESTED LOOPS | | 1 | 116 | 535 (7)| 00:00:04 | | |
|* 14 | HASH JOIN | | 1 | 65 | 533 (7)| 00:00:04 | | |
| 15 | PARTITION LIST ALL | | 1 | 26 | 315 (6)| 00:00:03 | 1 | 17 |
|* 16 | TABLE ACCESS FULL | OPT_ACCT_DIM | 1 | 26 | 315 (6)| 00:00:03 | 1 | 17 |
| 17 | PARTITION LIST ALL | | 114K| 4363K| 216 (7)| 00:00:02 | 1 | 17 |
| 18 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 114K| 4363K| 216 (7)| 00:00:02 | 1 | 17 |
|* 19 | INDEX RANGE SCAN | OPT_ACTVY_DIM_PK | 1 | | 1 (0)| 00:00:01 | | |
| 20 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACTVY_DIM | 1 | 51 | 2 (0)| 00:00:01 | ROWID | ROWID |
| 21 | PARTITION LIST ALL | | 19M| 1212M| 2423 (45)| 00:00:17 | 1 | 17 |
| 22 | TABLE ACCESS FULL | OPT_ACTVY_GTIN_BRAND_SFCT | 19M| 1212M| 2423 (45)| 00:00:17 | 1 | 17 |
| 23 | VIEW | | 1 | 57 | 5 (20)| 00:00:01 | | |
| 24 | HASH GROUP BY | | 1 | 108 | 5 (20)| 00:00:01 | | |
| 25 | NESTED LOOPS | | | | | | | |
| 26 | NESTED LOOPS | | 1 | 108 | 4 (0)| 00:00:01 | | |
| 27 | TABLE ACCESS FULL | OPT_ACTVY_BUOM_GTIN_COST_TFADS | 1 | 87 | 2 (0)| 00:00:01 | | |
|* 28 | INDEX RANGE SCAN | OPT_PROD_BRAND_ASSOC_DIM_PK | 1 | | 2 (0)| 00:00:01 | | |
| 29 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PROD_BRAND_ASSOC_DIM | 1 | 21 | 2 (0)| 00:00:01 | ROWID | ROWID |
| 30 | VIEW | | 718 | 31592 | 313 (17)| 00:00:03 | | |
| 31 | HASH GROUP BY | | 718 | 51696 | 313 (17)| 00:00:03 | | |
|* 32 | HASH JOIN | | 718 | 51696 | 311 (17)| 00:00:03 | | |
|* 33 | HASH JOIN | | 872 | 40112 | 211 (8)| 00:00:02 | | |
| 34 | PARTITION LIST ALL | | 872 | 31392 | 3 (0)| 00:00:01 | 1 | 17 |
| 35 | TABLE ACCESS FULL | OPT_ACTVY_GTIN_REVSD_SFCT | 872 | 31392 | 3 (0)| 00:00:01 | 1 | 17 |
| 36 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 36826 | 359K| 207 (8)| 00:00:02 | | |
| 37 | PARTITION LIST ALL | | 671K| 16M| 91 (28)| 00:00:01 | 1 | 17 |
| 38 | TABLE ACCESS FULL | OPT_PROD_BRAND_ASSOC_DIM | 671K| 16M| 91 (28)| 00:00:01 | 1 | 17 |
| 39 | VIEW | | 6174 | 343K| 315 (17)| 00:00:03 | | |
| 40 | HASH GROUP BY | | 6174 | 446K| 315 (17)| 00:00:03 | | |
|* 41 | HASH JOIN | | 6174 | 446K| 313 (17)| 00:00:03 | | |
|* 42 | HASH JOIN | | 8998 | 421K| 213 (8)| 00:00:02 | | |
| 43 | PARTITION LIST ALL | | 8998 | 333K| 4 (0)| 00:00:01 | 1 | 17 |
| 44 | TABLE ACCESS FULL | OPT_ACTVY_GTIN_ESTMT_SFCT | 8998 | 333K| 4 (0)| 00:00:01 | 1 | 17 |
| 45 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 36826 | 359K| 207 (8)| 00:00:02 | | |
| 46 | PARTITION LIST ALL | | 671K| 16M| 91 (28)| 00:00:01 | 1 | 17 |
| 47 | TABLE ACCESS FULL | OPT_PROD_BRAND_ASSOC_DIM | 671K| 16M| 91 (28)| 00:00:01 | 1 | 17 |
| 48 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 13 | 1 (0)| 00:00:01 | | |
|* 49 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 50 | INDEX RANGE SCAN | OPT_PRMTN_DIM_PK | 1 | | 1 (0)| 00:00:01 | | |
| 51 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PRMTN_DIM | 1 | 25 | 2 (0)| 00:00:01 | ROWID | ROWID |
--------------------------------------------------------------------------------------------------------------------------------------------------
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")
79 rows selected.
Elapsed: 00:00:03.45
由于这个SQL语句的执行计划太复杂，所以这里暂不关注执行计划，我们来监控该SQL语句的等待事件
我手工运行该SQL，监控等待事件，发现在等待 direct path write temp
SQL> select username,inst_id,sid,serial#,event,p1,p2,p3,sql_id,sql_child_number
2 from gv$session where username='ADWU_OPTIMA_LA11' and osuser='luobi';
USERNAME INST_ID SID SERIAL# EVENT P1 P2 P3 SQL_ID SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11 2 4754 10050 direct path write temp 20025 857328 7 6qsuc8mafy20m 0
SQL> /
USERNAME INST_ID SID SERIAL# EVENT P1 P2 P3 SQL_ID SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11 2 4754 10050 direct path write temp 20025 406768 7 6qsuc8mafy20m 0
SQL> /
USERNAME INST_ID SID SERIAL# EVENT P1 P2 P3 SQL_ID SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11 2 4754 10050 direct path write temp 20007 2849264 7 6qsuc8mafy20m 0
SQL> /
USERNAME INST_ID SID SERIAL# EVENT P1 P2 P3 SQL_ID SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11 2 4754 10050 direct path write temp 20007 115341 7 6qsuc8mafy20m 0
SQL> /
USERNAME INST_ID SID SERIAL# EVENT P1 P2 P3 SQL_ID SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11 2 4754 10050 direct path write temp 20007 81029 7 6qsuc8mafy20m 0
我又查询这个SESSION到底是HASH JOIN 暂用了 temp tablespace 还是 sort 占用了temp tablespace
SQL> select a.username,a.inst_id, a.sid, a.serial#, a.machine,a.sql_id,b.tablespace, b.blocks*
2 (select value from v$parameter where name='db_block_size')/1024/1024 "Size(M)",b.segtype
3 from gv$session a, gv$tempseg_usage b where a.inst_id=b.inst_id and a.saddr = b.session_addr
4 and a.inst_id=2 and a.sid=4754
5 ;
USERNAME INST_ID SID SERIAL# MACHINE SQL_ID TABLESPACE Size(M) SEGTYPE
-------------------- ---------- ---------- ---------- -------------------- ------------- ------------------------------- ---------- ---------
ADWU_OPTIMA_LA11 2 4754 10050 ASIAPACIFIC\BLUO7 6qsuc8mafy20m TEMP 1 DATA
ADWU_OPTIMA_LA11 2 4754 10050 ASIAPACIFIC\BLUO7 6qsuc8mafy20m TEMP 1 LOB_DATA
ADWU_OPTIMA_LA11 2 4754 10050 ASIAPACIFIC\BLUO7 6qsuc8mafy20m TEMP 1 INDEX
ADWU_OPTIMA_LA11 2 4754 10050 ASIAPACIFIC\BLUO7 6qsuc8mafy20m TEMP 1 LOB_DATA
ADWU_OPTIMA_LA11 2 4754 10050 ASIAPACIFIC\BLUO7 6qsuc8mafy20m TEMP 3304 HASH
那么根据查询，这个会话在等磁盘HASH操作，ETL开发人员跟我报告的时候，我用TOAD监控了一下，
那个job确实在等待 direct patch write temp 操作。
在OLAP中，由于数据量巨大，业务逻辑复杂，有时候确实无法避免磁盘HASH，磁盘SORT等操作
大家请注意观察p3, p3=7 也就是说一次 temp 写入只能写入7个block
好的，那么我现在kill 这个SESSION，我设置workarea 手工管理
SQL> alter session set workarea_size_policy = manual;
Session altered.
SQL> alter session set hash_area_size = 2100000000;
Session altered.
SQL> alter session set sort_area_size = 2100000000;
Session altered.
Oracle有个限制，每个进程分配的最大内存不能够操作2G 。在workarea自动管理中，每个进程的work area不能超过1G
所以当你尝试分配2G的hash_area给 这个进程，会报错
SQL> alter session set hash_area_size = 2147483648;
alter session set hash_area_size = 2147483648
*
ERROR at line 1:
ORA-02017: integer value required
SQL> alter session set hash_area_size = 2147483647;
Session altered.
好了 关于此话题，就到此结束，本人博客前面提到过这个问题，我们再来运行一下这个SQL，并且监控等待事件
SQL> select username,inst_id,sid,serial#,event,p1,p2,p3,sql_id,sql_child_number
2 from gv$session where username='ADWU_OPTIMA_LA11' and osuser='luobi';
USERNAME INST_ID SID SERIAL# EVENT P1 P2 P3 SQL_ID SQL_CHILD_NUMBER
-------------------- ---------- ---------- ---------- ------------------------------ ---------- ---------- ---------- ------------- ----------------
ADWU_OPTIMA_LA11 2 4885 11759 direct path write temp 20012 71053 64 6qsuc8mafy20m 1
请注意观察 p3 ，当设置 workarea 手工管理的时候，一次能写入64个block，相比以前的 7个block来说写入速度加快9倍
那么现在大家也该明白了，这个SQL的主要性能问题就是在于在 在workarea 自动管理模式下磁盘HASH 的时候一次只能写入7个block
而设置workarea 手工管理，可以让磁盘HASH 一次写入64个block。我现在还没搞明白为什么一次 磁盘HASH只能写入 7个block
我不是SYSDBA，不能做详细测试，关于这个问题就暂时到此为止
其实这个SQL不光有 磁盘hash 这个问题存在，它的执行计划也有问题的
大家请看执行计划中ID=13 到 ID=19 的步骤
| 13 | NESTED LOOPS | | 1 | 116 | 535 (7)| 00:00:04 | | |
|* 14 | HASH JOIN | | 1 | 65 | 533 (7)| 00:00:04 | | |
| 15 | PARTITION LIST ALL | | 1 | 26 | 315 (6)| 00:00:03 | 1 | 17 |
|* 16 | TABLE ACCESS FULL | OPT_ACCT_DIM | 1 | 26 | 315 (6)| 00:00:03 | 1 | 17 |
| 17 | PARTITION LIST ALL | | 114K| 4363K| 216 (7)| 00:00:02 | 1 | 17 |
| 18 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 114K| 4363K| 216 (7)| 00:00:02 | 1 | 17 |
|* 19 | INDEX RANGE SCAN | OPT_ACTVY_DIM_PK | 1 | | 1 (0)| 00:00:01 | | |
首先 是 OPT_ACCT_DIM 与 OPT_ACTVY_FCT HASH 连接，然后 作为一个嵌套循环的驱动行源，大家请看
这个HASH 连接， CBO认为它只返回1行数据， 为什么返回一行数据呢，原因在于 Oracle认为 扫描 OPT_ACCT_DIM只会返回1行数据
那么我来查询一下 扫描 OPT_ACCT_DIM 要返回多少数据
SQL> select count(*) from OPT_ACCT_DIM ;
COUNT(*)
----------
94398
Elapsed: 00:00:01.37
SQL> select count(*) from OPT_ACTVY_FCT;
COUNT(*)
----------
114066
很明显了，OPT_ACCT_DIM表的统计信息没收集，而OPT_ACTVY_FCT的统计信息是对的，于是我马上对OPT_ACCT_DIM收集统计信息
SQL> BEGIN
2 DBMS_STATS.GATHER_TABLE_STATS(ownname => 'adwu_optima_la11',
3 tabname => 'OPT_ACCT_DIM',
4 estimate_percent => 30,
5 method_opt=>'for all columns size auto',
6 degree => DBMS_STATS.AUTO_DEGREE,
7 cascade=>TRUE
8 );
9 END;
10 /
PL/SQL procedure successfully completed.
现在来看一下执行计划
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 183294992
---------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19M| 4718M| | 848K (2)| 01:37:06 | | |
| 1 | VIEW | | 19M| 4718M| | 848K (2)| 01:37:06 | | |
| 2 | WINDOW BUFFER | | 19M| 5836M| | 848K (2)| 01:37:06 | | |
| 3 | VIEW | | 19M| 5836M| | 848K (2)| 01:37:06 | | |
| 4 | WINDOW SORT | | 19M| 7125M| 7392M| 848K (2)| 01:37:06 | | |
|* 5 | HASH JOIN | | 19M| 7125M| | 28490 (12)| 00:03:16 | | |
| 6 | PARTITION LIST ALL | | 37880 | 924K| | 407 (3)| 00:00:03 | 1 | 17 |
| 7 | TABLE ACCESS FULL | OPT_PRMTN_DIM | 37880 | 924K| | 407 (3)| 00:00:03 | 1 | 17 |
|* 8 | HASH JOIN | | 19M| 6651M| | 27822 (11)| 00:03:12 | | |
| 9 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 36826 | 467K| | 200 (4)| 00:00:02 | | |
|* 10 | HASH JOIN RIGHT OUTER | | 19M| 6405M| | 27362 (10)| 00:03:08 | | |
| 11 | VIEW | | 6174 | 343K| | 315 (17)| 00:00:03 | | |
| 12 | HASH GROUP BY | | 6174 | 446K| | 315 (17)| 00:00:03 | | |
|* 13 | HASH JOIN | | 6174 | 446K| | 313 (17)| 00:00:03 | | |
|* 14 | HASH JOIN | | 8998 | 421K| | 213 (8)| 00:00:02 | | |
| 15 | PARTITION LIST ALL | | 8998 | 333K| | 4 (0)| 00:00:01 | 1 | 17 |
| 16 | TABLE ACCESS FULL | OPT_ACTVY_GTIN_ESTMT_SFCT | 8998 | 333K| | 4 (0)| 00:00:01 | 1 | 17 |
| 17 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 36826 | 359K| | 207 (8)| 00:00:02 | | |
| 18 | PARTITION LIST ALL | | 671K| 16M| | 91 (28)| 00:00:01 | 1 | 17 |
| 19 | TABLE ACCESS FULL | OPT_PROD_BRAND_ASSOC_DIM | 671K| 16M| | 91 (28)| 00:00:01 | 1 | 17 |
|* 20 | HASH JOIN RIGHT OUTER | | 19M| 5325M| | 26787 (9)| 00:03:05 | | |
| 21 | VIEW | | 718 | 31592 | | 313 (17)| 00:00:03 | | |
| 22 | HASH GROUP BY | | 718 | 51696 | | 313 (17)| 00:00:03 | | |
|* 23 | HASH JOIN | | 718 | 51696 | | 311 (17)| 00:00:03 | | |
|* 24 | HASH JOIN | | 872 | 40112 | | 211 (8)| 00:00:02 | | |
| 25 | PARTITION LIST ALL | | 872 | 31392 | | 3 (0)| 00:00:01 | 1 | 17 |
| 26 | TABLE ACCESS FULL | OPT_ACTVY_GTIN_REVSD_SFCT | 872 | 31392 | | 3 (0)| 00:00:01 | 1 | 17 |
| 27 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 36826 | 359K| | 207 (8)| 00:00:02 | | |
| 28 | PARTITION LIST ALL | | 671K| 16M| | 91 (28)| 00:00:01 | 1 | 17 |
| 29 | TABLE ACCESS FULL | OPT_PROD_BRAND_ASSOC_DIM | 671K| 16M| | 91 (28)| 00:00:01 | 1 | 17 |
|* 30 | HASH JOIN RIGHT OUTER | | 19M| 4491M| | 26215 (8)| 00:03:01 | | |
| 31 | VIEW | | 1 | 57 | | 5 (20)| 00:00:01 | | |
| 32 | HASH GROUP BY | | 1 | 108 | | 5 (20)| 00:00:01 | | |
| 33 | NESTED LOOPS | | | | | | | | |
| 34 | NESTED LOOPS | | 1 | 108 | | 4 (0)| 00:00:01 | | |
| 35 | TABLE ACCESS FULL | OPT_ACTVY_BUOM_GTIN_COST_TFADS | 1 | 87 | | 2 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | OPT_PROD_BRAND_ASSOC_DIM_PK | 1 | | | 2 (0)| 00:00:01 | | |
| 37 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PROD_BRAND_ASSOC_DIM | 1 | 21 | | 2 (0)| 00:00:01 | ROWID | ROWID |
|* 38 | HASH JOIN | | 19M| 3411M| 14M| 25950 (7)| 00:02:59 | | |
|* 39 | HASH JOIN | | 114K| 12M| 7104K| 1302 (6)| 00:00:09 | | |
| 40 | PARTITION LIST ALL | | 115K| 5745K| | 394 (7)| 00:00:03 | 1 | 17 |
| 41 | TABLE ACCESS FULL | OPT_ACTVY_DIM | 115K| 5745K| | 394 (7)| 00:00:03 | 1 | 17 |
|* 42 | HASH JOIN | | 114K| 7273K| 3520K| 672 (6)| 00:00:05 | | |
| 43 | PARTITION LIST ALL | | 94478 | 2398K| | 315 (6)| 00:00:03 | 1 | 17 |
|* 44 | TABLE ACCESS FULL | OPT_ACCT_DIM | 94478 | 2398K| | 315 (6)| 00:00:03 | 1 | 17 |
| 45 | PARTITION LIST ALL | | 114K| 4363K| | 216 (7)| 00:00:02 | 1 | 17 |
| 46 | TABLE ACCESS FULL | OPT_ACTVY_FCT | 114K| 4363K| | 216 (7)| 00:00:02 | 1 | 17 |
| 47 | PARTITION LIST ALL | | 19M| 1212M| | 2423 (45)| 00:00:17 | 1 | 17 |
| 48 | TABLE ACCESS FULL | OPT_ACTVY_GTIN_BRAND_SFCT | 19M| 1212M| | 2423 (45)| 00:00:17 | 1 | 17 |
---------------------------------------------------------------------------------------------------------------------------------------------------------
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%')
76 rows selected.
Elapsed: 00:00:04.28
我发现绝大多数表与表之间的连接都走了全表扫描以及HASH 连接，但是请看Id=35这一步
全表扫描 居然CBO也认为只返回 一行
| 34 | NESTED LOOPS | | 1 | 108 | | 4 (0)| 00:00:01 | | |
| 35 | TABLE ACCESS FULL | OPT_ACTVY_BUOM_GTIN_COST_TFADS | 1 | 87 | | 2 (0)| 00:00:01 | | |
|* 36 | INDEX RANGE SCAN | OPT_PROD_BRAND_ASSOC_DIM_PK | 1 | | | 2 (0)| 00:00:01 | | |
| 37 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_PROD_BRAND_ASSOC_DIM | 1 | 21 | | 2 (0)| 00:00:01 | ROWID | ROWID |
好的， 我检查一下 OPT_ACTVY_BUOM_GTIN_COST_TFADS 统计信息是否过期
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SQL> select owner || '.' || table_name name , object_type,stale_stats,last_analyzed from dba_tab_statistics
2 where owner=upper('adwu_optima_la11') and table_name='OPT_ACTVY_BUOM_GTIN_COST_TFADS';
NAME OBJECT_TYPE STALE_STATS LAST_ANALYZED
-------------------------------------------------- -------------------- -------------------- ------------------
ADWU_OPTIMA_LA11.OPT_ACTVY_BUOM_GTIN_COST_TFADS TABLE NO 03-DEC-10
统计信息是没过期的
好的，我现在再RUN一次这个SQL
先跑的是 没有并行的SQL 手动设置 workarea
后炮的是 自动workarea
经过测试 手工设置workarea的SQL 只需要50分钟左右就能完成，而自动的workarea管理的SQL 还在等待direct path write temp
6889440 rows selected.
Elapsed: 00:56:36.08
到此，这个400行的SQL优化完毕。
总结: 对于有大量磁盘HASH，磁盘SORT的操作，我们可以设置workarea 手工管理，从而突破workarea 不能超过1G的限制，另外也
让磁盘HASH 能一次性写入60个block以上，而非一次性写入7个。还有就是统计信息，统计信息对于Oracle太重要了统计信息没收集
好，会死人滴。
这里我有两个疑问：
1.hash_area_size和sort_area_size：两个参数，我首先查看了reference，最大值为os dependent，可见，官方并未明确规定这个参数的最大值，但我在我的本子上测试，确实象我这个朋友所说，不能超过2G，但我的本子memory是2G,是否和这个有关呢？我认为在workarea_size_policy=manual下，可以设置更大些，但会受到物理内存或OS的限制；workarea_size_policy=auto下，这两个值的设置不会再起作用，系统会算出这两个参数的默认值，或者通过修改某些隐含参数进行设定。
2.关于在workarea_size_policy=auto下，temp IO的大小会受到隐含参数_max_temp_IO和_min_temp_IO的限制；而在workarea_size_policy=manul下，temp IO也会受某些隐含参数的限制，只是，我没有发现这些参数。