昨天一个朋友问我如何监测一个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视图的时候可以得到估算的运行结束时间。不过时间是否准确与用户设置的值有关。


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html