Oracle 19c实时SQL监控跟踪存储过程执行进度

如何判断存储过程是否被SQL Monitor自动捕捉

首先明确核心要点：Oracle 19c 的 v$sql_monitor 并不会追踪所有存储过程，它仅对满足特定条件的SQL自动启用监控。判断依据非常清晰：单条SQL的CPU+I/O时间若达到或超过5秒，或者使用了并行执行，又或者显式添加了 /*+ MONITOR */ 提示——只要满足任意一条，监控便会自动启动。

这里有一个容易忽略的陷阱：如果存储过程中全是短小快速的DML语句，例如每条UPDATE仅运行几毫秒，即使整个存储过程执行了十几分钟，也可能完全不会出现在 V$SQL_MONITOR 中。原因很简单——监控触发点在于「单条语句」层面，而非「整个过程」层面。

• 想知道当前哪些会话正在被监控？直接查询：SELECT sql_id, status, elapsed_time, cpu_time, sql_text FROM v$sql_monitor WHERE status = 'EXECUTING';
• 确认某条SQL是否带有MONITOR提示：SELECT sql_fulltext FROM v$sql WHERE sql_id = 'xxx';，查看开头是否存在 /*+ MONITOR */
• 如果未命中自动条件，但又必须监控，可以在调用前手动添加提示，例如：EXEC /*+ MONITOR */ your_procedure_name;

如何通过V$SQL_MONITOR查看正在执行的语句位置

坦白说，V$SQL_MONITOR 并不直接告诉你“执行到了第几行”，但它能清晰反映当前正在运行哪条SQL、卡在哪一步、消耗了多少资源。真正意义上的“进度”，是通过 sql_id 和 sql_exec_start 对应的那条语句来体现的，而不是看存储过程名称。

很多人误以为查询 V$SQL_MONITOR 时使用 sql_text LIKE '%过程名%' 就能捕捉到，结果往往为空。原因很简单：这里存储的是最终解析后的SQL文本，而非PL/SQL源码。存储过程体中的 INSERT INTO t SELECT ... 才能被监控，而 FOR i IN 1..1000 LOOP 这类循环控制逻辑，根本不会进入视图。

• 定位当前执行语句：SELECT sql_id, sql_text, status, elapsed_time/1000000 elapsed_sec, px_servers_requested FROM v$sql_monitor WHERE session_id = SYS_CONTEXT('USERENV', 'SID') AND status = 'EXECUTING';
• 配合 V$SQL_PLAN_MONITOR 查看具体操作步进：SELECT operation, options, start_time, end_time, output_rows FROM v$sql_plan_monitor WHERE sql_id = 'xxx' AND plan_line_id > 0 ORDER BY first_refresh_time;
• 特别注意 output_rows 字段：对于INSERT/SELECT类语句，它代表已处理行数；对于UPDATE/DELETE则代表已修改行数——这应该是目前最接近“进度”概念的量化指标了

为什么TKPROF或SQL_TRACE无法实时看到进度

ALTER SESSION SET SQL_TRACE = TRUE; 生成的 .trc 文件本质上是用于事后分析的，内容需要等执行完全结束后才会落盘。当你在运行存储过程时打开该文件，只能看到零星的初始化记录，真正的SQL执行块、绑定变量、统计信息都要等到过程结束后才能写入。

更麻烦的是，.trc 文件的默认路径由 user_dump_dest 决定，但19c默认启用了ADRCI和自动诊断库，跟踪文件实际存放在 $ORACLE_BASE/diag/rdbms///trace/ 下，文件名形如 _ora_12345.trc，不再是以前那种直观的 ora*.trc 了。

• 想确认当前会话的跟踪文件路径？执行：SELECT value FROM v$diag_info WHERE name = 'Default Trace File';
• 不要指望在运行过程中 tail -f 该文件——即使文件在写入，内容也是按块缓冲的，并且完全没有结构化进度标记
• 真正想实时抓取中间状态，唯一可靠的方法是让存储过程自己主动输出：使用 DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS 在循环中更新 sofar 字段，然后查询 V$SESSION_LONGOPS

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS 是唯一可靠的进度反馈方式

Oracle官方并未提供类似“查看PL/SQL行号执行位置”的接口。V$SESSION_LONGOPS 是目前唯一支持主动上报进度的机制，但它有一个硬性前提：你必须提前在存储过程中埋下监控点，而不是开启一个开关就能生效。

典型用法是：在大循环开始时调用 SET_SESSION_LONGOPS 进行初始化，每次迭代后调用它更新 sofar，最后再调用一次标记完成。如果未做这些操作，那么查询 V$SESSION_LONGOPS 返回的结果将为空。

• 初始化示例：DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(rindex => longops_rindex, slno => 0, opname => 'MY_PROC', target_desc => 'Processing records', sofar => 0, totalwork => 10000);
• 循环中更新：DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(rindex => longops_rindex, sofar => i);
• 查询进度：SELECT opname, target_desc, sofar, totalwork, round(sofar/totalwork*100,1) pct_done FROM v$session_longops WHERE sofar < totalwork;
• 注意：rindex 是返回值，必须在后续调用中复用；totalwork 必须是确定值，不能是动态的 COUNT(*) 结果——否则无法预估进度

如果没有预先埋点，只能退而求其次：使用 V$SQL_MONITOR 查看当前SQL的耗时和输出行数，或者依靠 V$SESSION 的 last_call_et 粗略判断“该会话已经运行了多久”。但要精确知道执行到了哪一行逻辑——抱歉，这是无法实现的。