|
主要環境如下:
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優化完畢。
本文出自：億恩科技【www.endtimedelusion.com】
服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]
|
0371-60135900
京公網安備41019702002023號
香港服務器租用
美國服務器租用
電信骨干機房
聯通骨干機房
