Team,
Can you please let me know if there is a way to optimize the below query. As I am beginner, please let me know if any other details are required.
I don't see an option to attach the Explain Plan, I have pasted it below the query.
Query
SELECT /*+ FULL(8) PARALLEL(8)*/
to_char(TO_DATE (A.POSTING_DATE_KEY, 'YYYY-MM-DD'),'YYYY-MM') as Yr_Mo,
case
when B.CUSTOMER_TYPE_DESC in ('Business', 'Government') then 'Business and Government' else B.CUSTOMER_TYPE_DESC
end
AS CUSTOMER_TYPE,
POSTING_DATE_ENTITY_NM,
QUALIFICATION_RULE_NM,
C.SSCR_TYPE_DERIVED_DESC AS SUBSCRIBER_TYPE,
G.GL_ACCOUNT_NUM||' - '||G.GL_ACCOUNT_PRODUCT_DESC as GL_ACCOUNT, D.CHARGE_CD||' - '||D.CHARGE_CODE_DESC as CHARGE_CD,
case
when D.CHARGE_CD in ('DEVTHIRDPTY','SAAS','SAASBOBO','ACCTPSAAS','ACCTHIRDPTY','INSTLTPSAAS','NONCOMMSNBOBO') and C.SSCR_TYPE_DERIVED_DESC = 'M2M' then 'M2M VAS'
when D.CHARGE_CD in ('DEVTHIRDPTY','SAAS','SAASBOBO','ACCTPSAAS','ACCTHIRDPTY','INSTLTPSAAS','NONCOMMSNBOBO') and C.SSCR_TYPE_DERIVED_DESC <> 'M2M' then 'Other VAS/SaaS'
when D.CHARGE_CD in ('SIGASNREVR','DEVICE0000','S/H','DEVICE0000DF','DFINSTMNT','DFDWNPYM','ACCSRY0000','DFDWNPYMAGT','DFINSTMNTAGT') then 'M2M Non-Connectivity(Equipment)'
when D.CHARGE_CD like 'CDP%' then 'M2M CDP Connectivity'
else 'M2M Other Connectivity'
end
as CHARGE_TYPE,
SUM(A.CREDIT_AMT) AS CREDIT_AMOUNT,
sum(a.DEBIT_AMT) as DEBIT_AMT,
SUM(- A.DEBIT_AMT + A.CREDIT_AMT) as TOTAL_AMT
FROM FT_GL_TRANSACTION A
JOIN DIM_CUSTOMER B ON A.CUSTOMER_KEY = B.CUSTOMER_KEY
JOIN DIM_SUBSCRIBER C ON A.SUBSCRIBER_KEY = C.SUBSCRIBER_KEY
JOIN DIM_CHARGE_CODE D ON A.CHARGE_CODE_KEY = D.CHARGE_CODE_KEY
join dim_GL_ACCOUNT G on A.GL_ACCOUNT_KEY = G.GL_ACCOUNT_KEY
join dim_company_code CC on A.COMPANY_CODE_KEY = CC.COMPANY_CODE_KEY
join DIM_GL_METRIC_LABEL GLL on A.GL_METRIC_LABEL_KEY = GLL.GL_METRIC_LABEL_KEY
WHERE
TO_DATE (A.POSTING_DATE_KEY, 'YYYY-MM-DD') BETWEEN ADD_MONTHS (TRUNC (SYSDATE,'MM')- 1, -13) AND (TRUNC (SYSDATE,'MM') - 1)
and B.REVENUE_GENERATING_IND = 'Y'
and CC.COMPANY_CD not in ('0392','0393','0394')
and C.SSCR_TYPE_DERIVED_DESC = 'M2M'
and (POSTING_DATE_ENTITY_NM <> 'UnBilled'
or QUALIFICATION_RULE_NM NOT LIKE '%Unearned%')
and (G.GL_ACCOUNT_NUM like '54%' or G.GL_ACCOUNT_NUM like '60%')
GROUP BY to_char(TO_DATE (A.POSTING_DATE_KEY, 'YYYY-MM-DD'),'YYYY-MM'),
case
when B.CUSTOMER_TYPE_DESC in ('Business', 'Government') then 'Business and Government' else B.CUSTOMER_TYPE_DESC
end, C.SSCR_TYPE_DERIVED_DESC,
G.GL_ACCOUNT_NUM||' - '||G.GL_ACCOUNT_PRODUCT_DESC,
D.CHARGE_CD||' - '||D.CHARGE_CODE_DESC,
POSTING_DATE_ENTITY_NM,
QUALIFICATION_RULE_NM,
case
when D.CHARGE_CD in ('DEVTHIRDPTY','SAAS','SAASBOBO','ACCTPSAAS','ACCTHIRDPTY','INSTLTPSAAS','NONCOMMSNBOBO') and C.SSCR_TYPE_DERIVED_DESC = 'M2M' then 'M2M VAS'
when D.CHARGE_CD in ('DEVTHIRDPTY','SAAS','SAASBOBO','ACCTPSAAS','ACCTHIRDPTY','INSTLTPSAAS','NONCOMMSNBOBO') and C.SSCR_TYPE_DERIVED_DESC <> 'M2M' then 'Other VAS/SaaS'
when D.CHARGE_CD in ('SIGASNREVR','DEVICE0000','S/H','DEVICE0000DF','DFINSTMNT','DFDWNPYM','ACCSRY0000','DFDWNPYMAGT','DFINSTMNTAGT') then 'M2M Non-Connectivity(Equipment)'
when D.CHARGE_CD like 'CDP%' then 'M2M CDP Connectivity'
else 'M2M Other Connectivity'
end
EXPLAIN PLAN
Plan
SELECT STATEMENT ALL_ROWSCost: 92,945,268 Bytes: 948,779,325 Cardinality: 4,216,797
64 PX COORDINATOR
63 PX SEND QC (RANDOM) PARALLEL_TO_SERIAL SYS.:TQ10012 :Q1012Cost: 92,945,268 Bytes: 948,779,325 Cardinality: 4,216,797
62 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 92,945,268 Bytes: 948,779,325 Cardinality: 4,216,797
61 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1012Cost: 92,945,268 Bytes: 948,779,325 Cardinality: 4,216,797
60 PX SEND HASH PARALLEL_TO_PARALLEL SYS.:TQ10011 :Q1011Cost: 92,945,268 Bytes: 948,779,325 Cardinality: 4,216,797
59 HASH GROUP BY PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 92,945,268 Bytes: 948,779,325 Cardinality: 4,216,797
58 FILTER PARALLEL_COMBINED_WITH_CHILD :Q1011
57 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 92,921,301 Bytes: 948,779,325 Cardinality: 4,216,797
4 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 2 Bytes: 2,896 Cardinality: 724
3 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10006 :Q1006Cost: 2 Bytes: 2,896 Cardinality: 724
2 PX SELECTOR SINGLE_COMBINED_WITH_CHILD :Q1006
1 INDEX STORAGE FAST FULL SCAN INDEX (UNIQUE) SINGLE_COMBINED_WITH_PARENT DWDIL.GL_ACCOUNT_PK :Q1006Cost: 2 Bytes: 2,896 Cardinality: 724
56 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 92,921,298 Bytes: 931,912,137 Cardinality: 4,216,797
8 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 9 Bytes: 48,204 Cardinality: 1,236
7 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10007 :Q1007Cost: 9 Bytes: 48,204 Cardinality: 1,236
6 PX SELECTOR SINGLE_COMBINED_WITH_CHILD :Q1007
5 TABLE ACCESS STORAGE FULL TABLE SINGLE_COMBINED_WITH_PARENT DWDPL.DIM_CHARGE_CODE_T :Q1007Cost: 9 Bytes: 48,204 Cardinality: 1,236
55 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 92,921,287 Bytes: 767,482,716 Cardinality: 4,216,938
12 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 3 Bytes: 4,932 Cardinality: 1,233
11 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10008 :Q1008Cost: 3 Bytes: 4,932 Cardinality: 1,233
10 PX SELECTOR SINGLE_COMBINED_WITH_CHILD :Q1008
9 INDEX STORAGE FAST FULL SCAN INDEX (UNIQUE) SINGLE_COMBINED_WITH_PARENT DWDIL.CHARGE_CODE_T_PK :Q1008Cost: 3 Bytes: 4,932 Cardinality: 1,233
54 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 92,921,282 Bytes: 769,410,340 Cardinality: 4,322,530
17 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 53,098 Bytes: 130,492,600 Cardinality: 6,524,630
16 PX SEND HYBRID HASH PARALLEL_TO_PARALLEL SYS.:TQ10009 :Q1009Cost: 53,098 Bytes: 130,492,600 Cardinality: 6,524,630
15 STATISTICS COLLECTOR PARALLEL_COMBINED_WITH_CHILD :Q1009
14 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1009Cost: 53,098 Bytes: 130,492,600 Cardinality: 6,524,630
13 TABLE ACCESS STORAGE FULL TABLE PARALLEL_COMBINED_WITH_PARENT DWDPL.DIM_CUSTOMER_T :Q1009Cost: 53,098 Bytes: 130,492,600 Cardinality: 6,524,630
53 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1011Cost: 92,868,178 Bytes: 908,966,416 Cardinality: 5,752,952
52 PX SEND HYBRID HASH PARALLEL_TO_PARALLEL SYS.:TQ10010 :Q1010Cost: 92,868,178 Bytes: 908,966,416 Cardinality: 5,752,952
51 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 92,868,178 Bytes: 908,966,416 Cardinality: 5,752,952
21 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 9 Bytes: 21,497 Cardinality: 581
20 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10001 :Q1001Cost: 9 Bytes: 21,497 Cardinality: 581
19 PX SELECTOR SINGLE_COMBINED_WITH_CHILD :Q1001
18 TABLE ACCESS STORAGE FULL TABLE SINGLE_COMBINED_WITH_PARENT DWDIL.GL_QUALIFICATION_RULE_T :Q1001Cost: 9 Bytes: 21,497 Cardinality: 581
50 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 92,868,167 Bytes: 697,827,449 Cardinality: 5,767,169
25 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 204 Bytes: 816,660 Cardinality: 27,222
24 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10002 :Q1002Cost: 204 Bytes: 816,660 Cardinality: 27,222
23 PX SELECTOR SINGLE_COMBINED_WITH_CHILD :Q1002
22 TABLE ACCESS STORAGE FULL TABLE SINGLE_COMBINED_WITH_PARENT DWDIL.GL_METRIC_LABEL_T :Q1002Cost: 204 Bytes: 816,660 Cardinality: 27,222
49 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 92,867,960 Bytes: 524,812,379 Cardinality: 5,767,169
29 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 7 Bytes: 4,704 Cardinality: 336
28 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10003 :Q1003Cost: 7 Bytes: 4,704 Cardinality: 336
27 PX SELECTOR SINGLE_COMBINED_WITH_CHILD :Q1003
26 TABLE ACCESS STORAGE FULL TABLE SINGLE_COMBINED_WITH_PARENT DWDIL.GL_ACCOUNT_T :Q1003Cost: 7 Bytes: 4,704 Cardinality: 336
48 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 92,867,948 Bytes: 956,869,529 Cardinality: 12,426,877
36 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 2 Bytes: 1,060 Cardinality: 106
35 PX SEND BROADCAST PARALLEL_FROM_SERIAL SYS.:TQ10004 :Q1004Cost: 2 Bytes: 1,060 Cardinality: 106
34 PX SELECTOR SINGLE_COMBINED_WITH_CHILD :Q1004
33 VIEW VIEW SINGLE_COMBINED_WITH_CHILD DWDIL.index$_join$_021 :Q1004Cost: 2 Bytes: 1,060 Cardinality: 106
32 HASH JOIN SINGLE_COMBINED_WITH_CHILD :Q1004
30 INDEX STORAGE FAST FULL SCAN INDEX (UNIQUE) SINGLE_COMBINED_WITH_PARENT DWDIL.REF_COMPANY_CODE_AK :Q1004Cost: 1 Bytes: 1,060 Cardinality: 106
31 INDEX STORAGE FAST FULL SCAN INDEX (UNIQUE) SINGLE_COMBINED_WITH_PARENT DWDIL.REF_COMPANY_CODE_PK :Q1004Cost: 1 Bytes: 1,060 Cardinality: 106
47 HASH JOIN PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 92,867,941 Bytes: 856,164,927 Cardinality: 12,778,581
41 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 71,105 Bytes: 816,528 Cardinality: 34,022
40 PX SEND HYBRID HASH PARALLEL_TO_PARALLEL SYS.:TQ10005 :Q1005Cost: 71,105 Bytes: 816,528 Cardinality: 34,022
39 STATISTICS COLLECTOR PARALLEL_COMBINED_WITH_CHILD :Q1005
38 PX BLOCK ITERATOR PARALLEL_COMBINED_WITH_CHILD :Q1005Cost: 71,105 Bytes: 816,528 Cardinality: 34,022
37 TABLE ACCESS STORAGE FULL TABLE PARALLEL_COMBINED_WITH_PARENT DWDPL.DIM_SUBSCRIBER_T :Q1005Cost: 71,105 Bytes: 816,528 Cardinality: 34,022
46 BUFFER SORT PARALLEL_COMBINED_WITH_CHILD :Q1010
45 PX RECEIVE PARALLEL_COMBINED_WITH_PARENT :Q1010Cost: 92,792,273 Bytes: 486,923,837,654 Cardinality: 11,323,810,178
44 PX SEND HYBRID HASH PARALLEL_FROM_SERIAL SYS.:TQ10000 Cost: 92,792,273 Bytes: 486,923,837,654 Cardinality: 11,323,810,178
43 PARTITION RANGE ALL Cost: 92,792,273 Bytes: 486,923,837,654 Cardinality: 11,323,810,178 Partition #: 63 Partitions accessed #1 - #2469
42 TABLE ACCESS STORAGE FULL TABLE DWDPL.FT_GL_TRANSACTION_T Cost: 92,792,273 Bytes: 486,923,837,654 Cardinality: 11,323,810,178 Partition #: 63 Partitions accessed #1 - #2469
Thanks!
Bindu