Thanks for the question, Shinesh.

Asked: October 27, 2009 - 10:28 am UTC

Last updated: August 08, 2013 - 7:28 pm UTC

Version: 10gR2

Viewed 1000+ times

You Asked

Hi Tom,

Rescently I happen to hear about "100046 tracing", for which I could not find out much useful materials from the web.Kindly let me know the details of this and how should we make use of it.

Thanks&Regards
Shinesh

and Tom said...

Prior to 10g Release 1, if you wanted to trace your application and have in the trace file:

a) wait events, things the sql waited for
b) bind variable values, to see what inputs were used for each sql statement,

you had to set an event at level 8 or 12, eg:

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

that would enable tracing with waits and binds.

In 10g Release 1 and above, that is no longer necessary, nor preferred (since you need to have alter session granted to you for that).

DBMS_MONITOR does it all:


ops$tkyte%ORA10GR2> exec dbms_monitor.session_trace_enable();

PL/SQL procedure successfully completed.

<b>that is just like alter session set sql_trace=true</b>


ops$tkyte%ORA10GR2> exec dbms_monitor.session_trace_enable( waits => true );

PL/SQL procedure successfully completed.

<b>that gets sql trace turned on for your session AND has wait events recorded</b>

ops$tkyte%ORA10GR2> exec dbms_monitor.session_trace_enable( binds => true );

PL/SQL procedure successfully completed.

<b>that gets the binds</b>


ops$tkyte%ORA10GR2> exec dbms_monitor.session_trace_enable( waits => true, binds => true );

PL/SQL procedure successfully completed.

<b>that would get both</b>

dbms_monitor is fully documented - that is what you want to use.

Rating

(6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thank you..

shinesh, October 29, 2009 - 10:35 pm UTC

Thank you very much Tom.. that really is a great thing to know.

Enabling trace question

Kurt Arthur, August 02, 2013 - 7:54 pm UTC

Very dumb question here.

Besides the ability to specify a session and serial_num
for dbms_monitor.session_trace_enable, is there a
functional difference between the session trace enable
routines in dbms_session and dbms_monitor?

Assuming I want to trace the current session, does it
matter which I elect to call? Or is it strictly personal
preference?

Thanks for providing a great service to the Oracle
community!

August 02, 2013 - 11:32 pm UTC

there are many differences, dbms_monitor would be much better to enable tracing

o dbms_session does things other than enable tracing, so granting on dbms_monitor follows the concept of least privileges

o dbms_monitor allows you to trace connection pooled connections - so you can trace something by attributes other than sid,serial# (not relevant if you are just tracing the current session, but a powerful construct)

I'd prefer to use dbms_monitor personally - but in the case of "trace the current session", they can both do it.

Thank you!

Kurt Arthur, August 03, 2013 - 3:42 pm UTC

Thanks for explaining the differences between session
tracing from dbms_session and dbms_monitor.

If only the docs were so clear :-)

Tracefile Identifier

Narendra, August 08, 2013 - 3:39 pm UTC

Tom,

One thing missing from dbms_monitor and dbms_session is ability to set trace file identifier. It would be really nice to have an API added to DBMS_MONITOR that can be used to set the tracefile identifier. That way, one will not need to use ALTER SESSION for tracing at all. At least, I tend to set the TRACEFILE_IDENTIFIER almost always.

August 08, 2013 - 5:31 pm UTC

I agree - it would be logical to have that in there for completeness.

however......

you do not need alter session to issue many alter session statements, including the set tracefile_identifier!

ops$tkyte%ORA11GR2> create user a identified by a;

User created.

ops$tkyte%ORA11GR2> grant create session to a;

Grant succeeded.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> alter session set tracefile_identifier = 'foobar';

Session altered.

a%ORA11GR2> alter session set sql_trace=true;
ERROR:
ORA-01031: insufficient privileges

so, you don't need alter session for tracefile_identifier, but you do need alter session for enabling sql_trace (and many other alter sessions...)

many ?

Sokrates, August 08, 2013 - 5:50 pm UTC

... but you do need alter session for enabling sql_trace (and many other alter sessions...)

many ?

according to docs: just one ?
http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_2013.htm#i2231814
says

...
To enable and disable the SQL trace facility, you must have ALTER SESSION system privilege.

To enable or disable resumable space allocation, you must have the RESUMABLE system privilege.

You do not need any privileges to perform the other operations of this statement unless otherwise indicated.
...

And I can't find any "otherwise indicated" there.
Beside that: I can't see any logic in this ALTER SESSION system privilege

August 08, 2013 - 7:28 pm UTC

alter session setting some events...

enable/disable guard...

Learned something new today...

Narendra, August 09, 2013 - 1:27 pm UTC

Tom,

Many thanks. Didn't know that one. I will remember that.
But I must admit it sounds really strange that there exists an ALTER SESSION command that can be invoked by a user not having ALTER SESSION database priviledge.

Breadcrumb

Question and Answer