端到端跟踪简化了在多层环境中问题的诊断,在多层的环境中,一个客户端的请求被中间层路由到不同的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 as21.2371
. You can locate these values in theV$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 whichtrcsess
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 totrcsess
.
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.