Database Reference
In-Depth Information
-----------------------------------------------------------------------------------
|Id| Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | 2 (100)| |
| 1| NESTED LOOPS | | 1 | 2 (0)| 00:00:01 |
| 2| TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 1 (0)| 00:00:01 |
| 3| INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | 0 (0)| |
| 4| TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 1 (0)| 00:00:01 |
| 5| INDEX UNIQUE SCAN | EMP_EMP_ID_PK | 1 | 0 (0)| |
-----------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "D"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
INDEX_RS_ASC(@"SEL$1" "E"@"SEL$1" ("EMPLOYEES"."EMPLOYEE_ID"))
LEADING(@"SEL$1" "D"@"SEL$1" "E"@"SEL$1")
USE_NL(@"SEL$1" "E"@"SEL$1")
END_OUTLINE_DATA
*/
Peeked Binds (identified by position):
--------------------------------------
1 - :DEPT_ID (NUMBER): 50
2 - :EMP_ID (NUMBER): 120
3 - :FN (VARCHAR2(30), CSID=178): 'Matthew'
When the first two arguments to
DBMS_XPLAN.DISPLAY_CURSOR
are
NULL
, the SQL identifier
(
SQL_ID
) and child cursor number (
CHILD_NUMBER
) of the previous SQL statement are used as
defaults. As is evident from the previous example, a large set of hints is required to fully specify
an execution plan. Whenever the optimizer does not honor an individual hint, the reason may
be that it had to take some decisions of its own, which precluded honoring the individual hint.
Trace File Contents
A 10053 trace file is a protocol of the optimizer's inputs, calculations, and outputs. The correct
event level to use is 1. Higher levels do not produce additional output. Be warned that the trace
file contents as well as the costing formulas used by the CBO are subject to change without
notice. The main sections of an Oracle10
g
Release 2 trace file are as follows:
