oracle - pl/sql dbms_scheduler挂起，如何进一步调试？

Question

我有一个由 DBMS_SCHEDULER 执行的 PL/SQL block 。它于上周某个时候开始，现在应该已经完成​​，但事实并非如此。我正在尝试找出问题所在，但需要一些有关如何继续调试此问题的建议。这是事实。

1) Oracle 11g

2) PL/SQL 代码输出到日志表，我可以得到问题所在的一般区域，这就是我发现挂起的方法。我无法得到卡在哪里的行号。我不知道怎么办。

3) V$SESSION_LONGOPS 表显示用户/模式仍在运行，但日期都是过去几天了。从那时起它就没有改变过。

4) V$SESSION_LONGOPS 中的 OPNAME 分为“收集表的索引统计信息”和“表扫描”，用于一堆不同的表(第一个表 8 个，第二个表 8 个)。它们看起来是各种各样的 table 。对于每一行，SOFAR 和 TOTAL 值彼此相等。 “Gather...”行全为零，而“Table Scan”行非零但相等。

5) 如果我对 V$SESSION 进行查询，我的工作就是 ACTIVE。

6) 我正在处理的表上有一个锁，但是我没有看到在我的架构中正在播放其他 session 。

7) CPU 利用率非常低。

是否可以更深入地了解正在执行的 PL/SQL block ？我可以在 V$SQL 中找到完整的 SQL，它显示了传入的原始 block 。我想更深入地了解所调用的已编译自定义过程(行号等)。

关于锁，我可以在 V$LOCKED_OBJECTS 中看到分配给我的表的锁，是否有一个表可以向我显示被锁阻塞的进程？我想找到僵局(如果有的话)。

非常感谢任何建议，

米杰

Answer 1

如果不构建自己的工具，或者启用调试或分析，就无法找到当前正在执行的行号。这似乎有点限制，但想象一下跟踪每个行号对性能的影响。

大多数长时间运行的作业正在等待 SQL，而不是 PL/SQL。 (如果情况并非如此，那么您就会遇到其他问题。)有很多方法可以跟踪 SQL 语句。 V$SESSION_LONGOPS 可能很有用，但通常有更好的方法。最好的方法可能是使用 Oracle Enterprise Manager(如果有的话)。转到目标，然后转到性能 -> 主要事件，您将看到过去一小时的系统事件和 SQL 语句的图表。

例如(来自 OEM 概念指南):

如果您没有安装 OEM，或者没有访问权限，还有其他几种方法可以跟踪 SQL。首先，您要确定哪些是相关的 SQL 语句。这将告诉您现在正在运行什么以及它已经运行了多长时间:

select elapsed_time/1000000 seconds, gv$sql.*
from gv$sql
where users_executing > 0
order by 1 desc;

如果幸运的话，只有一条 SQL 语句需要很长时间。如果存在一系列小型 SQL 语句，您可能需要排除 where users_executing > 0 谓词并查找与您的应用程序相关的最昂贵的语句。

一般来说，SQL 语句会因为三个主要原因而变慢或停止:

1. 等待资源。检查select * from dba_resumable;也许您的查询占用了太多临时表空间，正在等待 DBA 添加更多临时表空间。或者也许您启用了并行排队并且它正在等待更多并行 session 。 (我不确定这是否会出现在 DBA_RESUMABLE 中。)
2. 等待锁定。检查select * from gv$session where Final_blocking_session is not null;其中的任何行都可能有问题。您通常不想要在对象级别跟踪锁。找出谁在阻止您而不是什么在阻止您要容易得多。当行或表被锁定时，您的 session 将等待事务，而不是行或对象。请注意，只有当两个 session 以不同的顺序需要相同的资源时才会发生死锁，并且如果发生这种情况，将会出现错误消息。
3. 等待SQL执行。这显然是最困难的问题。对于现在该做什么，没有简单的 list 。通常我会首先生成一个事件报告，显示计划以及每个步骤花费的时间:select dbms_sqltune.report_sql_monitor(sql_id => 'your sql id', type=>'active') from Dual;。该报告也适用于 PL/SQL block ，但它只会显示每个 SQL 花费的时间。然后由您来决定如何让它运行得更好。