Tuning this query...

[OracleToddler]

Question on tuning this query...

Count of records per table.
---------------------------
CCRT_CALENDAR_DIM - 7304
PORTFOLIOS_DIM - 17
ACCOUNT_MONTHLY_FACT - 39691552 PARTITIONED BY MONTH (has 38 partitions)

This create mv script (attached below) takes 2 hrs 30 minutes and I am doin a COMPLETE REFRESH since I am using ANALYTICAL FUNCTIONS, I cannot use FAST REFRESH option.

Can you guys throw me some light? Guide me wht are all the parameters that I should look into.
Wht type of indexes will do the trick, How to re-write the sql in a much betterway.

This is test data only. The actual size of ACCOUNT_MONTHLY_FACT is 520 millions rows.

I got 70 more mv to tune. (I got to do this before March 3)

I need your guidance. How to start and proceed from here...
My guts says I am in trouble. I need you guys to help me.

CREATE MATERIALIZED VIEW MV_CREDIT_MEASURES AS
SELECT
B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
(CASE WHEN GROUPING_ID
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_QTR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK) = 0 THEN
TRIM(D.CAL_QTR_STR)
WHEN GROUPING_ID
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_YEAR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK) = 0 THEN
TRIM(D.CAL_YEAR_STR)
ELSE
TRIM(D.CAL_MONTH_STR)
END) VINTAGE_PERIOD,
TRIM(C.CAL_MONTH_STR) REPORT_PERIOD,
A.MONTHS_ON_BOOK,
SUM(INT_AND_FEES) INT_AND_FEES,
SUM(OPENING_BAL) OPENING_BAL,
SUM(CLOSING_BAL) CLOSING_BAL,
SUM(NUM_ACCOUNTS_OPEN) NUM_ACCOUNTS_OPEN,
SUM(NUM_ACCOUNTS_VOLUNTARY_ATTR) NUM_ACCOUNTS_VOLUNTARY_ATTR,
SUM(NUM_ACCOUNTS_INVOLUNTARY_ATTR) NUM_ACCOUNTS_INVOLUNTARY_ATTR,
SUM(PRINCIPAL_CHGOFF) PRINCIPAL_CHGOFF,
SUM(GROSS_CHGOFF) GROSS_CHGOFF,
SUM(NET_CHGOFF) NET_CHGOFF,
SUM(TWO_PLUS_CLOSING_BAL) TWO_PLUS_CLOSING_BAL,
SUM(THREE_PLUS_CLOSING_BAL) THREE_PLUS_CLOSING_BAL,
SUM(FOUR_PLUS_CLOSING_BAL) FOUR_PLUS_CLOSING_BAL
FROM (SELECT
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
A.MONTHS_ON_BOOK,
A.VINTAGE_DATE,
A.MONTH_END_DATE,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
NVL(A.PURCHASE_FIN_CHG, 0) + NVL(A.CASH_ADV_FIN_CHG,0) + NVL(A.ANNUAL_FEE, 0) +
NVL(A.FRONT_END_FEE ,0) + NVL(A.INSURANCE_FEE, 0) + NVL(A.INTERCHANGE_FEE, 0) +
NVL(A.LATE_FEE, 0) + NVL(A.OTHER_FEE, 0) + NVL(A.OVERLIMIT_FEE, 0)
ELSE 0
END) INT_AND_FEES,
LAG(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN NVL(A.MTD_CLOSING_BAL,0) ELSE 0 END)
OVER (PARTITION BY
A.ACCOUNT_ID
ORDER BY
A.MONTH_END_DATE)
OPENING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID <> 10 THEN
1
ELSE 0
END) NUM_ACCOUNTS_OPEN,
(CASE WHEN A.ACCOUNT_STATUS_ID = 1 THEN
1
ELSE 0
END) NUM_ACCOUNTS_VOLUNTARY_ATTR,
(CASE WHEN A.ACCOUNT_STATUS_ID = 2 THEN
1
ELSE 0
END) NUM_ACCOUNTS_INVOLUNTARY_ATTR,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.PURCHASE_CHGOFF, 0) + NVL(A.CASH_ADV_CHGOFF, 0)
ELSE 0
END) PRINCIPAL_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.TOTAL_CHGOFF, 0)
ELSE 0
END) GROSS_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID = 10 THEN
NVL(A.TOTAL_CHGOFF, 0) - NVL(A.TOTAL_RECOVERY_AMT, 0) -
(NVL(A.PURCHASE_FIN_CHG_CHGOFF, 0) + NVL(A.CASH_ADV_FIN_CHG_CHGOFF, 0) +
NVL(A.MISC_CHGOFF, 0))
ELSE 0
END) NET_CHGOFF,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) TWO_PLUS_CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 2, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) THREE_PLUS_CLOSING_BAL,
(CASE WHEN A.DELINQUENCY_STATUS_ID NOT IN (0, 1, 2, 3, 10) THEN
NVL(A.MTD_CLOSING_BAL, 0)
ELSE 0
END) FOUR_PLUS_CLOSING_BAL
FROM ACCOUNT_MONTHLY_FACT A) A,
PORTFOLIOS_DIM B,
CCRT_CALENDAR_DIM C,
CCRT_CALENDAR_DIM D
WHERE A.PORTFOLIO_ID = B.PORTFOLIO_ID
AND A.MONTH_END_DATE = C.CAL_DAY_DT
AND A.VINTAGE_DATE = D.CAL_DAY_DT
GROUP BY GROUPING SETS (
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_MONTH_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK),
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_QTR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK),
(B.PORTFOLIO_GROUP_ID,
A.RISK_LEVEL_ID,
A.SUB_PORTFOLIO_ID,
A.SECURITIZATION_IND,
A.BRAND_ID,
TRIM(D.CAL_YEAR_STR),
TRIM(C.CAL_MONTH_STR),
A.MONTHS_ON_BOOK)
);

I was njoy my life by doin some small jobs in sql... all of a sudden they dropped a BIG bomb... and said KIDS its your baby tame him..

Thanks in Advance.

[kris109]

It is difficult to help you without looking at the table and index structure and an explain plan for the query.

[OracleToddler]

Thanks Kris.

Geme some suggetions. Wht type of HINT should I use for a query which uses GROUPING SET. I saw this HINT "EXPAND_GSET_TO_UNION"...

But dono how it work... I my query I have these many hints /*+ FULL(A) PARALLEL(A, DEFAULT, DEFAULT) EXPAND_GSET_TO_UNION */.

I don't have access to the TRACE FILES.
I'll get the stuff on tuesday (since if I put a request I might get the priv on tuseday).

Thanks in Advance

[slimdave]

what's the rationale behind using those hints?