Tracing Enhancements Using DBMS_MONITOR (In 10g, 11g and Above) (Doc ID 293661.1)

PURPOSE

This article will explain the new tracing possibilities in 10g and 11g with the package dbms_monitor and give some examples how you can use them.

SCOPE & APPLICATION

Support Analysts, DBA's and support analysts

Tracing Enhancements in 10g

New trace enabling procedures have been implemented as part of the package dbms_monitor. Tracing has been enabled for diagnose and workload management based on, a specified client identifier or a hierarchical combination of service name, module name and action name. Also we have the facility to trace on session level.

In some situation this can produce more than one trace file (e.g we trace a service level for a module) than we can use the new trcsess utility to scan through all trace files and combine the section into a single trace file. After combining the set of trace files, you can use standard trace file analysis methods.

In the next sections we show how the new trace function work.

The tracing state for a client identifier, or service/modul/action is persistent across session disconnects and database shutdown and applies to all instances

Content

1. Viewing Enabled Traces
2. Function session_trace_enable
3. Function client_id_trace_enable
4. Function serv_mod_act_trace_enable
5. Combine trace files with trcsess
6. The Package DBMS_APPLICATION_INFO


1. Viewing Enabled Traces

You can query DBA_ENABLED_TRACES to determine what traces are enabled.

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

At this database we have three different trace state.
1.) The first row shows that we trace on the server all SQL statements that are executed in SQL*Plus.
2.) We trace all session that are used in a session with the client identifier 'HUGO'
3.) We trace all program that are connected to the database via the service 'v101_DGB'.

In the next section we explain how we enable and disable tracing.

2. Function session_trace_enable

You can use the SESSION_TRACE_ENABLE procedure to enable SQL tracing for a given database session on the local instance.

Syntax
You enable tracing with
dbms_monitor.session_trace_enable(session_id => x, serial_num => y,
waits=>(TRUE|FALSE),binds=>(TRUE|FALSE) );
and disable tracing with
dbms_monitor.session_trace_disable(session_id => x, serial_num => y);
The default of waits is TRUE and the default of binds is FALSE.

Example
To get the session and the serial numbers you can query V$SESSION.

SQL> select serial#, sid , username from v$session; 

SERIAL#     SID   USERNAME
-------          -----  --------------
  1                 131
18                 139
  3                 140
 11                143     SCOTT

Than you can start tracing with the command
SQL> execute dbms_monitor.session_trace_enable(143,11);

This tracing state is not persistent across a database shutdown and you see no entry in DBA_ENABLED_TRACES.

SQL> select trace_type, primary_id, QUALIFIER_ID1, waits, binds 
            from DBA_ENABLED_TRACES;

no rows selected

As disconnect from the session or the following command stop tracing
SQL> execute dbms_monitor.session_trace_disable(143,11);

3. Function client_id_trace_enable

In multitier 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 is 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
You start tracing with
execute dbms_monitor.client_id_trace_enable ( client_id =>'client x',
waits => (TRUE|FALSE), binds => (TRUE|FALSE) );
and stop tracing with
execute dbms_monitor.client_id_trace_disable ( client_id =>'client x');
The default of waits is TRUE and the default of binds is FALSE.

Example 
You can set the CLIENT_IDENTIFIER with the function SET_IDENTIFIER of the function DBMS_SESSION.
An example
SQL> execute dbms_session.set_identifier('HUGO');
Has described above we have 2 ways to see what CLIENT_IDENTIFIER we have set 
SQL> select sys_context('USERENV','CLIENT_IDENTIFIER') client_id from dual;

CLIENT_ID
--------------
HUGO

SQL> select client_identifier client_id from v$session where sid = 142;

CLIENT_ID
--------------
HUGO

Now we start the tracing for the client identifier 'HUGO' with the function
SQL>execute dbms_monitor.client_id_trace_enable ('HUGO');
To check that the tracing is enabled use the query
SQL> select primary_id, QUALIFIER_ID1, waits, binds 
     from DBA_ENABLED_TRACES
     where trace_type = 'CLIENT_ID'; 

PRIMARY_ID   QUALIFIER_ID1 WAITS BINDS
----------------   --------------        -------- --------
HUGO                                              TRUE  FALSE

This tracing is persistent across a database shutdown. You have to call a function to disable tracing. This can be done via the execution of 
SQL>execute dbms_monitor.client_id_trace_disable ('HUGO');
for the client identifier 'HUGO'.
This would produce sometimes more than one trace file. As example when you use MTS, different shared server can execute the SQL statements. This will cause more than one trace file. The same can be true for RAC environments. We discuss in a later section how to combine this trace files with the utility trcsess to one file.

4. 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 globally, unless an instance name is specified.
The service name , module name for a session is visible in the SERVICE_NAME and MODULE columns in V$SESSION. 

Syntax
You start tracing with
execute dbms_monitor.serv_mod_act_trace_enable('Service S', 'Module M', 'Action A',
waits => (TRUE|FALSE), binds => (TRUE|FALSE), instance_name => 'ORCL' );
and stop tracing with
execute dbms_monitor.serv_mod_act_trace_disable('Service S', 'Module M', 'Action A');
The default of waits is TRUE and the default of binds is FALSE. The default of instance_name is null.

Example
We would like to trace all SQL statement execute via the program SQL Plus on the database server.
We know that the session 142 is such a session.

SQL> select module, service_name from v$session where sid = 142;

MODULE    SERVICE_NAME
------------- ---------------------
SQL*Plus     SYS$USERS

SQL> execute dbms_monitor.serv_mod_act_trace_enable('SYS$USERS', 'SQL*Plus' );

We can check that we tracing is enabled with the command

SQL> select primary_id, QUALIFIER_ID1, waits, binds 
           from  DBA_ENABLED_TRACES
           where trace_type = 'SERVICE_MODULE'; 

PRIMARY_ID QUALIFIER_ID1 WAITS BINDS
---------------  ------------------- -------- --------
SYS$USERS   SQL*Plus              TRUE   FALSE 

SQL> execute dbms_monitor.serv_mod_act_trace_disable('SYS$USERS', 'SQL*Plus' );

5. Combine trace files with trcsess

You get from some trace operation multiple trace files. In version prior to 10g you have manual put this trace file together. Now you have the utility trcsess that helps you to combine this trace files. You can say if you only want to do it for certain session or client identifiers. 

Syntax
trcsess [output=

] [session=] [clientid=] [service=] [action=] [module=]

6. The Package DBMS_APPLICATION_INFO

You can call the DBMS_APPLICATION_INFO SET procedure before beginning a transaction to register and name a transaction/client_info/module for later use when measuring  performance across an application. You should specify what type of activity a transaction performs so that the system tuners can later see which transactions are taking up the most system resources. 


For this purpose DBMS_APPLICATION_INFO has the functions
SET_CLIENT_INFO ( client_info IN VARCHAR2 ); 
SET_ACTION ( action_name IN VARCHAR2 ); 
SET_MODULE ( module_name IN VARCHAR2, action_name IN VARCHAR2 ); 
to set the names. 

Example 
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 ( name, emp_seq.nextval, salary, manager, title, SYSDATE, commission, department); 
DBMS_APPLICATION_INFO.SET_MODULE(null,null); 


The following sample query illustrates the use of the MODULE and ACTION column of the V$SQLAREA.

SELECT sql_text 
FROM    v$sqlarea 
WHERE module = 'add_employee'; 

SQL_TEXT           
----------------------------------------------------- 
INSERT INTO emp 1 add_employee insert into emp 
(ename, empno, sal, mgr, job, hiredate, comm, deptno) 
VALUES 
(name, next.emp_seq, manager, title, SYSDATE, commission, department) 


You can also read 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 ); 


See also Note 61722.1 How to use the DBMS_APPLICATION_INFO Package



Oracle??? Database Performance Tuning Guide 10g Release 1 (10.1) Part Number B10752-01
Chapter 20 Using Application Tracing Tools

PL/SQL Packages and Types Reference 10g Release 1 (10.1) Part Number B10802-01
Chapter 14 DBMS_APPLICATION_INFO
Chapter 53 DBMS_MONITOR

Oracle? Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
Part Number E25788-04

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-1069178/，如需轉載，請註明出處，否則將追究法律責任。