trcsess 进行端到端的跟踪

SYS@SOURCE10 > SELECT * FROM v$version WHERE ROWNUM=1;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

[oracle10@WESTZQ ~]$ which trcsess
/u01/oracle10/app/oracle/product/10.2.0/db_1/bin/trcsess

[oracle10@WESTZQ ~]$ trcsess
oracle.ss.tools.trcsess.SessTrcException: SessTrc-00002: Session Trace Usage error: Wrong parameters passed.
trcsess [output=]  [session=] [clientid=] [service=] [action=] [module=]

output= output destination default being standard output.
session= session to be traced.
Session id is a combination of session Index & session serial number e.g. 8.13.
clientid= clientid to be traced.
service= service to be traced.
action= action to be traced.
module= module to be traced.
Space separated list of trace files with wild card '*' supported.

可以看到，trcsess其实是个JAVA程序

可以跟踪：
 session id:为sid.serial#这种格式
 client id：对应v$session.client_identifier,由dbms_session.set_identifier设置
 service name：对应v$session.SERVICE_NAME,SYS$BACKGROUND / SYS$USERS 用于区分前后台进程
 action name:对应v$session.ACTION，由DBMS_APPLICATION_INFO.SET_ACTION 设置
 module name:对应v$session.MODULE，由DBMS_APPLICATION_INFO.SET_MODULE 设置

[oracle10@WESTZQ udump]$ date
Sun Jun 21 14:21:17 CST 2009

开了4个会话运行如下SQL
---------------------------------------------------------------------------------
 Alter session set events '10046 trace name context forever,level 12';
 
 BEGIN
 dbms_session.set_identifier('zhangqiaoc_test');
 END;
 /
 
 << SQL1 >>
 
 BEGIN
 dbms_session.set_identifier(NULL);
 END;
 /
 
 << SQL2 >>
 
 Alter session set events '10046 trace name context off';
---------------------------------------------------------------------------------

[oracle10@WESTZQ udump]$ trcsess clientid=zhangqiaoc_test * > /tmp/trcsess.out
[oracle10@WESTZQ udump]$ cat /tmp/trcsess.out|grep -i select
select 'x' from dual
SELECT * FROM test WHERE object_name='I_OBJ#'
SELECT * FROM test
select 'x' from dual
SELECT * FROM test WHERE data_object_id=44
SELECT * FROM test
select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,o.subname,o.dataobj#,o.flags from obj$ o where o.obj#=:1
SELECT COUNT(*) FROM test
SELECT * FROM test
SELECT COUNT(*) FROM test
SELECT * FROM test

经过验证，不存在前面中<< SQL2 >>中执行过的SQL语句

ORACLE是如何把同个client id的语句提取出来的了？
在运行了dbms_session.set_identifier后，会自动在trc文件中加上一行：
 *** CLIENT ID:(zhangqiaoc_test) 2009-06-21 14:27:23.214
 *** CLIENT ID:() 2009-06-21 14:27:23.259
这样，中间的就是client id=zhangqiaoc_test的SQL语句

trcsess生成的还是个裸的trc文件，仍然需要使用tkprof转下
[oracle10@WESTZQ tmp]$ tkprof trcsess.out tkprof.out

使用trcsess，加上dbms_session.set_identifier，可以进行共享服务器模式下端到端的跟踪，或者BS模式下端到端的跟踪
但是前提是应用上集成进了dbms_session.set_identifier，以设置client_identifier

通过变现一个func可以轻松实现设置：

CREATE OR REPLACE FUNCTION SET_IDENTIFIER(AVC_IDENTIFIER VARCHAR2)
 RETURN NUMBER AS
BEGIN
 DBMS_SESSION.SET_IDENTIFIER(AVC_IDENTIFIER);
 RETURN 100;
END;

---------------------------------------------------------------

刚才研究了下，用dbms_monitor.client_id_trace_disable('identified_id')开启追踪就好了

但是在应用服务器中怎么设置这个还是个问题

跟踪的视图

SELECT * FROM dba_enabled_traces;
SELECT * FROM dba_enabled_aggregations;

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8242091/viewspace-607222/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8242091/viewspace-607222/