V$SERV_MOD_ACT_STATS Has All ZERO "VALUE" When DBMS_APPLICATION_INFO.SET_MODULE Is Set In PL/SQL

(Doc ID 989645.1)

Last updated on JULY 05, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.1.0.7 - Release: 10.2 to 11.1
Information in this document applies to any platform.

Symptoms

When setting DBMS_APPLICATION_INFO.SET_MODULE inside of PL/SQL (either stored procedure or anonymous block), and using DBMS_MONITOR the "VALUES" column in V$SERV_MOD_ACT_STATS only contains Zeros (0).

When this is set outside of PL/SQL, the "VALUES" column contains non-zero values (actual results).

This happens in 10.2.0.4 and 11.1.0.7.

The following example illustrates the issue from within SQL*Plus

--Use the following commands to format the columns
column aggregation_type format a22
column service_name format a12
column module format a15
column action format a15
column stat_name format a32
set linesize 150

--This invokes the monitoring system.

SQL> execute DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE('SYS$USERS', 'TEST_MODULE', 'TEST_ACTION_');

--This shows there are no existing values.

SQL> SELECT * FROM V$SERV_MOD_ACT_STATS WHERE VALUE > 0 ORDER BY VALUE DESC;

no rows selected


--This anonymous block calls the SET_MODULE

SQL> declare
V_NUMBER NUMBER := NULL;
V_NUMBER1 NUMBER := NULL;
V_NUMBER2 NUMBER := NULL;
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE('TEST_MODULE','TEST_ACTION_');
SELECT COUNT(*), sum(pct_free), sum(pct_used) INTO V_NUMBER, v_number1, v_number2 FROM ALL_TABLES A ;
DBMS_APPLICATION_INFO.SET_MODULE(null, null);
END;
/

--This shows that still no data is captured even after calling the SET_MODULE

SQL> SELECT * FROM V$SERV_MOD_ACT_STATS WHERE VALUE > 0 ORDER BY VALUE DESC;
no rows selected


--This makes the same calls but outside the anonymous block

SQL> execute DBMS_APPLICATION_INFO.SET_MODULE('TEST_MODULE','TEST_ACTION_');

SQL> SELECT COUNT(*), sum(pct_free), sum(pct_used) FROM ALL_TABLES A ;

COUNT(*)   SUM(PCT_FREE) SUM(PCT_USED)
---------- ------------- -------------
2235       21313         22570

SQL> execute DBMS_APPLICATION_INFO.SET_MODULE(null, null);


--The data is now available

SQL> SELECT * FROM V$SERV_MOD_ACT_STATS WHERE VALUE > 0 ORDER BY VALUE DESC;

--Data output is scaled back for readability

AGGREGATION_TYPE       SERVICE_NAME ... STAT_NAME                      VALUE
---------------------- ------------     ------------------------------ ----------
SERVICE_MODULE_ACTION  SYS$USERS    ... DB time                        87870
SERVICE_MODULE_ACTION  SYS$USERS    ... DB CPU                         87870
SERVICE_MODULE_ACTION  SYS$USERS    ... sql execute elapsed time       87503
SERVICE_MODULE_ACTION  SYS$USERS    ... session logical reads           6935
SERVICE_MODULE_ACTION  SYS$USERS    ... parse time elapsed               152
SERVICE_MODULE_ACTION  SYS$USERS    ... workarea executions - optimal      5
SERVICE_MODULE_ACTION  SYS$USERS    ... user calls                         4
SERVICE_MODULE_ACTION  SYS$USERS    ... execute count                      3
SERVICE_MODULE_ACTION  SYS$USERS    ... parse count (total)                1
SERVICE_MODULE_ACTION  SYS$USERS    ... opened cursors cumulative          1


SQL> execute DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE('SYS$USERS', 'TEST_MODULE', 'TEST_ACTION_');

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms