Database Reference
In-Depth Information
HA cost: 6.53
resc: 6.53 resc_io: 6.00 resc_cpu: 1786642
resp: 6.53 resp_io: 6.00 resp_cpu: 1786642
Best:: JoinMethod: NestedLoop
Cost: 4.65 Degree: 1 Resp: 4.65 Card: 15.14 Bytes: 133
***************
Now joining: JOBS[J]#1
***************
NL Join
…
***********************
Best so far: Table#: 0 cost: 2.0044 card: 1.0000 bytes: 48
Table#: 2 cost: 3.0072 card: 3.8571 bytes: 268
Table#: 3 cost: 4.6454 card: 15.1429 bytes: 1995
Table#: 1 cost: 5.6827 card: 15.1429 bytes: 2400
Table#: 4 cost: 7.7205 card: 15.0013 bytes: 2580
…
Join order[76]: EMPLOYEES[MGR]#4 EMPLOYEES[EMP]#3
DEPARTMENTS[D]#2 LOCATIONS[L]#0 JOBS[J]#1
***************
Now joining: DEPARTMENTS[D]#2
…
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:76, maxperm:2000
Number of join permutations tried: 76
*********************************
(newjo-save) [1 2 4 0 3 ]
Final - All Rows Plan: Best join order: 8
Cost: 7.7205 Degree: 1 Card: 15.0000 Bytes: 2580
Resc: 7.7205 Resc_io: 7.6296 Resc_cpu: 305037
Resp: 7.7205 Resp_io: 7.6296 Resc_cpu: 305037
At the end of the report for a particular join sequence, the CBO prints the best join order
detected so far along with its cost, cardinality, and data volume (bytes). All three figures displayed
under the heading “Best so far” are cumulative in the same way that the cost of a row source in
an execution plan includes the cost of dependent row sources.
The CBO has decided that the join order 8, which joins the tables in the sequence
LOCATIONS
,
DEPARTMENTS
,
EMPLOYEES
(alias
EMP
),
JOBS
, and
EMPLOYEES
(alias
MGR
), has the lowest cost. This join
order is reflected in the execution plan and the
LEADING
hint in the next sections.
Execution Plan
This section contains a nicely formatted execution plan. Besides
DBMS_XPLAN.DISPLAY_CURSOR
,
V$SQL_PLAN
, AWR, and Statspack, the 10053 trace file is another reliable source for execution plans.
Remember that
EXPLAIN
PLAN
is notoriously unreliable and should never be used in optimization
