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

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

Oracle 19c的v$sql_monitor并不会跟踪所有存储过程。它仅对满足特定条件的SQL自动启用监控。判断依据非常明确：单条SQL的CPU加I/O时间等于或大于5秒，或者该SQL使用了并行执行，或者显式添加了/*+ 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对应的语句来体现的，而非存储过程名称。

许多人尝试使用sql_text LIKE '%过程名%'查询V$SQL_MONITOR，结果往往为空。原因在于该视图中存储的是最终解析后的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决定，但Oracle 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粗略判断“该会话已运行了多长时间”。但要精确知道执行到了哪一行逻辑，目前无法实现。