Oracle | DBMS

Oracle DBMS_MONITOR Version 21c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Replacement for DBMS_SUPPORT this package allows enabling 10046 tracing by session, service, module, and action

AUTHID DEFINER

Constants

Name	Data Type	Value
all_actions	VARCHAR2(14)	'###ALL_ACTIONS'
all_modules	VARCHAR2(14)	'###ALL_MODULES'

Dependencies

DBMS_MONITOR_LIB

Documented Yes: Packages and Types Reference

First Available 10.1

Security Model Owned by SYS with EXECUTE granted to the DBA and OEM_MONITOR roles

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	DATABASE_TRACE_DISABLE	SERV_MOD_ACT_TRACE_DISABLE
CLIENT_ID_STAT_ENABLE	DATABASE_TRACE_ENABLE	SERV_MOD_ACT_TRACE_ENABLE
CLIENT_ID_TRACE_DISABLE	SERV_MOD_ACT_STAT_DISABLE	SESSION_TRACE_DISABLE
CLIENT_ID_TRACE_ENABLE	SERV_MOD_ACT_STAT_ENABLE	SESSION_TRACE_ENABLE

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@pdbdev

      

      SELECT sid, client_identifier, service_name

      FROM gv$session;

      

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

      

      set linesize 161

      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:PERRITO5\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:PERRITO5\Daniel Morgan');

      

      -- run TKPROF on trace file

      exec dbms_monitor.client_id_stat_disable('UWCLASS:PERRITO5\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 sys@pdbdev 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@pdbdev

      

      exec stat_proc;

      

      conn sys@pdbdev 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);