Is there a tool to trace queries, like Profiler for sql server?

Answer

alter system set timed_statistics=true

--or

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

-- must be big enough:

select value from v$parameter p
where name='max_dump_file_size'

-- Find out sid and serial# of session you interested in:

 select sid, serial# from v$session
 where ...your_search_params...

--you can begin tracing with 10046 event, the fourth parameter sets the trace level(12 is the biggest):

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

--turn off tracing with setting zero level:

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

/*possible levels: 0 - turned off 1 - minimal level. Much like set sql_trace=true 4 - bind variables values are added to trace file 8 - waits are added 12 - both bind variable values and wait events are added */

--same if you want to trace your own session with bigger level:

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

--turn off:

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

--file with raw trace information will be located:

 select value from v$parameter p
 where name='user_dump_dest'

--name of the file(*.trc) will contain spid:

 select p.spid from v$session s, v$process p
 where s.paddr=p.addr
 and ...your_search_params...

--also you can set the name by yourself:

alter session set tracefile_identifier='UniqueString';

--finally, use tkproof to make trace file more readable:

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>

--to view state of trace file use:

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;

All oracle Questions

Ask your interview questions on oracle

Write Your comment or Questions if you want the answers on oracle from oracle Experts
Name* :
Email Id* :
Mob no* :
Question
Or
Comment* :
 





Disclimer: PCDS.CO.IN not responsible for any content, information, data or any feature of website. If you are using this website then its your own responsibility to understand the content of the website

--------- Tutorials ---