RE: new paper detailing the many ways to turn on trace in Oracle

  • From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 27 Feb 2004 11:36:59 -0800

> -----Original Message-----
> Pete Finnigan
>
> I recently needed to document the different ways to set trace
> in Oracle
> for a client. As i couldn't find one single source for all this info
> when i was looking a couple of weeks ago I decided to share the effort
> in collecting it and create a web page on my site detailing
> what i found
> out.
>
> I have detailed 11 ways to set trace in the Oracle database for the
> current session, for another session and also at instance level. If
> anyone is interested then its available at
> http://www.petefinnigan.com/ramblings/how_to_set_trace.htm
>
> Its not a fully detailed polished paper just some notes on
> the different
> methods to turn on trace. Any additions / corrections are welcome.



I didn't see a mention oradebug or dbms_system.set_sql_trace_in_session. Here's 
the list I give to new people:

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)
-- turn on
alter session set events '10046 trace name context forever, level 12' ;
-- turn off
alter session set events '10046 trace name context off' ;


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)
-- turn on (with level 12)
execute sys.dbms_system.set_ev (&sid, &serial, 10046, 12, '')
-- turn off
execute sys.dbms_system.set_ev (&sid, &serial, 10046, 0, '')


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
-- turn on
ALTER SYSTEM
 SET sql_trace = true
 COMMENT = 'turning on tracing for all sessions'
 SCOPE = SPFILE ;
-- turn off
ALTER SYSTEM
 SET sql_trace = false
 COMMENT = 'turning off 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
-- turn on
sql_trace = true
-- turn off
sql_trace = false


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
-- turn on
event = "10046 trace name context forever, level 12"
-- turn off
#event = "10046 trace name context forever, level 12"



The 10053 event trace shows all the access plans the CBO evaluated and the costs
assigned to them. It details the choices made by the CBO in evaluating the
execution path for a query. It externalizes most of the information that
the optimizer uses in generating a plan for a query.
Oracle does not provide any documentation on the output of the 10053 event.
Levels for the 10053 event are 1 and 2. Unlike other events, where higher
levels mean more detail, the 10053 event trace at level 2 produces less
detail than the trace at level 1. The 10053 event trace is written to
user_dump_dest. The trace is only generated if the query is parsed by the
cost based optimizer (CBO). This entails two conditions: the query must be
(hard) parsed and it must be parsed by the CBO. If the session for which the
10053 trace has been enabled is executing only SQL that is already parsed
and is being reused, no trace is produced. Likewise, if the SQL statement
is parsed by the rule based optimizer (RBO), the trace output will consist
of the SQL query only, but none of the other information.
Setting event 10053 - look at examples of setting an event above.
e.g.
-- turn on
alter session set events '10053 trace name context forever, level 1' ;
-- turn off
alter session set events '10053 trace name context off' ;
-- turn on (with level 1)
execute sys.dbms_system.set_ev (&sid, &serial, 10053, 1, '')
-- turn off
execute sys.dbms_system.set_ev (&sid, &serial, 10053, 0, '')


(information on event 10053 from "A Look under the Hood of CBO - the 10053 
Event.pdf"
- Wolfgang Breitling, Centrex Consulting Corporation
http://www.centrexcc.com/papers.html
See that document for information on how to interpret the output)


Other related posts: