Database Reference
In-Depth Information
------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 387 |
| 1 | SORT AGGREGATE | |
1
|
387
|
|* 2 | TABLE ACCESS FULL| T |
160
|
387
|
------------------------------------------------------
2 - filter("N2">246)
If you really have no choice but to look at the figures at the SQL statement level (for example, because the SQL
trace file doesn't contain the execution plan), you'll have a hard time using the rules of thumb provided earlier, simply
because you don't have enough information. In this case, however, at least for simple SQL statements, you might try to
guess the access path figures and adapt the rule of thumb. For example, you might carefully review the SQL statement
to check whether there isn't an aggregation in it, find out how many tables are referenced in the SQL statement, and
then increase the limits in the rules of thumb in proportion to the number of referenced tables.
Pitfalls
While examining the number of logical reads, you must be aware of two pitfalls that might distort the figures. The first
is related to read consistency, and the second is related to row prefetching.
Read Consistency
For every SQL statement, the database engine has to guarantee the consistency of the processed data. For that
purpose, based on current data blocks and undo blocks, consistent copies of data blocks might be created at runtime.
To execute such an operation, several logical reads are performed. Therefore, the number of logical reads performed
by an access path operation is strongly dependent on the number of blocks that have to be reconstructed. The
following excerpt of the output generated by the
read_consistency.sql
script shows that behavior. Note that the
query is the same as the one used in the previous section. According to the execution statistics, the same number of
rows was returned (actually it returns the same data). However, many more logical reads were performed (in total
354, compared to 28). That effect is because of another session that modified the blocks needed to process this query.
Because the changes weren't committed at the time the query was started, the database engine had to reconstruct the
blocks. This led to a much higher number of logical reads:
SELECT * FROM t WHERE n1 BETWEEN 6000 AND 7000 AND n2 = 19
-----------------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 354 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T | 3 |
354
|
|* 2 | INDEX RANGE SCAN | T_N2_I | 24 | 139 |
-----------------------------------------------------------------
1 - filter(("N1">=6000 AND "N1"<=7000))
2 - access("N2"=19)
