Database Reference
In-Depth Information
projects. Oracle Corporation cautions DBAs that “with bind variables in general, the
EXPLAIN
PLAN
output might not represent the real execution plan” (
Oracle Database Performance Tuning
Guide 10g Release 2
, page 19-4). It is a good idea to check the cardinalities (column “Rows”) in
the plan table. Suboptimal plans may result whenever these are grossly incorrect.
============
Plan Table
============
----------------------------------------------------------------------------------+
|Id|Operation |Name |Rows |Bytes|Cost |Time |
----------------------------------------------------------------------------------+
|0 |SELECT STATEMENT | | | | 8| |
|1 | NESTED LOOPS | | 15| 2580| 8|00:00:01 |
|2 | NESTED LOOPS | | 15| 2400| 6|00:00:01 |
|3 | NESTED LOOPS | | 15| 1995| 5|00:00:01 |
|4 | NESTED LOOPS | | 4| 268| 3|00:00:01 |
|5 | TABLE ACCESS BY INDEX ROWID|LOCATIONS | 1| 48| 2|00:00:01 |
|6 | INDEX RANGE SCAN |LOC_CITY_IX | 1| | 1|00:00:01 |
|7 | TABLE ACCESS BY INDEX ROWID|DEPARTMENTS | 4| 76| 1|00:00:01 |
|8 | INDEX RANGE SCAN |DEPT_LOCATION_IX | 4| | 0| |
|9 | TABLE ACCESS BY INDEX ROWID |EMPLOYEES | 4| 264| 1|00:00:01 |
|10| INDEX RANGE SCAN |EMP_DEPARTMENT_IX| 10| | 0| |
|11| TABLE ACCESS BY INDEX ROWID |JOBS | 1| 27| 1|00:00:01 |
|12| INDEX UNIQUE SCAN |JOB_ID_PK | 1| | 0| |
|13| TABLE ACCESS BY INDEX ROWID |EMPLOYEES | 1| 12| 1|00:00:01 |
|14| INDEX UNIQUE SCAN |EMP_EMP_ID_PK | 1| | 0| |
----------------------------------------------------------------------------------+
Predicate Information
The output in the predicate and plan sections is nearly identical to the output you would get
from running
SELECT
*
FROM
table
(DBMS_XPLAN.DISPLAY_CURSOR())
immediately after the state-
ment you are investigating.
Predicate Information:
----------------------
6 - access("L"."CITY"=:LOC)
8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
10 - access("EMP"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
12 - access("EMP"."JOB_ID"="J"."JOB_ID")
14 - access("EMP"."MANAGER_ID"="MGR"."EMPLOYEE_ID")
Hints and Query Block Names
This section comprises a full set of hints including query block names. The hints would be used
to define a stored outline, which fixes the plan chosen by the CBO. The data is displayed with
correct syntax for hints.
