How Oracle monitors the operation of a plsql process (ii) Professional Solutions

Yesterday a friend asked me how to monitor the operation of a pl/sql, here is a brief introduction to several methods.

Introduce a professional Dbms_application_info package.

The previous article introduced a solution through inserts and autonomous transactions, which is simple, but requires auxiliary tables and procedures, and this approach is somewhat unprofessional.

So here's a relatively professional solution, Oracle delivers the Dbms_application_info package:

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/

The PL/SQL process has completed successfully.

By simply invoking the Set_action procedure, other sessions can be performed by monitoring the V$session view, and from the current pl/sql to which step:

sql> SELECT SID, ACTION from v$session WHERE SID = 13;

SID ACTION

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

Step A

sql> SELECT SID, ACTION from v$session WHERE SID = 13;

SID ACTION

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

Step A

sql> SELECT SID, ACTION from v$session WHERE SID = 13;

SID ACTION

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

Step B

sql> SELECT SID, ACTION from v$session WHERE SID = 13;

SID ACTION

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

Step B

sql> SELECT SID, ACTION from v$session WHERE SID = 13;

SID ACTION

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

Step C

sql> SELECT SID, ACTION from v$session WHERE SID = 13;

SID ACTION

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

Step C

In another session you can see the changes in the pl/sql from step A to step c in the conversation just now. Note that if the session no longer modifies the action, the session's action remains at the end of the conversation.

More Wonderful content: http://www.bianceng.cn/database/Oracle/

The simplicity of this method does not have to be complex to insert, and the Dbms_application_info package is accessible to all users. It should be noted, however, that not all users have access to the V$session view, which requires additional authorization.

The above method does not seem to be very professional, using this package also has a more professional solution:

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,