端到端应用程序跟踪

端到端跟踪简化了在多层环境中问题的诊断，在多层的环境中，一个客户端的请求被中间层路由到不同的session中，让客户端到数据库端的跟踪变的很困难。端到端的跟踪使用一个客户端标识来唯一的跟踪客户端到数据库端信息。

Workload problems can be identified by End to End Application Tracing for:

• Client identifier - specifies an end user based on the logon Id, such as HR.HR

• Service - specifies a group of applications with common attributes, service level thresholds, and priorities; or a single application, such as ACCTG for an accounting application

• Module - specifies a functional block, such as Accounts Receivable or General Ledger, of an application

• Action - specifies an action, such as an INSERT or UPDATE operation, in a module

• Session - specifies a session based on a given database session identifier (SID), on the local instance

• Instance - specifies a given instance based on the instance name

在跟踪信息写入到文件后，可以使用trcsess工具加固，使用分析工具来分析，如tkprof

为客户端标识来收集信息

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_ENABLE(client_id => 'OE.OE');

在这个例子中，OE.OE是客户端的标识，来指定要收集哪个客户端的信息，你可以查看v$session的client_identifier列来看客户端标识符。

下面是禁用客户端标识收集

EXECUTE DBMS_MONITOR.CLIENT_ID_STAT_DISABLE(client_id => 'OE.OE');

收集service,module和action的信息

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'ACCTG', 
        module_name => 'PAYROLL');

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(service_name => 'ACCTG', 
        module_name => 'GLEDGER', action_name => 'INSERT ITEM');

如果上面的命令执行了，信息收集如下：

payroll模块的所有动作收集，gledger模块的insert item动作

下面是禁用

EXECUTE DBMS_MONITOR.SERV_MOD_ACT_STAT_DISABLE(service_name => 'ACCTG', 
        module_name => 'GLEDGER', action_name => 'INSERT ITEM');

查看

The statistics that have been gathered can be displayed with a number of dynamic views.

• The accumulated global statistics for the currently enabled statistics can be displayed with the DBA_ENABLED_AGGREGATIONS view.

• The accumulated statistics for a specified client identifier can be displayed in the V$CLIENT_STATS view.

• The accumulated statistics for a specified service can be displayed in V$SERVICE_STATS view.

• The accumulated statistics for a combination of specified service, module, and action can be displayed in the V$SERV_MOD_ACT_STATS view.

• The accumulated statistics for elapsed time of database calls and for CPU use can be displayed in the V$SVCMETRIC view.

跟踪会话

SELECT SID, SERIAL#, USERNAME FROM V$SESSION;

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        27         60 OE
...

Use the appropriate values to enable tracing for a specific session:

EXECUTE DBMS_MONITOR.SESSION_TRACE_ENABLE(session_id => 27, serial_num => 60,
        waits => TRUE, binds => FALSE);

The TRUE argument specifies that wait information will be present in the trace. The FALSE argument specifies that bind information will not be present in the trace.

The SESSION_TRACE_DISABLE procedure disables the trace for a given database session identifier (SID) and serial number. For example:

EXECUTE DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => 27, serial_num => 60);

跟踪整个实例

EXECUTE DBMS_MONITOR.DATABASE_TRACE_ENABLE(waits => TRUE, binds => FALSE, 
        instance_name => 

The DATABASE_TRACE_DISABLE procedure disables the tracing for an entire instance or database. For example:

EXECUTE DBMS_MONITOR.DATABASE_TRACE_DISABLE(instance_name => 'inst1');

查看dba_enabled_trace视图来查看已经启用的trace。

trcsess跟上面的是差不多的

Syntax for trcsess

The syntax for the trcsess utility is:

trcsess  [output=output_file_name]
         [session=session_id]
         [clientid=client_id]
         [service=service_name]
         [action=action_name]
         [module=module_name]
         [trace_files]

where

• output specifies the file where the output is generated. If this option is not specified, then standard output is used for the output.

• session consolidates the trace information for the session specified. The session identifier is a combination of session index and session serial number, such as 21.2371. You can locate these values in the V$SESSION view.

• clientid consolidates the trace information given client Id.

• service consolidates the trace information for the given service name.

• action consolidates the trace information for the given action name.

• module consolidates the trace information for the given module name.

• trace_files is a list of all the trace file names, separated by spaces, in which trcsess should look for trace information. The wild card character * can be used to specify the trace file names. If trace files are not specified, all the files in the current directory are taken as input to trcsess.

One of the session, clientid, service, action, or module options must be specified. If more then one of the session, clientid, service, action, or module options is specified, then the trace files which satisfies all the criteria specified are consolidated into the output file.