Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Manoj.

Asked: October 07, 2020 - 4:38 pm UTC

Last updated: October 09, 2020 - 3:02 am UTC

Version: 12.2.0.1

Viewed 1000+ times

You Asked

Hi Tom,

I have bee trying to tune the following sql. This is a datawarehouse query which used to run for 2 hrs but gradually after 3 months, this query is now taking around 3 hrs. I tried creating index, use hints (parallel hints, index hint)but getting worse execution plan. I also ran sql tuning advisor and it shows this is the best plan.
Please guide on this.

SELECT fpa.pnl_sum_rec_key,
       NVL (
          (SELECT SUM (fpa2.pnl_ptd_actual)
             FROM fact_pnl_consol fpa2
            WHERE     fpa2.pnl_report_key = fpa.pnl_report_key
                  AND fpa2.pnl_period_yr = fpa.pnl_period_yr - 1
                  AND fpa2.pnl_period_qtr = fpa.pnl_period_qtr
                  AND fpa2.pnl_period_num = fpa.pnl_period_num
                  AND fpa2.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa2.PNL_RPT_CURRENCY
                  AND NVL (fpa2.ASC_STANDARD, 'X') =
                         NVL (fpa.ASC_STANDARD, 'X')),
          0)
          calc_ptd_act_py,
       NVL (
          (SELECT SUM (fpa3.pnl_ptd_actual)
             FROM fact_pnl_consol fpa3
            WHERE     fpa3.pnl_report_key = fpa.pnl_report_key
                  AND fpa3.pnl_period_yr = fpa.pnl_period_yr
                  AND fpa3.pnl_period_qtr = fpa.pnl_period_qtr
                  AND fpa3.pnl_period_num <= fpa.pnl_period_num
                  AND fpa3.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa3.PNL_RPT_CURRENCY
                  AND NVL (fpa3.ASC_STANDARD, 'X') =
                         NVL (fpa.ASC_STANDARD, 'X')),
          0)
          calc_qtd_act,
       NVL (
          (SELECT SUM (fpa4.pnl_ptd_budget)
             FROM fact_pnl_consol fpa4
            WHERE     fpa4.pnl_report_key = fpa.pnl_report_key
                  AND fpa4.pnl_period_yr = fpa.pnl_period_yr
                  AND fpa4.pnl_period_qtr = fpa.pnl_period_qtr
                  AND fpa4.pnl_period_num <= fpa.pnl_period_num
                  AND fpa4.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa4.PNL_RPT_CURRENCY
                  AND NVL (fpa4.ASC_STANDARD, 'X') =
                         NVL (fpa.ASC_STANDARD, 'X')),
          0)
          calc_qtd_bud,
       NVL (
          (SELECT SUM (fpa5.pnl_ptd_actual)
             FROM fact_pnl_consol fpa5
            WHERE     fpa5.pnl_report_key = fpa.pnl_report_key
                  AND fpa5.pnl_period_yr = fpa.pnl_period_yr - 1
                  AND fpa5.pnl_period_qtr = fpa.pnl_period_qtr
                  AND fpa5.pnl_period_num <= fpa.pnl_period_num
                  AND fpa5.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa5.PNL_RPT_CURRENCY
                  AND NVL (fpa5.ASC_STANDARD, 'X') =
                         NVL (fpa.ASC_STANDARD, 'X')),
          0)
          calc_qtd_act_py,
       NVL (
          (SELECT SUM (fpa6.pnl_ptd_actual)
             FROM fact_pnl_consol fpa6
            WHERE     fpa6.pnl_report_key = fpa.pnl_report_key
                  AND fpa6.pnl_period_yr = fpa.pnl_period_yr
                  AND fpa6.pnl_period_num <= fpa.pnl_period_num
                  AND fpa6.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa6.PNL_RPT_CURRENCY
                  AND NVL (fpa6.ASC_STANDARD, 'X') =
                         NVL (fpa.ASC_STANDARD, 'X')),
          0)
          calc_ytd_act,
       NVL (
          (SELECT SUM (fpa7.pnl_ptd_budget)
             FROM fact_pnl_consol fpa7
            WHERE     fpa7.pnl_report_key = fpa.pnl_report_key
                  AND fpa7.pnl_period_yr = fpa.pnl_period_yr
                  AND fpa7.pnl_period_num <= fpa.pnl_period_num
                  AND fpa7.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa7.PNL_RPT_CURRENCY
                  AND NVL (fpa7.ASC_STANDARD, 'X') =
                         NVL (fpa.ASC_STANDARD, 'X')),
          0)
          calc_ytd_bud,
       NVL (
          (SELECT SUM (fpa8.pnl_ptd_actual)
             FROM fact_pnl_consol fpa8
            WHERE     fpa8.pnl_report_key = fpa.pnl_report_key
                  AND fpa8.pnl_period_yr = fpa.pnl_period_yr - 1
                  AND fpa8.pnl_period_num <= fpa.pnl_period_num
                  AND fpa8.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa8.PNL_RPT_CURRENCY
                  AND NVL (fpa8.ASC_STANDARD, 'X') =
                         NVL (fpa.ASC_STANDARD, 'X')),
          0)
          calc_ytd_act_py
  FROM fact_pnl_consol fpa, DIM_PNL_RPT_NAME dprn
 WHERE                                                --fpa.pnl_report_key > 7
      fpa  .pnl_report_key = dprn.prn_key
       AND SUBSTR (dprn.prn_rpt_code, 1, 3) = 'BUD'
       AND fpa.PNL_PERIOD_YR >= 2009
--------------------------------------------------------------------------
Below is the current execution plan:

 
-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |  OMem |  1Mem |  O/1/M   |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |        |       |   508K(100)|          |       |       |          |
|   1 |  SORT AGGREGATE                      |                  |      1 |    32 |            |          |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |      1 |    32 |     7   (0)| 00:00:01 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |      4 |       |     3   (0)| 00:00:01 |       |       |          |
|   4 |  SORT AGGREGATE                      |                  |      1 |    32 |            |          |       |       |          |
|*  5 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |      1 |    32 |     4   (0)| 00:00:01 |       |       |          |
|*  6 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |      1 |       |     3   (0)| 00:00:01 |       |       |          |
|   7 |  SORT AGGREGATE                      |                  |      1 |    29 |            |          |       |       |          |
|*  8 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |      1 |    29 |     4   (0)| 00:00:01 |       |       |          |
|*  9 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |      1 |       |     3   (0)| 00:00:01 |       |       |          |
|  10 |  SORT AGGREGATE                      |                  |      1 |    32 |            |          |       |       |          |
|* 11 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |      1 |    32 |     4   (0)| 00:00:01 |       |       |          |
|* 12 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |      1 |       |     3   (0)| 00:00:01 |       |       |          |
|  13 |  SORT AGGREGATE                      |                  |      1 |    29 |            |          |       |       |          |
|* 14 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |      1 |    29 |     4   (0)| 00:00:01 |       |       |          |
|* 15 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |      1 |       |     3   (0)| 00:00:01 |       |       |          |
|  16 |  SORT AGGREGATE                      |                  |      1 |    26 |            |          |       |       |          |
|* 17 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |      1 |    26 |     4   (0)| 00:00:01 |       |       |          |
|* 18 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |      1 |       |     3   (0)| 00:00:01 |       |       |          |
|  19 |  SORT AGGREGATE                      |                  |      1 |    29 |            |          |       |       |          |
|* 20 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |      1 |    29 |     4   (0)| 00:00:01 |       |       |          |
|* 21 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |      1 |       |     3   (0)| 00:00:01 |       |       |          |
|* 22 |  HASH JOIN                           |                  |  18418 |   845K|  5294   (1)| 00:00:01 |  2545K|  2545K|     1/0/0|
|* 23 |   TABLE ACCESS FULL                  | DIM_PNL_RPT_NAME |      1 |    15 |     2   (0)| 00:00:01 |       |       |          |
|* 24 |   TABLE ACCESS FULL                  | FACT_PNL_CONSOL  |   1447K|    44M|  5288   (1)| 00:00:01 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("FPA2"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA2"."ASC_STANDARD",'X')=NVL(:B2,'X')))
   3 - access("FPA2"."PNL_RPT_ROW_NUM"=:B1 AND "FPA2"."PNL_REPORT_KEY"=:B2 AND "FPA2"."PNL_RPT_CURRENCY"=:B3 AND 
              "FPA2"."PNL_PERIOD_YR"=:B4-1 AND "FPA2"."PNL_PERIOD_NUM"=:B5)
   5 - filter(("FPA3"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA3"."ASC_STANDARD",'X')=NVL(:B2,'X')))
   6 - access("FPA3"."PNL_RPT_ROW_NUM"=:B1 AND "FPA3"."PNL_REPORT_KEY"=:B2 AND "FPA3"."PNL_RPT_CURRENCY"=:B3 AND 
              "FPA3"."PNL_PERIOD_YR"=:B4 AND "FPA3"."PNL_PERIOD_NUM"<=:B5)
   8 - filter(("FPA4"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA4"."ASC_STANDARD",'X')=NVL(:B2,'X')))
   9 - access("FPA4"."PNL_RPT_ROW_NUM"=:B1 AND "FPA4"."PNL_REPORT_KEY"=:B2 AND "FPA4"."PNL_RPT_CURRENCY"=:B3 AND 
              "FPA4"."PNL_PERIOD_YR"=:B4 AND "FPA4"."PNL_PERIOD_NUM"<=:B5)
  11 - filter(("FPA5"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA5"."ASC_STANDARD",'X')=NVL(:B2,'X')))
  12 - access("FPA5"."PNL_RPT_ROW_NUM"=:B1 AND "FPA5"."PNL_REPORT_KEY"=:B2 AND "FPA5"."PNL_RPT_CURRENCY"=:B3 AND 
              "FPA5"."PNL_PERIOD_YR"=:B4-1 AND "FPA5"."PNL_PERIOD_NUM"<=:B5)
  14 - filter(NVL("FPA6"."ASC_STANDARD",'X')=NVL(:B1,'X'))
  15 - access("FPA6"."PNL_RPT_ROW_NUM"=:B1 AND "FPA6"."PNL_REPORT_KEY"=:B2 AND "FPA6"."PNL_RPT_CURRENCY"=:B3 AND 
              "FPA6"."PNL_PERIOD_YR"=:B4 AND "FPA6"."PNL_PERIOD_NUM"<=:B5)
  17 - filter(NVL("FPA7"."ASC_STANDARD",'X')=NVL(:B1,'X'))
  18 - access("FPA7"."PNL_RPT_ROW_NUM"=:B1 AND "FPA7"."PNL_REPORT_KEY"=:B2 AND "FPA7"."PNL_RPT_CURRENCY"=:B3 AND 
              "FPA7"."PNL_PERIOD_YR"=:B4 AND "FPA7"."PNL_PERIOD_NUM"<=:B5)
  20 - filter(NVL("FPA8"."ASC_STANDARD",'X')=NVL(:B1,'X'))
  21 - access("FPA8"."PNL_RPT_ROW_NUM"=:B1 AND "FPA8"."PNL_REPORT_KEY"=:B2 AND "FPA8"."PNL_RPT_CURRENCY"=:B3 AND 
              "FPA8"."PNL_PERIOD_YR"=:B4-1 AND "FPA8"."PNL_PERIOD_NUM"<=:B5)
  22 - access("FPA"."PNL_REPORT_KEY"="DPRN"."PRN_KEY")
  23 - filter(SUBSTR("DPRN"."PRN_RPT_CODE",1,3)='BUD')
  24 - filter(("FPA"."PNL_REPORT_KEY" IS NOT NULL AND "FPA"."PNL_PERIOD_YR">=2009))
 



and Connor said...

Take a look here

https://connor-mcdonald.com/2017/07/03/asktom-tv-episode-8/

which shows an example of how to use the "gather_plan_statistics" hint.

Paste the output back here as a review and we can take a look.

Rating

  (4 ratings)

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

Comments

output using hint

manoj, October 08, 2020 - 10:25 am UTC

 I formatted the utput but when its pasted here, its not clear. can this be uploaded as an attachment 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem |  O/1/M   |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |       |   449K(100)|          |  12649 |00:00:00.06 |    1080 |     41 |       |       |          |
|   1 |  SORT AGGREGATE                      |                  |  11238 |      1 |    32 |            |          |  11238 |00:00:00.15 |   22185 |     34 |       |       |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |  11238 |      1 |    32 |     7   (0)| 00:00:01 |      0 |00:00:00.13 |   22185 |     34 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |  11238 |      3 |       |     3   (0)| 00:00:01 |      0 |00:00:00.13 |   22185 |     34 |       |       |          |
|   4 |  SORT AGGREGATE                      |                  |  11238 |      1 |    32 |            |          |  11238 |00:00:54.32 |      23M|  12669 |       |       |          |
|*  5 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |  11238 |      1 |    32 |     4   (0)| 00:00:01 |   6481K|00:00:53.66 |      23M|  12669 |       |       |          |
|*  6 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |  11238 |      1 |       |     3   (0)| 00:00:01 |     22M|00:00:04.44 |     156K|     25 |       |       |          |
|   7 |  SORT AGGREGATE                      |                  |  11238 |      1 |    29 |            |          |  11238 |00:00:45.72 |      23M|      0 |       |       |          |
|*  8 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |  11238 |      1 |    29 |     4   (0)| 00:00:01 |   6481K|00:00:45.02 |      23M|      0 |       |       |          |
|*  9 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |  11238 |      1 |       |     3   (0)| 00:00:01 |     22M|00:00:04.16 |     156K|      0 |       |       |          |
|  10 |  SORT AGGREGATE                      |                  |  11238 |      1 |    32 |            |          |  11238 |00:00:00.13 |   22185 |      0 |       |       |          |
|* 11 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |  11238 |      1 |    32 |     4   (0)| 00:00:01 |      0 |00:00:00.12 |   22185 |      0 |       |       |          |
|* 12 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |  11238 |      1 |       |     3   (0)| 00:00:01 |      0 |00:00:00.11 |   22185 |      0 |       |       |          |
|  13 |  SORT AGGREGATE                      |                  |  11282 |      1 |    29 |            |          |  11281 |00:00:50.38 |      23M|      0 |       |       |          |
|* 14 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |  11282 |      1 |    29 |     4   (0)| 00:00:01 |     22M|00:00:48.13 |      23M|      0 |       |       |          |
|* 15 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |  11282 |      1 |       |     3   (0)| 00:00:01 |     22M|00:00:04.08 |     156K|      0 |       |       |          |
|  16 |  SORT AGGREGATE                      |                  |  11281 |      1 |    26 |            |          |  11281 |00:00:51.97 |      23M|      0 |       |       |          |
|* 17 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |  11281 |      1 |    26 |     4   (0)| 00:00:01 |     22M|00:00:49.47 |      23M|      0 |       |       |          |
|* 18 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |  11281 |      1 |       |     3   (0)| 00:00:01 |     22M|00:00:04.43 |     156K|      0 |       |       |          |
|  19 |  SORT AGGREGATE                      |                  |  11281 |      1 |    29 |            |          |  11281 |00:00:00.14 |   22272 |      0 |       |       |          |
|* 20 |   TABLE ACCESS BY INDEX ROWID BATCHED| FACT_PNL_CONSOL  |  11281 |      1 |    29 |     4   (0)| 00:00:01 |      0 |00:00:00.13 |   22272 |      0 |       |       |          |
|* 21 |    INDEX RANGE SCAN                  | IDX$$_562D0003   |  11281 |      1 |       |     3   (0)| 00:00:01 |      0 |00:00:00.11 |   22272 |      0 |       |       |          |
|* 22 |  HASH JOIN                           |                  |      1 |  16547 |   759K|  4664   (1)| 00:00:01 |  12649 |00:00:00.06 |    1080 |     41 |  2545K|  2545K|     1/0/0|
|* 23 |   TABLE ACCESS FULL                  | DIM_PNL_RPT_NAME |      1 |      1 |    15 |     2   (0)| 00:00:01 |     15 |00:00:00.01 |       3 |      2 |       |       |          |
|* 24 |   TABLE ACCESS FULL                  | FACT_PNL_CONSOL  |      1 |   1300K|    39M|  4658   (1)| 00:00:01 |  13981 |00:00:00.03 |    1074 |     38 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(("FPA2"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA2"."ASC_STANDARD",'X')=NVL(:B2,'X')))
   3 - access("FPA2"."PNL_RPT_ROW_NUM"=:B1 AND "FPA2"."PNL_REPORT_KEY"=:B2 AND "FPA2"."PNL_RPT_CURRENCY"=:B3 AND "FPA2"."PNL_PERIOD_YR"=:B4-1 AND "FPA2"."PNL_PERIOD_NUM"=:B5)
   5 - filter(("FPA3"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA3"."ASC_STANDARD",'X')=NVL(:B2,'X')))
   6 - access("FPA3"."PNL_RPT_ROW_NUM"=:B1 AND "FPA3"."PNL_REPORT_KEY"=:B2 AND "FPA3"."PNL_RPT_CURRENCY"=:B3 AND "FPA3"."PNL_PERIOD_YR"=:B4 AND "FPA3"."PNL_PERIOD_NUM"<=:B5)
   8 - filter(("FPA4"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA4"."ASC_STANDARD",'X')=NVL(:B2,'X')))
   9 - access("FPA4"."PNL_RPT_ROW_NUM"=:B1 AND "FPA4"."PNL_REPORT_KEY"=:B2 AND "FPA4"."PNL_RPT_CURRENCY"=:B3 AND "FPA4"."PNL_PERIOD_YR"=:B4 AND "FPA4"."PNL_PERIOD_NUM"<=:B5)
  11 - filter(("FPA5"."PNL_PERIOD_QTR"=:B1 AND NVL("FPA5"."ASC_STANDARD",'X')=NVL(:B2,'X')))
  12 - access("FPA5"."PNL_RPT_ROW_NUM"=:B1 AND "FPA5"."PNL_REPORT_KEY"=:B2 AND "FPA5"."PNL_RPT_CURRENCY"=:B3 AND "FPA5"."PNL_PERIOD_YR"=:B4-1 AND "FPA5"."PNL_PERIOD_NUM"<=:B5)
  14 - filter(NVL("FPA6"."ASC_STANDARD",'X')=NVL(:B1,'X'))
  15 - access("FPA6"."PNL_RPT_ROW_NUM"=:B1 AND "FPA6"."PNL_REPORT_KEY"=:B2 AND "FPA6"."PNL_RPT_CURRENCY"=:B3 AND "FPA6"."PNL_PERIOD_YR"=:B4 AND "FPA6"."PNL_PERIOD_NUM"<=:B5)
  17 - filter(NVL("FPA7"."ASC_STANDARD",'X')=NVL(:B1,'X'))
  18 - access("FPA7"."PNL_RPT_ROW_NUM"=:B1 AND "FPA7"."PNL_REPORT_KEY"=:B2 AND "FPA7"."PNL_RPT_CURRENCY"=:B3 AND "FPA7"."PNL_PERIOD_YR"=:B4 AND "FPA7"."PNL_PERIOD_NUM"<=:B5)
  20 - filter(NVL("FPA8"."ASC_STANDARD",'X')=NVL(:B1,'X'))
  21 - access("FPA8"."PNL_RPT_ROW_NUM"=:B1 AND "FPA8"."PNL_REPORT_KEY"=:B2 AND "FPA8"."PNL_RPT_CURRENCY"=:B3 AND "FPA8"."PNL_PERIOD_YR"=:B4-1 AND "FPA8"."PNL_PERIOD_NUM"<=:B5)
  22 - access("FPA"."PNL_REPORT_KEY"="DPRN"."PRN_KEY")
  23 - filter(SUBSTR("DPRN"."PRN_RPT_CODE",1,3)='BUD')
  24 - filter(("FPA"."PNL_REPORT_KEY" IS NOT NULL AND "FPA"."PNL_PERIOD_YR">=2009))
 

Connor McDonald
October 09, 2020 - 3:02 am UTC

Thats why we have "code" tags ... its the button right there when you type your review :-)

OK, you can see we finished the main hash join super quick, so the time is being lost in executing each of those scalar queries 12000 times.

Quick Fix #1 - reduce the number of scalars, eg

create or replace 
type all_the_scalars as object (
          calc_ptd_act_py,
          calc_qtd_act number,
          calc_qtd_bud number,
          calc_qtd_act_py number,
          calc_ytd_act number,
          calc_ytd_bud number,  
          calc_ytd_act_py number);
/


SELECT fpa.pnl_sum_rec_key,
  ( 
     select   
       all_the_scalars(
          SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr - 1 and fpa2.pnl_period_num = fpa.pnl_period_num   then fpa2.pnl_ptd_actual end) calc_ptd_act_py,
          SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr     and fpa2.pnl_period_num <= fpa.pnl_period_num  then fpa2.pnl_ptd_actual end) calc_qtd_act,
          SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr     and fpa2.pnl_period_num <= fpa.pnl_period_num  then fpa2.pnl_ptd_budget end) calc_qtd_bud,
          SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr - 1 and fpa2.pnl_period_num <= fpa.pnl_period_num  then fpa2.pnl_ptd_actual end) calc_qtd_act_py,
          SUM(case when ...                                                                                       then fpa2.pnl_ptd_actual end) calc_ytd_act,
          SUM(case when ...                                                                                       then fpa2.pnl_ptd_budget end) calc_ytd_bud,
          SUM(case when ...                                                                                       then fpa2.pnl_ptd_actual end) calc_ytd_act_py
          ) xxx
    from fact_pnl_consol fpa2          
            WHERE     fpa2.pnl_report_key = fpa.pnl_report_key
                  AND fpa2.pnl_period_yr in (fpa.pnl_period_yr - 1, fpa.pnl_period_yr)
                  AND fpa2.pnl_period_qtr = fpa.pnl_period_qtr
                  AND fpa2.pnl_period_num <= fpa.pnl_period_num
                  AND fpa2.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                  AND fpa.PNL_RPT_CURRENCY = fpa2.PNL_RPT_CURRENCY
                  AND NVL (fpa2.ASC_STANDARD, 'X') = NVL (fpa.ASC_STANDARD, 'X'))
  )
FROM fact_pnl_consol fpa, DIM_PNL_RPT_NAME dprn
 WHERE                                                --fpa.pnl_report_key > 7
      fpa  .pnl_report_key = dprn.prn_key
       AND SUBSTR (dprn.prn_rpt_code, 1, 3) = 'BUD'
       AND fpa.PNL_PERIOD_YR >= 2009


ie,

- the scalar gets the superset of data you might need
- the case statement extracts just the bits needed for each column

then wrap that to un-object the type, ie

select
  pnl_sum_rec_key,
  xxx.calc_qtd_act,
  xxx.calc_qtd_bud,
  xxx.calc_qtd_act_py,
  ....
from (  
    SELECT fpa.pnl_sum_rec_key,
      ( 
         select   
           all_the_scalars(
              SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr - 1 and fpa2.pnl_period_num = fpa.pnl_period_num   then fpa2.pnl_ptd_actual end) calc_ptd_act_py,
              SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr     and fpa2.pnl_period_num <= fpa.pnl_period_num  then fpa2.pnl_ptd_actual end) calc_qtd_act,
              SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr     and fpa2.pnl_period_num <= fpa.pnl_period_num  then fpa2.pnl_ptd_budget end) calc_qtd_bud,
              SUM(case when fpa2.pnl_period_yr = fpa.pnl_period_yr - 1 and fpa2.pnl_period_num <= fpa.pnl_period_num  then fpa2.pnl_ptd_actual end) calc_qtd_act_py,
              SUM(case when ...                                                                                       then fpa2.pnl_ptd_actual end) calc_ytd_act,
              SUM(case when ...                                                                                       then fpa2.pnl_ptd_budget end) calc_ytd_bud,
              SUM(case when ...                                                                                       then fpa2.pnl_ptd_actual end) calc_ytd_act_py
              ) xxx
        from fact_pnl_consol fpa2          
                WHERE     fpa2.pnl_report_key = fpa.pnl_report_key
                      AND fpa2.pnl_period_yr in (fpa.pnl_period_yr - 1, fpa.pnl_period_yr)
                      AND fpa2.pnl_period_qtr = fpa.pnl_period_qtr
                      AND fpa2.pnl_period_num <= fpa.pnl_period_num
                      AND fpa2.pnl_rpt_row_num = fpa.pnl_rpt_row_num
                      AND fpa.PNL_RPT_CURRENCY = fpa2.PNL_RPT_CURRENCY
                      AND NVL (fpa2.ASC_STANDARD, 'X') = NVL (fpa.ASC_STANDARD, 'X'))
      )
    FROM fact_pnl_consol fpa, DIM_PNL_RPT_NAME dprn
     WHERE                                                --fpa.pnl_report_key > 7
          fpa  .pnl_report_key = dprn.prn_key
           AND SUBSTR (dprn.prn_rpt_code, 1, 3) = 'BUD'
           AND fpa.PNL_PERIOD_YR >= 2009
)   


Fix #2 - convert to a join

Once you are at a single scalar, then that can be converted to a join so that you would only make a single pass through the table.

with fpa2 as
(
select pnl_report_key, pnl_period_yr, pnl_period_qtr, pnl_period_num, pnl_rpt_row_num, PNL_RPT_CURRENCY, NVL (ASC_STANDARD, 'X') ASC_STANDARD, 
       nvl(SUM (fpa2.pnl_ptd_actual),0) act
       nvl(SUM (fpa4.pnl_ptd_budget),0) bud
from      fact_pnl_consol
group by pnl_report_key, pnl_period_yr, pnl_period_qtr, pnl_period_num, pnl_rpt_row_num, PNL_RPT_CURRENCY, NVL (ASC_STANDARD, 'X')
)
select ... same style case statements as before ...
FROM fact_pnl_consol fpa, 
     DIM_PNL_RPT_NAME dprn,
     fpa2
WHERE   
          fpa  .pnl_report_key = dprn.prn_key
           AND SUBSTR (dprn.prn_rpt_code, 1, 3) = 'BUD'
           AND fpa.PNL_PERIOD_YR >= 2009
--
WHERE     fpa2.pnl_report_key = fpa.pnl_report_key
      AND fpa2.pnl_period_yr in (fpa.pnl_period_yr - 1, fpa.pnl_period_yr)
      AND fpa2.pnl_period_qtr = fpa.pnl_period_qtr
      AND fpa2.pnl_period_num <= fpa.pnl_period_num
      AND fpa2.pnl_rpt_row_num = fpa.pnl_rpt_row_num
      AND fpa.PNL_RPT_CURRENCY = fpa2.PNL_RPT_CURRENCY
      AND NVL (fpa2.ASC_STANDARD, 'X') = NVL (fpa.ASC_STANDARD, 'X'))


manoj, October 08, 2020 - 12:05 pm UTC

Execution Statistics

Below are the stats fetched from oem:

Total Per Execution Per Row
Executions 1 1 <0.01
Elapsed Time (sec) 13,624.67 13,624.67 0.01
CPU Time (sec) 13,434.63 13,434.63 <0.01
Buffer Gets 3,459,131,194 3,459,131,194.00 2,548.50
Disk Reads 70,301 70,301.00 0.05
Direct Writes 0 0.00 0.00
Rows 1,357,323 1,357,323.00 1
Fetches 1,876 1,876.00 <0.01

Chuck Jolley, October 08, 2020 - 1:49 pm UTC

You can try turning all those select/where clauses into case statements for each column so that it only has to run through the table fact_pnl_consol once.
Join it to the rest of the tables in the outside and case the columns to zero when they don't fit what's needed in that column.
fpa.pnl_report_key = fpa.pnl_report_key would go in the outside join.
The rest of the where would go in each column's case statement.
Connor McDonald
October 09, 2020 - 3:02 am UTC

yup...

A reader, October 09, 2020 - 8:58 am UTC

Thank you, Tom.
I will test these fixes.

More to Explore

Performance

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