You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

Oracle 19c慢查询性能优化求助：执行计划相关疑问

avatar
DevOps 小满
2026-6-28
问题描述
  • 一条SELECT查询耗时1515秒，需基于提供的执行计划优化性能
  • 已在USAGETYPE表的event_type列创建索引，但执行计划仍显示TABLE FULL SCAN，需明确原因
  • 执行计划中的BUFFER SORT是什么？是否需要避免？

注：使用Oracle 19c版本

查询语句 
SELECT          boi.documentid,
                     boi.device_id ,
                     bie.end_t ,
                     bie.vfqbalance_LOCATION   ,
                     bie.vfqbalance_CALLED_TO ,
                     bie.vfqbalance_DURATION ,
                     CEIL(bie.vfqbalance_DURATION / 60.0),
                     bie.vfqbalance_LOC_AREA_CODE   ,
                     NVL(bie.gsminfoNUMBER, 0)   ,
                     NVL(bie.evttotal_amount, 0)   ,
                     bid.vfBucketId,
                     bie.vfqbalance_AMOUNT   ,
                     0,
                     bie.vfqbalance_CALLING_NUMBER,
                     bie.crbtinfo_CHANNEL  ,
                     bie.crbtinfo_PRODUCT_NAME  ,
                     usageTypeId, usageName, usageType, usageCategory,
                     CASE ut.impact_category 
                        WHEN u'VAS CONTENT' THEN COALESCE(bie.crbtinfo_CHANNEL, u' ') || COALESCE(u' ' || bie.crbtinfo_PRODUCT_NAME, u' ')
                        ELSE usageDetail
                        END as usageDetail,
                    usageDst,
                    bie.vfqbalance_RECORD_TYPE,
                    100,100,
                    bie.vfqbalance_TYPE   ,
                     bie.vfqbalance_OPTIONS   ,
                     boi.TYPE_STR ,
                     bie.vfqbalance_purchase_start_t  ,
                     bie.vfqbalance_resource_name  ,
                     boi.AAC_ACCESS,0,0,0
        FROM (SELECT * FROM invoice_items WHERE ( ITEMTYPE = 'AR_ITEMS' OR ITEMTYPE = 'SUB_ITEMS')) boi
               JOIN invoice_items_events bie   ON  boi.item_obj_3 = bie.item_obj_3 AND boi.ITEMTYPE = bie.ITEMTYPE AND boi.DOCUMENTID = bie.documentid
               JOIN USAGETYPE ut  ON bie.event_obj_2 = ut.event_obj and
                    ( bie.telcoinfo_USAGE_CLASS = ut.USAGE_CLASS OR bie.telcoinfo_USAGE_CLASS=u'Non-roaming' or ut.usage_class IS NULL  ) AND 
                    ( bie.vfqbalance_RECORD_TYPE = ut.record_type or ut.record_type IS NULL ) AND
                    ( 
                    ut.event_type IS NULL 
                    ) AND
                    ( bie.impact_category = ut.impact_category or ut.impact_category IS NULL ) AND
                    ( bie.vfqbalance_CALLED_TO = ut.called_to or ut.called_to IS NULL 
                        OR (ut.called_to = u'!' 
                            And bie.vfqbalance_CALLED_TO NOT IN (
                                SELECT DISTINCT u.called_to
                                FROM USAGETYPE u
                                WHERE u.called_to IS NOT NULL 
                                AND u.called_to != u'!')
                            )
                    )
                    LEFT JOIN VFQBALANCEBUCKET bid on bid.vfBucketId = bie.vfqbalance_VFQ_BUCKET_ID
                Where bie.vfqbalance_RECORD_TYPE is not null or ut.usageType in (u'vas',u'video')
                    AND CASE WHEN ut.usageType = u'voice' AND bie.vfqbalance_DURATION = u'0' AND bie.evttotal_amount = 0 THEN 0 ELSE 1 END = 1;
执行计划 
Plan hash value: 194770266
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                            |     1 |  5809 |     5   (0)| 00:00:01 |
|*  1 |  FILTER                                  |                            |       |       |            |          |
|   2 |   NESTED LOOPS OUTER                     |                            |     1 |  5809 |     5   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                          |                            |     1 |  5787 |     5   (0)| 00:00:01 |
|   4 |     MERGE JOIN CARTESIAN                 |                            |     1 |  2068 |     5   (0)| 00:00:01 |
|   5 |      INLIST ITERATOR                     |                            |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| INVOICE_ITEMS              |     1 |  1313 |     0   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN                  | INVOICE_ITEMS_ITEMTYPE     |     1 |       |     0   (0)| 00:00:01 |
|   8 |      BUFFER SORT                         |                            |   390 |   287K|     5   (0)| 00:00:01 |
|*  9 |       TABLE ACCESS FULL                  | USAGETYPE                  |   390 |   287K|     5   (0)| 00:00:01 |
|* 10 |     TABLE ACCESS BY INDEX ROWID BATCHED  | INVOICE_ITEMS_EVENTS       |     1 |  3719 |     0   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN                    | INVOICE_ITEMS_EVENTS_DOCID |     1 |       |     0   (0)| 00:00:01 |
|* 12 |    INDEX RANGE SCAN                      | VFQBALANCEBUCKET           |     1 |    22 |     0   (0)| 00:00:01 |
|* 13 |   INDEX FAST FULL SCAN                   | USAGETYPE_CALLED_TO        |   342 |  6156 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("UT"."CALLED_TO" IS NULL OR "BIE"."VFQBALANCE_CALLED_TO"="UT"."CALLED_TO" OR 
              "UT"."CALLED_TO"=U'!' AND  NOT EXISTS (SELECT 0 FROM "USAGETYPE" "U" WHERE "U"."CALLED_TO" IS NOT NULL AND 
              "U"."CALLED_TO"<>'!' AND LNNVL("U"."CALLED_TO"<>:B1)))
   7 - access("ITEMTYPE"='AR_ITEMS' OR "ITEMTYPE"='SUB_ITEMS')
   9 - filter("UT"."EVENT_TYPE" IS NULL)
  10 - filter("BIE"."EVENT_OBJ_2"="UT"."EVENT_OBJ" AND "INVOICE_ITEMS"."ITEM_OBJ_3"="BIE"."ITEM_OBJ_3" AND 
              "INVOICE_ITEMS"."ITEMTYPE"="BIE"."ITEMTYPE" AND ("BIE"."VFQBALANCE_RECORD_TYPE"="UT"."RECORD_TYPE" OR 
              "UT"."RECORD_TYPE" IS NULL) AND ("BIE"."IMPACT_CATEGORY"="UT"."IMPACT_CATEGORY" OR "UT"."IMPACT_CATEGORY" IS 
              NULL) AND ("BIE"."VFQBALANCE_RECORD_TYPE" IS NOT NULL OR ("UT"."USAGETYPE"=U'vas' OR 
              "UT"."USAGETYPE"=U'video') AND CASE  WHEN ("UT"."USAGETYPE"=U'voice' AND "BIE"."VFQBALANCE_DURATION"=U'0' AND 
              "BIE"."EVTTOTAL_AMOUNT"=0) THEN 0 ELSE 1 END =1) AND ("BIE"."ITEMTYPE"='AR_ITEMS' OR 
              "BIE"."ITEMTYPE"='SUB_ITEMS') AND ("BIE"."TELCOINFO_USAGE_CLASS"="UT"."USAGE_CLASS" OR 
              "BIE"."TELCOINFO_USAGE_CLASS"=U'Non-roaming' OR "UT"."USAGE_CLASS" IS NULL))
  11 - access("INVOICE_ITEMS"."DOCUMENTID"="BIE"."DOCUMENTID")
  12 - access("BID"."VFBUCKETID"(+)="BIE"."VFQBALANCE_VFQ_BUCKET_ID")
  13 - filter("U"."CALLED_TO" IS NOT NULL AND "U"."CALLED_TO"<>'!' AND LNNVL("U"."CALLED_TO"<>:B1))
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
问题解答

一、USAGETYPE表全表扫描的原因

从执行计划的Predicate Information（ID9）可以看到，当前查询对USAGETYPE的过滤条件是"UT"."EVENT_TYPE" IS NULL，即只取event_type为NULL的行。
Oracle普通B树索引默认不会存储NULL值，因此你创建的event_type列索引无法用来定位这些NULL行，Oracle只能通过全表扫描来筛选符合条件的数据。

二、BUFFER SORT的含义与是否需要避免

含义

BUFFER SORT是Oracle将查询结果加载到内存并完成排序的操作，这里它出现在MERGE JOIN CARTESIAN之前，作用是把USAGETYPE全表扫描的结果排序后，为后续的笛卡尔积合并做准备。

是否需要避免

当前场景下不需要：USAGETYPE表只有390行，数据量极小，BUFFER SORT的内存开销可以忽略，Oracle选择这种执行方式的成本很低。只有当大表出现不必要的BUFFER SORT时（比如意外触发笛卡尔积），才需要优化调整。

三、整体性能优化建议

  1. 针对USAGETYPE的NULL查询优化
    创建函数索引来覆盖event_type IS NULL的过滤场景：

    CREATE INDEX idx_usagetype_event_null ON USAGETYPE (CASE WHEN event_type IS NULL THEN 1 END);

  2. 简化复杂JOIN逻辑
    针对ut.called_to = u'!'时的NOT IN子查询，可以提前预处理数据：

    • 用物化视图缓存USAGETYPE中符合条件的called_to列表，减少查询时的实时计算
    • 确认USAGETYPE_CALLED_TO索引是否包含called_to非NULL且不等于u'!'的过滤条件，确保子查询效率

  3. 调整表连接顺序
    当前执行计划先关联invoice_items和USAGETYPE做笛卡尔积，再关联invoice_items_events，可通过提示强制调整连接顺序，减少中间结果集大小：

    SELECT /*+ LEADING(boi bie) */
-- 原查询内容

  4. 更新统计信息
    执行计划显示使用了动态采样，说明表的统计信息可能过期，导致CBO行数估算不准确（当前估算行数为1，实际可能远大于此），更新统计信息：

    EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'INVOICE_ITEMS', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'INVOICE_ITEMS_EVENTS', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'USAGETYPE', CASCADE => TRUE);

  5. 简化WHERE子句的CASE表达式
    将原CASE表达式改写为更直观的逻辑，帮助CBO更好地优化：

    WHERE bie.vfqbalance_RECORD_TYPE is not null or ut.usageType in (u'vas',u'video')
  AND NOT (ut.usageType = u'voice' AND bie.vfqbalance_DURATION = u'0' AND bie.evttotal_amount = 0)

内容的提问来源于stack exchange，提问作者to-find

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.06.28 05:45:53