针对sql trace 做了如下案例分析

在当前seesion级别测试

1：启用session跟踪

SQL> alter session set sql_trace=true;

Session altered.

2：执行相关查询或dml语句，捕捉相关信息

SQL> select count(*) from C;

  COUNT(*)
----------
        10

3：此时会生成一个trace文件，然后关闭会话跟踪

SQL> alter session set sql_trace=false;

Session altered.

SQL>

4：查看trace文件保存路径

SQL> show parameter user_dump_dest;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /home/oracle/admin/ora10/udump
SQL>

5：查看进程产生的是哪个trace文件

SQL> SELECT c.value||'/'||d.instance_name||'_ora_'||a.spid||'.trc' trace_file_name  
    from v$process a,v$session b,v$parameter c,v$instance d
    where a.addr=b.paddr
   and b.audsid=userenv('sessionid')
    and c.name='user_dump_dest';

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/home/oracle/admin/ora10/udump/ora10_ora_6184.trc

SQL>

6：用tkprof 格式化trace文件

[oracle@sxit-test1 ~]$ tkprof /home/oracle/admin/ora10/udump/ora10_ora_6184.trc  /home/oracle/2.txt

TKPROF: Release 10.2.0.4.0 - Production on Mon Oct 10 18:34:06 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

[oracle@sxit-test1 ~]$

7：查看格式化后的结果

[oracle@sxit-test1 ~]$ more /home/oracle/2.txt

TKPROF: Release 10.2.0.4.0 - Production on Mon Oct 10 18:34:06 2011

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Trace file: /home/oracle/admin/ora10/udump/ora10_ora_6184.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 count(*)
from C

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         21          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         21          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 66 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=21 pr=0 pw=0 time=144 us)
     10   TABLE ACCESS FULL MT_AS_MSG_18 (cr=21 pr=0 pw=0 time=92 us)

针对结果，有几点说明：

parse ：表示解析

execute：表示执行

fetch：表示回显

列值：

count： 表示执行的次数

elapsed表示执行sql所花费的时间，cpu表示执行sql消耗的cpu 时间，elapsed里有wait 的时间
elapsed 和cpu time相差越大表示等待越严重
disk表示的是磁盘的物理读
current表示的是dml操作对应的get buffer
query是select操作对应的get buffer

如果针对其他用户进程，可参考以下网址

http://www.eygle.com/case/Use.sql_trace.to.Diagnose.database.htm