Oracle 19c慢SQL查询优化求助：复杂关联查询性能调优
2026-7-17
Oracle 19c SQL查询性能优化方案
问题分析
从执行计划可见，核心性能瓶颈包括：
- 多个大表的全表扫描（如
STA_ACL_WF_INSTANCE、STA_ACL_WF_TASK、STA_ACL_WF_TASK_COL_QUEST） - 正则拆分+
CONNECT BY的行生成逻辑效率低下，导致中间结果集爆炸（执行计划显示行数达9560M） - 大结果集上的
SORT GROUP BY和窗口函数ROW_NUMBER()带来的高内存/临时空间消耗
原查询语句
select transaction_id as APPL_ID ,cast(reason_desc as VARCHAR2(2000)) as APPL_REJECT_REASON_DESC ,cast(reason_code as VARCHAR2(2000)) as APPL_REJECT_REASON ,cast(user_reject as VARCHAR2(100)) as APPL_REJECT_USER ,cast(step_reject as VARCHAR2(100)) as APPL_REJECT_USER_ROLE ,cast(reason_desc_vn as VARCHAR2(2000)) as APPL_REJECT_REASON_DESC_VN from( select wfi.item_id transaction_id ,cq1.name as reason_desc ,cq1.response as reason_code ,COALESCE(wft.executed_by, wft.recipient_shortname) as user_reject ,wft.profile_access_right_sname as step_reject ,row_number() over (partition by wfi.item_id order by wft.start_date desc) stt ,cq1.name_1 as reason_desc_vn from STA.STA_ACL_WF_INSTANCE wfi inner join STA.STA_ACL_WF_TASK wft on (wfi.item = 'transaction' and wfi.wf_instance_id = wft.wf_instance_id) inner join ( SELECT t.task_id ,LISTAGG (t.response, ', ') WITHIN GROUP (ORDER BY t.response) as response ,LISTAGG (t11.name, ', ') WITHIN GROUP (ORDER BY t11.name) as name ,LISTAGG (t11.name_1, ', ') WITHIN GROUP (ORDER BY t11.name_1) as name_1 FROM ( SELECT task_id, CAST(TRIM(regexp_substr(t.RESPONSE, '[^,]+', 1, levels.column_value)) AS VARCHAR2(100)) AS RESPONSE FROM sta.sta_acl_wf_task_col_quest t, table(cast(multiset(select level from dual connect by level <= length ( regexp_replace(t.RESPONSE, '[^,]+')) + 1) as sys.OdciNumberList)) levels WHERE t.question in ('Reject Reason','Cancel Reason','Reject Reason For Recommendation','Reject Reason For Approval') AND t.response is not null ) t LEFT JOIN STA.STA_ACL_STATIC_DATA_TABLE t11 on (t.response= t11.SHORTNAME) GROUP BY t.task_id )cq1 on wft.task_id = cq1.task_id ) t8 where stt=1
原执行计划
Plan hash value: 3937279373 ----------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 9560M| 53T| | 3339M (2)| 72:27:52 | |* 1 | VIEW | | 9560M| 53T| | 3339M (2)| 72:27:52 | |* 2 | WINDOW NOSORT | | 9560M| 8431G| | 3339M (2)| 72:27:52 | | 3 | SORT GROUP BY | | 9560M| 8431G| 8580G| 3339M (2)| 72:27:52 | |* 4 | HASH JOIN RIGHT OUTER | | 9560M| 8431G| 3392K| 650M (1)| 14:07:31 | | 5 | TABLE ACCESS FULL | STA_ACL_STATIC_DATA_TABLE | 19812 | 3153K| | 399 (3)| 00:00:01 | | 6 | NESTED LOOPS | | 7985M| 5830G| | 40M (4)| 00:52:07 | |* 7 | HASH JOIN | | 488K| 364M| 446M| 958K (3)| 00:01:15 | |* 8 | TABLE ACCESS FULL | STA_ACL_WF_INSTANCE | 1592K| 428M| | 77403 (4)| 00:00:07 | |* 9 | HASH JOIN | | 986K| 470M| 57M| 787K (3)| 00:01:02 | |* 10 | TABLE ACCESS FULL | STA_ACL_WF_TASK_COL_QUEST | 986K| 46M| | 396K (5)| 00:00:32 | | 11 | TABLE ACCESS FULL | STA_ACL_WF_TASK | 5496K| 2364M| | 139K (3)| 00:00:11 | | 12 | COLLECTION ITERATOR SUBQUERY FETCH| | 16360 | 32720 | | 80 (4)| 00:00:01 | |* 13 | CONNECT BY WITHOUT FILTERING | | | | | | | | 14 | FAST DUAL | | 1 | | | 3 (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("STT"=1) 2 - filter(ROW_NUMBER() OVER ( PARTITION BY "WFI"."ITEM_ID" ORDER BY INTERNAL_FUNCTION("WFT"."START_DATE") DESC )<=1) 4 - access("T11"."SHORTNAME"(+)=SYS_OP_C2C(CAST(TRIM( REGEXP_SUBSTR ("T"."RESPONSE" /*+ LOB_BY_VALUE */ ,'[^,]+',1,VALUE(KOKBF$))) AS VARCHAR2(100)))) 7 - access("WFI"."WF_INSTANCE_ID"="WFT"."WF_INSTANCE_ID") 8 - filter("WFI"."ITEM"=U'transaction') 9 - access("WFT"."TASK_ID"="TASK_ID") 10 - filter(("T"."QUESTION"=U'Cancel Reason' OR "T"."QUESTION"=U'Reject Reason' OR "T"."QUESTION"=U'Reject Reason For Approval' OR "T"."QUESTION"=U'Reject Reason For Recommendation') AND "T"."RESPONSE" /*+ LOB_BY_VALUE */ IS NOT NULL) 13 - filter(LEVEL<=LENGTH( REGEXP_REPLACE (:B1,'[^,]+'))+1)
具体优化措施
1. 添加针对性索引，消除全表扫描
根据查询过滤条件和关联键创建复合索引：
针对
STA_ACL_WF_INSTANCE：CREATE INDEX IDX_WF_INSTANCE_ITEM_ID ON STA.STA_ACL_WF_INSTANCE(ITEM, WF_INSTANCE_ID, ITEM_ID);覆盖过滤条件
ITEM='transaction'以及关联、查询所需的字段，避免回表。针对
STA_ACL_WF_TASK：CREATE INDEX IDX_WF_TASK_INST_TASK ON STA.STA_ACL_WF_TASK(WF_INSTANCE_ID, TASK_ID, START_DATE, EXECUTED_BY, RECIPIENT_SHORTNAME, PROFILE_ACCESS_RIGHT_SNAME);覆盖关联键
WF_INSTANCE_ID、TASK_ID，以及查询、排序所需的字段，消除全表扫描并避免回表。针对
STA_ACL_WF_TASK_COL_QUEST：CREATE INDEX IDX_TASK_COL_QUEST_QUESTION ON STA.STA_ACL_WF_TASK_COL_QUEST(QUESTION, TASK_ID, RESPONSE);覆盖过滤条件
QUESTION以及关联、拆分所需的字段，加速数据筛选。针对
STA_ACL_STATIC_DATA_TABLE：CREATE INDEX IDX_STATIC_DATA_SHORTNAME ON STA.STA_ACL_STATIC_DATA_TABLE(SHORTNAME, NAME, NAME_1);覆盖关联键
SHORTNAME以及查询所需的字段，加速左连接。
2. 替换低效的正则拆分逻辑
原查询用REGEXP_REPLACE+CONNECT BY拆分逗号分隔的RESPONSE字段，效率极低。Oracle 12c+支持JSON_TABLE替代，拆分效率更高：
-- 替换原内联拆分逻辑 SELECT t.task_id, TRIM(j.COLUMN_VALUE) AS RESPONSE FROM sta.sta_acl_wf_task_col_quest t, JSON_TABLE('["' || REPLACE(t.RESPONSE, ',', '","') || '"]', '$[*]' COLUMNS COLUMN_VALUE VARCHAR2(100) PATH '$') j WHERE t.question IN ('Reject Reason','Cancel Reason','Reject Reason For Recommendation','Reject Reason For Approval') AND t.response IS NOT NULL
3. 提前过滤数据，缩小结果集
将窗口函数ROW_NUMBER()的过滤逻辑提前，只保留每个流程实例的最新任务记录，再进行后续关联：
WITH latest_wf_task AS ( SELECT wft.*, ROW_NUMBER() OVER (PARTITION BY wft.WF_INSTANCE_ID ORDER BY wft.START_DATE DESC) AS rn FROM STA.STA_ACL_WF_TASK wft ) -- 后续只关联rn=1的记录
这样能大幅减少后续关联和聚合的数据量。
4. 优化LISTAGG聚合逻辑
- 如果不需要重复值，添加
DISTINCT到LISTAGG中：LISTAGG(DISTINCT t.RESPONSE, ', ') ...，减少聚合后的数据长度。 - 若排序非必须，去掉
ORDER BY子句，降低排序开销。
5. 更新统计信息
确保Oracle表和索引的统计信息最新，执行以下命令：
EXEC DBMS_STATS.GATHER_TABLE_STATS('STA', 'STA_ACL_WF_INSTANCE', CASCADE => TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS('STA', 'STA_ACL_WF_TASK', CASCADE => TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS('STA', 'STA_ACL_WF_TASK_COL_QUEST', CASCADE => TRUE); EXEC DBMS_STATS.GATHER_TABLE_STATS('STA', 'STA_ACL_STATIC_DATA_TABLE', CASCADE => TRUE);
过时的统计信息会导致优化器生成错误的执行计划。
最终优化后查询
WITH latest_wf_task AS ( SELECT wft.WF_INSTANCE_ID, wft.TASK_ID, wft.EXECUTED_BY, wft.RECIPIENT_SHORTNAME, wft.PROFILE_ACCESS_RIGHT_SNAME FROM ( SELECT wft.*, ROW_NUMBER() OVER (PARTITION BY wft.WF_INSTANCE_ID ORDER BY wft.START_DATE DESC) AS rn FROM STA.STA_ACL_WF_TASK wft ) wft WHERE wft.rn = 1 ), task_reasons AS ( SELECT t.task_id, LISTAGG(DISTINCT t.RESPONSE, ', ') WITHIN GROUP (ORDER BY t.RESPONSE) AS response, LISTAGG(DISTINCT t11.NAME, ', ') WITHIN GROUP (ORDER BY t11.NAME) AS name, LISTAGG(DISTINCT t11.NAME_1, ', ') WITHIN GROUP (ORDER BY t11.NAME_1) AS name_1 FROM ( SELECT tcq.TASK_ID, TRIM(j.COLUMN_VALUE) AS RESPONSE FROM sta.sta_acl_wf_task_col_quest tcq, JSON_TABLE('["' || REPLACE(tcq.RESPONSE, ',', '","') || '"]', '$[*]' COLUMNS COLUMN_VALUE VARCHAR2(100) PATH '$') j WHERE tcq.question IN ('Reject Reason','Cancel Reason','Reject Reason For Recommendation','Reject Reason For Approval') AND tcq.response IS NOT NULL ) t LEFT JOIN STA.STA_ACL_STATIC_DATA_TABLE t11 ON t.RESPONSE = t11.SHORTNAME GROUP BY t.task_id ) SELECT wfi.item_id AS APPL_ID, CAST(tr.name AS VARCHAR2(2000)) AS APPL_REJECT_REASON_DESC, CAST(tr.response AS VARCHAR2(2000)) AS APPL_REJECT_REASON, CAST(COALESCE(lwt.EXECUTED_BY, lwt.RECIPIENT_SHORTNAME) AS VARCHAR2(100)) AS APPL_REJECT_USER, CAST(lwt.PROFILE_ACCESS_RIGHT_SNAME AS VARCHAR2(100)) AS APPL_REJECT_USER_ROLE, CAST(tr.name_1 AS VARCHAR2(2000)) AS APPL_REJECT_REASON_DESC_VN FROM STA.STA_ACL_WF_INSTANCE wfi INNER JOIN latest_wf_task lwt ON wfi.WF_INSTANCE_ID = lwt.WF_INSTANCE_ID AND wfi.item = 'transaction' INNER JOIN task_reasons tr ON lwt.TASK_ID = tr.TASK_ID;
内容的提问来源于stack exchange，提问作者john224
相关产品推荐
相关产品推荐
