Oracle DBMS_MONITOR Tracing Session Tuning Performance

Search the Reference Library pages:

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

-----