Database Reference
In-Depth Information
Following is an example
WAIT
entry due to a single block disk read associated with cursor 4:
WAIT #4: nam='db file sequential read' ela= 19045 p1=1 p2=19477 p3=1
In the preceding wait event,
p1
is the data file number (
V$DATAFILE.FILE#
),
p2
is the block
within the data file, and
p3
specifies the number of blocks read. As you will see shortly, wait
entries in Oracle9
i
trace files lack the timestamp (
tim
) that Oracle10
g
wait entries have.
WAIT in Oracle10
g
and Oracle11
g
Oracle10
g
has 872 wait events (Oracle11
g
has 959). This is partly due to the fact that the single
wait event
enqueue
in Oracle9
i
has been replaced by 208 individual wait events for each kind of
enqueue. Similarly, Oracle10
g
has 27 wait events for latches whereas Oracle9
i
has only two.
Oracle10
g
and Oracle11
g
wait entries have meaningful parameter names instead of the
generic
p1
,
p2
, and
p3
parameters in Oracle9
i
. A timestamp has also been added. Here's
an example:
WAIT #3: nam='db file scattered read' ela= 22652 file#=4 block#=253 blocks=4
obj#=14996 tim=81592211996
A total of 276 different wait event parameters exist in Oracle10
g
(293 in Oracle11
g
). Of
these, a significant portion has different spelling, but identical meaning, such as “retry count”
and “retry_count”. Another example is the parameters “obj#”, “object #”, and “object_id”. The
parameters may be retrieved by running the following query:
SQL> SELECT PARAMETER1
FROM v$event_name
UNION
SELECT PARAMETER2
FROM v$event_name
UNION
SELECT PARAMETER3
FROM v$event_name
ORDER BY 1;
Bind Variables
To obtain the maximum amount of diagnostic data, tracing of bind variables should be enabled.
Details on bind variables include the data type and value of a bind variable. Without this
information, it is impossible to find out whether an index was not used due to a mismatch
between the data type of an indexed column and the bind variable data type, for example an
indexed
DATE
column and a bind variable with type
TIMESTAMP
. A bind data type mismatch may
also cause increased CPU usage due to conversion from one data type to another. Bind variable
values may be used to tune queries under the exact same conditions that they were captured.
Without bind variable values, example values for query tuning must be retrieved from the
tables. This is a very tedious and time-consuming process. Better to accept the additional file
size and higher measurement intrusion incurred with bind variable tracing than waste time on
finding sample values.
