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'))