Whooa! Good golly! You just re-wrote Pete's paper! I also believe that any
pape appearing these days should include DBMS_MONITOR.
On 02/27/2004 02:36:59 PM, Jacques Kilchoer wrote:
>
> > -----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)
>
>
>
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 Fri Feb 27 2004 - 13:48:55 CST
