Jacques / Jeremiah ...
Thanks a lot!
Great help! (as usual)...!
I was forgotten to check the .trc file, and I just did
a tkprof to it! and I checked the resultant file...
I found the bind vars now...
Thanks again... best regards!
JL
- Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
wrote:
> > -----Original Message-----
> > From: Jose Luis Delgado
> [mailto:joseluis_delgado_at_yahoo.com]
> >
> > How can I trace BIND variables inside an
> application??
>
> I think someone else has already answered your
> question, but I will take the
> liberty of mentioning a few alternatives for tracing
> sessions (if anyone
> knows of a different method or any inaccuracies in
> what I say below, I would
> be glad to know! This is what I give to developers
> so I would welcome any
> corrections)
>
> When you trace the SQL for the database or for a
> particular session,
> the trace files will be found in the udump directory
> aka the
> user_dump_dest directory. The name of the directory
> can be found
> with this query:
> select value from v$parameter where name =
> 'user_dump_dest' ;
>
> For help in reading trace files you can use Oracle's
> tkprof utility.
> See Metalink note 41634.1
>
> To turn on tracing for any session, Oracle's
> "recommended" way
> is using dbms_support (see Metalink note 62294.1)
> --
>
> -- using dbms_support (SQL tracing)
> -- turn on
> execute sys.dbms_support.start_trace_in_session -
> (sid => &sid, serial => &serial, waits => true,
> binds => true)
> -- turn off
> execute sys.dbms_support.stop_trace_in_session -
> (sid => &sid, serial => &serial)
>
>
> There are various other ways of turning on tracing.
> Some of these ways are
> generic
> in that they are used to set an event and so could
> be used for any event. To
> see
> the list of events, look on a UNIX database server
> in file
> $ORACLE_HOME/rdmbs/mesg/oraus.msg .
> Setting events should only be done at the direction
> of Oracle technical
> support personnel etc etc.
>
> To signal Oracle to perform SQL_TRACE type actions,
> you should set
> event 10046. The 10046 trace is the equivalent of
> setting SQL_TRACE = TRUE.
> However when setting the event you have in addition
> the choice of several
> levels:
> 1 - Enable standard SQL_TRACE functionality
> (Default)
> 4 - As Level 1 PLUS trace bind values
> 8 - As Level 1 PLUS trace waits
> This is especially useful for spotting latch
> wait etc.
> but can also be used to spot full table scans
> and index scans.
> 12 - As Level 1 PLUS both trace bind values and
> waits
>
> For details of interpreting 10046 output see
> Metalink note 39817.1
>
>
> ALTER SESSION / SET SQL_TRACE
> Turn on tracing for your own session
> --
>
> -- using alter session (event tracing -- this event
> happens to be SQL
> tracing)
> alter session set sql_trace = true ;
>
>
> ALTER SESSION / SET EVENT
> Turn on tracing for your own session by setting
> event 10046
> --
>
> -- using alter session (event tracing -- this event
> happens to be SQL
> tracing)
> alter session set events '10046 trace name context
> forever, level 12' ;
>
>
> DBMS_SYSTEM / SET SQL_TRACE
> Turn on tracing for any session (dbms_system is an
> "undocumented"
> procedure):
> --
>
> -- using dbms_system (SQL tracing)
> -- turn on
> execute sys.dbms_system.set_sql_trace_in_session -
> (sid => &sid, serial# => &serial, sql_trace =>
> true)
> -- turn off
> execute sys.dbms_system.set_sql_trace_in_session -
> (sid => &sid, serial# => &serial, sql_trace =>
> false)
>
>
> DBMS_SYSTEM / SET EVENT
> Turn on tracing for any session by setting event
> 10046
> (dbms_system is an "undocumented" procedure, and
> set_ev even more so):
> --
>
> -- using dbms_system (event tracing -- this event
> happens to be SQL tracing)
> execute sys.dbms_system.set_ev (&sid, &serial,
> 10046, 12, '')
>
>
> ORADEBUG / SET EVENT
> Turn on tracing for any session by setting event
> 10046
> --
>
> -- using oradebug (from inside svrmgr or sql*Plus
> for 9.0 and higher)
> select pid, spid, username from v$process;
> PID SPID USERNAME
> ---- ----- --------
> 8 25807 oracle
> SVRMGR> oradebug setorapid 8
> -- or -
> SVRMGR> oradebug setospid 25807
> -- turn on
> SVRMGR> oradebug event 10046 trace name context
> forever, level 12
> -- turn off
> SVRMGR> oradebug event 10046 trace name context off
>
>
>
> SPFILE / ALTER SYSTEM SET SQL_TRACE
> Turn on tracing for ALL sessions
> note: restart the database for this change to take
> effect
> sql_trace is a static parameter so
> scope=spfile is required
> --
>
> -- to set in the spfile
> -- this example shows how to set two events, adapt
> it for setting
> -- the SQL_TRACE event with 10046 and the
> appropriate level
> ALTER SYSTEM
> SET sql_trace = true
> COMMENT = 'turning on tracing for all sessions'
> SCOPE = SPFILE ;
>
>
> SPFILE / ALTER SYSTEM SET EVENT
> Turn on tracing for ALL sessions by setting event
> 10046
> note: restart the database for this change to take
> effect
> event is a static parameter so scope=spfile is
> required
> --
>
> -- to set in the spfile
> -- this example shows how to set two events, adapt
> the example for setting
> -- the SQL_TRACE event with 10046 and the
> appropriate level
> ALTER SYSTEM
> SET EVENT = '10325 trace name context forever,
> level 10:10015 trace name
> context forever, level 1'
> COMMENT = 'Debug tracing of control and rollback'
> SCOPE = SPFILE ;
>
>
>
> INIT.ORA / SQL_TRACE
> Turn on tracing for ALL sessions
> note: restart the database for this change to take
> effect
> --
>
> -- setting in init.ora
> sql_trace = true
>
>
> INIT.ORA / EVENT
> Turn on tracing for ALL sessions by setting event
> 10046
> note: restart the database for this change to take
> effect
> --
>
> -- setting in init.ora
> event = "10046 trace name context forever, level 12"
>
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jose Luis Delgado
INET: joseluis_delgado_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed Apr 09 2003 - 09:43:51 CDT
