CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_MONITOR
Version 11.1
 
General Information
Purpose Replacement for DBMS_SUPPORT
Source {ORACLE_HOME}/rdbms/admin/dbmsmntr.sql
First Available 10.1
Constants
Name Data Type Value
all_actions VARCHAR2(14) '###ALL_ACTIONS'
all_modules VARCHAR2(14) '###ALL_MODULES'
Dependencies DBMS_MONITOR_LIB
GV_$ACTIVE_SESSION_HISTORY
GV_$CLIENT_STATS
GV_$SERV_MOD_ACT_STATS
Trace file location Trace files are written to the location specified by the  user_dump_dest parameter
SELECT value
FROM v_$parameter
WHERE name = 'user_dump_dest';
Security Model Execute is granted to the DBA role
 
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
 
CLIENT_ID_STAT_ENABLE

Enable statistic gathering for a given Client Identifier
dbms_monitor.client_id_stat_enable(client_id IN VARCHAR2);
conn / 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
 
CLIENT_ID_TRACE_ENABLE (new 11g parameter)

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
 
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
 
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_enable demo
 
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

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 gv$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
 
SERV_MOD_ACT_TRACE_ENABLE (new 11g parameter)

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
 
SESSION_TRACE_ENABLE (new 11g parameter)

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
TKPROF & TRACE
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [275 users online]    © 2010 psoug.org