一次 Oracle CPU 高故障处理实战：从 Scheduler Job 到 SQL 执行计划优化#
一、故障现象#
生产环境 Oracle 数据库出现 CPU 使用率持续偏高，峰值一度接近 90%。从操作系统侧观察，CPU user 使用率高，load average 偏高，多个 oracle 前台进程处于 Running，数据库无明显大面积 I/O 等待，iowait 并不高，说明问题更偏向数据库内部 SQL、PL/SQL、逻辑读、解析或业务并发消耗，而不是底层存储 I/O 瓶颈。
典型现象如下：
[oracle@newwmsdb ~]$ top
top - 17:06:28 up 115 days, 20:23, 2 users, load average: 28.43, 27.15, 27.62
Tasks: 1949 total, 29 running, 1920 sleeping, 0 stopped, 0 zombie
%Cpu(s): 84.6 us, 9.6 sy, 0.0 ni, 1.3 id, 3.5 wa, 0.0 hi, 0.9 si, 0.0 st
KiB Mem : 26397451+total, 16179944 free, 10548271+used, 14231185+buff/cache
KiB Swap: 16777212 total, 16777212 free, 0 used. 94851072 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
8704 oracle 20 0 130.8g 991.0m 814272 R 100.0 0.4 0:03.79 oracle_8704_wmp
9254 oracle 20 0 130.5g 3.2g 3.2g R 100.0 1.3 3:24.66 oracle_9254_wmp
9280 oracle 20 0 130.6g 4.3g 4.2g R 100.0 1.7 6:46.73 oracle_9280_wmp
22876 oracle 20 0 131.1g 1.6g 1.1g R 100.0 0.6 25:46.58 ora_j001_wmprod
26294 oracle 20 0 131.1g 1.2g 788392 R 100.0 0.5 3:22.41 ora_j000_wmprod
27298 oracle 20 0 130.7g 3.5g 3.3g R 100.0 1.4 3:00.25 oracle_27298_wm
8925 oracle 20 0 130.5g 200500 196444 R 94.7 0.1 0:00.96 oracle_8925_wmp
9345 oracle 20 0 130.5g 238664 232172 R 94.7 0.1 0:26.07 oracle_9345_wmp
8883 oracle 20 0 130.5g 244844 240220 R 89.5 0.1 0:01.17 oracle_8883_wmp
9011 oracle 20 0 130.5g 48084 42824 R 78.9 0.0 0:00.18 oracle_9011_wmp
9019 oracle 20 0 130.5g 48304 43660 S 73.7 0.0 0:00.24 oracle_9019_wmp
9009 oracle 20 0 130.5g 50292 45372 S 63.2 0.0 0:00.19 oracle_9009_wmp
9031 oracle 20 0 130.5g 48256 43608 S 57.9 0.0 0:00.20 oracle_9031_wmp
9035 oracle 20 0 130.5g 40548 35472 R 57.9 0.0 0:00.13 oracle_9035_wmp
18976 oracle 20 0 130.5g 4.2g 4.2g S 57.9 1.7 6:48.26 oracle_18976_wm
30540 oracle 20 0 130.5g 1.0g 1.0g R 57.9 0.4 1:52.96 oracle_30540_wm
1 root 20 0 191308 3416 1764 S 52.6 0.0 70933:20 systemd
8987 oracle 20 0 130.5g 51616 46320 R 52.6 0.0 0:00.44 oracle_8987_wmp
9023 oracle 20 0 130.5g 49284 44576 S 47.4 0.0 0:00.19 oracle_9023_wmp
9039 oracle 20 0 130.5g 36304 31436 R 36.8 0.0 0:00.09 oracle_9039_wmp
16939 oracle 20 0 130.5g 1.5g 1.5g R 31.6 0.6 0:24.39 oracle_16939_wm
10923 oracle 20 0 164048 4216 1548 R 21.1 0.0 0:00.06 top
18465 oracle 20 0 130.5g 1.6g 1.6g R 21.1 0.7 5:55.89 oracle_18465_wm
9007 oracle 20 0 130.5g 32424 27956 S 15.8 0.0 0:00.05 oracle_9007_wmp
10895 oracle 20 0 130.5g 33792 28748 S 15.8 0.0 0:00.07 oracle_10895_wm
13617 oracle 20 0 123468 3552 340 S 15.8 0.0 1650:31 logreader
18577 oracle 20 0 130.5g 1.9g 1.8g S 15.8 0.7 9:38.40 oracle_18577_wm
19196 oracle 20 0 123468 3552 340 R 15.8 0.0 1666:35 logreader
25222 oracle 20 0 130.5g 2.4g 2.3g R 15.8 0.9 5:59.17 oracle_25222_wm
10865 oracle 20 0 234724 11092 4496 S 10.5 0.0 12823:07 tnslsnr
10916 oracle 20 0 130.5g 37184 32184 S 10.5 0.0 0:00.04 oracle_10916_wm
10920 oracle 20 0 130.5g 31404 26340 S 10.5 0.0 0:00.04 oracle_10920_wm
10927 oracle 20 0 130.5g 27240 23244 S 10.5 0.0 0:00.02 oracle_10927_wm
10929 oracle 20 0 130.5g 27260 23264 S 10.5 0.0 0:00.02 oracle_10929_wm
10931 oracle 20 0 130.5g 27264 23268 S 10.5 0.0 0:00.02 oracle_10931_wm
10933 oracle 20 0 130.5g 27200 23208 S 10.5 0.0 0:00.02 oracle_10933_wm
10937 oracle 20 0 130.5g 22280 18592 S 10.5 0.0 0:00.02 oracle_10937_wm
10939 oracle 20 0 130.5g 22276 18592 S 10.5 0.0 0:00.02 oracle_10939_wm
11407 oracle 20 0 130.5g 265356 260936 D 10.5 0.1 1567:12 ora_lgwr_wmprod
18769 oracle 20 0 130.5g 1.6g 1.6g S 10.5 0.6 6:00.91 oracle_18769_wm
25276 oracle 20 0 130.5g 3.6g 3.6g R 10.5 1.4 7:37.36 oracle_25276_wm
1003 root 20 0 294716 3664 2556 S 5.3 0.0 48:51.67 abs_deployer
2473 root 20 0 1324136 9676 2244 S 5.3 0.0 919:38.63 edr_monitor
8997 oracle 20 0 130.5g 30988 25576 S 5.3 0.0 0:00.03 oracle_8997_wmp
10935 oracle 20 0 130.5g 22236 18548 R 5.3 0.0 0:00.01 oracle_10935_wm
11314 oracle -2 0 130.5g 20296 16572 S 5.3 0.0 2536:01 ora_vktm_wmprod
11401 oracle 20 0 130.5g 8.6g 8.6g D 5.3 3.4 112:27.65 ora_dbw5_wmprod
11403 oracle 20 0 130.5g 8.7g 8.7g S 5.3 3.4 113:43.95 ora_dbw6_wmprod
18819 oracle 20 0 130.5g 2.8g 2.8g R 5.3 1.1 15:19.46 oracle_18819_wm
18836 oracle 20 0 130.5g 4.6g 4.6g S 5.3 1.8 8:20.55 oracle_18836_wm
25187 oracle 20 0 130.5g 2.7g 2.7g S 5.3 1.1 2:52.53 oracle_25187_wm
25205 oracle 20 0 130.5g 4.1g 4.1g R 5.3 1.6 7:22.86 oracle_25205_wm
25262 oracle 20 0 130.5g 4.3g 4.3g S 5.3 1.7 7:08.06 oracle_25262_wm
25266 oracle 20 0 130.5g 1.3g 1.2g R 5.3 0.5 3:22.55 oracle_25266_wm
25287 oracle 20 0 130.5g 4.0g 4.0g S 5.3 1.6 8:16.27 oracle_25287_wm
25341 oracle 20 0 130.5g 3.5g 3.4g R 5.3 1.4 3:35.16 oracle_25341_wm
30276 oracle 20 0 130.5g 1.0g 1.0g S 5.3 0.4 39:24.45 oracle_30276_wm
30870 oracle 20 0 130.5g 1.3g 1.3g R 5.3 0.5 3:06.89 oracle_30870_wm
2 root 20 0 0 0 0 S 0.0 0.0 7:48.42 kthreadd
4 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 kworker/0:0H
6 root 20 0 0 0 0 S 0.0 0.0 18:02.02 ksoftirqd/0
7 root rt 0 0 0 0 S 0.0 0.0 0:55.58 migration/0
8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 rcu_bh
9 root 20 0 0 0 0 S 0.0 0.0 829:22.64 rcu_sched
10 root 0 -20 0 0 0 S 0.0 0.0 0:00.00 lru-add-drain
[oracle@newwmsdb ~]$ 二、故障排查#
通过观察top 系统 load average 在 28 左右，CPU 中 %us 达 84.6%，%id 只有 1.3%，并且多个 Oracle 前台进程、ora_j000_wmprod、ora_j001_wmprod 都接近或达到 100% CPU。
根据 top 里的 PID定位 SQL#
根据 top 里的 PID 反查对应SID、SQL_ID。比如 top 里看到 PID 8704、9254、9280、22876、26294 CPU 高，可以这样查：
set lines 300 pages 1000
col username for a15
col program for a35
col machine for a30
col event for a35
col sql_id for a15
col prev_sql_id for a15
select s.sid,
s.serial#,
p.spid,
s.username,
s.status,
s.machine,
s.program,
s.module,
s.sql_id,
s.prev_sql_id,
s.event,
s.state,
s.last_call_et
from v$session s,
v$process p
where s.paddr = p.addr
and p.spid in ('8704','9254','9280','22876','26294','27298')
order by s.last_call_et desc;你查询到的结果，当前最可疑的几个会话，DBMS_SCHEDULER 的 J000 / J001 正在跑重 SQL
| 进程 | SID | SQL_ID | 状态 | 等待事件 | SQL 摘要 | 判断 |
|---|---|---|---|---|---|---|
ora_j000_wmprod / PID 26294 | 3186 | fb9n9ybz4ww5u | ACTIVE | db file sequential read | INSERT INTO APP_ORDER_STATUS_CHANGE ... | Scheduler Job 正在大量插入 |
ora_j001_wmprod / PID 22876 | 8607 | dtgks897yabva | ACTIVE | db file sequential read | SELECT NVL(SUM(A.INVN_ADJMT_QTY),'0') FROM PIX_TRAN A LEFT JOIN ASN B ... | Scheduler Job 查询 PIX_TRAN/ASN 消耗高 |
这两个 Job 在 top 里之前都是 100% CPU，现在数据库里也能对应到 DBMS_SCHEDULER 模块，说明 定时任务是当前 CPU 高的核心来源之一。
查当前长时间运行的 Job#
set lines 300 pages 1000
col owner for a15
col job_name for a40
col elapsed_time for a25
col cpu_used for a25
col action for a120
select r.owner,
r.job_name,
r.session_id,
r.running_instance,
r.elapsed_time,
r.cpu_used,
s.sql_id,
s.event,
s.status,
s.last_call_et,
substr(j.job_action,1,120) action
from dba_scheduler_running_jobs r
join v$session s
on r.session_id = s.sid
left join dba_scheduler_jobs j
on r.owner = j.owner
and r.job_name = j.job_name
where r.owner = 'WMPROD'
order by r.elapsed_time desc;查询结果如下：
OWNER JOB_NAME SESSION_ID RUNNING_INSTANCE ELAPSED_TIME CPU_USED JOB_TYPE PROGRAM_OWNER PROGRAM_NAME
-------------------- ---------------------------------------- ---------- ---------------- ------------------------- ------------------------- -------------------- -------------------- ----------------------------------------
ACTION
----------------------------------------------------------------------------------------------------------------------------------------------------------------
WMPROD GET_WMSFE_ASN_STATUS_CHANGE 5953 1 +000 00:20:22.22 +000 00:20:16.82 PLSQL_BLOCK
BEGIN APP_WMSFE_PACKAGE.GET_WMSFE_ASN_STATUS_CHANGE; END;
WMPROD GET_WMSFE_JOB_ASN_ORDERS 7763 1 +000 00:05:48.89 +000 00:05:47.28 PLSQL_BLOCK
BEGIN APP_WMSFE_PACKAGE.GET_WMSFE_JOB_ASN_ORDERS; END;查这两个 Job 的调度频率#
重点看是不是调度太频繁、上一次没跑完下一次又启动、或者白天业务高峰还在跑。
set lines 300 pages 1000
col owner for a15
col job_name for a40
col enabled for a8
col state for a15
col repeat_interval for a100
col last_start_date for a35
col next_run_date for a35
col run_count for 999999
col failure_count for 999999
select owner,
job_name,
enabled,
state,
repeat_interval,
last_start_date,
next_run_date,
run_count,
failure_count
from dba_scheduler_jobs
where owner = 'WMPROD'
and job_name in ('GET_WMSFE_ASN_STATUS_CHANGE',
'GET_WMSFE_JOB_ASN_ORDERS');如果 repeat_interval 很短，比如每 1 分钟、5 分钟跑一次，而单次任务已经跑 20 分钟，这就是典型问题：任务执行时间 > 调度间隔。这种情况下会造成任务堆积、CPU 持续高、应用响应变慢。查询结果如下：
OWNER JOB_NAME ENABLED STATE REPEAT_INTERVAL LAST_START_DATE NEXT_RUN_DATE RUN_COUNT FAILURE_COUNT
--------------- ---------------------------------------- -------- --------------- ---------------------------------------------------------------------------------------------------- ----------------------------------- ----------------------------------- --------- -------------
WMPROD GET_WMSFE_ASN_STATUS_CHANGE TRUE RUNNING Freq=Minutely;Interval=3 12-MAY-26 06.57.28.208194 PM +08:00 12-MAY-26 06.36.45.000000 PM +08:00 3302 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS TRUE RUNNING Freq=MINUTELY;Interval=2 12-MAY-26 06.51.03.898406 PM +08:00 12-MAY-26 06.39.57.000000 PM +08:00 8529 0任务执行时间已经远大于调度间隔#
你现在两个 Job 是：
| Job | 调度间隔 | 当前运行时间 | CPU 使用时间 | 当前 SQL |
|---|---|---|---|---|
GET_WMSFE_ASN_STATUS_CHANGE | 每 3 分钟 | 20 分钟+ | 20 分钟+ | fb9n9ybz4ww5u，插入 APP_ORDER_STATUS_CHANGE |
GET_WMSFE_JOB_ASN_ORDERS | 每 2 分钟 | 6 分钟+ | 6 分钟+ | dtgks897yabva，查询 PIX_TRAN + ASN |
两个 Job 的 CPU_USED 基本等于 ELAPSED_TIME，说明它们运行期间几乎一直在吃 CPU；而且执行时间已经超过调度间隔，任务会形成“持续运行、几乎无空窗”的状态。你查到当前 SQL 分别是 dtgks897yabva 和 g14w83s9b652u，等待事件都是 db file sequential read。
这就是为什么 top 里 ora_j000/ora_j001 会持续高 CPU。
查这两个 Job 调用 SQL 的执行计划#
优先查这两个 SQL：
select *
from table(dbms_xplan.display_cursor('fb9n9ybz4ww5u', null, 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'));
select *
from table(dbms_xplan.display_cursor('dtgks897yabva', null, 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'));重点看执行计划里有没有这些问题：
| 现象 | 说明 |
|---|---|
TABLE ACCESS FULL | 全表扫描，可能缺索引或条件不合理 |
NESTED LOOPS 循环次数巨大 | 驱动表行数太大，连接顺序可能不合理 |
INDEX RANGE SCAN 后 TABLE ACCESS BY INDEX ROWID 很多 | 大量回表，逻辑读/CPU 会高 |
E-Rows 和 A-Rows 差异很大 | 统计信息不准 |
BUFFER_GETS 很高 | CPU 高的直接来源之一 |
db file sequential read 多 | 大量索引单块读/回表 |
在查涉及表的索引和统计信息#
1.APP_ORDER_STATUS_CHANGE#
set lines 300 pages 1000
col owner for a15
col table_name for a35
col index_name for a40
col column_name for a35
col status for a10
select i.owner,
i.table_name,
i.index_name,
i.status,
i.uniqueness,
c.column_name,
c.column_position,
i.blevel,
i.leaf_blocks,
i.clustering_factor,
i.num_rows,
i.last_analyzed
from dba_indexes i
join dba_ind_columns c
on i.owner = c.index_owner
and i.index_name = c.index_name
where i.table_owner = 'WMPROD'
and i.table_name = 'APP_ORDER_STATUS_CHANGE'
order by i.index_name, c.column_position;查询结果如下：
OWNER TABLE_NAME INDEX_NAME STATUS UNIQUENESS COLUMN_NAME COLUMN_POSITION BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED
--------------- ----------------------------------- ---------------------------------------- ---------- ------------------------------------ ----------------------------------- --------------- ---------- ----------- ----------------- ---------- -------------------
WMPROD APP_ORDER_STATUS_CHANGE APP_ORDER_STATUS_CHANGE_PK VALID UNIQUE APP_ORDER_STATUS_CHANGE_ID 1 1 342 2863 153305 2026-05-11 22:06:15
WMPROD APP_ORDER_STATUS_CHANGE APP_ORDER_STATUS_CHANGE_PK VALID UNIQUE ORDER_DTL_NO 2 1 342 2863 153305 2026-05-11 22:06:152.PIX_TRAN / ASN#
set lines 300 pages 1000
col owner for a15
col table_name for a30
col index_name for a40
col column_name for a35
select i.owner,
i.table_name,
i.index_name,
i.status,
i.uniqueness,
c.column_name,
c.column_position,
i.blevel,
i.leaf_blocks,
i.clustering_factor,
i.num_rows,
i.last_analyzed
from dba_indexes i
join dba_ind_columns c
on i.owner = c.index_owner
and i.index_name = c.index_name
where i.table_owner = 'WMPROD'
and i.table_name in ('PIX_TRAN','ASN')
order by i.table_name, i.index_name, c.column_position;查询结果如下：
OWNER TABLE_NAME INDEX_NAME STATUS UNIQUENESS COLUMN_NAME COLUMN_POSITION BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR NUM_ROWS LAST_ANALYZED
--------------- ------------------------------ ---------------------------------------- ---------- ------------------------------------ ----------------------------------- --------------- ---------- ----------- ----------------- ---------- -------------------
WMPROD ASN ASN_DFID_IDX VALID NONUNIQUE DESTINATION_FACILITY_ID 1 2 3627 201447 1255294 2026-05-12 02:00:36
WMPROD ASN ASN_OFID_IDX VALID NONUNIQUE ORIGIN_FACILITY_ID 1 0 0 0 0 2026-05-12 02:00:37
WMPROD ASN ASN_PK VALID UNIQUE ASN_ID 1 2 2700 1011132 1255294 2026-05-12 02:00:41
WMPROD ASN ASN_SHPID_IDX VALID NONUNIQUE SHIPMENT_ID 1 0 0 0 0 2026-05-12 02:00:36
WMPROD ASN ASN_UIDX VALID UNIQUE TC_ASN_ID 1 2 11273 1045288 1268524 2026-05-12 02:00:35
WMPROD ASN ASN_UIDX VALID UNIQUE TC_COMPANY_ID 2 2 11273 1045288 1268524 2026-05-12 02:00:35
WMPROD ASN IDX_ASN_TCPDTTM VALID NONUNIQUE TC_COMPANY_ID 1 2 5775 348899 1230588 2026-05-12 02:00:42
WMPROD ASN IDX_ASN_TCPDTTM VALID NONUNIQUE SYS_NC00144$ 2 2 5775 348899 1230588 2026-05-12 02:00:42
WMPROD ASN PE_ASN_LIST_IX VALID NONUNIQUE TC_COMPANY_ID 1 2 7059 439003 1255294 2026-05-12 02:00:41
WMPROD ASN PE_ASN_LIST_IX VALID NONUNIQUE INBOUND_REGION_ID 2 2 7059 439003 1255294 2026-05-12 02:00:41
WMPROD ASN PE_ASN_LIST_IX VALID NONUNIQUE OUTBOUND_REGION_ID 3 2 7059 439003 1255294 2026-05-12 02:00:41
WMPROD ASN PE_ASN_LIST_IX VALID NONUNIQUE ASN_ORGN_TYPE 4 2 7059 439003 1255294 2026-05-12 02:00:41
WMPROD ASN PE_ASN_LIST_IX VALID NONUNIQUE ASN_STATUS 5 2 7059 439003 1255294 2026-05-12 02:00:41
WMPROD ASN TM_ASN_IDX1 VALID NONUNIQUE ASN_LEVEL 1 2 3594 75094 1255294 2026-05-12 02:00:39
WMPROD ASN TM_ASN_IDX2 VALID NONUNIQUE ASN_STATUS 1 2 3493 118547 1255294 2026-05-12 02:00:38
WMPROD ASN TM_ASN_IDX3 VALID NONUNIQUE ASN_TYPE 1 2 3594 75091 1255294 2026-05-12 02:00:37
WMPROD ASN TM_ASN_IDX4 VALID NONUNIQUE DOCK_DOOR_ID 1 0 0 0 0 2026-05-12 02:00:37
WMPROD ASN TM_ASN_IDX5 VALID NONUNIQUE MODE_ID 1 0 0 0 0 2026-05-12 02:00:37
WMPROD ASN TM_ASN_IDX6 VALID NONUNIQUE SHIPPING_COST_CURRENCY_CODE 1 0 0 0 0 2026-05-12 02:00:37
WMPROD ASN TM_ASN_IDX7 VALID NONUNIQUE TC_COMPANY_ID 1 2 3734 302554 1255294 2026-05-12 02:00:36
WMPROD PIX_TRAN PE_PIXTRN_IDX_1 VALID NONUNIQUE CASE_NBR 1 2 42220 2219357 8425260 2026-05-10 03:50:57
WMPROD PIX_TRAN PE_PIXTRN_IDX_1 VALID NONUNIQUE TRAN_TYPE 2 2 42220 2219357 8425260 2026-05-10 03:50:57
WMPROD PIX_TRAN PE_PIXTRN_IDX_1 VALID NONUNIQUE TRAN_CODE 3 2 42220 2219357 8425260 2026-05-10 03:50:57
WMPROD PIX_TRAN PE_PIXTRN_IDX_1 VALID NONUNIQUE ACTN_CODE 4 2 42220 2219357 8425260 2026-05-10 03:50:57
WMPROD PIX_TRAN PE_PIXTRN_IDX_1 VALID NONUNIQUE REF_CODE_ID_5 5 2 42220 2219357 8425260 2026-05-10 03:50:57
WMPROD PIX_TRAN PE_PIX_CASE_IDX_1 VALID NONUNIQUE SYS_NC00081$ 1 3 67708 2952932 8425260 2026-05-10 03:50:59
WMPROD PIX_TRAN PE_PIX_CASE_IDX_1 VALID NONUNIQUE SYS_NC00082$ 2 3 67708 2952932 8425260 2026-05-10 03:50:59
WMPROD PIX_TRAN PE_PIX_CASE_IDX_1 VALID NONUNIQUE TC_COMPANY_ID 3 3 67708 2952932 8425260 2026-05-10 03:50:59
WMPROD PIX_TRAN PE_PIX_CASE_IDX_1 VALID NONUNIQUE WHSE 4 3 67708 2952932 8425260 2026-05-10 03:50:59
WMPROD PIX_TRAN PE_PIX_CASE_IDX_1 VALID NONUNIQUE CREATE_DATE_TIME 5 3 67708 2952932 8425260 2026-05-10 03:50:59
WMPROD PIX_TRAN PIXTRAN_CREATEDDTTM_IDX_1 VALID NONUNIQUE CREATE_DATE_TIME 1 2 26903 1461234 8425260 2026-05-10 03:50:59
WMPROD PIX_TRAN PIXTRAN_ITEMID_IDX_1 VALID NONUNIQUE ITEM_ID 1 2 20015 4269182 6419937 2026-05-10 03:51:00
WMPROD PIX_TRAN PIXTRAN_PSTATCODEWHSE_IDX_1 VALID NONUNIQUE PROC_STAT_CODE 1 2 55066 1415620 8425260 2026-05-10 03:51:00
WMPROD PIX_TRAN PIXTRAN_PSTATCODEWHSE_IDX_1 VALID NONUNIQUE WHSE 2 2 55066 1415620 8425260 2026-05-10 03:51:00
WMPROD PIX_TRAN PIXTRAN_PSTATCODEWHSE_IDX_1 VALID NONUNIQUE MOD_DATE_TIME 3 2 55066 1415620 8425260 2026-05-10 03:51:00
WMPROD PIX_TRAN PIXTRAN_REFFLDPSTATCODE_IDX_1 VALID NONUNIQUE REF_FIELD_3 1 2 50758 3415884 8425260 2026-05-10 03:51:01
WMPROD PIX_TRAN PIXTRAN_REFFLDPSTATCODE_IDX_1 VALID NONUNIQUE REF_FIELD_1 2 2 50758 3415884 8425260 2026-05-10 03:51:01
WMPROD PIX_TRAN PIXTRAN_REFFLDPSTATCODE_IDX_1 VALID NONUNIQUE PROC_STAT_CODE 3 2 50758 3415884 8425260 2026-05-10 03:51:01
WMPROD PIX_TRAN PIXTRAN_TRANNBR_IDX_1 VALID NONUNIQUE TRAN_NBR 1 2 26784 1492306 8425260 2026-05-10 03:51:02
WMPROD PIX_TRAN PIXTRAN_XMLGROUPID_IDX_1 VALID NONUNIQUE XML_GROUP_ID 1 2 23011 679095 8330022 2026-05-10 03:51:02
WMPROD PIX_TRAN PK_PIX_TRAN_1 VALID UNIQUE PIX_TRAN_ID 1 2 18377 1459923 8425260 2026-05-10 03:51:03
WMPROD PIX_TRAN PURGE_IDX_PT_01_1 VALID NONUNIQUE TC_COMPANY_ID 1 3 64163 1577558 8425260 2026-05-10 03:51:03
WMPROD PIX_TRAN PURGE_IDX_PT_01_1 VALID NONUNIQUE WHSE 2 3 64163 1577558 8425260 2026-05-10 03:51:03
WMPROD PIX_TRAN PURGE_IDX_PT_01_1 VALID NONUNIQUE PROC_STAT_CODE 3 3 64163 1577558 8425260 2026-05-10 03:51:03
WMPROD PIX_TRAN PURGE_IDX_PT_01_1 VALID NONUNIQUE MOD_DATE_TIME 4 3 64163 1577558 8425260 2026-05-10 03:51:03
WMPROD PIX_TRAN UK_PIX_TRAN_1 VALID UNIQUE TRAN_TYPE 1 2 56471 2910423 8425260 2026-05-10 03:51:04
WMPROD PIX_TRAN UK_PIX_TRAN_1 VALID UNIQUE TRAN_CODE 2 2 56471 2910423 8425260 2026-05-10 03:51:04
WMPROD PIX_TRAN UK_PIX_TRAN_1 VALID UNIQUE TRAN_NBR 3 2 56471 2910423 8425260 2026-05-10 03:51:04
WMPROD PIX_TRAN UK_PIX_TRAN_1 VALID UNIQUE PIX_SEQ_NBR 4 2 56471 2910423 8425260 2026-05-10 03:51:04
49 rows selected.
SQL> 执行计划分析#
fb9n9ybz4ww5u 的SQL语句如下：
INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID, OWNER_NO, FACILITY_NO, ORDER_TYPE, ORDER_NO, ORDER_DTL_NO, STATUS, STATUS_DESC, STATUS_TIME, OPERATOR, COMMENTS, WAVE_NO, CREATE_DATE_TIME, MAHN_WMSFE_F, MAHN_WMSFE_DTM, FIELD_001, FIELD_002, FIELD_003, FIELD_004, FIELD_005, FIELD_006, FIELD_007, FIELD_008, FIELD_009, FIELD_010) SELECT APP_ORDER_STATUS_CHANGE_ID_SEQ.NEXTVAL, SEASON, WHSE, '1', TC_ASN_ID, SEQ_NBR, '35', '上架开始', NVL(CREATE_DATE_TIME, SYSDATE), USER_NAME, '', '', SYSDATE, '0', SYSDATE, '', '', '', '', '', '', '', '', '', '' FROM (SELECT PTT.SEASON, PTT.WHSE, A.TC_ASN_ID, (SELECT NVL(NVL(AD.SEQ_NBR, AD.REF_FIELD_4), '1') FROM ASN_DETAIL AD WHERE AD.ASN_ID = A.ASN_ID AND ROWNUM < 2) AS SEQ_NBR, PTT.CREATE_DATE_TIME, (SELECT UU.USER_FIRST_NAME || UU.USER_LAST_NAME FROM UCL_USER UU WHERE UU.USER_NAME = PTT.USER_ID AND ROWNUM < 2) AS USER_NAME, ROW_NUMBER() OVER(PARTITION BY PTT.REF_FIELD_1 ORDER BY PTT.CREATE_DATE_TIME) AS RN FROM PIX_TRAN PTT LEFT JOIN ASN A ON A.TC_ASN_ID = PTT.REF_FIELD_1 WHERE PTT.TRAN_TYPE = '608' AND PTT.TRAN_CODE = '12' AND PTT.REF_FIELD_4 = 'PP' AND PTT.REF_FIELD_1 = :B1 AND PTT.PROC_STAT_CODE <> 0) WHERE RN = 1执行计划如下：
SQL> select *
from table(dbms_xplan.display_cursor('fb9n9ybz4ww5u', null, 'ALLSTATS LAST +PEEKED_BINDS +OUTLINE')); 2
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID fb9n9ybz4ww5u, child number 0
-------------------------------------
INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID,
OWNER_NO, FACILITY_NO, ORDER_TYPE, ORDER_NO, ORDER_DTL_NO, STATUS,
STATUS_DESC, STATUS_TIME, OPERATOR, COMMENTS, WAVE_NO,
CREATE_DATE_TIME, MAHN_WMSFE_F, MAHN_WMSFE_DTM, FIELD_001, FIELD_002,
FIELD_003, FIELD_004, FIELD_005, FIELD_006, FIELD_007, FIELD_008,
FIELD_009, FIELD_010) SELECT APP_ORDER_STATUS_CHANGE_ID_SEQ.NEXTVAL,
SEASON, WHSE, '1', TC_ASN_ID, SEQ_NBR, '35', '涓寮濮,
NVL(CREATE_DATE_TIME,SYSDATE), USER_NAME, '', '', SYSDATE, '0',
SYSDATE, '', '', '', '', '', '', '', '', '', '' FROM (SELECT
PTT.SEASON, PTT.WHSE, A.TC_ASN_ID, (SELECT NVL(NVL(AD.SEQ_NBR,
AD.REF_FIELD_4), '1') FROM ASN_DETAIL AD WHERE AD.ASN_ID = A.ASN_ID AND
ROWNUM < 2) AS SEQ_NBR, PTT.CREATE_DATE_TIME, (SELECT
UU.USER_FIRST_NAME || UU.USER_LAST_NAME FROM UCL_USER UU WHERE
UU.USER_NAME = PTT.USER_ID AND ROWNUM < 2) AS USER_NAME, ROW_NUMBER()
OVER(PARTITION BY PTT.REF_FIELD_1 ORDER BY PTT.CREATE_DATE_TIME) AS RN
FROM PIX_TRAN PTT LEFT JOIN A
Plan hash value: 2327536561
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | |
| 1 | LOAD TABLE CONVENTIONAL | APP_ORDER_STATUS_CHANGE | |
|* 2 | COUNT STOPKEY | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | ASN_DETAIL | 1 |
|* 4 | INDEX RANGE SCAN | TM_ASN_DETAIL_IDX2 | 1 |
|* 5 | COUNT STOPKEY | | |
| 6 | TABLE ACCESS BY INDEX ROWID | UCL_USER | 1 |
|* 7 | INDEX UNIQUE SCAN | UCL_USER_USRNM_UK | 1 |
| 8 | SEQUENCE | APP_ORDER_STATUS_CHANGE_ID_SEQ | |
|* 9 | VIEW | | 1 |
|* 10 | WINDOW SORT PUSHED RANK | | 1 |
| 11 | MERGE JOIN OUTER | | 1 |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | PIX_TRAN | 1 |
|* 13 | INDEX RANGE SCAN | UK_PIX_TRAN_1 | 53410 |
| 14 | BUFFER SORT | | 1 |
| 15 | TABLE ACCESS BY INDEX ROWID BATCHED| ASN | 1 |
|* 16 | INDEX RANGE SCAN | ASN_UIDX | 1 |
---------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(100)
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$54D64B3C")
MERGE(@"SEL$64EAE176" >"SEL$4")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$64EAE176")
MERGE(@"SEL$2" >"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
FULL(@"INS$1" "APP_ORDER_STATUS_CHANGE"@"INS$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_002"@"SEL$1")
INDEX_RS_ASC(@"SEL$54D64B3C" "PTT"@"SEL$3" ("PIX_TRAN"."TRAN_TYPE"
"PIX_TRAN"."TRAN_CODE" "PIX_TRAN"."TRAN_NBR" "PIX_TRAN"."PIX_SEQ_NBR"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$54D64B3C" "PTT"@"SEL$3")
INDEX_RS_ASC(@"SEL$54D64B3C" "A"@"SEL$2" ("ASN"."TC_ASN_ID" "ASN"."TC_COMPANY_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$54D64B3C" "A"@"SEL$2")
LEADING(@"SEL$54D64B3C" "PTT"@"SEL$3" "A"@"SEL$2")
USE_MERGE_CARTESIAN(@"SEL$54D64B3C" "A"@"SEL$2")
INDEX_RS_ASC(@"SEL$6" "UU"@"SEL$6" ("UCL_USER"."USER_NAME"))
INDEX_RS_ASC(@"SEL$5" "AD"@"SEL$5" ("ASN_DETAIL"."ASN_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "AD"@"SEL$5")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<2)
4 - access("AD"."ASN_ID"=:B1)
5 - filter(ROWNUM<2)
7 - access("UU"."USER_NAME"=:B1)
9 - filter("RN"=1)
10 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTT"."REF_FIELD_1" ORDER BY
"PTT"."CREATE_DATE_TIME")<=1)
12 - filter(("PTT"."REF_FIELD_1"=:B1 AND "PTT"."REF_FIELD_4"='PP' AND
"PTT"."PROC_STAT_CODE"<>0))
13 - access("PTT"."TRAN_TYPE"='608' AND "PTT"."TRAN_CODE"='12')
16 - access("A"."TC_ASN_ID"=:B1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID fb9n9ybz4ww5u, child number 1
-------------------------------------
INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID,
OWNER_NO, FACILITY_NO, ORDER_TYPE, ORDER_NO, ORDER_DTL_NO, STATUS,
STATUS_DESC, STATUS_TIME, OPERATOR, COMMENTS, WAVE_NO,
CREATE_DATE_TIME, MAHN_WMSFE_F, MAHN_WMSFE_DTM, FIELD_001, FIELD_002,
FIELD_003, FIELD_004, FIELD_005, FIELD_006, FIELD_007, FIELD_008,
FIELD_009, FIELD_010) SELECT APP_ORDER_STATUS_CHANGE_ID_SEQ.NEXTVAL,
SEASON, WHSE, '1', TC_ASN_ID, SEQ_NBR, '35', '涓寮濮,
NVL(CREATE_DATE_TIME,SYSDATE), USER_NAME, '', '', SYSDATE, '0',
SYSDATE, '', '', '', '', '', '', '', '', '', '' FROM (SELECT
PTT.SEASON, PTT.WHSE, A.TC_ASN_ID, (SELECT NVL(NVL(AD.SEQ_NBR,
AD.REF_FIELD_4), '1') FROM ASN_DETAIL AD WHERE AD.ASN_ID = A.ASN_ID AND
ROWNUM < 2) AS SEQ_NBR, PTT.CREATE_DATE_TIME, (SELECT
UU.USER_FIRST_NAME || UU.USER_LAST_NAME FROM UCL_USER UU WHERE
UU.USER_NAME = PTT.USER_ID AND ROWNUM < 2) AS USER_NAME, ROW_NUMBER()
OVER(PARTITION BY PTT.REF_FIELD_1 ORDER BY PTT.CREATE_DATE_TIME) AS RN
FROM PIX_TRAN PTT LEFT JOIN A
Plan hash value: 2327536561
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | |
| 1 | LOAD TABLE CONVENTIONAL | APP_ORDER_STATUS_CHANGE | |
|* 2 | COUNT STOPKEY | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | ASN_DETAIL | 1 |
|* 4 | INDEX RANGE SCAN | TM_ASN_DETAIL_IDX2 | 1 |
|* 5 | COUNT STOPKEY | | |
| 6 | TABLE ACCESS BY INDEX ROWID | UCL_USER | 1 |
|* 7 | INDEX UNIQUE SCAN | UCL_USER_USRNM_UK | 1 |
| 8 | SEQUENCE | APP_ORDER_STATUS_CHANGE_ID_SEQ | |
|* 9 | VIEW | | 1 |
|* 10 | WINDOW SORT PUSHED RANK | | 1 |
| 11 | MERGE JOIN OUTER | | 1 |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | PIX_TRAN | 1 |
|* 13 | INDEX RANGE SCAN | UK_PIX_TRAN_1 | 53410 |
| 14 | BUFFER SORT | | 1 |
| 15 | TABLE ACCESS BY INDEX ROWID BATCHED| ASN | 1 |
|* 16 | INDEX RANGE SCAN | ASN_UIDX | 1 |
---------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(100)
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$54D64B3C")
MERGE(@"SEL$64EAE176" >"SEL$4")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$64EAE176")
MERGE(@"SEL$2" >"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
FULL(@"INS$1" "APP_ORDER_STATUS_CHANGE"@"INS$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_002"@"SEL$1")
INDEX_RS_ASC(@"SEL$54D64B3C" "PTT"@"SEL$3" ("PIX_TRAN"."TRAN_TYPE"
"PIX_TRAN"."TRAN_CODE" "PIX_TRAN"."TRAN_NBR" "PIX_TRAN"."PIX_SEQ_NBR"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$54D64B3C" "PTT"@"SEL$3")
INDEX_RS_ASC(@"SEL$54D64B3C" "A"@"SEL$2" ("ASN"."TC_ASN_ID" "ASN"."TC_COMPANY_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$54D64B3C" "A"@"SEL$2")
LEADING(@"SEL$54D64B3C" "PTT"@"SEL$3" "A"@"SEL$2")
USE_MERGE_CARTESIAN(@"SEL$54D64B3C" "A"@"SEL$2")
INDEX_RS_ASC(@"SEL$6" "UU"@"SEL$6" ("UCL_USER"."USER_NAME"))
INDEX_RS_ASC(@"SEL$5" "AD"@"SEL$5" ("ASN_DETAIL"."ASN_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "AD"@"SEL$5")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<2)
4 - access("AD"."ASN_ID"=:B1)
5 - filter(ROWNUM<2)
7 - access("UU"."USER_NAME"=:B1)
9 - filter("RN"=1)
10 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTT"."REF_FIELD_1" ORDER BY
"PTT"."CREATE_DATE_TIME")<=1)
12 - filter(("PTT"."REF_FIELD_1"=:B1 AND "PTT"."REF_FIELD_4"='PP' AND
"PTT"."PROC_STAT_CODE"<>0))
13 - access("PTT"."TRAN_TYPE"='608' AND "PTT"."TRAN_CODE"='12')
16 - access("A"."TC_ASN_ID"=:B1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL_ID fb9n9ybz4ww5u, child number 2
-------------------------------------
INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID,
OWNER_NO, FACILITY_NO, ORDER_TYPE, ORDER_NO, ORDER_DTL_NO, STATUS,
STATUS_DESC, STATUS_TIME, OPERATOR, COMMENTS, WAVE_NO,
CREATE_DATE_TIME, MAHN_WMSFE_F, MAHN_WMSFE_DTM, FIELD_001, FIELD_002,
FIELD_003, FIELD_004, FIELD_005, FIELD_006, FIELD_007, FIELD_008,
FIELD_009, FIELD_010) SELECT APP_ORDER_STATUS_CHANGE_ID_SEQ.NEXTVAL,
SEASON, WHSE, '1', TC_ASN_ID, SEQ_NBR, '35', '涓寮濮,
NVL(CREATE_DATE_TIME,SYSDATE), USER_NAME, '', '', SYSDATE, '0',
SYSDATE, '', '', '', '', '', '', '', '', '', '' FROM (SELECT
PTT.SEASON, PTT.WHSE, A.TC_ASN_ID, (SELECT NVL(NVL(AD.SEQ_NBR,
AD.REF_FIELD_4), '1') FROM ASN_DETAIL AD WHERE AD.ASN_ID = A.ASN_ID AND
ROWNUM < 2) AS SEQ_NBR, PTT.CREATE_DATE_TIME, (SELECT
UU.USER_FIRST_NAME || UU.USER_LAST_NAME FROM UCL_USER UU WHERE
UU.USER_NAME = PTT.USER_ID AND ROWNUM < 2) AS USER_NAME, ROW_NUMBER()
OVER(PARTITION BY PTT.REF_FIELD_1 ORDER BY PTT.CREATE_DATE_TIME) AS RN
FROM PIX_TRAN PTT LEFT JOIN A
Plan hash value: 2327536561
---------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |
---------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | |
| 1 | LOAD TABLE CONVENTIONAL | APP_ORDER_STATUS_CHANGE | |
|* 2 | COUNT STOPKEY | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | ASN_DETAIL | 1 |
|* 4 | INDEX RANGE SCAN | TM_ASN_DETAIL_IDX2 | 1 |
|* 5 | COUNT STOPKEY | | |
| 6 | TABLE ACCESS BY INDEX ROWID | UCL_USER | 1 |
|* 7 | INDEX UNIQUE SCAN | UCL_USER_USRNM_UK | 1 |
| 8 | SEQUENCE | APP_ORDER_STATUS_CHANGE_ID_SEQ | |
|* 9 | VIEW | | 1 |
|* 10 | WINDOW SORT PUSHED RANK | | 1 |
| 11 | MERGE JOIN OUTER | | 1 |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED | PIX_TRAN | 1 |
|* 13 | INDEX RANGE SCAN | UK_PIX_TRAN_1 | 49706 |
| 14 | BUFFER SORT | | 1 |
| 15 | TABLE ACCESS BY INDEX ROWID BATCHED| ASN | 1 |
|* 16 | INDEX RANGE SCAN | ASN_UIDX | 1 |
---------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(100)
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$54D64B3C")
MERGE(@"SEL$64EAE176" >"SEL$4")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$64EAE176")
MERGE(@"SEL$2" >"SEL$3")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$2")
FULL(@"INS$1" "APP_ORDER_STATUS_CHANGE"@"INS$1")
NO_ACCESS(@"SEL$1" "from$_subquery$_002"@"SEL$1")
INDEX_RS_ASC(@"SEL$54D64B3C" "PTT"@"SEL$3" ("PIX_TRAN"."TRAN_TYPE"
"PIX_TRAN"."TRAN_CODE" "PIX_TRAN"."TRAN_NBR" "PIX_TRAN"."PIX_SEQ_NBR"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$54D64B3C" "PTT"@"SEL$3")
INDEX_RS_ASC(@"SEL$54D64B3C" "A"@"SEL$2" ("ASN"."TC_ASN_ID" "ASN"."TC_COMPANY_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$54D64B3C" "A"@"SEL$2")
LEADING(@"SEL$54D64B3C" "PTT"@"SEL$3" "A"@"SEL$2")
USE_MERGE_CARTESIAN(@"SEL$54D64B3C" "A"@"SEL$2")
INDEX_RS_ASC(@"SEL$6" "UU"@"SEL$6" ("UCL_USER"."USER_NAME"))
INDEX_RS_ASC(@"SEL$5" "AD"@"SEL$5" ("ASN_DETAIL"."ASN_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "AD"@"SEL$5")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<2)
4 - access("AD"."ASN_ID"=:B1)
5 - filter(ROWNUM<2)
7 - access("UU"."USER_NAME"=:B1)
9 - filter("RN"=1)
10 - filter(ROW_NUMBER() OVER ( PARTITION BY "PTT"."REF_FIELD_1" ORDER BY
"PTT"."CREATE_DATE_TIME")<=1)
12 - filter(("PTT"."REF_FIELD_1"=:B1 AND "PTT"."REF_FIELD_4"='PP' AND
"PTT"."PROC_STAT_CODE"<>0))
13 - access("PTT"."TRAN_TYPE"='608' AND "PTT"."TRAN_CODE"='12')
16 - access("A"."TC_ASN_ID"=:B1)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
。。。省略分析执行计划发现核心问题在于：
INDEX RANGE SCAN UK_PIX_TRAN_1 E-Rows 约 5 万+，谓词里显示：
access: PTT.TRAN_TYPE='608' AND PTT.TRAN_CODE='12'
filter: PTT.REF_FIELD_1=:B1
PTT.REF_FIELD_4='PP'
PTT.PROC_STAT_CODE<>0也就是说，Oracle 当前用的是 UK_PIX_TRAN_1(TRAN_TYPE, TRAN_CODE, TRAN_NBR, PIX_SEQ_NBR)，只能先按 TRAN_TYPE='608' 和 TRAN_CODE='12' 扫一大批数据，然后再回表过滤 REF_FIELD_1、REF_FIELD_4、PROC_STAT_CODE。这会产生大量单块读和 CPU 消耗。
而你现有 PIX_TRAN 索引里，虽然有：
PIXTRAN_REFFLDPSTATCODE_IDX_1(REF_FIELD_3, REF_FIELD_1, PROC_STAT_CODE)
PIXTRAN_PSTATCODEWHSE_IDX_1(PROC_STAT_CODE, WHSE, MOD_DATE_TIME)
UK_PIX_TRAN_1(TRAN_TYPE, TRAN_CODE, TRAN_NBR, PIX_SEQ_NBR)但没有一个索引能同时很好覆盖这个 SQL 的核心条件：REF_FIELD_1 + TRAN_TYPE + TRAN_CODE + REF_FIELD_4 + PROC_STAT_CODE + CREATE_DATE_TIME
针对当前计划里 PIX_TRAN 的谓词：
PTT.REF_FIELD_1 = :B1
AND PTT.REF_FIELD_4 = 'PP'
AND PTT.PROC_STAT_CODE <> 0
AND PTT.TRAN_TYPE = '608'
AND PTT.TRAN_CODE = '12'
ORDER BY PTT.CREATE_DATE_TIME建议添加这个索引
create index WMPROD.IDX_PIX_TRAN_WMSFE_ASN_01
on WMPROD.PIX_TRAN
(
REF_FIELD_1,
TRAN_TYPE,
TRAN_CODE,
REF_FIELD_4,
PROC_STAT_CODE,
CREATE_DATE_TIME
);原因：
REF_FIELD_1 直接按 ASN 单号定位
TRAN_TYPE 固定条件 608
TRAN_CODE 固定条件 12
REF_FIELD_4 固定条件 PP
PROC_STAT_CODE 状态过滤
CREATE_DATE_TIME 用于 ROW_NUMBER 排序不过这个索引需要先评估数据分布。建议先查选择性：
set lines 300 pages 1000
select count(*) total_rows,
count(case when tran_type = '608' and tran_code = '12' then 1 end) cnt_608_12,
count(case when tran_type = '608' and tran_code = '12'
and ref_field_4 = 'PP'
and proc_stat_code <> 0 then 1 end) cnt_target
from wmprod.pix_tran;
select ref_field_1,
count(*) cnt
from wmprod.pix_tran
where tran_type = '608'
and tran_code = '12'
and ref_field_4 = 'PP'
and proc_stat_code <> 0
group by ref_field_1
order by count(*) desc
fetch first 20 rows only;如果 REF_FIELD_1 很分散，这个索引效果会比较好。
查询结果如下：
PIX_TRAN 总行数 = 8,560,301
TRAN_TYPE='608' and TRAN_CODE='12' = 685,717
再加 REF_FIELD_4='PP' and PROC_STAT_CODE<>0 = 344,304也就是说，当前执行计划如果走：
UK_PIX_TRAN_1(TRAN_TYPE, TRAN_CODE, TRAN_NBR, PIX_SEQ_NBR)它会先扫出大约 68.5 万行，再过滤到 34.4 万行，最后再按 REF_FIELD_1 = :B1 过滤。这个访问路径成本肯定高。索引范围扫描 + 大量回表过滤。
但你按 REF_FIELD_1 分组后，Top 20 最大值也就：
210、199、154、144、140...说明单个 ASN 单号对应的数据量很小。 所以最优访问路径应该是：
先用 REF_FIELD_1 精准定位 ASN 单号
再过滤 TRAN_TYPE / TRAN_CODE / REF_FIELD_4 / PROC_STAT_CODEPIX_TRAN 应该补一个以 REF_FIELD_1 为前导列的组合索引。
第二个g14w83s9b652u 的SQL语句如下：
INSERT INTO APP_ORDER_STATUS_CHANGE ...
SELECT ...
FROM APP_ASN_RECEIVE AR
LEFT JOIN ASN A ON A.TC_ASN_ID = AR.TC_ASN_ID
LEFT JOIN COMPANY C ON C.COMPANY_ID = A.TC_COMPANY_ID
WHERE AR.TC_ASN_ID = :B1
AND AR.DELETED = 0
AND ROWNUM < 2执行计划如下：
SQL> select *
from table(dbms_xplan.display_cursor(
'g14w83s9b652u',
null,
'TYPICAL +PEEKED_BINDS +OUTLINE'
)); 2 3 4 5 6
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g14w83s9b652u, child number 0
-------------------------------------
INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID,
OWNER_NO, FACILITY_NO, ORDER_TYPE, ORDER_NO, ORDER_DTL_NO, STATUS,
STATUS_DESC, STATUS_TIME, OPERATOR, COMMENTS, WAVE_NO,
CREATE_DATE_TIME, MAHN_WMSFE_F, MAHN_WMSFE_DTM, FIELD_001, FIELD_002,
FIELD_003, FIELD_004, FIELD_005, FIELD_006, FIELD_007, FIELD_008,
FIELD_009, FIELD_010) SELECT APP_ORDER_STATUS_CHANGE_ID_SEQ.NEXTVAL,
C.COMPANY_CODE, A.DESTINATION_FACILITY_ALIAS_ID, '1', A.TC_ASN_ID,
AR.SEQ_NBR, '15', '寮濮璐?, NVL(AR.CREATE_TIME,SYSDATE),
AR.CREATE_USER_NAME, '', '', SYSDATE, '0', SYSDATE, '', '', '', '', '',
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
'', '', '', '', '' FROM APP_ASN_RECEIVE AR LEFT JOIN ASN A ON
A.TC_ASN_ID = AR.TC_ASN_ID LEFT JOIN COMPANY C ON C.COMPANY_ID =
A.TC_COMPANY_ID WHERE AR.TC_ASN_ID = :B1 AND AR.DELETED = 0 AND ROWNUM
< 2
Plan hash value: 3958100088
--------------------------------------------------------------------------------
--------------------------------------------
| Id | Operation | Name
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
--------------------------------------------
| 0 | INSERT STATEMENT |
| | | 1207 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | APP_ORDER_STATUS_CHANGE
| | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 2 | SEQUENCE | APP_ORDER_STATUS_CHANGE_ID_S
EQ | | | | |
|* 3 | COUNT STOPKEY |
| | | | |
| 4 | NESTED LOOPS OUTER |
| 1 | 87 | 1207 (1)| 00:00:01 |
| 5 | MERGE JOIN OUTER |
| 1 | 71 | 1206 (1)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 6 | TABLE ACCESS FULL | APP_ASN_RECEIVE
| 1 | 42 | 1204 (1)| 00:00:01 |
| 7 | BUFFER SORT |
| 1 | 29 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| ASN
| 1 | 29 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | ASN_UIDX
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | COMPANY
| 1 | 16 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | COMPANY_PK
| 1 | | 0 (0)| |
--------------------------------------------------------------------------------
--------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(100)
OUTLINE_LEAF(@"SEL$B331CAF9")
MERGE(@"SEL$7237DA6D" >"SEL$5")
OUTLINE_LEAF(@"INS$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$7237DA6D")
MERGE(@"SEL$3" >"SEL$4")
MERGE(@"SEL$58A6D7F6" >"SEL$4")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"INS$1" "APP_ORDER_STATUS_CHANGE"@"INS$1")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
FULL(@"SEL$B331CAF9" "AR"@"SEL$2")
INDEX_RS_ASC(@"SEL$B331CAF9" "A"@"SEL$1" ("ASN"."TC_ASN_ID" "ASN"."TC_COMP
ANY_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B331CAF9" "A"@"SEL$1")
INDEX_RS_ASC(@"SEL$B331CAF9" "C"@"SEL$3" ("COMPANY"."COMPANY_ID"))
LEADING(@"SEL$B331CAF9" "AR"@"SEL$2" "A"@"SEL$1" "C"@"SEL$3")
USE_MERGE_CARTESIAN(@"SEL$B331CAF9" "A"@"SEL$1")
USE_NL(@"SEL$B331CAF9" "C"@"SEL$3")
END_OUTLINE_DATA
*/
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM<2)
6 - filter(("AR"."TC_ASN_ID"=:B1 AND "AR"."DELETED"=0))
9 - access("A"."TC_ASN_ID"=:B1)
11 - access("C"."COMPANY_ID"="A"."TC_COMPANY_ID")
SQL_ID g14w83s9b652u, child number 1
-------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID,
OWNER_NO, FACILITY_NO, ORDER_TYPE, ORDER_NO, ORDER_DTL_NO, STATUS,
STATUS_DESC, STATUS_TIME, OPERATOR, COMMENTS, WAVE_NO,
CREATE_DATE_TIME, MAHN_WMSFE_F, MAHN_WMSFE_DTM, FIELD_001, FIELD_002,
FIELD_003, FIELD_004, FIELD_005, FIELD_006, FIELD_007, FIELD_008,
FIELD_009, FIELD_010) SELECT APP_ORDER_STATUS_CHANGE_ID_SEQ.NEXTVAL,
C.COMPANY_CODE, A.DESTINATION_FACILITY_ALIAS_ID, '1', A.TC_ASN_ID,
AR.SEQ_NBR, '15', '寮濮璐?, NVL(AR.CREATE_TIME,SYSDATE),
AR.CREATE_USER_NAME, '', '', SYSDATE, '0', SYSDATE, '', '', '', '', '',
'', '', '', '', '' FROM APP_ASN_RECEIVE AR LEFT JOIN ASN A ON
A.TC_ASN_ID = AR.TC_ASN_ID LEFT JOIN COMPANY C ON C.COMPANY_ID =
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
A.TC_COMPANY_ID WHERE AR.TC_ASN_ID = :B1 AND AR.DELETED = 0 AND ROWNUM
< 2
Plan hash value: 3958100088
--------------------------------------------------------------------------------
--------------------------------------------
| Id | Operation | Name
| Rows | Bytes | Cost (%CPU)| Time |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------
| 0 | INSERT STATEMENT |
| | | 1207 (100)| |
| 1 | LOAD TABLE CONVENTIONAL | APP_ORDER_STATUS_CHANGE
| | | | |
| 2 | SEQUENCE | APP_ORDER_STATUS_CHANGE_ID_S
EQ | | | | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 3 | COUNT STOPKEY |
| | | | |
| 4 | NESTED LOOPS OUTER |
| 1 | 87 | 1207 (1)| 00:00:01 |
| 5 | MERGE JOIN OUTER |
| 1 | 71 | 1206 (1)| 00:00:01 |
|* 6 | TABLE ACCESS FULL | APP_ASN_RECEIVE
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 1 | 42 | 1204 (1)| 00:00:01 |
| 7 | BUFFER SORT |
| 1 | 29 | 2 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID BATCHED| ASN
| 1 | 29 | 2 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | ASN_UIDX
| 1 | | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 10 | TABLE ACCESS BY INDEX ROWID | COMPANY
| 1 | 16 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | COMPANY_PK
| 1 | | 0 (0)| |
--------------------------------------------------------------------------------
--------------------------------------------
Outline Data
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('18.1.0')
DB_VERSION('18.1.0')
OPT_PARAM('_optim_peek_user_binds' 'false')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('_optimizer_adaptive_cursor_sharing' 'false')
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
OPT_PARAM('_optimizer_use_feedback' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(100)
OUTLINE_LEAF(@"SEL$B331CAF9")
MERGE(@"SEL$7237DA6D" >"SEL$5")
OUTLINE_LEAF(@"INS$1")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$7237DA6D")
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
MERGE(@"SEL$3" >"SEL$4")
MERGE(@"SEL$58A6D7F6" >"SEL$4")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$58A6D7F6")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
FULL(@"INS$1" "APP_ORDER_STATUS_CHANGE"@"INS$1")
FULL(@"SEL$B331CAF9" "AR"@"SEL$2")
INDEX_RS_ASC(@"SEL$B331CAF9" "A"@"SEL$1" ("ASN"."TC_ASN_ID" "ASN"."TC_COMP
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
ANY_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B331CAF9" "A"@"SEL$1")
INDEX_RS_ASC(@"SEL$B331CAF9" "C"@"SEL$3" ("COMPANY"."COMPANY_ID"))
LEADING(@"SEL$B331CAF9" "AR"@"SEL$2" "A"@"SEL$1" "C"@"SEL$3")
USE_MERGE_CARTESIAN(@"SEL$B331CAF9" "A"@"SEL$1")
USE_NL(@"SEL$B331CAF9" "C"@"SEL$3")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------
3 - filter(ROWNUM<2)
6 - filter(("AR"."TC_ASN_ID"=:B1 AND "AR"."DELETED"=0))
9 - access("A"."TC_ASN_ID"=:B1)
11 - access("C"."COMPANY_ID"="A"."TC_COMPANY_ID")
。。。省略执行计划里最明显的问题是：
TABLE ACCESS FULL APP_ASN_RECEIVE
filter: AR.TC_ASN_ID = :B1 AND AR.DELETED = 0
Cost: 1200+这个 SQL 现在需要的是 APP_ASN_RECEIVE 上的索引。
优先建议这个
create index WMPROD.IDX_APP_ASN_RECEIVE_01
on WMPROD.APP_ASN_RECEIVE(TC_ASN_ID, DELETED);另外 APP_ORDER_STATUS_CHANGE.ORDER_NO 包体里大量出现如下SQL：
FROM APP_ORDER_STATUS_CHANGE A
WHERE A.ORDER_NO = P_TC_ASN_ID但当前 APP_ORDER_STATUS_CHANGE 只有主键索引 (APP_ORDER_STATUS_CHANGE_ID, ORDER_DTL_NO)。建议增加：
create index WMPROD.IDX_AOSC_ORDER_NO_01
on WMPROD.APP_ORDER_STATUS_CHANGE(ORDER_NO);三、故障解决#
调整 Job 调度间隔#
索引优化之前，优先需要避免 Job 继续堆积。如果一个 Job 单次运行 10～20 分钟，却每 2～3 分钟触发一次，那么即使 SQL 优化后，也可能在业务高峰再次堆积。因此将两个 Job 调度间隔调整为 30 分钟，先让系统稳定下来。
begin
dbms_scheduler.set_attribute(
name => 'WMPROD.GET_WMSFE_ASN_STATUS_CHANGE',
attribute => 'repeat_interval',
value => 'FREQ=MINUTELY;INTERVAL=30'
);
dbms_scheduler.set_attribute(
name => 'WMPROD.GET_WMSFE_JOB_ASN_ORDERS',
attribute => 'repeat_interval',
value => 'FREQ=MINUTELY;INTERVAL=30'
);
end;
/创建索引#
根据 SQL 谓词和关联条件，创建以下索引：
create index WMPROD.IDX_PIX_TRAN_WMSFE_ASN_01
on WMPROD.PIX_TRAN
(
REF_FIELD_1,
TRAN_TYPE,
TRAN_CODE,
REF_FIELD_4,
PROC_STAT_CODE,
CREATE_DATE_TIME
) online;
create index WMPROD.IDX_AOSC_ORDER_NO_01
on WMPROD.APP_ORDER_STATUS_CHANGE
(
ORDER_NO
) online;
create index WMPROD.IDX_APP_ASN_RECEIVE_01
on WMPROD.APP_ASN_RECEIVE
(
TC_ASN_ID, DELETED
) online;创建完成后收集索引统计信息：
begin
dbms_stats.gather_index_stats(
ownname => 'WMPROD',
indname => 'IDX_PIX_TRAN_WMSFE_ASN_01'
);
dbms_stats.gather_index_stats(
ownname => 'WMPROD',
indname => 'IDX_AOSC_ORDER_NO_01'
);
dbms_stats.gather_index_stats(
ownname => 'WMPROD',
indname => 'IDX_APP_ASN_RECEIVE_01'
);
end;
/四、验证优化效果#
查 Job 最近一次执行耗时#
set lines 300 pages 1000
col owner for a15
col job_name for a40
col status for a12
col actual_start_date for a35
col run_duration for a25
col cpu_used for a25
col additional_info for a120
select owner,
job_name,
status,
actual_start_date,
run_duration,
cpu_used,
error#,
substr(additional_info,1,120) additional_info
from dba_scheduler_job_run_details
where owner = 'WMPROD'
and job_name in ('GET_WMSFE_ASN_STATUS_CHANGE',
'GET_WMSFE_JOB_ASN_ORDERS')
order by actual_start_date desc
fetch first 20 rows only;结果如下：
OWNER JOB_NAME STATUS ACTUAL_START_DATE RUN_DURATION CPU_USED ERROR# ADDITIONAL_INFO
--------------- ---------------------------------------- ------------ ----------------------------------- ------------------------- ------------------------- ---------- ------------------------------------------------------------------------------------------------------------------------
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 07.53.57.162100 PM +08:00 +000 00:01:26 +000 00:01:24.26 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 07.42.45.104700 PM +08:00 +000 00:04:32 +000 00:04:31.07 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 07.20.37.431258 PM +08:00 +000 00:14:47 +000 00:05:04.75 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 07.16.48.331879 PM +08:00 +000 00:24:20 +000 00:14:12.86 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 07.11.20.056484 PM +08:00 +000 00:09:17 +000 00:09:16.05 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 07.01.19.775652 PM +08:00 +000 00:10:00 +000 00:09:58.79 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 06.57.28.208280 PM +08:00 +000 00:19:20 +000 00:19:15.89 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 06.51.03.898434 PM +08:00 +000 00:10:16 +000 00:10:09.16 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 06.39.35.485777 PM +08:00 +000 00:11:28 +000 00:11:22.23 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 06.36.30.575480 PM +08:00 +000 00:20:58 +000 00:20:52.48 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 06.27.52.480582 PM +08:00 +000 00:11:43 +000 00:10:37.42 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 06.17.28.586754 PM +08:00 +000 00:10:24 +000 00:10:21.93 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 06.14.39.457713 PM +08:00 +000 00:21:51 +000 00:21:03.08 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 06.06.09.108112 PM +08:00 +000 00:11:19 +000 00:11:02.94 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 05.52.32.919112 PM +08:00 +000 00:22:07 +000 00:21:57.87 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 05.48.44.165838 PM +08:00 +000 00:17:25 +000 00:12:53.41 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 05.28.34.598389 PM +08:00 +000 00:23:58 +000 00:23:47.90 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 05.27.43.404375 PM +08:00 +000 00:21:01 +000 00:15:48.76 0
WMPROD GET_WMSFE_JOB_ASN_ORDERS SUCCEEDED 12-MAY-26 05.04.55.458669 PM +08:00 +000 00:22:48 +000 00:15:49.82 0
WMPROD GET_WMSFE_ASN_STATUS_CHANGE SUCCEEDED 12-MAY-26 05.03.12.056408 PM +08:00 +000 00:25:22 +000 00:25:08.38 0
20 rows selected.
SQL> 从这组结果看，优化已经明显生效，尤其是两个核心 Job 的执行时间和 SQL 逻辑读都大幅下降。
最新执行记录：
| Job | 优化前典型耗时 | 最新耗时 | 最新 CPU | 结论 |
|---|---|---|---|---|
GET_WMSFE_ASN_STATUS_CHANGE | 约 20～25 分钟 | 4分32秒 | 4分31秒 | 明显下降 |
GET_WMSFE_JOB_ASN_ORDERS | 约 10～22 分钟 | 1分26秒 | 1分24秒 | 明显下降 |
之前 GET_WMSFE_ASN_STATUS_CHANGE 多次运行 20 分钟以上，例如 18:36 运行 20分58秒、18:57 运行 19分20秒、19:16 运行 24分20秒；现在最近一次已经降到 4分32秒。GET_WMSFE_JOB_ASN_ORDERS 之前多次在 10 分钟以上，现在最近一次降到 1分26秒。
在查新索引是否降低了 SQL 消耗#
set lines 300 pages 1000
col sql_id for a15
col last_active_time for a25
col sql_text for a100
select sql_id,
child_number,
plan_hash_value,
executions,
round(cpu_time/1000000,2) cpu_sec,
round(elapsed_time/1000000,2) ela_sec,
buffer_gets,
round(buffer_gets / nullif(executions,0),2) gets_per_exec,
rows_processed,
last_active_time,
substr(sql_text,1,100) sql_text
from v$sql
where sql_id in (
'fb9n9ybz4ww5u',
'dtgks897yabva',
'g14w83s9b652u',
'cpkzgst2mfjms',
'bkwc8nr3k6c53',
'776jksjydvds8'
)
order by sql_id, child_number;查询结果如下：
SQL_ID CHILD_NUMBER PLAN_HASH_VALUE EXECUTIONS CPU_SEC ELA_SEC BUFFER_GETS GETS_PER_EXEC ROWS_PROCESSED LAST_ACTIVE_TIME SQL_TEXT
--------------- ------------ --------------- ---------- ---------- ---------- ----------- ------------- -------------- ------------------------- ----------------------------------------------------------------------------------------------------
776jksjydvds8 1 757813248 14 .49 1.87 88302 6307.29 3 2026-05-12 15:21:55 DELETE FROM APP_ASN_RECEIVE_ARVL_EXT A WHERE A.ARVL_ID IN (SELECT ID FROM APP_ASN_RECEIVE_ARVL WHERE
776jksjydvds8 2 757813248 8 .28 .95 50942 6367.75 0 2026-05-12 16:58:29 DELETE FROM APP_ASN_RECEIVE_ARVL_EXT A WHERE A.ARVL_ID IN (SELECT ID FROM APP_ASN_RECEIVE_ARVL WHERE
bkwc8nr3k6c53 1 1389005756 14 .32 .32 84941 6067.21 9 2026-05-12 15:21:55 DELETE FROM APP_ASN_RECEIVE_ARVL A WHERE A.TC_ASN_ID = :B1
bkwc8nr3k6c53 2 1389005756 8 .2 .2 49405 6175.63 4 2026-05-12 16:58:29 DELETE FROM APP_ASN_RECEIVE_ARVL A WHERE A.TC_ASN_ID = :B1
cpkzgst2mfjms 0 1389005756 868 17.69 18.47 5221461 6015.51 825 2026-05-11 11:39:01 DELETE FROM app_asn_receive_arvl WHERE (receive_id = :1 )
cpkzgst2mfjms 1 1389005756 405 8.71 9.26 2500912 6175.09 369 2026-05-12 18:28:02 DELETE FROM app_asn_receive_arvl WHERE (receive_id = :1 )
dtgks897yabva 0 3695283177 921 .09 .16 6171 6.7 921 2026-05-12 19:55:03 SELECT NVL(SUM(A.INVN_ADJMT_QTY), '0') FROM PIX_TRAN A LEFT JOIN ASN B ON B.TC_ASN_ID = A.REF_FIELD_
fb9n9ybz4ww5u 0 554698197 1071 .16 .17 8712 8.13 186 2026-05-12 20:14:23 INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID, OWNER_NO, FACILITY_NO, ORDER_TYPE,
g14w83s9b652u 0 4012314491 4964 .17 .17 14934 3.01 1 2026-05-12 20:14:23 INSERT INTO APP_ORDER_STATUS_CHANGE (APP_ORDER_STATUS_CHANGE_ID, OWNER_NO, FACILITY_NO, ORDER_TYPE,
9 rows selected.
SQL> 结论核心 SQL 的逻辑读已经降下来了，你现在 v$sql 里最关键的数据是：
| SQL_ID | SQL | gets_per_exec | 判断 |
|---|---|---|---|
dtgks897yabva | PIX_TRAN + ASN 聚合查询 | 6.7 | 很低，已优化 |
fb9n9ybz4ww5u | PIX_TRAN -> APP_ORDER_STATUS_CHANGE 插入 | 8.13 | 很低，已优化 |
g14w83s9b652u | APP_ASN_RECEIVE -> APP_ORDER_STATUS_CHANGE 插入 | 3.01 | 很低，已优化 |
这说明三个关键 SQL 已经不是高逻辑读 SQL 了。
尤其是 g14w83s9b652u，之前你看到它对 APP_ASN_RECEIVE 做全表扫描，Cost 1200+；现在 plan_hash_value 已经变成 4012314491，buffer_gets / executions = 3.01，说明它已经生成了新计划，基本可以判断 IDX_APP_ASN_RECEIVE_01 已经生效。
CPU 高的根因是 WMPROD 接口类 Scheduler Job 调度过密，同时 Job 内部 SQL 缺少合适索引，导致大量逻辑读和 CPU 消耗。通过调整 Job 间隔和补充关键索引，Job 执行时间从 10～25 分钟下降到 1～2 分钟，CPU 压力解除。
最后总结#
本次 Oracle CPU 高问题主要是 WMPROD 下 Scheduler Job 调度过密，任务执行时间远大于调度间隔，叠加包内 SQL 访问路径不佳，导致 CPU 长时间被持续消耗；通过调整 Job 调度间隔、补充关键组合索引并收集统计信息后，核心 Job 执行时间从 10～25 分钟下降到 1～2 分钟。