trcsess工具的使用

trcsess工具位于$ORACLE_HOME/bin目录下，通过该工具，我们可以将多个tracefile 文件中的内容，根据一定的条件组合到单个输出文件中，然后对该文件使用tkprof等工具进行处理。

该工具主要应用于共享服务器或者采用连接池模式下，我们很难针对某用户进行跟踪等情况下。

[oracle@oadata bin]$ pwd
/oracle/app/product/11.2.0/db_3/bin
[oracle@oadata bin]$ trcsess
oracle.ss.tools.trcsess.SessTrcException: SessTrc-00002: 浼..璺.釜?ㄦ??..: 浼..??..?..?般
trcsess [output=<output file name >]  [session=<session ID>] [clientid=<clientid>] [service=<service name>] [action=<action name>] [module=<module name>] <trace file names>

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

实例：

session 1：

SQL> alter session set tracefile_identifier='joe';

会话已更改。

SQL> alter session set sql_trace=true;

会话已更改。

SQL> select 'session 1' from dual;

'SESSION1
---------
session 1

session 2：

SQL> alter session set tracefile_identifier='joe';

会话已更改。

SQL> alter session set sql_trace=true;

会话已更改。

SQL> select 'session 2' from dual;

'SESSION2
---------
session 2

trcsess的使用：

[oracle@oadata trace]$ trcsess output=b.txt module=SQL*Plus  *joe.trc

[oracle@oadata trace]$ tkprof b.txt c.txt

TKPROF: Release 11.2.0.3.0 - Development on 星期三 12月 25 17:00:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.


[oracle@oadata trace]$ cat c.txt

TKPROF: Release 11.2.0.3.0 - Development on 星期三 12月 25 17:00:05 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: b.txt
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 4tk6t8tfsfqbf Plan Hash: 0

alter session set sql_trace=true


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Misses in library cache during execute: 2
Optimizer mode: ALL_ROWS
Parsing user id: 83  
********************************************************************************

SQL ID: 9puhtpxj6f9fd Plan Hash: 1388734953

--select 'session 1' 
--from
-- dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  FAST DUAL  (cr=0 pr=0 pw=0 time=1 us cost=2 size=0 card=1)

********************************************************************************

SQL ID: g3335j98k2a5q Plan Hash: 1388734953

--select 'session 2' 
--from
-- dual


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          0          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 83  
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  FAST DUAL  (cr=0 pr=0 pw=0 time=3 us cost=2 size=0 card=1)




********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      4      0.00       0.00          0          0          0           0
Fetch        4      0.00       0.00          0          0          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.00       0.00          0          0          0           2

Misses in library cache during parse: 2
Misses in library cache during execute: 2


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0

    4  user  SQL statements in session.
    0  internal SQL statements in session.
    4  SQL statements in session.
********************************************************************************
Trace file: b.txt
Trace file compatibility: 11.1.0.7
Sort options: default

       0  session in tracefile.
       4  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       4  SQL statements in trace file.
       3  unique SQL statements in trace file.
      44  lines in trace file.
      42  elapsed seconds in trace file.