Database Reference
In-Depth Information
Following is an example of an Oracle10
g
BINDS
section. It is the result of tracing the same
SQL statement that was used in the previous example for Oracle9
i
.
PARSING IN CURSOR #1 len=205 dep=1 uid=67 oct=3 lid=67 tim=8546016035 hv=3746754718
ad='2548145c'
SELECT E.LAST_NAME, E.FIRST_NAME, E.SALARY, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E, HR.DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID
AND E.SALARY > :B3
AND E.HIRE_DATE > :B2
AND D.DEPARTMENT_NAME=:B1
END OF STMT
PARSE #1:c=0,e=96,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,tim=8546016029
BINDS #1:
kkscoacd
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=160 off=0
kxsbbbfp=07d9e508 bln=22 avl=04 flg=05
value=4999.99
Bind#1
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=00 csi=00 siz=0 off=24
kxsbbbfp=07d9e520 bln=07 avl=07 flg=01
value="12/31/1995 0:0:0"
Bind#2
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1206001 frm=01 csi=178 siz=0 off=32
kxsbbbfp=07d9e528 bln=128 avl=08 flg=01
value="Shipping"
Oracle10
g
introduced two additional data types. These are
BINARY_FLOAT
and
BINARY_DOUBLE
.
Their data type codes (
oacdty
) are 21 and 22 respectively.
Statement Tuning, Execution Plans, and Bind Variables
When tuning a statement that was captured with SQL trace and includes bind variables, do not
replace bind variables with literals. In doing so, the optimizer might make different decisions.
Instead, when tuning the statement in SQL*Plus, declare SQL*Plus bind variables matching the
data types of the bind variables in the trace file (parameter
dty
or
oacdty
). Since SQL*Plus vari-
ables do not support all data types, for example
DATE
and
TIMESTAMP
are not provided, you may
have to resort to an anonymous PL/SQL block to replicate the data types exactly. The use of
conversion functions, such as
TO_DATE
or
TO_TIMESTAMP
with a SQL*Plus variable of type
VARCHAR2
when the original data type is not available, is another option, but this too might affect the plan
chosen by the optimizer. Even if you do reproduce the bind data types exactly, you may still get
a plan that is different from a previous execution, since the previous execution may have reused a
plan that was built based on different peeked bind variable values. If that plan has meanwhile
been aged out of the shared pool, you may get a different plan based on peeking the current
