In Oracle subquery takes lots of time compared to SQL Server

Question

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.

Answer 1

You:

• can use OUTER JOINs to sub-queries containing the aggregations rather than correlated sub-queries; and
• Do not need to query the usage view twice. Instead you can use conditional aggregation.

Something 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.

Answer 2

It is literally impossible to specifically tell you what is going on because your query is based on views and your explain plan is showing the relationship with the underlying tables in ways we have no insight into as the views are opaque to us.

Generically in my experience, performance issues are caused by the following:

1. poorly written SQL - number one cause of poor performance
2. lack of indexing - add appropriate indexes
3. poor indexing - indexes exist and are being used as cost based optimiser (CBO) determines they are more effective than full table scan but as not optimally designed for the query are still inefficient, you can even investigate different types of index eg bitmap, function based etc
4. stale statistics - when using the CBO, Oracle take advantage of statistics to make decisions so it is important to make sure your statics are up to date
5. other - on massive tables there are many ways to improve performance eg index-organised-tables, partitioning, query parallelisation etc.

More specifically, look at the explain plan above, identify the operations having the highest costs and focus on targeting the above rules at those items. Reducing even one of the numbers can have a massively positive impact on performance.

While I know not a specific answer for your exact question, applying the above rules will likely significantly improve performance, particularly as you say you are seeing better performance on SQL Server which means similar should certainly should be possible to achieve on Oracle.

Hope helpful