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

 

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

介绍最简单的INSERT的方法。

 

 

他们的问题是执行下面的PL/SQL代码：

SQL> SET SERVEROUT ON

SQL> BEGIN

  2  DBMS_OUTPUT.PUT_LINE('A');

  3  DBMS_LOCK.SLEEP(10);

  4  DBMS_OUTPUT.PUT_LINE('B');

  5  DBMS_LOCK.SLEEP(10);

  6  DBMS_OUTPUT.PUT_LINE('C');

  7  END;

  8  /

A

B

C

PL/SQL过程已成功完成。

他们希望在PL/SQL代码运行一开始就得到A，运行10秒后，得到B，最后运行完成的时候，得到C。

但是，DBMS_OUTPUT包的调用是发生在PL/SQL代码结束之后的，也就是说，不可能在PL/SQL运行的过程中分批的得多DBMS_OUTPUT的输出结果，最终的输出结果是A、B、C同时输出。

其实解决这个问题的方法很多，最简单的方法是通过INSERT就能解决这个问题。

SQL> CREATE TABLE T_RECORD

  2  (STR VARCHAR2(10),

  3  TIME DATE);

表已创建。

SQL> BEGIN

  2  INSERT INTO T_RECORD VALUES ('A', SYSDATE);

  3  COMMIT;

  4  DBMS_LOCK.SLEEP(10);

  5  INSERT INTO T_RECORD VALUES ('B', SYSDATE);

  6  COMMIT;

  7  DBMS_LOCK.SLEEP(10);

  8  INSERT INTO T_RECORD VALUES ('C', SYSDATE);

  9  COMMIT;

 10  END;

 11  /

PL/SQL过程已成功完成。

这时通过另一个会话就可以时刻检查会话执行到哪个步骤了：

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:37:18

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:37:18

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:37:18

B          2010-04-26 16:37:28

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:37:18

B          2010-04-26 16:37:28

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:37:18

B          2010-04-26 16:37:28

C          2010-04-26 16:37:39

当然这种方法的前提是PL/SQL中可以提交，如果整个PL/SQL代码是一个完整的事务，或者COMMIT是根据业务需要，那么就不能采用这个方法。需要简单的变通一下，利用自治事务的方式：

SQL> TRUNCATE TABLE T_RECORD;

表已截掉。

SQL> CREATE OR REPLACE PROCEDURE P_RECORD (P_STR IN VARCHAR2) AS

  2  PRAGMA AUTONOMOUS_TRANSACTION;

  3  BEGIN

  4  INSERT INTO T_RECORD VALUES (P_STR, SYSDATE);

  5  COMMIT;

  6  END;

  7  /

过程已创建。

SQL> BEGIN

  2  P_RECORD('A');

  3  DBMS_LOCK.SLEEP(10);

  4  P_RECORD('B');

  5  DBMS_LOCK.SLEEP(10);

  6  P_RECORD('C');

  7  END;

  8  /

PL/SQL过程已成功完成。

在另一个会话中可以检查会话运行的状态：

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:49:59

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:49:59

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:49:59

B          2010-04-26 16:50:09

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:49:59

B          2010-04-26 16:50:09

SQL> SELECT * FROM T_RECORD;

STR        TIME

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

A          2010-04-26 16:49:59

B          2010-04-26 16:50:09

C          2010-04-26 16:50:20

这种方法的最大优点就是简单，编码量也很小，需要的辅助对象仅仅是一张表和一个存储过程而已。