Read the Execution Statistics

David Clement

September 2004

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.

          update m2accts
          set m2type = upper (rtrim (m2type))
          where m2type <> upper (rtrim (m2type));

A question was raised about the need for the last line of code. Using autotrace with the explain option showed a full table scan whether the last line of code, the where clause, was included or not. (Naturally enough; there was no index on this table.) Using

set autotrace traceonly
						statistics

showed significant differences, as in the table below.

Metrics Without the Where Clause With the Where Clause

recursive calls 0 0

db block gets 1495 2

consistent gets 1518 74

physical reads 4 0

redo size 270647 0

bytes sent via SQL*Net 61 61

bytes rec'd via SQL*Net 159 205

SQL*Net roundtrips 2 2

sorts (memory) 0 0

sorts (disk) 0 0

rows processed 1377 0

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 with the 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 "The Oracle Read-Consistency Model"for an explanation of this.