oracle 跟踪其他用户

最新推荐文章于 2024-10-17 18:58:27 发布

ckth47350

最新推荐文章于 2024-10-17 18:58:27 发布

阅读量96

点赞数

文章标签：数据库开发工具

在SYS用户下，跟踪CHEN用户

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

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

12 rows selected.

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

====
CHEN
====
SQL> show user
USER is "CHEN"
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"

关闭CHEN用户 跟踪
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 chen0626.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.

查看CHEN用户跟踪 文件

[oracle11@localhost trace]$ vim chen0626.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)

。。。。。。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1712802/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1712802/