Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Himanshu.

Asked: October 11, 2019 - 5:25 pm UTC

Last updated: October 29, 2019 - 11:32 am UTC

Version: 11.2.

Viewed 1000+ times

You Asked


Hi Team,

we have a SQL query which is a source query for the ETL load job, this take around 3 hours to run, could you please help us how we can make it run faster.

The row count of the tables involved are as follows.
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 |       |       |
----------------------------------------------------------------------------------------------------------------------------------------------

and Connor said...

We need to see the *runtime* plan with the performance metrics. So you can do this:

- Run "set serveroutput off"
- Adding the /*+ gather_plan_statistics */ hint to your query
- Run the query
- Get the *true* plan by running:

select *
from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Recode

Gh, October 22, 2019 - 5:40 am UTC

First change the beginning as
WITH max_dt AS (SELECT MAX (JRNL_POST_DT) max_post_dt FROM SYSADM.F_POS_SAL_EXP_DET
Where JRNL_POST_DT > trunc (sysdate)
)

For you are filtering on the greatest afterwhile so you don't need lesser dates. If you have a local index on this date this would be faster scanning the hash partitioned table.

And second, provide the tables desc in order to optimize the whole query.

Himanshu Kandpal, October 23, 2019 - 11:45 pm UTC

Thanks for the help.
Please find the details as requested by you.

1 select *
2* from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'))
19:34:45 PSDUDW01.ITS.NYU.EDU SQL> /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------
SQL_ID 81j9xht5pnu6j, child number 0
-------------------------------------
WITH max_dt AS (SELECT /*+ gather_plan_statistics */ 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),

Plan hash value: 3372268594

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | Writes | OMem | 1Mem | Used-Mem | U
sed-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------
| 0 | SELECT STATEMENT | | 1 | | 7167K|00:06:54.04 | 1298K| 162M| 977K| | | |
|
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | 7167K|00:06:54.04 | 1298K| 162M| 977K| | | |
|
| 2 | LOAD AS SELECT | | 1 | | 0 |00:00:06.05 | 339K| 0 | 1 | 269K| 269K| 269K (0)|
|
| 3 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:06.03 | 339K| 0 | 0 | | | |
|
| 4 | INDEX FAST FULL SCAN | F_POS_SAL_EXP_DET | 1 | 14M| 14M|00:00:06.29 | 339K| 0 | 0 | | | |
|
| 5 | SORT UNIQUE | | 1 | 124M| 7167K|00:06:45.14 | 959K| 162M| 977K| 1891M| 13M| 97M (1)|
1685K|
| 6 | SORT GROUP BY | | 1 | 124M| 7167K|00:05:31.51 | 959K| 161M| 761K| 438M| 17M| 97M (1)|
2791K|
|* 7 | HASH JOIN RIGHT OUTER | | 1 | 124M| 50M|00:05:47.39 | 958K| 161M| 404K| 84M| 9148K| 119M (0)|
|
| 8 | VIEW | index$_join$_014 | 1 | 1026K| 1198K|00:00:01.37 | 11242 | 0 | 0 | | | |
|
|* 9 | HASH JOIN | | 1 | | 1198K|00:00:01.23 | 11242 | 0 | 0 | 48M| 5667K| 74M (0)|
|
| 10 | INDEX FAST FULL SCAN | PKD_POSITION_V2 | 1 | 1026K| 1198K|00:00:00.16 | 3784 | 0 | 0 | | | |
|
| 11 | INDEX FAST FULL SCAN | DZZZPOSTION_V2 | 1 | 1026K| 1198K|00:00:00.33 | 7458 | 0 | 0 | | | |
|
|* 12 | VIEW | | 1 | 124M| 50M|00:04:18.63 | 947K| 161M| 404K| | | |
|
| 13 | MERGE JOIN OUTER | | 1 | 124M| 1532M|02:09:05.30 | 947K| 161M| 404K| | | |
|
| 14 | SORT JOIN | | 1 | 6419 | 2261 |00:00:00.06 | 2801 | 1 | 0 | 196K| 196K| 174K (0)|
|
| 15 | VIEW | | 1 | 6419 | 2261 |00:00:00.06 | 2801 | 1 | 0 | | | |
|
| 16 | SORT ORDER BY | | 1 | 6419 | 2261 |00:00:00.06 | 2801 | 1 | 0 | 267K| 267K| 237K (0)|
|
| 17 | WINDOW SORT | | 1 | 6419 | 2261 |00:00:00.06 | 2801 | 1 | 0 | 267K| 267K| 237K (0)|
|
| 18 | MERGE JOIN | | 1 | 6419 | 2261 |00:00:00.05 | 2801 | 1 | 0 | | | |
|
|* 19 | TABLE ACCESS BY INDEX ROWID| PS_NYU_D_DAY | 1 | 1587 | 2267 |00:00:00.03 | 2779 | 1 | 0 | | | |
|
|* 20 | INDEX RANGE SCAN | PS0NYU_D_DAY | 1 | 661 | 43785 |00:00:00.01 | 221 | 1 | 0 | | | |
|
| 21 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 5 | 1 | 0 | | | |
|
| 22 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ED1_8FA49205 | 1 | 1 | 1 |00:00:00.01 | 5 | 1 | 0 | | | |
|
|* 23 | FILTER | | 2267 | | 2261 |00:00:00.02 | 22 | 0 | 0 | | | |
|
|* 24 | SORT JOIN | | 2267 | 288 | 136K|00:00:00.02 | 22 | 0 | 0 | 36864 | 36864 |32768 (0)|
|
|* 25 | TABLE ACCESS FULL | PS_NYU_D_DT_PERIOD_FIN | 1 | 288 | 289 |00:00:00.01 | 22 | 0 | 0 | | | |
|
|* 26 | FILTER | | 2261 | | 1532M|00:50:59.92 | 944K| 161M| 404K| | | |
|
|* 27 | SORT JOIN | | 2261 | 7775K| 6141M|03:12:01.53 | 944K| 161M| 404K| 1779M| 13M| 961M (1)|
1584K|
| 28 | VIEW | | 1 | 7775K| 7727K|00:00:58.58 | 944K| 940K| 0 | | | |
|
|* 29 | HASH JOIN RIGHT OUTER | | 1 | 7775K| 7727K|00:00:55.51 | 944K| 940K| 0 | 1134K| 1134K| 1295K (0)|
|
| 30 | TABLE ACCESS FULL | PS_NYU_D_DT_PERIOD_FIN | 1 | 389 | 389 |00:00:00.01 | 22 | 0 | 0 | | | |
|
| 31 | VIEW | | 1 | 7775K| 7727K|00:00:52.65 | 944K| 940K| 0 | | | |
|
|* 32 | HASH JOIN RIGHT OUTER | | 1 | 7775K| 7727K|00:00:51.26 | 944K| 940K| 0 | 214M| 10M| 288M (0)|
|
| 33 | PARTITION HASH SINGLE | | 1 | 4622K| 4622K|00:00:15.69 | 201K| 201K| 0 | | | |
|
| 34 | TABLE ACCESS FULL | D_PERSON | 1 | 4622K| 4622K|00:00:15.03 | 201K| 201K| 0 | | | |
|
| 35 | PARTITION HASH ALL | | 1 | 7775K| 7727K|00:00:15.82 | 743K| 738K| 0 | | | |
|
|* 36 | TABLE ACCESS FULL | F_POS_SAL_EXP_DET | 64 | 7775K| 7727K|00:00:26.64 | 743K| 738K| 0 | | | |
|
| 37 | VIEW | | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | |
|
| 38 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6ED1_8FA49205 | 1 | 1 | 1 |00:00:00.01 | 2 | 0 | 0 | | | |
|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("PBD"."POS_CD"="POS"."POS_CD")
filter(("DD"."PPERIOD_END_DT"<"POS"."EFF_END_DT" AND "DD"."PPERIOD_END_DT">="POS"."EFF_START_DT"))
9 - access(ROWID=ROWID)
12 - filter(("from$_subquery$_013"."DAY_DT"="from$_subquery$_013"."QCSJ_C000000001300000" OR "from$_subquery$_013"."DAY_DT"=GREATEST(TRUNC(SYSDATE@!),)))
19 - filter("DD"."FISCAL_YEAR_NUM">=2014)
20 - access("DD"."DAY_DT"<=GREATEST(TRUNC(SYSDATE@!),))
23 - filter("DD"."DAY_DT">="PP"."PPERIOD_BEGIN_DT")
24 - access("DD"."DAY_DT"<="PP"."PPERIOD_END_DT")
filter("DD"."DAY_DT"<="PP"."PPERIOD_END_DT")
25 - filter(("PP"."PPERIOD_CD"<=12 AND "PP"."PPERIOD_CD">=1 AND "PP"."DT_PATTERN_CD"='DT'))
26 - filter("PBD"."PPERIOD_END_DT">="DD"."FISCAL_YEAR_BEGIN_DT")
27 - access(INTERNAL_FUNCTION("PBD"."PPERIOD_END_DT")<=INTERNAL_FUNCTION("DD"."PPERIOD_END_DT"))
filter(INTERNAL_FUNCTION("PBD"."PPERIOD_END_DT")<=INTERNAL_FUNCTION("DD"."PPERIOD_END_DT"))
29 - access("PBD"."EFF_PPERIOD_SID"="PP"."PPERIOD_SID")
32 - access("PBD"."PERSON_SID"="PER"."PERSON_SID")
36 - filter("PBD"."SRC_SYS_ID"='WD')


84 rows selected.

Elapsed: 00:00:00.11
19:34:47 PSDUDW01.ITS.NYU.EDU SQL>

sending again as forgot to format the reply

Himanshu Kandpal, October 24, 2019 - 12:01 am UTC


Thanks for the help.
Please find the details as requested by you.


1  select *
  2* from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'))
19:34:45 PSDUDW01.ITS.NYU.EDU SQL> /

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------
SQL_ID  81j9xht5pnu6j, child number 0
-------------------------------------
WITH max_dt      AS  (SELECT /*+ gather_plan_statistics */  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),

Plan hash value: 3372268594

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------
| Id  | Operation                              | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | U
sed-Tmp|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT                       |                             |      1 |        |   7167K|00:06:54.04 |    1298K|    162M|    977K|       |       |          |
      |
|   1 |  TEMP TABLE TRANSFORMATION             |                             |      1 |        |   7167K|00:06:54.04 |    1298K|    162M|    977K|       |       |          |
      |
|   2 |   LOAD AS SELECT                       |                             |      1 |        |      0 |00:00:06.05 |     339K|      0 |      1 |   269K|   269K|  269K (0)|
      |
|   3 |    SORT AGGREGATE                      |                             |      1 |      1 |      1 |00:00:06.03 |     339K|      0 |      0 |       |       |          |
      |
|   4 |     INDEX FAST FULL SCAN               | F_POS_SAL_EXP_DET           |      1 |     14M|     14M|00:00:06.29 |     339K|      0 |      0 |       |       |          |
      |
|   5 |   SORT UNIQUE                          |                             |      1 |    124M|   7167K|00:06:45.14 |     959K|    162M|    977K|  1891M|    13M|   97M (1)|
 1685K|
|   6 |    SORT GROUP BY                       |                             |      1 |    124M|   7167K|00:05:31.51 |     959K|    161M|    761K|   438M|    17M|   97M (1)|
 2791K|
|*  7 |     HASH JOIN RIGHT OUTER              |                             |      1 |    124M|     50M|00:05:47.39 |     958K|    161M|    404K|    84M|  9148K|  119M (0)|
      |
|   8 |      VIEW                              | index$_join$_014            |      1 |   1026K|   1198K|00:00:01.37 |   11242 |      0 |      0 |       |       |          |
      |
|*  9 |       HASH JOIN                        |                             |      1 |        |   1198K|00:00:01.23 |   11242 |      0 |      0 |    48M|  5667K|   74M (0)|
      |
|  10 |        INDEX FAST FULL SCAN            | PKD_POSITION_V2             |      1 |   1026K|   1198K|00:00:00.16 |    3784 |      0 |      0 |       |       |          |
      |
|  11 |        INDEX FAST FULL SCAN            | DZZZPOSTION_V2              |      1 |   1026K|   1198K|00:00:00.33 |    7458 |      0 |      0 |       |       |          |
      |
|* 12 |      VIEW                              |                             |      1 |    124M|     50M|00:04:18.63 |     947K|    161M|    404K|       |       |          |
      |
|  13 |       MERGE JOIN OUTER                 |                             |      1 |    124M|   1532M|02:09:05.30 |     947K|    161M|    404K|       |       |          |
      |
|  14 |        SORT JOIN                       |                             |      1 |   6419 |   2261 |00:00:00.06 |    2801 |      1 |      0 |   196K|   196K|  174K (0)|
      |
|  15 |         VIEW                           |                             |      1 |   6419 |   2261 |00:00:00.06 |    2801 |      1 |      0 |       |       |          |
      |
|  16 |          SORT ORDER BY                 |                             |      1 |   6419 |   2261 |00:00:00.06 |    2801 |      1 |      0 |   267K|   267K|  237K (0)|
      |
|  17 |           WINDOW SORT                  |                             |      1 |   6419 |   2261 |00:00:00.06 |    2801 |      1 |      0 |   267K|   267K|  237K (0)|
      |
|  18 |            MERGE JOIN                  |                             |      1 |   6419 |   2261 |00:00:00.05 |    2801 |      1 |      0 |       |       |          |
      |
|* 19 |             TABLE ACCESS BY INDEX ROWID| PS_NYU_D_DAY                |      1 |   1587 |   2267 |00:00:00.03 |    2779 |      1 |      0 |       |       |          |
      |
|* 20 |              INDEX RANGE SCAN          | PS0NYU_D_DAY                |      1 |    661 |  43785 |00:00:00.01 |     221 |      1 |      0 |       |       |          |
      |
|  21 |               VIEW                     |                             |      1 |      1 |      1 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
      |
|  22 |                TABLE ACCESS FULL       | SYS_TEMP_0FD9D6ED1_8FA49205 |      1 |      1 |      1 |00:00:00.01 |       5 |      1 |      0 |       |       |          |
      |
|* 23 |             FILTER                     |                             |   2267 |        |   2261 |00:00:00.02 |      22 |      0 |      0 |       |       |          |
      |
|* 24 |              SORT JOIN                 |                             |   2267 |    288 |    136K|00:00:00.02 |      22 |      0 |      0 | 36864 | 36864 |32768  (0)|
      |
|* 25 |               TABLE ACCESS FULL        | PS_NYU_D_DT_PERIOD_FIN      |      1 |    288 |    289 |00:00:00.01 |      22 |      0 |      0 |       |       |          |
      |
|* 26 |        FILTER                          |                             |   2261 |        |   1532M|00:50:59.92 |     944K|    161M|    404K|       |       |          |
      |
|* 27 |         SORT JOIN                      |                             |   2261 |   7775K|   6141M|03:12:01.53 |     944K|    161M|    404K|  1779M|    13M|  961M (1)|
 1584K|
|  28 |          VIEW                          |                             |      1 |   7775K|   7727K|00:00:58.58 |     944K|    940K|      0 |       |       |          |
      |
|* 29 |           HASH JOIN RIGHT OUTER        |                             |      1 |   7775K|   7727K|00:00:55.51 |     944K|    940K|      0 |  1134K|  1134K| 1295K (0)|
      |
|  30 |            TABLE ACCESS FULL           | PS_NYU_D_DT_PERIOD_FIN      |      1 |    389 |    389 |00:00:00.01 |      22 |      0 |      0 |       |       |          |
      |
|  31 |            VIEW                        |                             |      1 |   7775K|   7727K|00:00:52.65 |     944K|    940K|      0 |       |       |          |
      |
|* 32 |             HASH JOIN RIGHT OUTER      |                             |      1 |   7775K|   7727K|00:00:51.26 |     944K|    940K|      0 |   214M|    10M|  288M (0)|
      |
|  33 |              PARTITION HASH SINGLE     |                             |      1 |   4622K|   4622K|00:00:15.69 |     201K|    201K|      0 |       |       |          |
      |
|  34 |               TABLE ACCESS FULL        | D_PERSON                    |      1 |   4622K|   4622K|00:00:15.03 |     201K|    201K|      0 |       |       |          |
      |
|  35 |              PARTITION HASH ALL        |                             |      1 |   7775K|   7727K|00:00:15.82 |     743K|    738K|      0 |       |       |          |
      |
|* 36 |               TABLE ACCESS FULL        | F_POS_SAL_EXP_DET           |     64 |   7775K|   7727K|00:00:26.64 |     743K|    738K|      0 |       |       |          |
      |
|  37 |       VIEW                             |                             |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
      |
|  38 |        TABLE ACCESS FULL               | SYS_TEMP_0FD9D6ED1_8FA49205 |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |      0 |       |       |          |
      |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------

Predicate Information (identified by operation id):
---------------------------------------------------

   7 - access("PBD"."POS_CD"="POS"."POS_CD")
       filter(("DD"."PPERIOD_END_DT"<"POS"."EFF_END_DT" AND "DD"."PPERIOD_END_DT">="POS"."EFF_START_DT"))
   9 - access(ROWID=ROWID)
  12 - filter(("from$_subquery$_013"."DAY_DT"="from$_subquery$_013"."QCSJ_C000000001300000" OR "from$_subquery$_013"."DAY_DT"=GREATEST(TRUNC(SYSDATE@!),)))
  19 - filter("DD"."FISCAL_YEAR_NUM">=2014)
  20 - access("DD"."DAY_DT"<=GREATEST(TRUNC(SYSDATE@!),))
  23 - filter("DD"."DAY_DT">="PP"."PPERIOD_BEGIN_DT")
  24 - access("DD"."DAY_DT"<="PP"."PPERIOD_END_DT")
       filter("DD"."DAY_DT"<="PP"."PPERIOD_END_DT")
  25 - filter(("PP"."PPERIOD_CD"<=12 AND "PP"."PPERIOD_CD">=1 AND "PP"."DT_PATTERN_CD"='DT'))
  26 - filter("PBD"."PPERIOD_END_DT">="DD"."FISCAL_YEAR_BEGIN_DT")
  27 - access(INTERNAL_FUNCTION("PBD"."PPERIOD_END_DT")<=INTERNAL_FUNCTION("DD"."PPERIOD_END_DT"))
       filter(INTERNAL_FUNCTION("PBD"."PPERIOD_END_DT")<=INTERNAL_FUNCTION("DD"."PPERIOD_END_DT"))
  29 - access("PBD"."EFF_PPERIOD_SID"="PP"."PPERIOD_SID")
  32 - access("PBD"."PERSON_SID"="PER"."PERSON_SID")
  36 - filter("PBD"."SRC_SYS_ID"='WD')


84 rows selected.

Elapsed: 00:00:00.11
19:34:47 PSDUDW01.ITS.NYU.EDU SQL>

Connor McDonald
October 29, 2019 - 11:32 am UTC

Line 26-28 are where you will want to focus your efforts. Notice that you ran the SORT on line 27, over 2000 times! (coming to a total number of rows sorted at over 6 billion rows). Hence the 3 hours down in that phase.

I'd be looking at trying to filter out data earlier so that line 29 is not funnelling 7million rows into the wrapping join.


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.