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 an invocation of a procedure and for all instrumented procedures it calls, you use the PROFILE 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 (5,14,'logical reads','Y',null);
1 row created.
SQL> INSERT INTO bmc_debug_stats_group (stats_group,stat_id,stat_name,delta,min_call_stat_value)
VALUES (5,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 = 5;
STATS_GROUP STAT_ID STAT_NAME D MIN_CALL_STAT_VALUE
----------- ---------- ------------------------------ - -------------------
5 14 logical reads Y
5 5002 call count Y 2
So we have defined STATS_GROUP 5 containing two statistics which we want to record. We then apply that group as the PROFILE 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 ('PROFILE STATS GROUP',5,'N','PROFILE_STATS_TEST','PROCA');
1 row created.
SQL> commit;
Commit complete.
Now we can create and run the test package PROFILE_STATS_TEST, which contains PROCA, the procedure we want to profile, and
SQL> CREATE OR REPLACE PACKAGE profile_stats_test IS
PROCEDURE proca;
PROCEDURE procb;
PROCEDURE procc;
END profile_stats_test;
/
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY profile_stats_test IS
PROCEDURE proca IS
BEGIN
bmc_debug.begincall($$PLSQL_UNIT,'PROCA');
procb;
procc;
procb;
procc;
procb;
bmc_debug.endcall($$PLSQL_UNIT,'PROCA');
END proca;
PROCEDURE procb IS
BEGIN
bmc_debug.begincall($$PLSQL_UNIT,'PROCB');
bmc_debug.endcall($$PLSQL_UNIT,'PROCB');
END procb;
PROCEDURE procc IS
BEGIN
bmc_debug.begincall($$PLSQL_UNIT,'PROCC');
bmc_debug.endcall($$PLSQL_UNIT,'PROCC');
END procc;
END profile_stats_test;
/
Package body created.
SQL> exec profile_stats_test.proca;
If we look at the BMC_DEBUG_PROFILE_STATS table :-
SQL> SELECT sid,profiled_package,profiled_procedure,called_package,called_procedure,stat_name,stat_total
FROM bmc_debug_profile_stats
WHERE profiled_procedure = 'PROCA'
ORDER BY called_procedure,stat_name;
SID PROFILED_PACKAGE PROFILED_PROCEDURE CALLED_PACKAGE CALLED_PROCEDURE STAT_NAME STAT_TOTAL
------ ------------------ -------------------- ------------------ -------------------- -------------------- ----------
143 PROFILE_STATS_TEST PROCA PROFILE_STATS_TEST PROCB call count 3
143 PROFILE_STATS_TEST PROCA PROFILE_STATS_TEST PROCB logical reads 2358
143 PROFILE_STATS_TEST PROCA PROFILE_STATS_TEST PROCC call count 2
143 PROFILE_STATS_TEST PROCA PROFILE_STATS_TEST PROCC logical reads 1572
143 PROFILE_STATS_TEST PROCA call count 1
143 PROFILE_STATS_TEST PROCA logical reads 8739
So the statistics in the PROFILE STATS GROUP have been recorded for PROCA and for every instrumented procedure it called, and aggregated into the BMC_DEBUG_PROFILE_STATS table.