I have a view definition as below. Selecting from this view takes 24 seconds but it takes only 8 seconds in SQL Server.
create or replace view XWDDSB143.v_getBillSummaryByService
as
SELECT
b.BILL_NO BILLINFO_BILLNO, b.ACCOUNT_NO ACCTINFO_ACCOUNTNO, d.device_id as msisdn, d.name, d.serviceTag,
( SELECT NVL( Sum( amount ), 0 ) FROM XWDDSB143.v_getplans p WHERE p.device_id = d.device_id ) as plans,
( SELECT NVL( Sum( amount ), 0 ) FROM XWDDSB143.v_getOneTime o WHERE o.device_id = d.device_id ) as onetimes,
( SELECT NVL( Sum( amount ), 0 ) FROM XWDDSB143.v_getDiscounts dc WHERE dc.device_id = d.device_id ) as discounts,
( SELECT NVL( Sum( amount ), 0 ) FROM XWDDSB143.v_getUsage_detailed_report u WHERE u.device_id = d.device_id ) as usage,
CASE WHEN d.serviceTag IN ( 'mobileline', 'fixedline', 'sipt' ) THEN
( SELECT NVL( Sum( amount ), 0 ) FROM XWDDSB143.v_getExtras e WHERE e.device_id = d.device_id )
ELSE 0 END
AS extras,
CASE WHEN d.serviceTag IN ( 'mobileline', 'fixedline', 'sipt' ) THEN
( SELECT NVL( Sum( amount ), 0 ) FROM XWDDSB143.v_getUsage_detailed_report u WHERE u.device_id = d.device_id and u.usageCategory = 'local' and u.usageType = 'voice' )
ELSE 0 END AS usage_calls_local,
0 as tax_amount,0 as total
FROM XWDDSB143.v_getDevices_detailed_report d
inner join XWDDSB143.v_getBill b on 1=1;
Below is the execution plan
Plan hash value: 3897901392
1352 SELECT STATEMENT
1 SORT AGGREGATE
4 VIEW V_GETPLANS
UNION-ALL
3 NESTED LOOPS
3 NESTED LOOPS OUTER
3 NESTED LOOPS OUTER
3 NESTED LOOPS
3 NESTED LOOPS
3 TABLE ACCESS FULL AR_ITEMS
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS
1 INDEX RANGE SCAN IX_AR_ITEMS_EVENTS_13140_13139
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_TOTAL
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TOTAL
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TAXES
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_PRODUCTS
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_PRODUCTS
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_CYCLE_INFO
1 HASH JOIN
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 TABLE ACCESS FULL SUB_ITEMS_EVENTS
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_TAXES
1 TABLE ACCESS BY INDEX ROWID BATCHED SUB_ITEMS_EVENTS_TOTAL
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_TOTAL
1 TABLE ACCESS BY INDEX ROWID BATCHED SUB_ITEMS_EVENTS_PRODUCTS
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_PRODUCTS
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_CYCLE_INFO
1 TABLE ACCESS FULL SUB_ITEMS
1 SORT AGGREGATE
2 VIEW V_GETONETIME
UNION-ALL
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS
BITMAP CONVERSION TO ROWIDS
BITMAP OR
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN IX_AR_ITEMS_EVENTS_23188_23187
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN IX_AR_ITEMS_EVENTS_23188_23187
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
INDEX RANGE SCAN IX_AR_ITEMS_EVENTS_23188_23187
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TAXES
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS
1 INDEX RANGE SCAN IDX_ARITEMSID
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_PRODUCTS
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_PRODUCTS
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TOTAL
1 TABLE ACCESS BY INDEX ROWID AR_ITEMS_EVENTS_TOTAL
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 NESTED LOOPS
1 HASH JOIN
1 TABLE ACCESS FULL SUB_ITEMS
1 TABLE ACCESS FULL SUB_ITEMS_EVENTS
1 TABLE ACCESS BY INDEX ROWID BATCHED SUB_ITEMS_EVENTS_PRODUCTS
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_PRODUCTS
1 TABLE ACCESS BY INDEX ROWID BATCHED SUB_ITEMS_EVENTS_TOTAL
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_TOTAL
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_TAXES
1 SORT AGGREGATE
2 VIEW V_GETDISCOUNTS
UNION-ALL
FILTER
1 SORT GROUP BY
3 NESTED LOOPS OUTER
3 HASH JOIN
15 NESTED LOOPS
477 NESTED LOOPS
3 TABLE ACCESS FULL AR_ITEMS
159 INDEX RANGE SCAN IX_AR_ITEMS_EVENTS_13140_13139
5 TABLE ACCESS BY INDEX ROWID AR_ITEMS_EVENTS
1919 TABLE ACCESS FULL AR_ITEMS_EVENTS_DISCOUNTS
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_CYCLE_INFO
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_CYCLE_INFO
FILTER
1 SORT GROUP BY
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 HASH JOIN
1 TABLE ACCESS FULL SUB_ITEMS
1 TABLE ACCESS FULL SUB_ITEMS_EVENTS
1 TABLE ACCESS BY INDEX ROWID BATCHED SUB_ITEMS_EVENTS_CYCLE_INFO
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_CYCLE_INFO
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_DISCOUNTS
1 TABLE ACCESS BY INDEX ROWID SUB_ITEMS_EVENTS_DISCOUNTS
1 SORT AGGREGATE
6 VIEW V_GETUSAGE_DETAILED_REPORT
6 SORT GROUP BY
481 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
2 VIEW V_GETEXTRAS
UNION-ALL
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 NESTED LOOPS
3 TABLE ACCESS FULL AR_ITEMS
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS
1 INDEX RANGE SCAN IX_AR_ITEMS_EVENTS_13140_13139
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TAXES
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_TOTAL
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TOTAL
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_CYCLE_INFO
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_PRODUCTS
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_PRODUCTS
1 HASH JOIN
1 NESTED LOOPS OUTER
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 NESTED LOOPS OUTER
1 TABLE ACCESS FULL SUB_ITEMS_EVENTS
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_TAXES
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_CYCLE_INFO
1 TABLE ACCESS BY INDEX ROWID BATCHED SUB_ITEMS_EVENTS_TOTAL
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_TOTAL
1 TABLE ACCESS BY INDEX ROWID BATCHED SUB_ITEMS_EVENTS_PRODUCTS
1 INDEX RANGE SCAN IDX_SUBITEMSEVENTSID_PRODUCTS
1 TABLE ACCESS FULL SUB_ITEMS
1 SORT AGGREGATE
1 VIEW V_GETUSAGE_DETAILED_REPORT
1 SORT GROUP BY
335 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
1 VIEW V_GETUSAGE_DETAILED_REPORT
1 SORT GROUP BY
8 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
1 VIEW V_GETUSAGE_DETAILED_REPORT
1 SORT GROUP BY
1 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
2 VIEW V_GETUSAGE_DETAILED_REPORT
2 SORT GROUP BY
8 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
1 VIEW V_GETUSAGE_DETAILED_REPORT
1 SORT GROUP BY
94 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
1 VIEW V_GETUSAGE_DETAILED_REPORT
1 SORT GROUP BY
1 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
1 VIEW V_GETUSAGE_DETAILED_REPORT
1 SORT GROUP BY
1 TABLE ACCESS FULL USAGEDATA
1 SORT AGGREGATE
1 VIEW V_GETVAS
1 SORT GROUP BY
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS
1 NESTED LOOPS OUTER
1 NESTED LOOPS
INLIST ITERATOR
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_CRBT_INFO
1 INDEX RANGE SCAN IDX_AR_ITEMS_EVENTS_CRBT_INFO_EVENTYPE
1 INDEX RANGE SCAN IX_AR_ITEMS_EVENTS_699075_699074
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS_EVENTS_TAXES
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TAXES
1 TABLE ACCESS BY INDEX ROWID BATCHED AR_ITEMS
1 INDEX RANGE SCAN IDX_ARITEMSID
1 INDEX RANGE SCAN IDX_ARITEMSEVENTSID_TOTAL
1 TABLE ACCESS BY INDEX ROWID AR_ITEMS_EVENTS_TOTAL
FILTER
1412 NESTED LOOPS
26 MERGE JOIN CARTESIAN
1 MERGE JOIN CARTESIAN
1 MERGE JOIN CARTESIAN
1 MERGE JOIN CARTESIAN
1 VIEW
1 WINDOW NOSORT STOPKEY
1 TABLE ACCESS FULL NAMEINFO
1 BUFFER SORT
1 INDEX FULL SCAN INV_INFO_PK
1 BUFFER SORT
1 TABLE ACCESS FULL ACCTINFO
1 BUFFER SORT
1 TABLE ACCESS FULL BILLINFO
26 BUFFER SORT
26 TABLE ACCESS FULL SERVICETYPE
54 VIEW V_GETDEVICES
692 SORT UNIQUE
692 TABLE ACCESS FULL DEVICES
1 TABLE ACCESS FULL SERVICETYPE
Can anyone suggest me what causing more time by looking into the execution plan.I am new to Oracle and could not understand the performance issue here. For me it seems that subqueries with lots of case statements causing the performance issues. But wondering how SQL Server is performing better with the same subqueries and all.
You:
OUTER JOINs to sub-queries containing the aggregations rather than correlated sub-queries; andSomething like this (untested as I do not have your tables or data):
SELECT b.BILL_NO AS BILLINFO_BILLNO,
b.ACCOUNT_NO AS ACCTINFO_ACCOUNTNO,
d.device_id as msisdn,
d.name,
d.serviceTag,
COALESCE(p.amount, 0) AS plans,
COALESCE(o.amount, 0) AS onetimes,
COALESCE(dc.amount, 0) AS discounts,
COALESCE(u.amount, 0) AS usage,
COALESCE(e.amount, 0) AS extras,
CASE
WHEN d.serviceTag IN ( 'mobileline', 'fixedline', 'sipt' )
THEN COALESCE(u.amount_local, 0)
ELSE 0
END AS usage_calls_local,
0 as tax_amount,
0 as total
FROM XWDDSB143.v_getDevices_detailed_report d
LEFT OUTER JOIN (
SELECT device_id,
SUM(amount) AS amount
FROM XWDDSB143.v_getplans
GROUP BY device_id
) p
ON (d.device_id = p.device_id)
LEFT OUTER JOIN (
SELECT device_id,
SUM(amount) AS amount
FROM XWDDSB143.v_getOneTime
GROUP BY device_id
) o
ON (d.device_id = o.device_id)
LEFT OUTER JOIN (
SELECT device_id,
SUM(amount) AS amount
FROM XWDDSB143.v_getDiscounts
GROUP BY device_id
) dc
ON (d.device_id = dc.device_id)
LEFT OUTER JOIN (
SELECT device_id,
SUM(amount) AS amount,
SUM(
CASE
WHEN usageCategory = 'local' AND usageType = 'voice'
THEN amount
END
) AS amount_local
FROM XWDDSB143.v_getUsage_detailed_report
GROUP BY device_id
) u
ON (d.device_id = u.device_id)
LEFT OUTER JOIN (
SELECT device_id,
SUM(amount) AS amount
FROM XWDDSB143.v_getExtras
GROUP BY device_id
) e
ON ( d.serviceTag IN ('mobileline', 'fixedline', 'sipt')
AND d.device_id = u.device_id)
CROSS JOIN XWDDSB143.v_getBill b;
You may be able to simplify the query further as your queries all appear to join views (assuming that is what the v_ prefix means) and if those views use tables common across multiple views then you could combine them. However, we do not have the DDL statements for those views so cannot make any suggestions.