利用sql_trace跟踪一个指定会话的操作

2016-12-24 08:34:45来源:作者:人点击

第七城市

利用sql_trace跟踪一个指定会话的操作:

1. sys用户给管理用户授权。

SQL> grant execute on sys.dbms_system to andy;

Grant succeeded.

2. 查询被跟踪用户的sid,serial#

SQL> select sid,serial# from v$session where username='DBLINK';

SID SERIAL#

---------- ----------

31 7

3. 管理用户andy开始跟踪dblink用户

SQL> exec sys.dbms_system.set_sql_trace_in_session(31,7,true);

PL/SQL procedure successfully completed.

4. dblink被跟踪用户执行测试语句。

SQL> select * from user_tables;

5. 跟踪结束

SQL> exec sys.dbms_system.set_sql_trace_in_session(31,7,false);

PL/SQL procedure successfully completed.

6. dblink用户查询自己当前trace文件号

SQL> select * from v$diag_info where name like 'Default%';

INST_ID NAME

---------- ----------------------------------------------------------------

VALUE

--------------------------------------------------------------------------------

1 Default Trace File

/home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3524.trc

7. 用tkprof工具生成查看文件

[oracle@11g ~]$ tkprof /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3524.trc andy2.txt sys=no

TKPROF: Release 11.2.0.1.0 - Development on Mon Nov 17 16:48:38 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

8. 查看跟踪记录

[oracle@11g ~]$ cat andy2.txt

TKPROF: Release 11.2.0.1.0 - Development on Mon Nov 17 16:48:38 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Trace file: /home/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_3524.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: dtbhjabjx3v1u

Plan Hash: 4102440123

select *

from <-- #刚执行sql找到

user_tables

call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse 1 0.09 0.10 0 0 0 0

Execute 1 0.00 0.00 0 0 0 0

Fetch 2 0.11 0.31 174 1390 0 1

------- ------ -------- ---------- ---------- ---------- ---------- ----------

total 4 0.21 0.41 174 1390 0 1

。。。。省略输出。

第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台