Database Reference
In-Depth Information
oacflg=03 fl2=1000000 frm=00 csi=00 siz=184 off=0
kxsbbbfp=04d99cb0 bln=22 avl=04 flg=05
value=4999.99
Bind#1
oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=0 off=24
kxsbbbfp=04d99cc8 bln=32 avl=10 flg=01
value="31.12.1995"
Bind#2
oacdty=01 mxl=128(64) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=01 csi=178 siz=0 off=56
kxsbbbfp=04d99ce8 bln=128 avl=08 flg=01
value="Shipping"
The example has pointed out how to leverage information from
BINDS
entries in trace files
by reproducing the statement as closely as possible in respect to the data types of bind variables
and their values. Such precise reproduction of traced statements is the optimal starting point
for tuning.
To use the
DISPLAY_CURSOR
functionality, the calling user must have
SELECT
privilege on
V$SQL
,
V$SQL_PLAN
, and
V$SQL_PLAN_STATISTICS_ALL
. The role
SELECT_CATALOG_ROLE
may be
granted to ensure these privileges are available. The previous example showed that calling
DBMS_XPLAN.DISPLAY_CURSOR
without arguments retrieves the plan of the previous statement
from
V$SQL_PLAN
, even when several execution plans for a single statement (i.e., several child
cursors) exist. This functionality cannot be replicated in Oracle9
i
, since the column
V$SESSION.
PREV_CHILD_NUMBER
is not available.
4
Even in Oracle10
g
, SQL*Plus
AUTOTRACE
uses
EXPLAIN
PLAN
, such that it suffers from the
same deficiencies of
EXPLAIN
PLAN
mentioned earlier. This also applies to the TKPROF option
EXPLAIN=
user
/
password
, which also runs
EXPLAIN
PLAN
, this time even in a different database
session from the one that generated the trace file supplied to TKPROF, such that the chances of
getting incorrect results are even greater. Of course, you should never use this TKPROF switch,
but instead allow TKPROF to format the
STAT
lines in the trace file. In case a cursor was not
closed while tracing was active, there won't be any
STAT
lines for that particular cursor in the trace
file. Under such circumstances, you need to query
V$SQL_PLAN
(using
DBMS_XPLAN
in Oracle10
g
),
which succeeds only if the statement in question is still cached. If the statement in question is
no longer cached, access the Statspack repository using the script
$ORACLE_HOME/rdbms/admin/
sprepsql.sql
or the AWR using
DBMS_XPLAN.DISPLAY_AWR
.
Miscellaneous Trace File Entries
The miscellaneous category consists among others of entries that document which session,
module, or action generated trace file entries. Some of these entries are written automatically,
while others require application coding.
In Oracle9
i
, the underlying X$ fixed table
X$KSUSE
does not hold the child cursor number of the
previous statement either.
4.
