Database Reference
In-Depth Information
-----------------------------------------------------------
1 - "D"."DEPARTMENT_NAME"[VARCHAR2,30], "FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"
[VARCHAR2,25]
2 - "D"."DEPARTMENT_NAME"[VARCHAR2,30]
3 - "D".ROWID[ROWID,10]
4 - "FIRST_NAME"[VARCHAR2,20], "E"."LAST_NAME"[VARCHAR2,25]
5 - "E".ROWID[ROWID,10]
68 rows selected.
The output of
DBMS_XPLAN
provides information on estimated (E-Rows) versus actual rows
(A-Rows), estimated (E- Time) versus actual time (A-Time), peeked binds, predicates, and
column projection. Except for actual rows and actual time, this information is absent from SQL
trace files. If an inaccurate estimate leads to a slow execution plan, it may be corrected using a
SQL profile, given that a license for both the diagnostics and the tuning pack has been obtained.
Correcting a false assumption by the CBO may lead to a faster execution plan.
CLOSE Entry Format
As the name suggests, the
CLOSE
entry indicates that a cursor is closed. This type of entry was
introduced with release 11.1.0.7 (Oracle 11
g
Release 1 patch set 1). The minimum trace level for
enabling
CLOSE
entries is 1. Following is an example that shows how cursor 2 is opened by a
parse call, an update is executed, the execution plan is emitted, and the cursor is closed:
PARSING IN CURSOR #2 len=42 dep=0 uid=32 oct=6 lid=32 tim=1177102683619
hv=2139321929 ad='1f4b3f6c' sqlid='12kkd2pzs6xk9'
UPDATE hr.employees SET salary=salary*1.10
END OF STMT
PARSE #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=964452392,tim=1177102683619
EXEC #2:c=31250,e=167015,p=0,cr=7,cu=236,mis=0,r=108,dep=0,og=1,plh=964452392,
tim=1177102850634
STAT #2 id=1 cnt=0 pid=0 pos=1 obj=0 op='UPDATE EMPLOYEES (cr=7 pr=0 pw=0
time=0 us)'
STAT #2 id=2 cnt=108 pid=1 pos=1 obj=19139 op='TABLE ACCESS FULL EMPLOYEES (cr=7
pr=0 pw=0 time=0 us cost=3 size=428 card=107)'
CLOSE #2:c=0,e=0,dep=0,type=0,tim=1177105944410
Table 24-5 explains the parameters associated with the
CLOSE
entry. A cursor may be hard
closed (
type=0
) for several reasons, including the following:
The cursor is associated with a DDL statement. DDL statements are not eligible for
caching.
The server-side cursor cache is disabled, i.e., the initialization parameter
SESSION_CACHED_CURSORS
has the value zero.
The SQL statement is eligible for caching and the server-side cursor cache is enabled,
but the statement was executed less than three times.
