User and System statistics

As well as the statistics which are recorded by including STATS GROUPs in parameter table entries, it is also possible for applications to record their own statistics via a couple of BMC_DEBUG procedures.

User Statistics

User code can write application specific statistics to the same tables in which BMC_DEBUG stores its call statistics and session statistics.

The procedures which are used to do this are :-

PROCEDURE record_user_call_stat (stat_name IN VARCHAR2,

stat_value IN NUMBER);

PROCEDURE record_user_session_stat (stat_name IN VARCHAR2,

delta_value IN NUMBER DEFAULT 1);

The RECORD_USER_CALL_STAT procedure can be called by user code to record its own statistics e.g.

bmc_debug.record_user_call_stat('High value sales total',15000);

Multiple invocations of the procedure for the same stat_name will accumulate the values. Just like other statistics recorded by a CALL STATS GROUP, the statistic values are written to the BMC_DEBUG_CALL_STATS table when the procedure calls ENDCALL.

In order for these statistics to be recorded, the CALL STATS GROUP which is in effect for the procedure must contain the custom statistic ID 5000. This gives the BMC_DEBUG administrator a way of controlling the recording of user statistics.

The RECORD_USER_SESSION_STAT procedure can be called to record user statistics which are stored at the session level e.g.

bmc_debug.record_user_session_stat('Number of orders processed');

The second parameter is optional and defaults to 1, so just passing the stat_name will increment that statistic total by 1.

In order for these statistics to be recorded, the SESSION STATS GROUP which is in effect for the procedure must contain the custom statistic ID 5000.

Call Timers

Another statistic you can record in your code is the elapsed time between different parts of the same procedure, using this BMC_DEBUG procedure :-

PROCEDURE call_timer (msglevel IN PLS_INTEGER,

timer_name IN VARCHAR2,

log_elapsed IN BOOLEAN DEFAULT TRUE);

When you call this for the first time, the current system time is recorded. When it is called for the second time, the number of seconds between the current and previous invocations is calculated, and is available via the GET_CALL_TIMER_ELAPSED_TIME function e.g.

-- Invoke the timer

bmc_debug.call_timer (5,'TIMER1');

-- Do some processing here

-- Invoke the timer again

bmc_debug.call_timer (5,'TIMER1');

-- Get the timer elapsed time

ct := bmc_debug.get_call_timer_elapsed ('TIMER1');

Multiple timers can be active at the same time, and timers in different procedures can have the same name without interfering with each other.

Recording Oracle session statistics during execution

If you want to gain a more granular view of code performance by sampling Oracle session statistics at different stages of a procedure, you can do it using the following BMC_DEBUG procedure :-

PROCEDURE read_oracle_session_stat (msglevel IN PLS_INTEGER,

stat_name IN VARCHAR2,

call_name IN VARCHAR2 DEFAULT NULL,

log_delta IN BOOLEAN DEFAULT TRUE,

override_stat_value IN NUMBER DEFAULT NULL);

The STAT_NAME is the name of an Oracle session statsistic as it appears in V$STATNAME. The CALL_NAME is a free text name of the step in your processing for which you are recording the statistic.

When you call the procedure with a given statistic name for the first time, the value of the statistic from V$SESSTAT is recorded. When you call it again for the same statistic name, the value from V$SESSTAT is sampled again, and the delta (difference between this and the previous values) is also recorded.

Here is an example of recording the number of blocks gotten by a table scan for a SELECT statement :-

SQL> DECLARE

ct NUMBER;

BEGIN

bmc_debug.begincall('PROCEDURE','OSS_TEST',3); -- Force DEBUG LEVEL 3

bmc_debug.read_oracle_session_stat(3,'table scan blocks gotten','BEFORE SELECT',TRUE);

SELECT count(*) INTO ct FROM all_source WHERE text like '%zzz%';

bmc_debug.read_oracle_session_stat(3,'table scan blocks gotten','AFTER SELECT',TRUE);

bmc_debug.endcall('PROCEDURE','OSS_TEST');

END;

/

Delta of table scan blocks gotten between BEFORE SELECT and AFTER SELECT = 10,004

PL/SQL procedure successfully completed.

Once you have called read_oracle_session_stat, you can interrogate the statistic values which were retrieved using the following functions :-

FUNCTION get_oracle_session_stat_value (stat_name IN VARCHAR2) RETURN NUMBER;

FUNCTION get_oracle_session_stat_delta (stat_name IN VARCHAR2) RETURN NUMBER;

The get_oracle_session_stat_value function returns the last value which was recorded from V$SESSTAT for the given statistic name.

The get_oracle_session_stat_delta function returns the difference between the last two recorded values from V$SESSTAT for the given statistic name.