Read the Execution Statistics
Oracle versions 7-8
Autotrace shows not only the optimizer's chosen execution plan, but
also the statistics for the SQL statement, which can be revealing.
This is easiest to explain by working through an example. An update
statement was proposed as a data validation tool to clean up manually
entered data. It looked like this.
A question was raised about the need for the last line of code. Using
autotrace with the
set m2type = upper (rtrim (m2type))
where m2type <> upper (rtrim (m2type));
explain option showed a full table
scan whether the last line of code, the
was included or not. (Naturally enough; there was no index on this
set autotrace traceonly
statistics showed significant differences, as
in the table below.
Without the Where Clause
With the Where Clause
|db block gets
|bytes sent via SQL*Net
|bytes rec'd via SQL*Net
In this table, "recursive calls" are SQL statements generated
internally, in order to perform queries on the data dictionary and
other processing of similar kinds. "DB block gets" are accesses of
current blocks in the database block buffer. "Consistent gets" are
accesses of blocks in the rollback segments for read consistency.
"Physical reads" are calls to the disk I/O system. "Redo size" is the
amount of redo information (here including rollback segment) that had
to be saved. The other figures reported are self-explanatory.
The number that sparked the most interest in this report is the redo
size. Since the number of rows processed dropped from 1,377 in the
statement without the
where clause to 0 in the statement
where clause, adding that last line filtered
out all rows. This implies that the original update statement would
not actually have changed any data at all, in the sense that every
column that the statement updated would already have had the value
that it was updated to. Nevertheless, Oracle would have used up a lot
of redo space changing data blocks to no effect. See
Read-Consistency Model"for an explanation of this.