Database Reference
In-Depth Information
FROM hr.employees emp, hr.departments d
WHERE emp.department_id=d.department_id
AND d.department_id=:dept_id
AND emp.employee_id=:emp_id
AND first_name=:fn;
PL/SQL procedure successfully completed.
In release 11.1.0.7, the preceding equi-join results in the following trace file entries (excerpt):
PARSING IN CURSOR #2 len=201 dep=0 uid=32 oct=3 lid=32 tim=1000634574600
hv=3363518900 ad='1d7f9688' sqlid='9w4xfcb47qfdn'
SELECT e.last_name, e.first_name, d.department_name
FROM hr.employees e, hr.departments d
WHERE e.department_id=d.department_id
AND d.department_id=:dept_id
AND e.employee_id=:emp_id
AND first_name=:fn
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1000634574600
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4225575861,tim=1000634574600
FETCH #2:c=0,e=0,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,plh=4225575861,tim=1000634574600
STAT #2 id=1 cnt=1 pid=0 pos=1 obj=0 op='NESTED LOOPS (cr=4 pr=0 pw=0 time=0 us cost
=2 size=38 card=1)'
STAT #2 id=2 cnt=1 pid=1 pos=1 obj=19136 op='TABLE ACCESS BY INDEX ROWID DEPARTMENTS
(cr=2 pr=0 pw=0 time=0 us cost=1 size=16 card=1)'
STAT #2 id=3 cnt=1 pid=2 pos=1 obj=19137 op='INDEX UNIQUE SCAN DEPT_ID_PK (cr=1 pr=0
pw=0 time=0 us cost=0 size=0 card=1)'
STAT #2 id=4 cnt=1 pid=1 pos=2 obj=19139 op='TABLE ACCESS BY INDEX ROWID EMPLOYEES (
cr=2 pr=0 pw=0 time=0 us cost=1 size=22 card=1)'
STAT #2 id=5 cnt=1 pid=4 pos=1 obj=19143 op='INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 p
r=0 pw=0 time=0 us cost=0 size=0 card=1)'
Since the SQL statement had not been cached in the library cache the parse call incurs a
cursor miss (
mis=1
). The plan hash value is not yet known at this point due to the cursor miss.
The
plh
would have been emitted if the statement had already been cached (
mis=0
). Both the
EXEC and FETCH calls include the execution plan hash value (
plh
). To obtain additional infor-
mation on the execution plan represented by the preceding STAT entries (discussed later in
this chapter) using
DBMS_XPLAN.DISPLAY_CURSOR
, the child cursor number must be determined.
SQL> SELECT DISTINCT child_number FROM v$sql_plan WHERE sql_id='9w4xfcb47qfdn'
AND plan_hash_value=4225575861;
CHILD_NUMBER
------------
0
