TIP #38 : Tracing session with 10046 event

Using event 10046 is a common way to gather extended information about sessions which mostly is useful got performance analysis.
Here are different tips on how to enable it and how to read its output.

Enabling trace for session.
  1. alter session set timed_statistics = true;
  2. alter session set statistics_level=all;
  3. alter session set max_dump_file_size = unlimited;
  4. alter session set events '10046 trace name context forever,level 12' ; 12 will trace wait_events and bind variables;

Disabling trace for a session.

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

Tracing existing process

  • Find SID of that session
  • Find its OS pid from the following (This does not work for Windows)
  • select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID;
  • oradebug setospid spid??? (spid from above query)
  • oradebug unlimit
  • oradebug event 10046 trace name context forever,level 12;

OR

  • Find SID of that session
  • Find its Orable pid from the following (This does work for Windows)
    select p.PID,p.SPID,s.SID from v$process p,v$session s where s.paddr = p.addr and s.sid = &SESSION_ID;
  • oradebug setorapid pid??? (pid from above query)
  • oradebug unlimit
  • oradebug event 10046 trace name context forever,level 12;

Disable Tracing of existing session

  • oradebug event 10046 trace name context off

Enabling Trace on user login with trigger

CREATE OR REPLACE TRIGGER SYS.set_trace AFTER LOGON ON DATABASE WHEN (USER like '&USERNAME') DECLARE lcommand varchar(200);

BEGIN

EXECUTE IMMEDIATE 'alter session set statistics_level=ALL';

EXECUTE IMMEDIATE 'alter session set max_dump_file_size=UNLIMITED';

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

END set_trace;

Enabling trace on whole instance.

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

Disabling trace on whole instance

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

How to interpret trace file

Trace file is created in user_dump_dest.There is TKPROF traditional way to make trace file more readable but what I `d like are the following 2 tools which create HTML report.

  • ORASRP (oracledba.ru/orasrp) : OpenSource , available in Windows and Linux
  • TRCANLZR (Metalink note:224270.1) : Creates stored-procedure and bunch of tables in Oracle database, Available on any platform which oracle is available.

5 comments:

jforonda said...

Shervin,

I thought you might be interested in my posts about OraSRP v3.0b. I have 3 posts so far. I may make one more post his week.

http://jforonda.blogspot.com/2007/02/orasrp-v30b-very-short-initial.html
http://jforonda.blogspot.com/2007/03/orasrp-author-replies.html
http://jforonda.blogspot.com/2007/03/orasrp-v30b-questionable-session.html

James

Shervin said...

Thanks James for comment.
Great stuff and great analysis.
I am wondering if you have ever done the same thing for Oracle TRCANLZR. Good thing about this tools is that it should run on the same database which trace file is generated.As the result, it can provide more info such as execution plan of SQL statement.
Appreciate any feedback on that.

Anonymous said...

I have developed one application using oracle apache server and HTP,HTF Language
When I access the application the after some time trace files getting genereated in User Dump folder and it gets growing in size of GB
and in short time it floods my drive
Application becomes non accesseble even im not able to connect to database
i have exlpicitly disabled the trace in parameter file
Still trace files of big size is getting generated
Can you all give me some other way out and some tips how i can stop it.

- Payal H.

Shervin said...

Hi Payal,

Thanks for comment.
I would follow below steps.

1. Make sure, event parameter in init.ora has not been set. (you can check the file or use sqlplus to login to database and run "show parameter event"). If this parameter has setting, it may be the cause of problem.

2. I am wondering if you know Apache server connects to which user in database.
There might be trigger which enables tracing for connection.

3. Make sure auditing on database is off. Login to database and run "show parameter aud".

4. Would you please send me first lines of trace file. (First 200 lines)?

5. Make sure sql_trace parameter is off. login to database and run "show parameter sql_trace"

If that does not help, I will provide you a trigger to disable tracing in whole database.

Please keep me posted.

Admirador said...


I use PL SQL Developer to connect to ORACLE Server (Solaris).

How can I use dDBMS_OUTPUT.PUT_LINE to log a file

“alter session set events ‘10046 trace name context forever, level 4′”;

to enable extended sql tracing. and to create trace file in user_dump_dest directory ?

what is means 10046 value ? and level 4 ?