Oracle 跟踪全部用户

查看 sql_trace的值
SQL> show parameter sql_trace

VALUE
-----
FALSE

开启全局sql_trace
SQL> alter system set sql_trace=true scope=spfile;

重启数据库生效
SQL> alter system checkpoint;
SQL> startup force

查看跟踪文件
cd $ORACLE_HOME/rdbms/log
tkprof ora_3288.trc 0626.trc
vim 0626.trc

关闭全局sql_trace
SQL> alter system set sql_trace=flase scope=spfile;

重启数据库生效
SQL> alter system checkpoint;
SQL> startup force

在SYS用户下，跟踪LIU用户

SYS

SQL> select sid,serial#,username from v$session where username is not null;

SID    SERIAL# USERNAME
---------- ---------- ------------------------------
       124       2264 LIU
。。。。。。

12 rows selected.

打开LIU用户 跟踪
SQL> exec dbms_system.set_sql_trace_in_session(124,2264,true);

LIU

SQL> show user
USER is "LIU"
SQL> select * from tab;
SQL> select ename,deptno,sal,rank() over(partition by deptno order by sal desc)  from emp;
SQL> select deptno,sum(sal),max(sal),min(sal) from emp group by deptno;

SYS

SQL> show user
USER is "SYS"

关闭LIU用户 跟踪
SQL> exec dbms_system.set_sql_trace_in_session(124,2264,false);
查看跟踪文件位置

SQL> show parameter user_dump
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /u2/app/oracle/diag/rdbms/orcl
                                                 /orcl/trace


[oracle11@localhost ~]$ cd /u2/app/oracle/diag/rdbms/orcl/orcl/trace/

格式跟踪文件

[oracle11@localhost trace]$ tkprof orcl_ora_22891.trc LIU0626.trc

TKPROF: Release 11.2.0.1.0 - Development on Fri Jun 26 10:25:07 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

查看LIU用户跟踪 文件

[oracle11@localhost trace]$ vim LIU0626.trc 
TKPROF: Release 11.2.0.1.0 - Development on Fri Jun 26 10:25:07 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: orcl_ora_22891.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
********************************************************************************

********************************************************************************
SQL ID: cxfjxyu06hwcy
Plan Hash: 3145491563
select ename,deptno,sal,rank() over(partition by deptno order by sal desc)
from
 emp

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          3          0          14
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          3          0          14


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 112
Rows     Row Source Operation
-------  ---------------------------------------------------
     14  WINDOW SORT (cr=3 pr=0 pw=0 time=0 us cost=4 size=182 card=14)     14   TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=13 us cost=3 size=182 card=14)
********************************************************************************
SQL ID: 5strk8bvth25r
Plan Hash: 4067220884
select deptno,sum(sal),max(sal),min(sal)
from
 emp group by deptno
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.02          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          3          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.02          0          3          0           3

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

Rows     Row Source Operation
-------  ---------------------------------------------------
      3  HASH GROUP BY (cr=3 pr=0 pw=0 time=0 us cost=4 size=21 card=3)     14   TABLE ACCESS FULL EMP (cr=3 pr=0 pw=0 time=26 us cost=3 size=98 card=14)

。。。。。。