Oracle EBS SQL Trace日志收集的方法

Raw Trace的收集方法

1. 打开Trace，Help > Diagnostics > Trace > Trace > Trace with Binds and Waits

Trace项代表的意思

• No Trace – turns trace off.

• Regular Trace – generates a regular SQL trace by performing the following statement:

                               ALTER SESSION SET SQL_TRACE = TRUE;

• Trace with Binds – writes bind variable values in the SQL trace file

• Trace with Waits – writes wait events in the SQL trace file

• Trace with Binds and Waits – writes both bind variable values and wait events in the SQL trace file

2.执行业务功能

3.关闭Trace，Help > Diagnostics > Trace > Trace > No Trace

系统会弹出一个窗口，告诉你Trace文件所在的目录。

这个目录实际上是数据库系统参数表(v$parameter)中的 user_dump_dest的值，可以执行下边的SQL来找到Trace文件所在的目录

SELECT value FROM v$parameter WHERE name = 'user_dump_dest';

Output:/slot/ems7061/oracle/db/tech_st/11.2.0/admin/az1mu213_rws60145rems/diag/rdbms/az1mu213/az1mu213/trace

trace文件名后边的那个数字是Database Server PID，也可以从Help>About中找到。

TKPROF

之前我们收集的trace日志仍为Raw Trace，如果要做性能分析的话，往往要转换为TKPROF，TKPROF可以把Raw Trace转换为更易读的形式。

TKPROF Command 
$tkprof raw_trace_file.trc output_file explain=apps/apps sort=(exeela,fchela) sys=no

Sample:
TKPROF raw_trace.trc OUTPUTA.TKPROF EXPLAIN=scott/tiger SYS=NO SORT=(EXECPU,FCHCPU)

• raw_trace_file.trc: Name of trace file
  
• output_file: tkprof out file
  
• explain: This option provides the explain plan for the sql statements
• sort: his provides the sort criteria in which all sql statements will be sorted.  This will bring the bad sql at the top of the outputfile.The SORT value causes TKPROF to sort the SQL statements in order of the sum of the CPU time spent executing and the CPU time spent fetching rows before writing them to the output file. For greatest efficiency, always use SORT parameters.
• sys=no:Disables sql statements issued by user SYS

TKRPOF Sample Output

Tkprof: Release 9.2.0.1.0 - Production on Tue Dec 24 15:32:43 2002
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Trace file: ORCL102_ora_3064.trc

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
********************************************************************************

select *
from
 employee where emp_id = 3737
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       10      0.00       0.03          0          0          0           0
Execute     10      0.00       0.00          0          0          0           0
Fetch       20      0.34       0.35         72       4730          0          10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       40      0.34       0.39         72       4730          0          10
 
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 59
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS FULL EMPLOYEE