Another major advantage of adding instrumentation to your code is that you can collect statistics about its execution, and dynamically change which statistics are collected and when.
(NOTE - In order to collect statistics, the user in which BMC_DEBUG is installed needs SELECT privilege on the V$MYSTAT and V$STATNAME views. It will compile and run without these privileges, but statistics collection will not be available. It can also use V$SESSION_EVENT and V$SESS_TIME_MODEL to collect extra statistics on waits and events if SELECT privilege is granted on those views)
BMC_DEBUG can collect any statistics which are provided by the V$MYSTAT view, the names of which are visible in the V$STATNAME view. Here are a few example statistics which you might want to collect :-
SQL> SELECT statistic#,name FROM v$statname WHERE statistic# IN (14,37,84,194); STATISTIC# NAME ---------- ---------------------------------------------------------------- 14 session logical reads 37 session pga memory 84 db block gets 194 redo size
It is important to note that the STATISTIC# of these statistics can change between major Oracle releases, so you should query the V$STATNAME view on your system rather than copying the IDs from these examples.
Once you have decided which statistics you want to collect, you add them to the BMC_DEBUG_STATS_GROUP table. The sample rows which are provided are :-
SQL> SELECT stats_group,stat_id,stat_name,delta,min_call_stat_value,comments FROM bmc_debug_stats_group;
STATS_GROUP STAT_ID STAT_NAME DELTA MIN_CALL_STAT_VALUE COMMENTS
-------------- -------------- ---------------------- ------ ------------------- -----------------------------
1 194 redo size Y 10000
2 37 session pga memory N 1
3 5001 elapsed time (secs) Y 10
3 5002 call count Y 2
The values in each of the columns are as follows :-
Some statistics, such as 'session logical reads' and 'redo size' will only increase as a procedure executes. This means that it is valid to sample such a statistic at the beginning and end of a procedure, and subtract the two values to find the amount by which the statistic has increased, which is the delta value. For these statistics, the DELTA flag in the BMC_DEBUG_STATS_GROUP entry should be 'Y'.
However some statistics such as 'session pga memory' or 'opened cursors current' may increase or decrease as a procedure executes, so those should have their DELTA flag as 'N', in which case both the beginning and end values of the statistic will be recorded.
How STATS_GROUPs are matched to procedures
To record statistics when a procedure executes, you apply the STATS_GROUP to the procedure via the BMC_DEBUG_PARAMETER table, in the same way as we have previously seen for the DEBUG LEVEL parameter.
Groups can be applied to procedures in 3 ways :-
As CALL STATS GROUP where statistics are gathered and stored for each individual invocation of a procedure or function.
As PROFILE STATS GROUP so that when a procedure is profiled, statistics are collected for it and all of the procedures it calls, then aggregated
As SESSION STATS GROUP where statistics are accumulated for all invocations of a procedure in a session, then aggregated when the session ends
The following chapters describe in detail the collection of statistics in these scenarios.
Custom Statistics
Along with the statistics defined in V$MYSTAT and V$STATNAME, there are 3 custom values which can be used as a STAT_ID in the BMC_DEBUG_STATS_GROUP table :-
STAT_ID 5000 enables collection of user statistics for the executing procedure. See User and System statistics for more details.
STAT_ID 5001 collects the elapsed time (in seconds) of the executing procedure. When used with a MIN_CALL_STATS_VALUE, you can record when a procedure takes more than a specified number of seconds to execute.
STAT_ID 5002 collects a count of the number of times a procedure is executed.
If you are including STAT_ID 5002 in one of your groups, you should set the value of MIN_CALL_STAT_VALUE to 2. The reason for this is that if the group is used as a CALL STATS GROUP recording statistics for each individual call to a procedure, the value of STAT_ID 5002 will always be 1 (as the procedure has been executed once), so there is no point in recording it. Setting MIN_CALL_STAT_VALUE to 2 prevents this, but the statistic can still be recorded at PROFILE or SESSION level.
If you are including STAT_IDs 5001 or 5002, the DELTA flag should be set to Y.
Recording Additional Statistics
The above examples show how to collect statistics from V$MYSTAT by adding rows to the BMC_DEBUG_STATS_GROUP table using the STATISTIC# from V$MYSTAT as the STAT_ID.
BMC_DEBUG can also statistic from two other views, V$SESSION_EVENT and V$SESS_TIME_MODEL (as long as the BMC_DEBUG owner has been granted SELECT privilege on them).
Some example statistics you can collect from V$SESSION_EVENT are :-
SQL> select distinct event_id,event from v$session_event;
EVENT_ID EVENT
---------- ----------------------------------------------------------------
13102552 Disk file Mirror Read
86156091 os thread startup
133155944 db file async I/O submit
166678035 Disk file operations I/O
215477332 log file single write
218649935 wait for unread message on broadcast channel
317011907 ges remote message
341609523 GCR sleep
506183215 db file scattered read
549236675 log file sequential read
670556006 libcache interrupt action by LCK
834992820 db file parallel read
So when creating BMC_DEBUG_STATS_GROUP entries for these statistics, the EVENT_ID is used as the STAT_ID, and the V$SESSION_EVENT value which is recorded is TIME_WAITED, which is the time which the session spent waiting for the given event (in hundredths of a second).
Some example statistics you can collect from V$SESS_TIME_MODEL are :-
SQL> select distinct stat_id,stat_name from v$sess_time_model;
STAT_ID STAT_NAME
---------- ----------------------------------------------------------------
268357648 hard parse (bind mismatch) elapsed time
290749718 inbound PL/SQL rpc elapsed time
372226525 hard parse elapsed time
751169994 Java execution elapsed time
1159091985 repeated bind elapsed time
1311180441 PL/SQL compilation elapsed time
1431595225 parse time elapsed
1824284809 failed parse elapsed time
1990024365 connection management call elapsed time
2411117902 RMAN cpu time (backup/restore)
2451517896 background cpu time
2643905994 PL/SQL execution elapsed time
2748282437 DB CPU
2821698184 sql execute elapsed time
3138706091 hard parse (sharing criteria) elapsed time
3649082374 DB time
4125607023 failed parse (out of shared memory) elapsed time
4127043053 sequence load elapsed time
4157170894 background elapsed time
So the STAT_ID is used as the STAT_ID in BMC_DEBUG_STATS_GROUP, and the VALUE column from V$SESS_TIME_MODEL is recorded.
As there are no conflicts between the STAT_IDs from V$MYSTAT and V$SESS_TIME_MODEL, or the EVENT_IDs from V$SESSION_EVENT, statistics from all of these tables may be defined within the same STATS_GROUP.
The collection of statistics from these tables is controlled by two global parameters :-
RECORD TIME MODEL STATS
RECORD WAIT EVENT STATS
See The Global Parameter Table for more details on setting global parameter values.