Re: question about dbms_syste.set_sql_trace_in

From: Mladen Gogala <mladen_at_wangtrading.com>
Date: Wed, 3 Mar 2004 10:38:41 -0500
Message-ID: <20040303153841.GA1871@mladen.wangtrading.com>

In version 10, one doesn't need to use dbms_support (although the file to create it exists), there is the following procedure:

DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL, serial_num IN BINARY_INTEGER DEFAULT NULL,

    waits        IN  BOOLEAN DEFAULT TRUE,
    binds        IN  BOOLEAN DEFAULT FALSE)

If the application sets MODULE and ACTION fields by using DBMS_APPLICATION_INFO package, one can trace all clients which execute that particular module automagically, by using the following:

DBMS_MONITOR.SERV_MOD_ACT_STAT_ENABLE(
service_name IN VARCHAR2,

   module_name     IN VARCHAR2,
   action_name     IN VARCHAR2 DEFAULT ALL_ACTIONS);

and there is a similar procedure for CLIENT_ID, setable from both DBMS_APPLICATION_INFO and DBMS_SESSION. That is the very same client_id from V$SESSION that is also being used for fine grain access control (FGAC). The only thing missing is the possibility to set up trace identifier, so that the trace files could be easily distingusihed among by using "grep".

So, event 10046 is no longer necessary, the tracing mechanism can do that with the usual SQL_TRACE.

On 03/03/2004 09:59:55 AM, Cary Millsap wrote:
> You shouldn't have to turn tracing off to see a trace file. However, you
> do have to make sure that session (sid.serial#) emits at least one line
> of trace data; otherwise, the Oracle kernel won't have opened the trace
> file yet for writing.
>
> Note that with DBMS_SYSTEM.START_SQL_TRACE_IN_SESSION, you will not be
> able to activate *extended* SQL tracing (levels higher than 1). To get
> that, you'll have to use DBMS_SYSTEM.SET_EV(sid, serial#, 10046, level,
> ''), or--better yet--DBMS_SUPPORT.START_TRACE_IN_SESSION. It's
> confusing, I know. If you want to see either "bind" or "wait" data, then
> stay away from the package that has the word "SQL_TRACE" in the name.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Senthil Kumar
> Sent: Wednesday, March 03, 2004 7:44 AM
> To: oracle-l_at_freelists.org
> Subject: RE: question about dbms_syste.set_sql_trace_in_session
>
> Hi,
>
> run exec dbms_system.set_sql_trace_in_session(sid,serial#,false);
>
> then check the trace dir.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of ryan.gaffuri_at_cox.net
> Sent: Wednesday, March 03, 2004 6:35 PM
> To: oracle-l_at_freelists.org
> Subject: question about dbms_syste.set_sql_trace_in_session
>
>
> i ran the following
>
> exec dbms_system.set_sql_trace_in_session(sid,serial#,true);
>
> Procedure Completed Successfully
>
>
> I then went to my udump directory to check for a trace file and could
> not
> find one?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Mar 03 2004 - 09:36:00 CST

Re: question about dbms_syste.set_sql_trace_in_session