Oracle EBS SQL Trace日记收集的方法

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 tracefile outputfile [explain= ] [table= ] [print= ] [insert= ] [sys= ] [sort= ] ...Sample: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

Tkprof: Release 9.2.0.1.0 - Production on Tue Dec 24 15:32:43 2002Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Trace file: ORCL102_ora_3064.trcSort options: default********************************************************************************count = number of times OCI procedure was executedcpu = cpu time in seconds executingelapsed = elapsed time in seconds executingdisk = number of physical reads of buffers from diskquery = number of buffers gotten for consistent readcurrent = 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 0Execute 10 0.00 0.00 0 0 0 0Fetch 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: 1Optimizer goal: CHOOSEParsing user id: 59 Rows Row Source Operation------- --------------------------------------------------- 1 TABLE ACCESS FULL EMPLOYEE

转载请注明出处：http://blog.csdn.net/pan_tian/article/details/7677120