问题描述
嗨，团队，
我们有一个SQL查询，它是ETL加载作业的源查询，这需要大约3个小时才能运行，您能否帮助我们如何使它运行得更快。
所涉及的表的行数如下。
D_PERSON 4618595
F _ pos _ sal_exp_det 14890639
PS_NYU_D_DT_PERIOD_FIN 389
WITH max_dt
AS (SELECT MAX (PSED.JRNL_POST_DT) max_post_dt
FROM SYSADM.F_POS_SAL_EXP_DET psed),
DD
AS ( SELECT DD.FISCAL_YEAR_NUM,
MIN (DD.DAY_DT) OVER (PARTITION BY DD.FISCAL_YEAR_NUM)
fiscal_year_begin_dt,
DD.DAY_DT,
DD.DAY_SID,
PP.PPERIOD_SID,
PP.PPERIOD_CD,
PP.PPERIOD_DESCR,
PP.PPERIOD_BEGIN_DT,
PP.PPERIOD_END_DT
FROM SYSADM.PS_NYU_D_DAY dd
LEFT JOIN SYSADM.PS_NYU_D_DT_PERIOD_FIN pp
ON DD.DAY_DT BETWEEN PP.PPERIOD_BEGIN_DT
AND PP.PPERIOD_END_DT
AND PP.DT_PATTERN_CD = 'DT'
AND PP.PPERIOD_CD BETWEEN 1 AND 12
WHERE (dd.day_dt <=
GREATEST (TRUNC (SYSDATE),
(SELECT max_post_dt FROM max_dt))) -- modified 5/4/2016 calculate how far should the YTD calc project forward
-- not based on the data journal date and sysdate, which ever is later
AND FISCAL_YEAR_NUM >= 2014
AND ( PP.DT_PATTERN_CD = 'DT'
AND PP.PPERIOD_CD BETWEEN 1 AND 12) -- Added 5/4/2016
ORDER BY DD.DAY_DT)
SELECT distinct dd.FISCAL_YEAR_NUM,
dd.FISCAL_YEAR_BEGIN_DT,
dd.PPERIOD_END_DT,
dd.DAY_DT,
dd.DAY_SID,
NVL (dd.PPERIOD_SID, 0),
NVL (POS.POS_SID, 0), -- the latest position row for the given accunting period
NVL (PBD.BU_SID, 0),
NVL (PBD.FUND_CODE_SID, 0),
NVL (PBD.DEPT_SID, 0),
NVL (PBD.ACCOUNT_SID, 0),
NVL (PBD.PROGRAM_FDM_SID, 0),
NVL (PBD.PRJ_SID, 0),
NVL (PBD.PERSON_SID, 0),
NVL (PBD.JOB_PRFL_SID, 0),
NVL (PBD.SUP_ORG_SID, 0),
NVL (PBD.LOC_SID, 0),
NVL (NYU_ACTIVE_CF_SID, 0),
NVL (PBD.PAY_GRP_SID, 0),
NVL (PBD.SUP_RPT_DEPT_SID, 0) SUP_RPT_DEPT_SID,
NVL (PBD.CST_RPT_DEPT_SID, 0) CST_RPT_DEPT_SID,
NVL(CASE
WHEN PBD.PERSON_SID != 0 THEN PBD.STD_FULL_NAME
ELSE NVL (PBD.PERSON_FULL_NAME, '-')
END,'-')
AS PERSON_FULL_NAME,
NVL (PBD.POS_CD, '-'),
NVL (PBD.OLD_CAMPUS_ID, '-'),
SUM (NVL (PBD.PRE_ENC_CHANGE_AMT, 0)) PRE_ENC_CHANGE_AMT,
SUM (NVL (PBD.PRE_ENC_CHANGE_FRINGE_AMT, 0)) PRE_ENC_CHANGE_FRINGE_AMT,
SUM (NVL (PBD.ENC_CHANGE_AMT, 0)) ENC_CHANGE_AMT,
SUM (NVL (PBD.ENC_CHANGE_FRINGE_AMT, 0)) ENC_CHANGE_FRINGE_AMT,
SUM (NVL (PBD.PAY_DSTRB_BASE_AMT, 0)) PAY_DSTRB_BASE_AMT,
SUM (NVL (PBD.PAY_DSTRB_OT_AMT, 0)) PAY_DSTRB_OT_AMT,
SUM (NVL (PBD.PAY_DSTRB_EXTRA_AMT, 0)) PAY_DSTRB_EXTRA_AMT,
SUM (NVL (PBD.PAY_DSTRB_FRINGE_AMT, 0)) PAY_DSTRB_FRINGE_AMT,
SUM (NVL (PBD.PAY_DSTRB_TRANSFER_AMT, 0)) PAY_DSTRB_TRANSFER_AMT,
-- Budget
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'BUDGET' AND PBD.CHANGE_TYPE_CD = 'Original' THEN PBD.BDGT_CHANGE_AMT ELSE 0 END) AS BDGT_ORIG_YTD_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'BUDGET' AND PBD.CHANGE_TYPE_CD = 'Original' THEN PBD.BDGT_CHANGE_FRINGE_AMT ELSE 0 END)AS BDGT_ORIG_YTD_FRINGE_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'BUDGET' AND PBD.CHANGE_TYPE_CD = 'Amendment' THEN PBD.BDGT_CHANGE_AMT ELSE 0 END) AS BDGT_AMEND_YTD_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'BUDGET' AND PBD.CHANGE_TYPE_CD = 'Amendment' THEN PBD.BDGT_CHANGE_FRINGE_AMT ELSE 0 END) AS BDGT_AMEND_YTD_FRINGE_AMT,
SUM (NVL (PBD.BDGT_CHANGE_AMT, 0)) AS BDGT_REVISED_YTD_AMT,
SUM (NVL (PBD.BDGT_CHANGE_FRINGE_AMT, 0))AS BDGT_REVISED_YTD_FRINGE_AMT,
-- Pre Encumbrance
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'PRE_ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Adjustment' THEN PBD.PRE_ENC_CHANGE_AMT ELSE 0 END) AS PRE_ENC_ADJ_YTD_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'PRE_ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Relief' THEN PBD.PRE_ENC_CHANGE_AMT ELSE 0 END) PRE_ENC_RELIEF_YTD_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'PRE_ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Adjustment' THEN PBD.PRE_ENC_CHANGE_FRINGE_AMT ELSE 0 END) PRE_ENC_ADJ_YTD_FRINGE_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'PRE_ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Relief' THEN PBD.PRE_ENC_CHANGE_FRINGE_AMT ELSE 0 END)PRE_ENC_RELIEF_YTD_FRINGE_AMT,
-- Encumbrance
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Adjustment' THEN PBD.ENC_CHANGE_AMT ELSE 0 END) ENC_ADJ_YTD_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Relief' THEN PBD.ENC_CHANGE_AMT ELSE 0 END) ENC_RELIEF_YTD_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Adjustment' THEN PBD.ENC_CHANGE_FRINGE_AMT ELSE 0 END) ENC_ADJ_YTD_FRINGE_AMT,
SUM (CASE WHEN PBD.RECORD_TYPE_CD = 'ENCUMB_ADJ_DAILY' AND PBD.CHANGE_TYPE_CD = 'Relief' THEN PBD.ENC_CHANGE_FRINGE_AMT ELSE 0 END) ENC_RELIEF_FRINGE_YTD_AMT
FROM dd
LEFT JOIN
(SELECT PER.CAMPUS_ID,PP.PPERIOD_END_DT,PBD.OLD_CAMPUS_ID,PBD.PERSON_FULL_NAME,PBD.CST_RPT_DEPT_SID,
PBD.BU_SID,PBD.FUND_CODE_SID, PBD.DEPT_SID, PBD.ACCOUNT_SID, PBD.PROGRAM_FDM_SID,PBD.PRJ_SID, PBD.PERSON_SID,
PBD.JOB_PRFL_SID, PBD.SUP_ORG_SID, PBD.LOC_SID, PBD.NYU_ACTIVE_CF_SID, PBD.PAY_GRP_SID, PBD.SUP_RPT_DEPT_SID,
PBD.POS_CD,PBD.ENC_CHANGE_FRINGE_AMT, PBD.CHANGE_TYPE_CD,PBD.RECORD_TYPE_CD,PBD.ENC_CHANGE_AMT,PBD.PRE_ENC_CHANGE_FRINGE_AMT,
PBD.PRE_ENC_CHANGE_AMT,PBD.BDGT_CHANGE_FRINGE_AMT,PBD.BDGT_CHANGE_AMT,PBD.PAY_DSTRB_TRANSFER_AMT,PBD.PAY_DSTRB_FRINGE_AMT,
PBD.PAY_DSTRB_EXTRA_AMT,PBD.PAY_DSTRB_OT_AMT,PBD.PAY_DSTRB_BASE_AMT,
UPPER (PER.FULL_NAME) AS STD_FULL_NAME
FROM sysadm.F_POS_SAL_EXP_DET PBD
LEFT JOIN SYSADM.D_PERSON per
ON PBD.PERSON_SID = PER.PERSON_SID
LEFT JOIN SYSADM.PS_NYU_D_DT_PERIOD_FIN pp
ON PBD.EFF_PPERIOD_SID = PP.PPERIOD_SID
WHERE PBD.SRC_SYS_ID = 'WD') PBD
ON PBD.PPERIOD_END_DT BETWEEN dd.fiscal_year_begin_dt
AND dd.PPERIOD_END_DT
-- Get the latest Position Dimension for the given accounting period. New join below
LEFT JOIN SYSADM.D_POSITION_V2 pos
ON PBD.POS_CD = POS.POS_CD
AND DD.PPERIOD_END_DT >= POS.EFF_START_DT
AND DD.PPERIOD_END_DT < POS.EFF_END_DT
WHERE ( DD.DAY_DT = DD.PPERIOD_END_DT
OR DD.day_dt =
GREATEST (TRUNC (SYSDATE),
(SELECT max_post_dt FROM max_dt))) -- modified 5/4/2016 to set the limit the YTD calculation based on post date and sysdate, which ever is later
--AND PBD.pos_cd = '1000267' -- '1000325' -- '6009177' -- '1007985' --
GROUP BY dd.FISCAL_YEAR_NUM,
dd.FISCAL_YEAR_BEGIN_DT,
dd.PPERIOD_END_DT,
dd.DAY_DT,
dd.DAY_SID,
NVL (dd.PPERIOD_SID, 0),
--NVL (PBD.POS_SID, 0), -- the latest position row for the given accunting period
NVL (POS.POS_SID, 0), -- the latest position row for the given accunting period
NVL (PBD.BU_SID, 0),
NVL (PBD.FUND_CODE_SID, 0),
NVL (PBD.DEPT_SID, 0),
NVL (PBD.ACCOUNT_SID, 0),
NVL (PBD.PROGRAM_FDM_SID, 0),
NVL (PBD.PRJ_SID, 0),
NVL (PBD.PERSON_SID, 0),
NVL (PBD.JOB_PRFL_SID, 0),
NVL (PBD.SUP_ORG_SID, 0),
NVL (PBD.LOC_SID, 0),
NVL (PBD.NYU_ACTIVE_CF_SID, 0),
NVL (PBD.PAY_GRP_SID, 0),
NVL (PBD.SUP_RPT_DEPT_SID, 0),
NVL (PBD.CST_RPT_DEPT_SID, 0),
CASE
WHEN PBD.PERSON_SID != 0 THEN PBD.STD_FULL_NAME
ELSE NVL (PBD.PERSON_FULL_NAME, '-')
END,
NVL (PBD.POS_CD, '-'),
NVL (PBD.OLD_CAMPUS_ID, '-')
--ORDER BY NVL (PBD.OLD_CAMPUS_ID, '-'), dd.PPERIOD_END_DT, dd.day_dt---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 25M| 16G| | 5890K (1)| 19:38:06 | | | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6C4E_8FA49205 | | | | | | | | | 3 | SORT AGGREGATE | | 1 | 8 | | | | | | | 4 | INDEX FAST FULL SCAN | F_POS_SAL_EXP_DET | 14M| 113M| | 91688 (1)| 00:18:21 | | | | 5 | HASH UNIQUE | | 25M| 16G| | 5798K (1)| 19:19:46 | | | | 6 | HASH GROUP BY | | 25M| 16G| 17G| 5798K (1)| 19:19:46 | | | |* 7 | HASH JOIN RIGHT OUTER | | 25M| 16G| 42M| 2132K (1)| 07:06:32 | | | | 8 | VIEW | index$_join$_014 | 1024K| 30M| | 13022 (1)| 00:02:37 | | | |* 9 | HASH JOIN | | | | | | | | | | 10 | INDEX FAST FULL SCAN | PKD_POSITION_V2 | 1024K| 30M| | 4028 (1)| 00:00:49 | | | | 11 | INDEX FAST FULL SCAN | DZZZPOSTION_V2 | 1024K| 30M| | 7679 (1)| 00:01:33 | | | |* 12 | VIEW | | 25M| 15G| | 1317K (1)| 04:23:35 | | | | 13 | MERGE JOIN OUTER | | 25M| 15G| | 1317K (1)| 04:23:35 | | | | 14 | SORT JOIN | | 1353 | 70356 | | 60 (7)| 00:00:01 | | | | 15 | VIEW | | 1353 | 70356 | | 59 (6)| 00:00:01 | | | | 16 | SORT ORDER BY | | 1353 | 82533 | | 59 (6)| 00:00:01 | | | | 17 | WINDOW SORT | | 1353 | 82533 | | 59 (6)| 00:00:01 | | | | 18 | MERGE JOIN | | 1353 | 82533 | | 55 (2)| 00:00:01 | | | |* 19 | TABLE ACCESS BY INDEX ROWID| PS_NYU_D_DAY | 1573 | 28314 | | 47 (0)| 00:00:01 | | | |* 20 | INDEX RANGE SCAN | PS0NYU_D_DAY | 661 | | | 5 (0)| 00:00:01 | | | | 21 | VIEW | | 1 | 9 | | 2 (0)| 00:00:01 | | | | 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C4E_8FA49205 | 1 | 8 | | 2 (0)| 00:00:01 | | | |* 23 | FILTER | | | | | | | | | |* 24 | SORT JOIN | | 288 | 12384 | | 8 (13)| 00:00:01 | | | |* 25 | TABLE ACCESS FULL | PS_NYU_D_DT_PERIOD_FIN | 288 | 12384 | | 7 (0)| 00:00:01 | | | |* 26 | FILTER | | | | | | | | | |* 27 | SORT JOIN | | 7615K| 4437M| 9916M| 1314K (1)| 04:22:58 | | | | 28 | VIEW | | 7615K| 4437M| | 331K (1)| 01:06:20 | | | |* 29 | HASH JOIN RIGHT OUTER | | 7615K| 4582M| | 331K (1)| 01:06:20 | | | | 30 | TABLE ACCESS FULL | PS_NYU_D_DT_PERIOD_FIN | 389 | 4668 | | 7 (0)| 00:00:01 | | | | 31 | VIEW | | 7615K| 4495M| | 331K (1)| 01:06:20 | | | |* 32 | HASH JOIN RIGHT OUTER | | 7615K| 1329M| 158M| 331K (1)| 01:06:20 | | | | 33 | PARTITION HASH SINGLE | | 4619K| 105M| | 55205 (1)| 00:11:03 | 1 | 1 | | 34 | TABLE ACCESS FULL | D_PERSON | 4619K| 105M| | 55205 (1)| 00:11:03 | 1 | 1 | | 35 | PARTITION HASH ALL | | 7615K| 1154M| | 206K (1)| 00:41:22 | 1 | 64 | |* 36 | TABLE ACCESS FULL | F_POS_SAL_EXP_DET | 7615K| 1154M| | 206K (1)| 00:41:22 | 1 | 64 | | 37 | VIEW | | 1 | 9 | | 2 (0)| 00:00:01 | | | | 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6C4E_8FA49205 | 1 | 8 | | 2 (0)| 00:00:01 | | | ----------------------------------------------------------------------------------------------------------------------------------------------
专家解答
我们需要查看带有性能指标的 * runtime * 计划。所以你可以这样做:
-运行 “设置服务器输出关闭”
-向您的查询添加/* gather_plan_statistics */提示
-运行查询
-通过运行获得 * true * 计划:
选择 *
从表 (dbms_xplan.display_curs(null，null，'ALLSTATS LAST'));
-运行 “设置服务器输出关闭”
-向您的查询添加/* gather_plan_statistics */提示
-运行查询
-通过运行获得 * true * 计划:
选择 *
从表 (dbms_xplan.display_curs(null，null，'ALLSTATS LAST'));
文章转载自ASKTOM，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
