Database Reference
In-Depth Information
Query blocks and object identifiers (
DBA_OBJECTS.OBJECT_ID
) of the tables involved in an
optimized statement
Query transformations considered (predicate move-around, subquery unnesting, etc.)
Legend (abbreviations used)
Results of bind variable peeking
Optimizer parameters (documented and hidden)
System statistics (workload or noworkload)
Object statistics for tables and indexes
Single table access path and cost for each table
List of join orders and cost of each
Execu ion plan
Predicate information
A full set of hints including query block names, which would be used to define a stored
outline
If, after enabling event 10053, you do not find the aforementioned sections in a trace file,
the CBO may have used a cached execution plan instead of optimizing a statement from scratch.
You can force a cursor miss by inserting a comment into the statement.
Case Study
In the subsequent section, we will generate a 10053 trace file for a 5-way join on tables of the
sample schema HR.
SQL> VARIABLE loc VARCHAR2(30)
SQL> EXEC :loc:='South San Francisco'
SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
SQL> SELECT emp.last_name, emp.first_name, j.job_title, d.department_name, l.city,
l.state_province, l.postal_code, l.street_address, emp.email,
emp.phone_number, emp.hire_date, emp.salary, mgr.last_name
FROM hr.employees emp, hr.employees mgr, hr.departments d, hr.locations l, hr.jobs j
WHERE l.city=:loc
AND emp.manager_id=mgr.employee_id
AND emp.department_id=d.department_id
AND d.location_id=l.location_id
AND emp.job_id=j.job_id;
SQL> ALTER SESSION SET EVENTS '10053 trace name context off';
The query used in the case study is available in the file
hr_5way_join.sql
in the source
code depot. The following trace file excerpts are from Oracle10
g
Release 2.
