Once you have defined the statistics you want to collect in the BMC_DEBUG_STATS_GROUP table, you can apply them to procedures by adding entries to the BMC_DEBUG_PARAMETER table.
To collect statistics for all invocations of a procedure across the life of a session, you use the SESSION STATS GROUP parameter to assign the appropriate STATS_GROUP. Here is an example :-
SQL> INSERT INTO bmc_debug_stats_group (stats_group,stat_id,stat_name,delta,min_call_stat_value)
VALUES (6,5002,'call count','Y',2);
1 row created.
SQL> commit;
Commit complete.
SQL> SELECT stats_group,stat_id,stat_name,delta,min_call_stat_value FROM bmc_debug_stats_group
WHERE stats_group = 6;
STATS_GROUP STAT_ID STAT_NAME D MIN_CALL_STAT_VALUE
----------- ---------- ------------------------------ - -------------------
6 5002 call count Y 2
So we have defined STATS_GROUP 6 containing the statistic which we want to record. We then apply that group as the SESSION STATS GROUP for the procedure we want to monitor :-
SQL> INSERT INTO bmc_debug_parameter (param_name, param_value, inherit, package_name, procedure_name)
VALUES ('SESSION STATS GROUP',6,'N','PROCEDURE','SESS_STATS');
1 row created.
SQL> commit;
Commit complete.
Now we could create and run the test procedure SESS_STATS, but first there is a problem which we need to address.
When BMC_DEBUG collects statistics at the session level, the running totals of the statistics are maintained in a global temporary table (GTT). The GTT entries will persist while the session exists, but as soon as the session exits the GTT entries will be purged. In order for the statistics to be inserted into the permanent BMC_DEBUG_SESSION_STATS table, a BEFORE LOGOFF trigger needs to be created for each database user which will run instrumented code.
The best way to do this is to grant the CREATE ANY TRIGGER privilege to the user which owns the BMC_DEBUG installation. It will then be able to create the BEFORE LOGOFF triggers without any need to grant privileges on BMC_DEBUG to the end users. An example trigger for the end user called APP_USER would be :-
SQL> CREATE OR REPLACE TRIGGER appuser_logoff_trigger
BEFORE LOGOFF ON app_user.schema
BEGIN
bmc_debug.write_session_stats(end_of_session => TRUE);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
Trigger created.
So this trigger will run when the APP_USER user logs off from a session, and calls the BMC_DEBUG.WRITE_SESSION_STATS procedure which will copy the session statistics from the GTT into the permanent table.
If you want to run the following examples against the BMC_DEBUG installation owner, you'll need to create a BEFORE LOGOFF trigger for its schema, similar to the one above. Once you've done this, you can create the test procedure :-
SQL> CREATE OR REPLACE PROCEDURE sess_stats IS
ct number;
BEGIN
bmc_debug.begincall('PROCEDURE','SESS_STATS');
select count(*) INTO ct FROM all_objects;
bmc_debug.endcall('PROCEDURE','SESS_STATS');
END sess_stats;
/
Procedure created.
Now we can run the procedure multiple times, and disconnect from the SQL*Plus session
SQL> exec sess_stats;
PL/SQL procedure successfully completed.
SQL> exec sess_stats;
PL/SQL procedure successfully completed.
SQL> exec sess_stats;
PL/SQL procedure successfully completed.
SQL> exec sess_stats;
PL/SQL procedure successfully completed.
SQL> disc
Disconnected from Oracle Database 11g Enterprise Edition With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options
Now if we look at what has been recorded in the BMC_DEBUG_SESSION_STATS table :-
SQL> SELECT call_date_from,sid,package_name,procedure_name,stat_name,stat_total FROM bmc_debug_session_stats;
CALL_DATE_FROM SID PACKAGE_NAME PROCEDURE_NAME STAT_NAME STAT_TOTAL
------------------------------- ---------- -------------------- ------------------------------ -------------------- ----------
14-NOV-2014 08.16.06.000000 PM 135 PROCEDURE SESS_STATS call count 4
So each of the invocations of the SESS_STATS procedure collected the statistic value from the SESSION STATS GROUP, and stored it in the GTT, then the BEFORE LOGOFF trigger called BMC_DEBUG.WRITE_SESSION_STATS to copy the GTT contents to the permanent table.