Database Reference
In-Depth Information
When blocking operations (for example, aggregation operations) are executed, the SQL engine uses row
prefetching internally. As a result, when aggregations are part of the execution plan, the number of logical reads of
an access path is very close to the number of blocks. In other words, every time the SQL engine accesses a block, it
extracts all rows contained in it, regardless of the row prefetching setting. The following example illustrates this:
SQL>
set arraysize 2
SQL> SELECT sum(n1) FROM t;
------------------------------------------------------
| Id | Operation | Name | A-Rows | Buffers |
------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 388 |
| 1 | SORT AGGREGATE | | 1 | 388 |
| 2 | TABLE ACCESS FULL| T | 10000 |
388
|
------------------------------------------------------
Causes
There are several main causes of inefficient access paths:
•
No suitable access structures (for example, indexes) are available.
•
A suitable access structure is available, but the syntax of the SQL statement doesn't allow the
query optimizer to use it.
•
The table or the index is partitioned, but no pruning is possible. As a result, all partitions
are accessed.
•
The table or the index, or both, aren't suitably partitioned.
In addition to the examples in the previous list, two additional situations lead to inefficient access paths:
•
When the query optimizer makes wrong estimations because of a lack of object statistics,
because of object statistics that aren't up-to-date, or because a wrong query optimizer
configuration is in place. I don't cover that here, because I assume that the necessary object
statistics are in place and that the query optimizer is correctly configured (Chapters 8 and 9
fully described these two topics).
•
When the query optimizer is itself the problem, for example, when there are internal bugs
or limitations in how it works. I don't deal with this either, because bugs or query optimizer
limitations are responsible for a very limited number of problems.
Solutions
As described in the previous sections, to efficiently execute a SQL statement, the objective is to minimize the number
of logical reads or, in other words, to use the access path that accesses fewer blocks. To reach this objective, it may be
necessary to add new access structures (for example, indexes) or change the physical layout (for example, partition
some tables or their indexes). Given a SQL statement, there are many combinations of access structures and physical
layouts. Luckily, to make the choice easier, it's possible to classify SQL statements (or better, data access operations) in
two main categories with regard to selectivity: