Database Reference
In-Depth Information
Independently of how you enabled the tracing, the query optimizer generates a trace file containing plenty of
information about the work it carries out. In it you'll find the execution environment determined by initialization
parameters, system statistics, and object statistics, as well as the estimations performed for the purpose of finding out
the most efficient execution plan. Describing the content of the trace file generated by this event is beyond the scope
of this topic. If necessary, please refer to the following sources:
•
Wolfgang Breitling's paper
A Look under the Hood of CBO: The 10053 Event
•
Oracle Support note
CASE STUDY: Analyzing 10053 Trace Files
(338137.1)
•
Chapter 14 of Jonathan Lewis's book
Cost-Based Oracle Fundamentals
, (Apress, 2006)
Each server process writes all data about the SQL statements it parses in its own trace file. This means not only
that a trace file can contain information about several SQL statements, but also that several trace files will be used
whenever the generation of the trace file is enabled in several sessions. For information about the name and location
of trace files, refer to the “Finding Trace Files” section in Chapter 3.
Event 10132
You can use event 10132 to cause a trace file to be generated, containing the execution plan related to every hard
parse. This may be useful if you want to keep a history of all execution plans for a specific module or application.
The following example shows the kind of information stored in the trace file for every SQL statement, principally the
SQL statement and its execution plan (which includes information about predicates). Notice that from this output
I cut out, in two different places, long lists of parameters and bug fixes that provide information about the execution
environment:
----- Current SQL Statement for this session (sql_id=gbxvdrz7jvt80) -----
SELECT count(n) FROM t WHERE n BETWEEN 6 AND 19
----- Explain Plan Dump -----
--------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 2 | |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | TABLE ACCESS FULL | T | 14 | 182 | 2 | 00:00:01 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter(("N">=6 AND "N"<=19))
Content of other_xml column
===========================
db_version : 11.2.0.3
parse_schema : CHRIS
dynamic_sampling: 2
plan_hash : 2966233522
plan_hash_2 : 1071362934
