如何监测一个PLSQL过程的运行情况（二）

 

昨天一个朋友问我如何监测一个PL/SQL的运行状况，这里简单介绍几种方法。

介绍专业一点的DBMS_APPLICATION_INFO包。

 

上一篇介绍了通过INSERT和自治事务的解决方案，方法很简单，但是需要辅助的表和过程，而且这种方法显得有点不够专业。

那么这里介绍一篇相对PROFESSIONAL的解决方案，Oracle提供的DBMS_APPLICATION_INFO包：

SQL> SELECT SID, ACTION

  2  FROM V$SESSION

  3  WHERE SID IN

  4  (SELECT SID   

  5  FROM V$MYSTAT

  6  WHERE ROWNUM = 1);

       SID ACTION

---------- --------------------------------

        13

SQL> BEGIN

  2  DBMS_APPLICATION_INFO.SET_ACTION('STEP A');

  3  DBMS_LOCK.SLEEP(10);

  4  DBMS_APPLICATION_INFO.SET_ACTION('STEP B');

  5  DBMS_LOCK.SLEEP(10);

  6  DBMS_APPLICATION_INFO.SET_ACTION('STEP C');

  7  END;

  8  /

PL/SQL过程已成功完成。

通过简单的调用SET_ACTION过程，其他会话就可以通过监测V$SESSION视图，而从得到当前的PL/SQL执行到哪个步骤：

SQL> SELECT SID, ACTION FROM V$SESSION WHERE SID = 13;

       SID ACTION

---------- --------------------------------

        13 STEP A

SQL> SELECT SID, ACTION FROM V$SESSION WHERE SID = 13;

       SID ACTION

---------- --------------------------------

        13 STEP A

SQL> SELECT SID, ACTION FROM V$SESSION WHERE SID = 13;

       SID ACTION

---------- --------------------------------

        13 STEP B

SQL> SELECT SID, ACTION FROM V$SESSION WHERE SID = 13;

       SID ACTION

---------- --------------------------------

        13 STEP B

SQL> SELECT SID, ACTION FROM V$SESSION WHERE SID = 13;

       SID ACTION

---------- --------------------------------

        13 STEP C

SQL> SELECT SID, ACTION FROM V$SESSION WHERE SID = 13;

       SID ACTION

---------- --------------------------------

        13 STEP C

在另外的会话可以看到，刚才会话的PL/SQL从STEP A到STEP C的变化。需要注意，如果会话不再修改ACTION，那么这个会话的ACTION会一直保持到会话的结束。

这种方法的简单程度一点都不必INSERT的方法复杂，而且DBMS_APPLICATION_INFO包是所有用户都可以访问的。但是需要注意，并不是所有用户都可以访问V$SESSION视图的，这个视图需要额外的授权。

上面的方法似乎还不是很专业，利用这个包还有更专业的解决方案：

SQL> SELECT SID, SERIAL#

  2  FROM V$SESSION

  3  WHERE SID IN

  4  (SELECT SID 

  5  FROM V$MYSTAT

  6  WHERE ROWNUM = 1);

       SID    SERIAL#

---------- ----------

        13       6610

SQL> DECLARE

  2     V_ID BINARY_INTEGER := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;

  3     V_SLNO BINARY_INTEGER;

  4     V_TOTALWORK NUMBER := 5;

  5  BEGIN

  6     FOR I IN 1..V_TOTALWORK LOOP

  7             DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(

  8                     V_ID,

  9                     V_SLNO,

 10                     'ANONYMOUS BLOCK',

 11                     0,

 12                     0,

 13                     I - 1,

 14                     V_TOTALWORK,

 15                     'PL/SQL',

 16                     'STEP');

 17             DBMS_LOCK.SLEEP(10);

 18     END LOOP;

 19     DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(

 20             V_ID,

 21             V_SLNO,

 22             'ANONYMOUS BLOCK',

 23             0,

 24             0,

 25             V_TOTALWORK,

 26             V_TOTALWORK,

 27             'PL/SQL',

 28             'STEP');

 29  END;

 30  /

PL/SQL过程已成功完成。

通过SET_SESSSION_LONGOPS过程，可以使得PL/SQL代码更新V$SESSION_LONGOPS视图的结果，其他用户不仅可以看到当前在执行什么操作，而且还可以根据用户设置的SOFAR和TOTALWORK的值，了解当前运行到PL/SQL中的什么位置。V$SESSION_LONGOPS会根据运行的时间，以及SOFAR和TOTALWORK的值，自动估算运行还需要多久才能完成：

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             0          5 STEP                               0

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             1          5 STEP              32               8

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             1          5 STEP              32               8

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             2          5 STEP              30              20

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             3          5 STEP              19              29

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             4          5 STEP              10              38

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             4          5 STEP              10              38

SQL> SELECT OPNAME, TARGET, TARGET_DESC, SOFAR, TOTALWORK, UNITS, TIME_REMAINING, ELAPSED_SECONDS

  2  FROM V$SESSION_LONGOPS

  3  WHERE SID = 13

  4  AND SERIAL# = 6610;

OPNAME          TARGET TARGET_DESC    SOFAR  TOTALWORK UNITS TIME_REMAINING ELAPSED_SECONDS

--------------- ------ ------------ ------- ---------- ----- -------------- ---------------

ANONYMOUS BLOCK        PL/SQL             5          5 STEP               0              50

同样，V$SESSION_LONGOPS不是所有会话都可以直接访问的，需要授权的用户才可以访问，这种方法的最大好处是，用户可以自己量化运行的阶段，使得其他用户监测V$SESSION_LONGOPS视图的时候可以得到估算的运行结束时间。不过时间是否准确与用户设置的值有关。