Oracle EBS SQL Trace日志收集的方法

http://www.linuxidc.com/Linux/2012-06/63322.htm

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文件所在的目录
 
1.SELECT value FROM v$parameter WHERE name = 'user_dump_dest'; 
 2. 
 3.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转换为更易读的形式。
 
1.TKPROF Command  
 2.$tkprof tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ... 
 3. 
 4.Sample: 
 5.TKPROF raw_trace.trc OUTPUTA.TKPROF EXPLAIN=scott/tiger SYS=NO SORT=(EXECPU,FCHCPU) 
 
tracefile：你要分析的trace文件
 
outputfile：格式化后的文件

 
explain=user/password@connectstring

 
table=schema.tablename

 
    注1：这两个参数是一起使用的，通过连接数据库对在trace文件中出现的每条sql语句查看执行计划，并将之输出到outputfile中

 
    注2：该table必须是数据库中不存在的，如果存在会报错

 
print=n：只列出最初N个sql执行语句

 
insert=filename：会产生一个sql文件，运行此文件可将收集到的数据insert到数据库表中

 
sys=no：过滤掉由sys执行的语句

 
record=filename：可将非嵌套执行的sql语句过滤到指定的文件中去

 
waits=yes|no：是否统计任何等待事件

 
aggregate=yes|no：是否将相同sql语句的执行信息合计起来，默认为yes

 
sort=option：设置排序选项，选项如下：
 
    prscnt：number of times parse was called
    prscpu：cpu time parsing
    prsela：elapsed time parsing
    prsdsk：number of disk reads during parse
    prsqry：number of buffers for consistent read during parse
    prscu：number of buffers for current read during parse
    prsmis：number of misses in library cache during parse
    execnt：number of execute was called
    execpu：cpu time spent executing
    exeela：elapsed time executing
    exedsk：number of disk reads during execute
    exeqry：number of buffers for consistent read during execute
    execu：number of buffers for current read during execute
    exerow：number of rows processed during execute
   exemis：number of library cache misses during execute
    fchcnt：number of times fetch was called
    fchcpu：cpu time spent fetching
    fchela：elapsed time fetching
    fchdsk：number of disk reads during fetch
    fchqry：number of buffers for consistent read during fetch
    fchcu：number of buffers for current read during fetch
    fchrow：number of rows fetched
    userid：userid of user that parsed the cursor

 

 
 1.Tkprof: Release 9.2.0.1.0 - Production on Tue Dec 24 15:32:43 2002 
 2.Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved. 
 3. 
 4.Trace file: ORCL102_ora_3064.trc 
 5. 
 6.Sort options: default 
 7. 
 8.******************************************************************************** 
 9.count    = number of times OCI procedure was executed 
 10.cpu      = cpu time in seconds executing 
 11.elapsed  = elapsed time in seconds executing 
 12.disk     = number of physical reads of buffers from disk 
 13.query    = number of buffers gotten for consistent read 
 14.current  = number of buffers gotten in current mode (usually for update) 
 15.rows     = number of rows processed by the fetch or execute call 
 16.******************************************************************************** 
 17. 
 18.select * 
 19.from 
 20. employee where emp_id = 3737 
 21.  
 22.call     count       cpu    elapsed       disk      query    current        rows 
 23.------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
 24.Parse       10      0.00       0.03          0          0          0           0 
 25.Execute     10      0.00       0.00          0          0          0           0 
 26.Fetch       20      0.34       0.35         72       4730          0          10 
 27.------- ------  -------- ---------- ---------- ---------- ----------  ---------- 
 28.total       40      0.34       0.39         72       4730          0          10 
 29.  
 30.Misses in library cache during parse: 1 
 31.Optimizer goal: CHOOSE 
 32.Parsing user id: 59 
 33.  
 34.Rows     Row Source Operation 
 35.-------  --------------------------------------------------- 
 36.      1  TABLE ACCESS FULL EMPLOYEE 

本篇文章来源于 Linux公社网站(www.linuxidc.com)  原文链接：http://www.linuxidc.com/Linux/2012-06/63322p2.htm

 

 

 

query1：Select value from v$parameter
where name = 'user_dump_dest';

query2： Select oracle_process_id
from fnd_concurrent_requests
where request_id = 2754278

command：tkprof dv11_ora_7830_OPERATIONS_CR2759648.trc dv11_ora_7830_OPERATIONS_CR2759648.tkp sort=prsdsk,exedsk,fchdsk

1，在并发程序定义界面勾上trace选项

2，执行并发程序

3，query1找到生成的trc文件的路径，query2找到文件名中的processing_id

4，tkprof生成tkp文件

5，选取tkp文件中的前5个query在Toad里Explain Plan

使用tkprof

命令在DV11

要先运行一下环境变量的文件

. ./appenv.sh $env $bash

appenv.sh所在目录为：/rdbms/apps1159

要先cd到/u01/oracle/DV11/dv11db/9.2.0/admin/DV11_prcsgisol03/udump目录下，再执行tkprof tracefile outputfile