Oracle DBMS_MONITOR
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Replacement for DBMS_SUPPORT this package allows enabling 10046 tracing by session, service, module, and action
AUTHID CURRENT_USER
Constants
Name Data Type Value
all_actions VARCHAR2(14) '###ALL_ACTIONS'
all_modules VARCHAR2(14) '###ALL_MODULES'
Dependencies
DBMS_MONITOR_LIB GV_$CLIENT_STATS GV_$SERV_MOD_ACT_STATS
GV_$ACTIVE_SESSION_HISTORY    
Documented Yes
First Available 10gR1
Security Model Owned by SYS with EXECUTE granted to the DBA role
Source {ORACLE_HOME}/rdbms/admin/dbmsmntr.sql
Trace File Location Trace files are written to the location ADR Trace directory
SELECT value
FROM v_$parameter
WHERE name = 'user_dump_dest';
Subprograms
 
CLIENT_ID_STAT_DISABLE
Disable previously enabled statistic gathering dbms_monitor.client_id_stat_disable(client_id IN VARCHAR2);
See CLIENT_ID_STAT_ENABLE Demo Below
 
CLIENT_ID_STAT_ENABLE
Enable statistic gathering for a given Client Identifier dbms_monitor.client_id_stat_enable(client_id IN VARCHAR2);
conn sys@pdbdev as sysdba

col client_identifier format a35
col service_name format a20

SELECT sid, client_identifier, service_name
FROM v_$session;

CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON
ON DATABASE
DECLARE
 uid VARCHAR2(64);
BEGIN
  SELECT ora_login_user ||':'|| SYS_CONTEXT('USERENV', 'OS_USER')
  INTO uid
  FROM dual;

  dbms_session.set_identifier(uid);
END logon_trigger;
/

conn uwclass/uwclass

SELECT sid, client_identifier, service_name
FROM gv$session;

exec dbms_monitor.client_id_stat_enable('UWCLASS:PERRITO2\Daniel Morgan');

set linesize 160
col stat_name format a30
col value format 99999999

SELECT *
FROM gv$client_stats;

col module format a20
col action format a20

-- an intentionally bad query
SELECT COUNT(*)
FROM all_tables t, all_indexes i
WHERE t.tablespace_name = i.tablespace_name;

SELECT *
FROM gv$client_stats;

exec dbms_monitor.client_id_trace_enable('UWCLASS:PERRITO2\Daniel Morgan', TRUE, FALSE);

SELECT COUNT(*)
FROM all_tables t, all_indexes i
WHERE t.table_name = i.table_name;

exec dbms_monitor.client_id_trace_disable('UWCLASS:PERRITO2\Daniel Morgan');

-- run TKPROF on trace file
exec dbms_monitor.client_id_stat_disable('UWCLASS:PERRITO2\Daniel Morgan');

SELECT *
FROM gv$client_stats;
 
CLIENT_ID_TRACE_DISABLE
Disables a previously enabled trace dbms_monitor.client_id_trace_disable(client_id IN VARCHAR2);
See CLIENT_ID_STAT_ENABLE Demo Above
 
CLIENT_ID_TRACE_ENABLE
Enables the trace for a given Client Identifier globally for the database dbms_monitor.client_id_trace_enable(
client_id IN VARCHAR2,
waits     IN BOOLEAN  DEFAULT TRUE,
binds     IN BOOLEAN  DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);
See CLIENT_ID_STAT_ENABLE Demo Above
 
DATABASE_TRACE_DISABLE
Disables SQL trace for the whole database or given instance dbms_monitor.database_trace_disable(instance_name IN VARCHAR2 DEFAULT NULL);
See DATABASE_TRACE_ENABLE Demo Below
 
DATABASE_TRACE_ENABLE
Enables SQL trace for the whole database or given instance dbms_monitor.database_trace_enable(
waits         IN BOOLEAN  DEFAULT TRUE,
binds         IN BOOLEAN  DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL,
plan_stat     IN VARCHAR2 DEFAULT NULL);
ALTER SESSION SET tracefile_identifier = 'dbms_monitor';

exec dbms_monitor.database_trace_enable(binds=>TRUE);

exec dbms_monitor.database_trace_disable;
 
SERV_MOD_ACT_STAT_DISABLE
Disables statistic gathering enabled for a given combination of Service Name, MODULE and ACTION dbms_monitor.serv_mod_act_stat_disable(
service_name IN VARCHAR2,
module_name  IN VARCHAR2,
action_name  IN VARCHAR2 DEFAULT ALL_ACTIONS);
See SERV_MOD_ACT_STAT_ENABLEe Demo Below
 
SERV_MOD_ACT_STAT_ENABLE
Enables statistic gathering for a given combination of Service Name, MODULE and ACTION exec dbms_monitor.serv_mod_act_stat_enable(
service_name IN VARCHAR2,
module_name  IN VARCHAR2,
action_name  IN VARCHAR2 DEFAULT ALL_ACTIONS);
conn uwclass/uwclass@pdbdev

CREATE OR REPLACE PROCEDURE stat_proc IS
 sid   gv$session.sid%TYPE;
 cliid gv$session.client_identifier%TYPE;
 modl  gv$session.module%TYPE;
 act   gv$session.action%TYPE;
BEGIN
  dbms_session.set_identifier('Morgan:UW');

  dbms_application_info.set_module('stat_proc', 'demo');

  SELECT sid, client_identifier, module, action
  INTO sid, cliid, modl, act
  FROM gv$session
  WHERE sid = (SELECT sid FROM v$mystat WHERE rownum = 1);

  dbms_output.put_line('SID: ' || sid);
  dbms_output.put_line('Client Identifier: ' || cliid);
  dbms_output.put_line('Module: ' || modl);
  dbms_output.put_line('Activity: ' || act);
END stat_proc;
/

set serveroutput on

exec stat_proc;

conn / as sysdba

set linesize 141
col client_identifier format a30
col service_name format a15
col module format a15
col action format a20

SELECT sid, client_identifier, service_name, module, action
FROM gv$session;

SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name
FROM gv$serv_mod_act_stats;

exec dbms_monitor.serv_mod_act_stat_enable('TESTSERV', 'stat_proc', dbms_monitor.ALL_ACTIONS);

col service_name format a20
col stat_name format a30

SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name
FROM gv$serv_mod_act_stats;

conn uwclass/uwclass

exec stat_proc;

conn / as sysdba

SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name
FROM gv$serv_mod_act_stats;

exec dbms_monitor.serv_mod_act_stat_disable('TESTSERV', 'stat_proc', dbms_monitor.ALL_ACTIONS);

SELECT inst_id, aggregation_type, service_name, module, stat_id, stat_name
FROM gv$serv_mod_act_stats;
 
 SERV_MOD_ACT_TRACE_DISABLE
Globally disables the trace for ALL enabled instances for a given combination of Service Name, MODULE and ACTION name dbms_monitor.serv_mod_act_trace_disable(
service_name  IN VARCHAR2,
module_name   IN VARCHAR2 DEFAULT ALL_MODULES,
action_name   IN VARCHAR2 DEFAULT ALL_ACTIONS,
instance_name IN VARCHAR2 DEFAULT NULL);
See SERV_MOD_ACT_TRACE_ENABLE Demo Below
 
SERV_MOD_ACT_TRACE_ENABLE
Enables SQL tracing for a given combination of Service Name, MODULE and ACTION globally unless an instance_name is specified dbms_monitor.serv_mod_act_trace_enable(
service_name  IN VARCHAR2,
module_name   IN VARCHAR2 DEFAULT ANY_MODULE,
action_name   IN VARCHAR2 DEFAULT ANY_ACTION,
waits         IN BOOLEAN  DEFAULT TRUE,
binds         IN BOOLEAN  DEFAULT FALSE,
instance_name IN VARCHAR2 DEFAULT NULL,
plan_stat     IN VARCHAR2 DEFAULT NULL);
SELECT instance_name
FROM gv$instance;

exec dbms_monitor.serv_mod_act_trace_enable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, TRUE, TRUE, 'orabase');

exec dbms_monitor.serv_mod_act_trace_disable('TESTSERV', dbms_monitor.all_modules, dbms_monitor.all_actions, 'orabase');
 
SESSION_TRACE_DISABLE
Disables the previously enabled trace for a given database session identifier (SID) on the local instance dbms_monitor.SESSION_TRACE_DISABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL);
See SESSION_TRACE_ENABLE Demo Below
 
SESSION_TRACE_ENABLE
Enables the trace for a given database session identifier (SID) on the local instance DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits      IN BOOLEAN        DEFAULT TRUE,
binds      IN BOOLEAN        DEFAULT FALSE,
plan_stat  IN VARCHAR2       DEFAULT NULL);
-- enable tracing for a client with a given client session ID:

SELECT schemaname, sid, serial#
FROM gv$session;

exec dbms_monitor.session_trace_enable(144, 253, TRUE, FALSE);

-- disable tracing specified in the previous step:

exec dbms_monitor.session_trace_disable(144, 253);

-- either

exec dbms_monitor.session_trace_enable(144);

-- or

exec dbms_monitor.session_trace_enable(144, NULL);

-- traces the session with session ID of 144, while either

exec dbms_monitor.session_trace_enable;

-- or

exec dbms_monitor.session_trace_enable(NULL, NULL);

-- trace the current user session

exec dbms_monitor.session_trace_enable(NULL, NULL, TRUE, TRUE);

-- traces the current user session including waits and binds.
-- the same can be also expressed using keyword syntax:


exec dbms_monitor.session_trace_enable(binds=>TRUE);

-- enable tracing for a client with a given client session ID:

exec dbms_monitor.session_trace_enable(144, 253, TRUE, FALSE);

-- disable tracing specified in the previous step:

exec dbms_monitor.session_trace_disable(144, 253);

Related Topics
DBMS_SERVICE
DBMS_SESSION
DBMS_SUPPORT
DBMS_SYSTEM
DBMS_TRACE
Packages
TKPROF & TRACE
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved