Query performance problem when adding in SUM(blah)...

[lmctech]

I have a huge query and it runs in about 30 seconds UNTIL I add in ANY additional SUMs in the SELECT statement.
Can somebody please explain to me what I am doing wrong that is causing this to occur?

SELECT
SOURCE,
SR_RCL,
SR_RCL_GRP,
RCL_TEAM_LEAD_GRP,
COUNT(APPLICATION_NBR) AS APPS,
STORE_NAME,
REGION_NAME,
MARKET_NAME,
SUM(REQ_DAY1_IN) REQ_DAY1_IN,
/*
SUM(REQ_YTD_IN) REQ_YTD_IN, ************************ADDING ANY OF THESE SUMS AFTER THE FIRST ONE CAUSES A PROBLEM****************
SUM(REQ_MTD_IN) REQ_MTD_IN,
SUM(REQ_LYTD_IN) REQ_LYTD_IN,
*/
SUM(REQ_LMTD_IN) REQ_LMTD_IN
FROM
(

SELECT
(SELECT
MAX(K0.TRANSACTION_DATE)
FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID) DATA_DATE,
CASE
WHEN LENGTH(Y.SOURCE)<1
OR Y.SOURCE IS NULL
THEN 'OTHER'
ELSE
Y.SOURCE END AS SOURCE,
Y.SR_RCL,
CASE WHEN UPPER(Y.SR_RCL) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.SR_RCL) END AS SR_RCL_GRP,
CASE WHEN UPPER(Y.RCL) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.RCL) END AS RCL_GRP,
CASE WHEN UPPER(Y.RCL_TEAM_LEAD) = 'UNKNOWN' THEN 'ZUNKNOWN' ELSE UPPER(Y.RCL_TEAM_LEAD) END AS RCL_TEAM_LEAD_GRP,
Y.APPLICATION_NBR,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'AUTO' OR Y.PRODUCT_NAME_CATEGORY='RV' THEN 'AUTO/RV'
ELSE Y.PRODUCT_NAME_CATEGORY END) PRODUCT_NAME_CATEGORY2,
(CASE
WHEN Y.PRODUCT_NAME_CATEGORY = 'HELOC' OR Y.PRODUCT_NAME_CATEGORY='HOME EQ FIXED'
THEN 'LINES AND LOANS'
ELSE 'OTHER' END) PRODUCT_GROUPING,
Y.STORE_NAME,
Y.REGION_NAME,
Y.MARKET_NAME,
Y.METRO_NAME,
(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) REQ_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN 1 ELSE 0 END) REQ_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN 1 ELSE 0 END) REQ_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) REQ_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) REQ_LMTD_IN,

(CASE WHEN Y.REQ_DATE >= Y.DAY1_START AND Y.REQ_DATE <= Y.DAY1_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_DAY1_IN,
(CASE WHEN Y.REQ_DATE >= Y.YTD_START AND Y.REQ_DATE <= Y.YTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_YTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.MTD_START AND Y.REQ_DATE <=Y.MTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_MTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LYTD_START AND Y.REQ_DATE <=Y.LYTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LYTD_IN,
(CASE WHEN Y.REQ_DATE >= Y.LMTD_START AND Y.REQ_DATE <=Y.LMTD_END THEN REQ_DOLLAR_AMT ELSE 0 END) REQDOL_LMTD_IN,

(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) DEC_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN 1 ELSE 0 END) DEC_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN 1 ELSE 0 END) DEC_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) DEC_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) DEC_LMTD_IN,

(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) FUN_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN 1 ELSE 0 END) FUN_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN 1 ELSE 0 END) FUN_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) FUN_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) FUN_LMTD_IN,

(CASE WHEN Y.FUN_DATE >= Y.DAY1_START AND Y.FUN_DATE <= Y.DAY1_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_DAY1_IN,
(CASE WHEN Y.FUN_DATE >= Y.YTD_START AND Y.FUN_DATE <= Y.YTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_YTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.MTD_START AND Y.FUN_DATE <=Y.MTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_MTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LYTD_START AND Y.FUN_DATE <=Y.LYTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LYTD_IN,
(CASE WHEN Y.FUN_DATE >= Y.LMTD_START AND Y.FUN_DATE <=Y.LMTD_END THEN FUN_DOLLAR_AMT ELSE 0 END) FUNDOL_LMTD_IN,

(CASE WHEN Y.DEC_DATE >= Y.DAY1_START AND Y.DEC_DATE <= Y.DAY1_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_DAY1_IN,
(CASE WHEN Y.DEC_DATE >= Y.YTD_START AND Y.DEC_DATE <= Y.YTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_YTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.MTD_START AND Y.DEC_DATE <=Y.MTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_MTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LYTD_START AND Y.DEC_DATE <=Y.LYTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LYTD_IN,
(CASE WHEN Y.DEC_DATE >= Y.LMTD_START AND Y.DEC_DATE <=Y.LMTD_END THEN Dec_Dollar_Amt ELSE 0 END) DECDOL_LMTD_IN,

(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN 1 ELSE 0 END) APR_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN 1 ELSE 0 END) APR_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN 1 ELSE 0 END) APR_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN 1 ELSE 0 END) APR_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN 1 ELSE 0 END) APR_LMTD_IN,

(CASE WHEN Y.APR_DATE >= Y.DAY1_START AND Y.APR_DATE <= Y.DAY1_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_DAY1_IN,
(CASE WHEN Y.APR_DATE >= Y.YTD_START AND Y.APR_DATE <= Y.YTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_YTD_IN,
(CASE WHEN Y.APR_DATE >= Y.MTD_START AND Y.APR_DATE <=Y.MTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_MTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LYTD_START AND Y.APR_DATE <=Y.LYTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LYTD_IN,
(CASE WHEN Y.APR_DATE >= Y.LMTD_START AND Y.APR_DATE <=Y.LMTD_END THEN APR_DOLLAR_AMT ELSE 0 END) APRDOL_LMTD_IN

FROM
(
SELECT
K1.START_DATE AS DATA_DATE,
K1.APPLICATION_NBR APPLICATION_NBR,
K1.Product Product,
K1.PRODUCT_NAME_CATEGORY PRODUCT_NAME_CATEGORY,
K1.PRODUCT_NAME_DETAIL PRODUCT_NAME_DETAIL,
UPPER(CLRR.CLRR_STORE_DESC) STORE_NAME,
UPPER(CLRR.CLRR_REGION_DESC) REGION_NAME,
CASE
WHEN UPPER(CLRR.CLRR_MARKET_DESC) = 'UNKNOWN MARKET' THEN 'ZUNKNOWN MARKET' ELSE UPPER(CLRR.CLRR_MARKET_DESC) END AS MARKET_NAME,
UPPER(CLRR.CLRR_MARKET_DESC) MARKET_NAME_PLAIN,
UPPER(CLRR.METRO_NAME) METRO_NAME,
X.DAY1_START,
X.DAY1_END,
X.MTD_START,
X.MTD_END,
X.YTD_START,
X.YTD_END,
X.LMTD_START,
X.LMTD_END,
X.LYTD_START,
X.LYTD_END,
SOURCE.SOURCE,
CLRR.SR_RCL,
CLRR.RCL,
CLRR.RCL_TEAM_LEAD,
CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START)
AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)
THEN K0.ORIG_REQUESTED_AMT ELSE 0 END REQ_DOLLAR_AMT,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END DEC_DOLLAR_AMT,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Booked_Amt ELSE 0 END FUN_DOLLAR_AMT,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN K0.Loan_Financed_Amt ELSE 0 END APR_DOLLAR_AMT,

CASE
WHEN
(TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7)
AND (TRUNC(T_REQ.DAY_DATE) >= X.LYTD_START
AND TRUNC(T_REQ.DAY_DATE) <= X.YTD_END)
THEN TRUNC(T_REQ.DAY_DATE)
ELSE TO_DATE('01-JAN-00','DD-MON-RR')END REQ_DATE,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) <= 6
AND TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) >=2
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END DEC_DATE,

CASE
WHEN
TO_NUMBER(REPLACE(P.APPLICATION_STATUS_CODE,'Not classified at this level', '999')) = 6
AND (TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND
TRUNC(K1.Funding_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Funding_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END FUN_DATE,

CASE
WHEN
TO_NUMBER(P.APPLICATION_STATUS_CODE ) IN (2,4,5,6)
AND (TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START
AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END)
THEN TRUNC(K1.Dec_Complete_TS)
ELSE TO_DATE('01-JAN-00','DD-MON-RR') END APR_DATE

FROM
WHSUSR.SVF_CURR_APPLICATION K0
LEFT JOIN WHSUSR.VW_CURR_CBE_LOAN_APPLICATION K1
ON K0.LOAN_APPLICATION_OID = K1.OBJECT_ID
LEFT JOIN WHSUSR.VW_RPT_TIME_CALENDAR T_REQ
ON K1.DAY__APPLICATION_DATE_OID = T_REQ.BE_ID

LEFT JOIN
(SELECT
STORE_OID,
CLRR_MARKET_DESC,
CLRR_REGION_DESC,
CLRR_STORE_DESC,
METRO_NAME,
COALESCE(SR_REGIONAL_CONSUMER_LENDER,'UNKNOWN') SR_RCL,
COALESCE(REG_CONSUMER_LND_TEAM_LEAD, 'UNKNOWN') RCL_TEAM_LEAD,
COALESCE(REGIONAL_CONSUMER_LENDER_I, 'UNKNOWN') RCL
FROM WHSUSR.VW_RPT_STORE_CLRR) CLRR
ON K0.STORE_OID = CLRR.STORE_OID

LEFT JOIN
(SELECT
BE_ID,
CASE
WHEN LENGTH(APP_SOURCE_GROUP)<1 THEN 'OTHER'
ELSE
APP_SOURCE_GROUP END AS SOURCE
FROM WHSUSR.VW_CURR_MAP_APPLICATION_SOURCE
WHERE APPLICATION_SOURCE <> '$NOOID' AND
APP_SOURCE_GROUP <> 'Not classified at this level') SOURCE
ON K1.APPLICATION_SOURCE_OID=SOURCE.BE_ID

LEFT JOIN
(SELECT
BE_ID,
APPLICATION_STATUS,
APPLICATION_STATUS_NAME,
APPLICATION_STATUS_CODE
FROM
WHSUSR.VW_CURR_MAP_APPLICATION
WHERE APPLICATION_STATUS <> '$NOOID'
AND TO_NUMBER(REPLACE(APPLICATION_STATUS_CODE,'Not classified at this level', '999')) < 7) P
ON K1.APPLICATION_STATUS_OID = P.BE_ID

LEFT JOIN
(SELECT
SYSDATE AS XSYS,
D.DAY1_S AS DAY1_START,
D.DAY1_E AS DAY1_END,
trunc(D.DAY1_S,'mm') AS MTD_START,
D.DAY1_E AS MTD_END,
TRUNC(D.DAY1_S,'YYYY') AS YTD_START,
D.DAY1_E AS YTD_END,
TRUNC(LAST_DAY(D.DAY1_S)-32,'MM') as LMTD_START,
ADD_MONTHS(D.DAY1_S,-1) AS LMTD_END,
TRUNC(TRUNC(D.DAY1_S,'YYYY') -1,'YYYY') AS LYTD_START,
ADD_MONTHS(D.DAY1_E,-12) AS LYTD_END
FROM
(SELECT
TRUNC(TO_DATE('20-DEC-09','DD-MON-RR'),'DD') AS DAY1_S,
TRUNC(TO_DATE('20-DEC-09','DD-MON-RR'),'DD') +1 -(1/24/60/60) AS DAY1_E
FROM DUAL)D
)X
ON
TRUNC(X.XSYS) >= TRUNC(K1.START_DATE)
AND TRUNC(X.XSYS) < TRUNC(K1.END_DATE)

WHERE
(((TRUNC(K1.Dec_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Dec_Complete_TS) <= X.YTD_END))
OR
((TRUNC(K1.Funding_Complete_TS) >= X.LYTD_START AND TRUNC(K1.Funding_Complete_TS) <= X.YTD_END))
OR
(TRUNC(T_REQ.DAY_DATE) >= TRUNC(X.LYTD_START) AND TRUNC(T_REQ.DAY_DATE) <= TRUNC(X.YTD_END)))
) Y
)
GROUP BY

SOURCE,
SR_RCL,
RCL_TEAM_LEAD_GRP,
STORE_NAME,
REGION_NAME,
MARKET_NAME

 

[Thargy]

Without seeing an explain plan or something similar, I can only surmise, but I suspect that the SUM function is forcing a full table scan.

How many rows are there in the table(s) in question, and when were statistics last refreshed?

Regards

T

 

[lmctech]

I am really puzzled because the first SUM being done works, but adding any additional ones makes it impossible.

I can execute the query in TOAD but cannot see the explain plan. I'm being told it is my query, not the db. GRR!!!

Any other ideas on how to get around it?

 

[lmctech]

FYI: Trying to see the explain plan shows that I have insufficient privs on underlying objects in the view.

 

[Thargy]

Then get your DBA to grant you select on those things that you need. There's (quite rightly) no way round that.

Regards

T

 

[lmctech]

The DBA ran the query this am under her rights and said that the query is NOT doing a full table scan. She says theres just something wrong with my query, too much going on in it. I'm stuck. Ideas?

 

[carp]

Would the DBA be kind enough to provide the execution plan? It might be more useful to you (and us) than a yes/no response. While I agree with the DBA that you have a lot going on here, I also find the response interesting because you have several left joins; I generally see a FTS whenever using an outer join.

What might be even more useful would be the execution plans for (a) one SUM and (b) two SUMs. Then you could compare the two and see if they differ at all. If they do, then you at least have a clue.

Also, if you replace your SUM() with one of the other sums, does the query still run quickly? In other words, is it the specific sum or the presence of multiple sums that is slowing you down? I suspect that it will still run quickly regardless of which SUM you use, but it's good to confirm these things.

 

[lmctech]

1. I cannot get my hands on the explain plan - all I have to work with is the following from the dba:
" I ran explain plans on both versions (without the sums/with the sums), and neither had any full table scans - indexes were being used for every single table reference."

2. The performance is quick with any ONE sum being done,
it only goes nuts when I add a second one, regardless of which one I try to add in.

 

[carp]

Why is your DBA so afraid of your seeing the execution plan? It is not really that difficult to copy/paste it into a document (and, for that matter, it is hardly reasonable to deny the ability to perform plan analysis to developers - this is silliness on stilts!).

 

[Thargy]

Ask your DBA precisely how you are supposed to develop efficient queries without the ability to obtain an explain plan.

If you have to, use DBMS_MONITOR to trace your own session and get the DBA to provide you with the output trace files.

If this is too much hassle for your poor DBA, install oracle locally, and you can be your own DBA. Write efficient queries locally, and as a final stage, test them on a production platform.

I agree with Carp, this is DBA twaddle (and I'm a full-time DBA).

Regards

T