oracle类似sqlcmd工具,Oracle:是否有一个跟踪查询的工具,比如Profiler for sql server？...

小智..

15

alter system set timed_statistics=true

- 要么

alter session set timed_statistics=true --if want to trace your own session

- 必须足够大:

select value from v$parameter p

where name='max_dump_file_size'

- 找出您感兴趣的会话的sid和序列号:

select sid, serial# from v$session

where ...your_search_params...

- 你可以用10046事件开始跟踪,第四个参数设置跟踪级别(12是最大的):

begin

sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');

end;

- 设置零级别关闭跟踪:

begin

sys.dbms_system.set_ev(sid, serial#, 10046, 0, '');

end;

/*可能的等级:0 - 关闭1 - 最低等级.很像set sql_trace = true 4 - 将绑定变量值添加到跟踪文件8中 - 添加等待12 - 绑定变量值和添加等待事件*/

- 如果你想用更高级别跟踪你自己的会话,那就相同:

alter session set events '10046 trace name context forever, level 12';

- 关掉:

alter session set events '10046 trace name context off';

- 将包含具有原始跟踪信息的文件:

select value from v$parameter p

where name='user_dump_dest'

- 文件名(*.trc)将包含spid:

select p.spid from v$session s, v$process p

where s.paddr=p.addr

and ...your_search_params...

- 您也可以自己设置名称:

alter session set tracefile_identifier='UniqueString';

- 最后,用于TKPROF使跟踪文件更具可读性:

C:\ORACLE\admin\databaseSID\udump>

C:\ORACLE\admin\databaseSID\udump>tkprof my_trace_file.trc output=my_file.prf

TKPROF: Release 9.2.0.1.0 - Production on Wed Sep 22 18:05:00 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

C:\ORACLE\admin\databaseSID\udump>

- 查看跟踪文件使用状态:

set serveroutput on size 30000;

declare

ALevel binary_integer;

begin

SYS.DBMS_SYSTEM.Read_Ev(10046, ALevel);

if ALevel = 0 then

DBMS_OUTPUT.Put_Line('sql_trace is off');

else

DBMS_OUTPUT.Put_Line('sql_trace is on');

end if;

end;

/