| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: new paper detailing the many ways to turn on trace in Oracle
> -----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)
-- ---------------------------------------------------------------------------
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
-- ---------------------------------------------------------------------------
ALTER SESSION / SET EVENT
Turn on tracing for your own session by setting event 10046
-- ---------------------------------------------------------------------------
DBMS_SYSTEM / SET SQL_TRACE
Turn on tracing for any session (dbms_system is an "undocumented" procedure):
-- ---------------------------------------------------------------------------
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):
-- ---------------------------------------------------------------------------
ORADEBUG / SET EVENT
Turn on tracing for any session by setting event 10046
-- ---------------------------------------------------------------------------
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
-- ---------------------------------------------------------------------------
event is a static parameter so scope=spfile is required
-- ---------------------------------------------------------------------------
INIT.ORA / EVENT
Turn on tracing for ALL sessions by setting event 10046
note: restart the database for this change to take effect
-- ---------------------------------------------------------------------------
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)
-- 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:34:11 CST
 |
 |