Database Reference
In-Depth Information
The two parameters of the
XCTEND
entry allow four combinations. These are summarized in
Table 24-7. Note that
rd_only=1
does not imply that the session previously issued the statement
SET
TRANSACTION
READ
ONLY
.
Table 24-7.
XCTEND Parameter Combinations
XCTEND Parameters
Client Operation
Example
rlbk=0
,
rd_only=1
COMMIT
, no data modified
COMMIT
after
SELECT
from remote table
to release locks
rlbk=0
,
rd_only=0
COMMIT
, data modified
COMMIT
after
INSERT
/
UPDATE
/
DELETE
of
one or more rows
rlbk=1
,
rd_only=1
ROLLBACK
, no data modified
ROLLBACK
after
SELECT
from local
tables, e.g., to increment the snapshot
SCN after
SET
TRANSACTION
READ
ONLY
rlbk=1
,
rd_only=0
ROLLBACK
, data modified
ROLLBACK
after changing data, e.g.,
through a
MERGE
statement
UNMAP
When interrupting ongoing disk sorts with Ctrl+C in Oracle10
g
,
UNMAP
entries are written to the
trace file irrespective of the setting of the parameter
WORKAREA_SIZE_POLICY
. These are appar-
ently related to cleanup of the sort segment observed in
V$SORT_USAGE
. In all my testing with
Oracle9
i
and Oracle10
g
I have never seen an
UNMAP
entry when a
SELECT
statement that triggered a
disk sort ran to completion. My conclusion is that
UNMAP
entries are not part of normal operations
and do not merit further investigation. Here's an example:
FETCH #3:c=1972837,e=30633987,p=4725,cr=4405,cu=25,mis=0,r=1,dep=0,og=1,tim=18549919
3369
FETCH #3:c=0,e=3113,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,tim=185499197879
…
UNMAP #3:c=0,e=136661,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=185552120691
UNMAP
parameters are the same as those of
PARSE
entries.
Execution Plans, Statistics, and the STAT
Entry Format
Execution plans and statistics are reported by STAT entries. Each line of a group of
STAT
entries
represents a row source that contributed to the result of a statement. The term row source
designates either data retrieved from a table or index or an intermediate result within a larger
execution plan. Since no more than two tables can be joined simultaneously, a three-way join
includes a row source that is the intermediate result of joining the other two tables. The minimum
SQL trace level for enabling
STAT
entries is 1.
STAT
entry parameters are summarized in Table 24-8.