Oracle中子查询性能远慢于SQL Server的原因排查求助

我定义了如下Oracle视图，查询耗时24秒，但相同逻辑在SQL Server中仅需8秒。现提供视图定义及Oracle执行计划，请求分析性能差异原因。

视图定义 #

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;

Oracle执行计划 #

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

性能差异原因分析 #

1. 笛卡尔积导致的数据爆炸 #

视图中inner join XWDDSB143.v_getBill b on 1=1是无关联条件的笛卡尔积，这会让v_getDevices_detailed_report的每一行都和v_getBill的所有行匹配，数据量直接相乘。从执行计划能看到多次MERGE JOIN CARTESIAN操作，这会让后续所有关联子查询的执行次数呈几何级增长。SQL Server对这种场景的优化策略更高效，可能自动规避了无意义的笛卡尔积重复计算。

2. 关联子查询的重复执行 #

视图包含6个关联子查询（含CASE中的2个），每个子查询都会针对笛卡尔积后的每一行单独执行。比如plans字段的子查询，若笛卡尔积后有1000行，这个子查询就要跑1000次。执行计划中多次出现SORT AGGREGATE和底层视图的重复调用，比如V_GETUSAGE_DETAILED_REPORT被反复执行全表扫描和聚合，这是性能瓶颈的核心原因。SQL Server会将这类关联子查询转换为JOIN聚合，一次性计算所有device_id的结果，避免重复执行。

3. 全表扫描的重复开销 #

执行计划里多次出现TABLE ACCESS FULL USAGEDATA，每次都要做SORT GROUP BY，说明同一个表被反复扫描聚合，没有复用计算结果。比如usage和usage_calls_local都依赖v_getUsage_detailed_report，但Oracle没有合并这两个逻辑，而是分开执行两次全表扫描，浪费大量IO和CPU资源。

4. 视图嵌套的优化限制 #

所有子查询都基于嵌套视图，Oracle优化器难以穿透多层视图解析完整逻辑，无法生成最优执行计划。比如v_getplans本身是UNION ALL视图，内部还有多层嵌套循环，每次执行都要重新解析整个视图逻辑，叠加后开销巨大。

优化建议 #

• 替换无意义的笛卡尔积：确认业务逻辑，用实际关联条件（比如账户ID、账单ID）替换on 1=1，避免数据膨胀。
• 将关联子查询改为JOIN聚合：把所有子查询转换为预聚合的LEFT JOIN，一次性计算所有device_id的聚合值，示例如下：

  SELECT
      b.BILL_NO BILLINFO_BILLNO, b.ACCOUNT_NO ACCTINFO_ACCOUNTNO, d.device_id as msisdn, d.name, d.serviceTag,
      NVL(p.plans,0) plans,
      NVL(o.onetimes,0) onetimes,
      NVL(dc.discounts,0) discounts,
      NVL(u.usage,0) usage,
      CASE WHEN d.serviceTag IN ('mobileline','fixedline','sipt') THEN NVL(e.extras,0) ELSE 0 END extras,
      CASE WHEN d.serviceTag IN ('mobileline','fixedline','sipt') THEN NVL(u_local.usage_calls_local,0) ELSE 0 END usage_calls_local,
      0 as tax_amount,0 as total
  FROM XWDDSB143.v_getDevices_detailed_report d
  -- 替换笛卡尔积为合理关联
  JOIN XWDDSB143.v_getBill b ON d.account_no = b.account_no
  LEFT JOIN (SELECT device_id, SUM(amount) plans FROM XWDDSB143.v_getplans GROUP BY device_id) p ON p.device_id = d.device_id
  LEFT JOIN (SELECT device_id, SUM(amount) onetimes FROM XWDDSB143.v_getOneTime GROUP BY device_id) o ON o.device_id = d.device_id
  LEFT JOIN (SELECT device_id, SUM(amount) discounts FROM XWDDSB143.v_getDiscounts GROUP BY device_id) dc ON dc.device_id = d.device_id
  LEFT JOIN (SELECT device_id, SUM(amount) usage FROM XWDDSB143.v_getUsage_detailed_report GROUP BY device_id) u ON u.device_id = d.device_id
  LEFT JOIN (SELECT device_id, SUM(amount) extras FROM XWDDSB143.v_getExtras GROUP BY device_id) e ON e.device_id = d.device_id
  LEFT JOIN (SELECT device_id, SUM