Oracle DBMS_MONITOR
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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
Packages
TKPROF & TRACE

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-2014 Daniel A. Morgan All Rights Reserved