Oracle-L: RE: Powerful tracing...

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 08 Apr 2003 15:43:37 -0800
Message-ID: <F001.0057D7B6.20030408154337@fatcity.com>

> -----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"

--

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

Author: Jacques Kilchoer
INET: Jacques.Kilchoer_at_quest.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 Tue Apr 08 2003 - 18:43:37 CDT