dbms_monitor

admin

v>Applies to:
Oracle Database - Standard Edition - Version 10.1.0.2 and later
Oracle Database - Personal Edition - Version 10.1.0.2 and later
Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 9-September-2013***
Purpose
This article explains the new tracing possibilities introduced with the DBMS_MONITOR package and includes some examples of how to
use it.
Scope
Support Analysts, DBA's and Programmers
Details
DBMS_MONITOR
New trace enabling procedures have been implemented as part of the DBMS_MONITOR package. Tracing has been enabled for
diagnosis and workload management based on a specified client identifier or a hierarchical combination of service name, module name
and action name. Tracing can also be enabled on a session level.
In some situations this can produce multiple trace files (for example, when tracing a service level for a module). The TRCSESS facility
can be used to scan through all the trace files and combine the sections into a single trace file. After combining the set of trace files,
standard trace file analysis methods such as TKPROF can be used.
Note:
The tracing state for a client identifier, or service/module/action is persistent across session disconnects and database shutdown and can
apply to all instances.
Tracing will stay enabled until disabled using DBMS_MONITOR.
How to View Enabled Traces
As client and service/module/action is persistent across session disconnects and database shutdown, a way to determine which
attributes have tracing enabled.
Tracing is tracked in DBA_ENABLED_TRACES.
Example:
SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES;
TRACE_TYPE PRIMARY_ID QUALIFIER_ID1 WAITS BINDS
--------------------- --------------------------------------- ------------------------------- ----- -----
SERVICE_MODULE SYS$USERS SQL*Plus TRUE FALSE
CLIENT_ID HUGO TRUE FALSE
SERVICE v101_DGB TRUE FALSE
We see three different traces enabled:
First row: Tracing all SQL statements that are executed in SQL*Plus.
Second row: Tracing all session that are used in a session with the client identifier 'HUGO'
Third Row:Tracing all programs that are connected to the database via the service 'v101_DGB'.
Function SESSION_TRACE_ENABLE
SESSION_TRACE_ENABLE enables SQL tracing for a given database session on the local instance.
Syntax
Enable Tracing
dbms_monitor.session_trace_enable(session_id => x, serial_num => y, waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
Disable Tracing
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);


v>The default of waits is TRUE and the default of binds is FALSE.
Example
Get the session and the serial numbers by querying V$SESSION.
SQL> select sid, serial#,username from v$session;
SID SERIAL# USERNAME
---------- ---------- ------------------------------
68 19339
136 5419 SYS
142 4717
214 5473 SCOTT
Start tracing with the command
SQL> execute dbms_monitor.session_trace_enable(214,5473);
Note: No entry will be created in DBA_ENABLED_TRACES and the tracing state is not persistent across a database shutdown.
SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES;
no rows selected
It is possible to get a list of sessions traced by querying v$session as follows:
SELECT sid,serial#,username,sql_trace_waits,sql_trace_binds,sql_trace_plan_stats
FROM v$session
WHERE sql_trace = 'ENABLED';
Tracing will be stopped when the session disconnects or by executing the following command:
SQL> execute dbms_monitor.session_trace_disable(214,5473);
Function CLIENT_ID_TRACE_ENABLE
In multi-tier environments, a request from an end client is routed to different database sessions by the middle tier. This means that the
association between the end client and the database session is non-static. Prior to version 10g, there was no easy way to keep track of a
client across different database sessions. End-to-end tracing makes this possible by introducing a new attribute, CLIENT_IDENTIFIER,
which uniquely identifies a given end client. The client identifier is visible in the CLIENT_IDENTIFIER column of V$SESSION. It is also
visible through the system context.
Syntax
Enable Tracing
execute dbms_monitor.client_id_trace_enable ( client_id =>'client_id', waits => (TRUE|FALSE), binds => (TRUE|FALSE) );
Disable Tracing
execute dbms_monitor.client_id_trace_disable ( client_id =>'client_id');
The default of waits is TRUE and the default of binds is FALSE.
Example
The CLIENT_IDENTIFIER can be set with the function SET_IDENTIFIER of the DBMS_SESSION package.
SQL> execute dbms_session.set_identifier('HUGO');
The client identifier can be found in 2 ways:
1. In the actual session:
SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;
CLIENT_ID
--------------
HUGO
2. From a different session
SQL> select client_identifier client_id from v$session where sid = 142;
CLIENT_ID
--------------
HUGO
Start the tracing for all sessions with the client identifier 'HUGO'
SQL>execute dbms_monitor.client_id_trace_enable ('HUGO');
This tracing is persistent across a database shutdown.
In order to disable tracing, you need to execute the following:
SQL>execute dbms_monitor.client_id_trace_disable ('HUGO');

v>This can sometimes produce more than one trace file. For example, when using Shared Servers, different shared server processes can
execute the SQL statements. This will result in multiple trace files. The same can be true for RAC environments. In a later section we
discuss how to combine these trace files into one trace file using utility TRCSESS.
Function SERV_MOD_ACT_TRACE_ENABLE
End-to-end tracing is also useful for efficient management and accounting of workload for applications using SERVICES that have been
instrumented with MODULE and ACTION name annotation. Service name, module and action name provide a means to set apart
important transactions in an application.
You can use the SERV_ACT_TRACE_ENABLE procedure to enable SQL tracing for a given combination of service name, module name
and action name either globally or for a specific instance.
The service name, module and action for a session are visible in the SERVICE_NAME, MODULE and ACTION columns in V$SESSION.
Syntax
Enable Tracing
execute dbms_monitor.serv_mod_act_trace_enable('Service1', 'Module1', 'Action1', waits => (TRUE|FALSE), binds => (TRUE|FALSE),
instance_name => 'ORCL' );
Disable Tracing
execute dbms_monitor.serv_mod_act_trace_disable('Service1', 'Module1', 'Action1');
The default for waits is TRUE and the default for binds is FALSE.
The default for instance_name is null.
Example
To trace all SQL statement executed via the program SQL Plus on the default service SYS$USERS
SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );
We can check that tracing is enabled with the command
SQL> select primary_id, QUALIFIER_ID1, waits, binds from DBA_ENABLED_TRACES where trace_type like 'SERVICE_MODULE%';
PRIMARY_ID QUALIFIER_ID1 WAITS BINDS
---------------------------------------------------------------- ------------------------------------------------ ----- -----
SYS$USERS SQL*Plus TRUE FALSE
To disable:
SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus' );
Combining trace files with TRCSESS
Some trace operations produce multiple trace files. TRCSESS combines trace files according to certain session or client identifiers.
Syntax
trcsess [output=<output file name >] [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>]
[module=<module name>] <trace file names>
DBMS_APPLICATION_INFO Package
The DBMS_APPLICATION_INFO.SET_X_INFO procedures can be called before beginning a transaction in order to register and name
a transaction/client_info/module for later use when measuring performance across an application.
DBMS_APPLICATION_INFO includes the following functions:
SET_CLIENT_INFO ( client_info IN VARCHAR2 );
SET_ACTION ( action_name IN VARCHAR2 );
SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2 );
Example
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE ( module_name => 'add_employee',action_name => 'insert into emp');
INSERT INTO emp ( ename, empno, sal, mgr, job, hiredate, comm, deptno )
VALUES ( 'SCOTT', 9998, 1000, 7698,'CLERK', SYSDATE,0, 10);
DBMS_APPLICATION_INFO.SET_MODULE(null,null);
END;
/
The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.
SQL> SELECT sql_text FROM v$sqlarea WHERE module = 'add_employee' AND action = 'insert into emp';
SQL_TEXT


v>---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
INSERT INTO EMP ( ENAME, EMPNO, SAL, MGR, JOB, HIREDATE, COMM, DEPTNO ) VALUES ( 'SCOTT', 9999, 1000,
7698,'CLERK', SYSDATE,0, 10)
You can also obtain the information via the functions:
DBMS_APPLICATION_INFO.READ_CLIENT_INFO ( client_info OUT VARCHAR2 );
DBMS_APPLICATION_INFO.READ_MODULE ( module_name OUT VARCHAR2, action_name OUT VARCHAR2 );
Documentation
References
BUG:6743165 - NO ENTRY IN DBA_ENABLED_TRACES WITH FUNCTION SESSION_TRACE_ENABLE[This section is not visible to
customers.]