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

最新推荐文章于 2021-10-06 10:50:46 发布

张玉龙

最新推荐文章于 2021-10-06 10:50:46 发布

阅读量153

点赞数

分类专栏： oracle优化学习

原文链接：https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=516314130098665&parent=DOCUMENT&sourceId=376442.1&id=293661.1&_afrWindowMode=0&_adf.ctrl-state=10u9i4u8a4_102

版权

oracle优化学习专栏收录该内容

62 篇文章 4 订阅

订阅专栏

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

PURPOSE

This article explains the new tracing possibilities introduced with the DBMS_MONITOR package and includes some examples of how to use it. 本文介绍了DBMS_MONITOR包引入的新的跟踪可能性，并包括一些使用它的示例。

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.
新的跟踪启用过程已作为DBMS_MONITOR软件包的一部分实施。已基于指定的客户端标识符或服务名称，模块名称和操作名称的分层组合为诊断和工作负载管理启用了跟踪。跟踪也可以在会话级别上启用。
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. 在某些情况下，这可能会产生多个Trace文件(例如，在跟踪模块的服务级别时)。TRCSESS工具可用于扫描所有Trace文件，并将这些部分组合成一个Trace文件。合并Trace文件集之后，可以使用标准Trace文件分析方法，例如TKPROF。

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. 客户端标识符或service/module/action的跟踪状态在session断开连接和数据库关闭之间是持久的，并且可以应用于所有实例。
Tracing will stay enabled until disabled using DBMS_MONITOR. 跟踪将保持启用状态，直到使用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. 由于client and service/module/action在session断开连接和数据库关闭之间是持久的，因此可以确定启用了跟踪的属性。
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. 第一行：跟踪在SQL*Plus中执行的所有SQL语句。
Second row: Tracing all session that are used in a session with the client identifier 'HUGO' 第二行：跟踪客户端标识符为'HUGO'的会话中使用的所有会话。
Third Row:Tracing all programs that are connected to the database via the service 'v101_DGB'. 第三行：跟踪通过服务'v101_DGB'连接到数据库的所有程序。

Function SESSION_TRACE_ENABLE

SESSION_TRACE_ENABLE enables SQL tracing for a given database session on the local instance.
SESSION_TRACE_ENABLE 对本地实例上的给定数据库会话启用SQL跟踪。
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);

The default of waits is TRUE and the default of binds is FALSE. waits的默认值为TRUE，binds的默认值为FALSE。

Example
Get the session and the serial numbers by querying V$SESSION. 通过查询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.

注意：在DBA_ENABLED_TRACES中将不会创建任何条目，并且在数据库关闭期间跟踪状态不会持久。

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: 通过查询v$session可以获取跟踪的会话列表，如下所示：

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.
在多层环境中，来自最终客户端的请求由中间层路由到不同的数据库会话。这意味着最终客户端和数据库会话之间的关联是非静态的。在版本10g之前，没有简单的方法可以跨不同的数据库会话跟踪客户端。端到端跟踪通过引入新属性CLIENT_IDENTIFIER来唯一可行地标识给定的最终客户端，从而使这成为可能。客户端标识符在V$SESSION的CLIENT_IDENTIFIER列中可见。通过系统上下文也可以看到它。

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. waits的默认值为TRUE，binds的默认值为FALSE。

Example
The CLIENT_IDENTIFIER can be set with the function SET_IDENTIFIER of the DBMS_SESSION package. 可以使用DBMS_SESSION包的函数SET_IDENTIFIER设置CLIENT_IDENTIFIER。

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' 使用客户端标识符'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');

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. 有时可能会产生多个Trace文件。例如，当使用共享服务器时，不同的共享服务器进程可以执行SQL语句。这将导致多个Trace文件。RAC环境也是如此。在下一节中，我们讨论如何使用实用程序TRCSESS将这些Trace文件组合成一个Trace文件。

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. 端到端跟踪对于使用已通过MODULE和ACTION名称注释进行检测的SERVICES的应用程序进行有效的管理和工作量核算也很有用。服务名称，模块和操作名称提供了在应用程序中划分重要事务的方法。
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. 您可以使用SERV_ACT_TRACE_ENABLE过程对全局或特定实例的服务名称，模块名称和操作名称的给定组合启用SQL跟踪。

The service name, module and action for a session are visible in the SERVICE_NAME, MODULE and ACTION columns in V$SESSION. 会话的服务名称，模块和操作在V$SESSION的SERVICE_NAME，MODULE和ACTION列中可见。

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. waits的默认值为TRUE，绑定的默认值为FALSE。
The default for instance_name is null. instance_name的默认值为null。

Example

To trace all SQL statement executed via the program SQL Plus on the default service SYS$USERS 跟踪在默认服务SYS$USERS上通过程序SQL Plus执行的所有SQL语句

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. 某些跟踪操作会生成多个Trace文件。TRCSESS根据某些会话或客户端标识符组合Trace文件。

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.SET_X_INFO过程，以便注册和命名transaction/client_info/module，以供以后在衡量应用程序性能时使用。

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. 以下示例查询说明了V$SQLAREA的MODULE和ACTION列的用法。

SQL> SELECT sql_text FROM v$sqlarea WHERE module = 'add_employee' AND action = 'insert into emp';

SQL_TEXT           
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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 );

Also see

Document 61722.1 How to use the DBMS_APPLICATION_INFO Package

Documentation 文献资料

Document 376442.1 How To Collect 10046 Trace (SQL_TRACE) Diagnostics for Performance Issues
Document 797756.1 Enabling Session Tracing With Other Methods Than DBMS_MONITOR Does Not Reflect In v$SESSION.SQL_TRACE
Document 556756.1 SQL TRACE GENERATING TO TRACE FILES NEVER STOPS

Oracle® Database PL/SQL Packages and Types Reference
11g Release 2 (11.2)
E40758-03
92 DBMS_MONITOR

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_monitor.htm#ARPLS091

Oracle® Database Performance Tuning Guide
11g Release 2 (11.2)
21 Using Application Tracing Tools
21.1 End-to-End Application Tracing

http://docs.oracle.com/cd/E11882_01/server.112/e41573/sqltrace.htm#PFGRF01005