Quick Start
If you are familiar with Oracle and want to leap in and see what BMC_DEBUG can do, you will need an Oracle database (10gR2 or later, Standard or Enterprise edition, single instance or RAC), and a user in which to create the tables and procedures. The user will need the following privileges :-
CREATE SESSION
CREATE TABLE
CREATE PROCEDURE
These are the minimum privileges required for BMC_DEBUG to compile and run, although if possible you should grant SELECT privilege on :-
V$MYSTAT
V$STATNAME
V$SESSION_EVENT
V$SESS_TIME_MODEL
to your user, as this will allow the collection of statistics about the execution of your code.
Once your user is created, download the latest bmcdebug-nnn.zip file from the Files page, and unzip it on the machine from which you can run sqlplus.
Connect to your user in sqlplus and run the following scripts from the zip file :-
cre.sql
bmcx.pls
bmcerror.pls
bmcdebug.pls
bmcerror.plb
bmcdebug.plb
The cre.sql script will create and populate the tables, the .pls files are the package specifications,and the .plb files are the package bodies. The source code is not wrapped so you are free to examine its functionality.
All of these scripts should run without error . If they do report errors, you'll need to refer to the Detailed Installation Guide for further information (unless you have not granted SELECT privilege on V$STATNAME to your user, in which case two of the INSERTs in cre.sql will fail with ORA-942, which is expected).
Once the tables and packages are installed, we can demonstrate some basic functionality by writing our first instrumented procedure.
Launch sqlplus and login as your user, then create the following procedure which writes a message :-
SQL> CREATE OR REPLACE PROCEDURE quickstart IS
BEGIN
bmc_debug.begincall('PROCEDURE','QUICKSTART');
bmc_debug.msg(4,'This is a message');
bmc_debug.endcall('PROCEDURE','QUICKSTART');
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle();
RAISE;
END quickstart;
/
Procedure created.
As we're going to be using DBMS_OUTPUT, make sure it is enabled :-
SQL> exec dbms_output.enable(100000);
PL/SQL procedure successfully completed.
SQL> set serveroutput on size unlimited format wrapped;
Now we can run the test procedure :-
SQL> exec quickstart;
PL/SQL procedure successfully completed.
Wasn't that exciting? The reason we didn't get any output is that the BMC_DEBUG.MSG statement is logging its message at level 4, and the default DEBUG LEVEL is 0. We set the DEBUG LEVEL by inserting a row into the BMC_DEBUG_PARAMETER table :-
SQL> INSERT INTO bmc_debug_parameter (param_name, param_value, inherit, package_name, procedure_name)
VALUES ('DEBUG LEVEL',4,'N','PROCEDURE','QUICKSTART');
1 row inserted
SQL> commit;
Commit complete.
If we run the test procedure again, we get some output :-
SQL> exec quickstart;
This is a message
PL/SQL procedure successfully completed.
So the value of DEBUG LEVEL is read from the parameter table by BEGINCALL, and if the DEBUG LEVEL is >= the level specified in the call to MSG, the message is output.
As we are executing the procedure interactively in SQL*Plus, any MSG output is written to the screen via DBMS_OUTPUT, but it is also written to the BMC_DEBUG_LOG table (whether code is being run interactively or not). If we check the table we see :-
SQL> SELECT log_date,sid,package_name,procedure_name,msg_level,msg FROM bmc_debug_log;
LOG_DATE SID PACKAGE_NAME PROCEDURE_NAME MSG_LEVEL MSG
---------------------------------------- --- -------------- -------------- --------------- ------------------------------------ 02-NOV-2014 10:41:56.827809 +00:00 205 PROCEDURE QUICKSTART 4 This is a message
This example shows how messages can be embedded in procedures and enabled when needed by modifying parameter table entries.
BMC_DEBUG can also dynamically collect statistics on instrumented code. If we look at some sample rows from the BMC_DEBUG_STATS_GROUP table :-
SQL> SELECT stats_group,stat_id,stat_name,delta,min_call_stat_value FROM bmc_debug_stats_group WHERE stats_group = 3;
STATS_GROUP STAT_ID STAT_NAME DELTA MIN_CALL_STAT_VALUE
-------------- ---------- ---------------------- ------ -------------------
3 5001 elapsed time (secs) Y 10
3 5002 call count Y 2
This group of statistics records the elapsed time of a procedure, but only if it takes at least 10 seconds, and the number of times the procedure was called, if it is called 2 or more times. We can apply this group to a procedure via the parameter table :-
SQL> INSERT INTO bmc_debug_parameter (param_name, param_value, inherit, package_name, procedure_name)
VALUES ('CALL STATS GROUP',3,'N','PROCEDURE','QUICKSTART2');
1 row created.
SQL> commit;
Commit complete.
Now if we execute a procedure which takes more than 10 seconds to run :-
SQL> CREATE OR REPLACE PROCEDURE quickstart2 IS
ct NUMBER;
BEGIN
bmc_debug.begincall('PROCEDURE','QUICKSTART2');
ct := mod(to_number(to_char(sysdate,'SS')) + 15,60);
WHILE ct <> to_number(to_char(sysdate,'SS'))
LOOP
null;
END LOOP;
bmc_debug.endcall('PROCEDURE','QUICKSTART2');
EXCEPTION
WHEN OTHERS THEN
bmc_error.error_handle();
RAISE;
END quickstart2;
/
Procedure created.
SQL> exec quickstart2;
PL/SQL procedure successfully completed.
If we now check the BMC_DEBUG_CALL_STATS table, we find :-
SQL> SELECT call_date,sid,serial#,package_name,procedure_name,flag,stat_name,stat_value FROM bmc_debug_call_stats;
CALL_DATE SID SERIAL# PACKAGE_NAME PROCEDURE_NAME FLA STAT_NAME STAT_VALUE
--------------------------------------- ----- ------- -------------- -------------- --- ---------------------- -----------
02-NOV-2014 12:59:17.927913 +00:00 205 2739 PROCEDURE QUICKSTART2 D elapsed time (secs) 15.22
So we applied CALL STATS GROUP 3 to this procedure, which records the elapsed time of the procedure if it is greater than 10 seconds. It was, so a row was inserted into the BMC_DEBUG_CALL_STATS table with the details.
This shows that once you have instrumented your code, you can record any statistic which appears in V$MYSTAT (and a couple of custom statistics), and dynamically change which statistics are recorded and when.
The key point to remember about instrumentation is that it isn't added to your code to identify a particular problem, and then removed before the code goes live. Instrumentation remains in the code when it runs in your live production system, with the parameter table used to control when it becomes active and what it does.
When you insert or update values in the parameter table, as soon as you commit the changes their values immediately affect any instrumented code which is executing, so you can dynamically change what any instrumented procedure records in the log table or stats tables without any code changes, redeployment or application restarts.
If you need a more detailed guide to installation, see Detailed Installation Guide, otherwise read on for full details on how to instrument your PL/SQL code, and a full description of the BMC_DEBUG feature set.