Monitoring Execution
Another advantage of adding instrumentation to your code is that it allows you monitor its execution. The details of which procedure is running in a session and what it is doing are made available in the V$SESSION view, and can be monitored from another session.
How BMC_DEBUG uses DBMS_APPLICATION_INFO
When BMC_DEBUG.BEGINCALL is invoked at the start of an instrumented procedure, it calls DBMS_APPLICATION_INFO to set the session MODULE and ACTION values to the names of the package and procedure. This allows you to monitor which procedures are executing and when from another session with SELECT privilege on V$SESSION.
When BMC_DEBUG.MSG is called, the first 64 characters of the message text is written to the session's CLIENT_INFO value by DBMS_APPLICATION INFO, irrespective of the value of the DEBUG LEVEL parameter for the executing procedure. This allows the progress of a procedure to be monitored from another session e.g.
SQL> SELECT module,action,client_info FROM v$session where sid = 284;
MODULE ACTION CLIENT_INFO
------------------------------ ------------------------------ ------------------------------------------------------
PROCEDURE TEST4 This is the second message
As some applications use these columns to store state information, this functionality can be disabled via a Global Parameter if required. If this is the case, applications can still be monitored using a context (See Monitoring using a Global Context for more details).
Use of DBMS_OUTPUT when running instrumented code interactively
As we've seen with previous examples, messages which are written to the BMC_DEBUG_LOG table by BMC_DEBUG.MSG are also output to the screen using DBMS_OUTPUT when instrumented code is run interactively in SQL*Plus. This is especially useful during development as it provides instant feedback on what a procedure has done without needing to query the BMC_DEBUG_LOG table.
Its a good idea to enable DBMS_OUTPUT in your SQL*Plus login.sql file, so that it is automatically enabled whenever you work. The statements to enable it are :-
exec dbms_output.enable(100000);
set serveroutput on size unlimited format wrapped;
While there are Global Parameters to disable many BMC_DEBUG functions, such as the use of DBMS_APPLICATION_INFO, there isn't one to disable the use of DBMS_OUTPUT, as this can be controlled with SET SERVEROUTPUT OFF and DBMS_OUTPUT.DISABLE.
Linking SQL statements to procedures with MODULE and ACTION in V$SQL
Another advantage of writing the package and procedure names to the MODULE and ACTION columns of V$SESSION via DBMS_APPLICATION_INFO is that it helps to identify which SQL statements originate from which procedures. Consider the following example :-
SQL> CREATE OR REPLACE PROCEDURE infotest IS
ct NUMBER;
BEGIN
bmc_debug.begincall('PROCEDURE','INFOTEST');
SELECT count(*) INTO ct FROM all_objects;
bmc_debug.endcall('PROCEDURE','INFOTEST');
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle();
RAISE;
END infotest;
/
Procedure created.
SQL> exec infotest;
PL/SQL procedure successfully completed.
If we try to find the SELECT statement inside that procedure using the V$SQL view, we see :-
SQL> select sql_id,sql_text,disk_reads from v$sql where module = 'PROCEDURE' and action = 'INFOTEST';
SQL_ID SQL_TEXT DISK_READS
------------- ---------------------------------------- -----------------
dr2v8xdg4w2ag SELECT COUNT(*) FROM ALL_OBJECTS 2118
So when the package and procedure names are written to MODULE and ACTION, it can help to track which SQL statements are executed by which procedures.
(Note - If identical SQL statements are issued from multiple instrumented procedures, the first one to execute a particular statement will have its MODULE and ACTION reported by V$SQL. Tom Kyte managed to clarify this in a blog post http://tkyte.blogspot.co.uk/2006/09/action-module-program-id-and-vsql.html)