Database Reference
In-Depth Information
that takes too much time to be implemented isn't acceptable in practice, especially if you need to work on tens, or
even hundreds, of SQL statements that are candidates for optimization, or simply because you're working on a
tight schedule.
As a side note, keep in mind that this section focuses on efficiency, not on speed alone. It's essential to
understand that the most efficient access path isn't always the fastest one. As described in Chapter 15, with parallel
processing, it's sometimes possible to achieve a better response time even though the amount of resources used is
higher. Of course, when you consider the whole system, the fewer resources used by SQL statements (in other words,
the higher their efficiency is), the more scalable, and faster, the system is. This is true because, by definition, resources
are limited.
As a first approximation, the amount of resources used by an access path is acceptable when it's proportional to
the amount of returned rows (that is, the number of rows that are returned to the parent operation in the execution
plan). In other words, when few rows are returned, the expected utilization of resources is low, and when lots of rows
are returned, the expected utilization of resources is high. Consequently, the check should be based on the amount of
resources used to return a single row.
In an ideal world, you would like to measure the resource consumption by considering all four main types of
resources used by the database engine: CPU, memory, the disk, and the network. Certainly, this can be done, but
unfortunately getting and assessing all these figures takes a lot of time and effort and can usually be done only for a
limited number of SQL statements in an optimization session. You should also consider that when processing a row,
the CPU processing time depends on the speed of the processor, which obviously changes from system to system.
Further, the amount of memory used is all but proportional to the number of returned rows, and the disk and network
resources aren't always used. It's, in fact, not uncommon at all to see long-running SQL statements that use a modest
amount of memory and are without disk or network access.
Fortunately, there's a single database metric, which is very easy to collect, that can tell you a lot about the amount
of work done by the database engine: the number of logical reads—that is, the number of blocks that are accessed
during the execution of a SQL statement. There are five good reasons for this. First, a logical read is a CPU-bound
operation and, therefore, reflects CPU utilization very well. Second, a logical read might lead to a physical read, and
therefore, if you reduce the number of logical reads, you likely reduce the disk I/O operations as well. Third, a logical
read is an operation subject to serialization. Because you usually have to optimize for a multiuser load, minimizing the
logical reads is good for avoiding scalability problems. Fourth, the number of logical reads is readily available at the
SQL statement and execution plan operation levels, in both SQL trace files and dynamic performance views. And fifth,
the number of logical reads is independent from the load to which the CPU and the disk I/O subsystem are subject.
Because logical reads are very good at approximating overall resource consumption, you can concentrate (at least
for the first round of optimization) on access paths that have a high number of logical reads per returned rows. The
following are generally considered good “rules of thumb”:
•
Access paths that lead to less than about 5 logical reads per returned row are probably good.
•
Access paths that lead to up to about 10-15 logical reads per returned row are probably
acceptable.
•
Access paths that lead to more than about 20 logical reads per returned row are probably
inefficient. In other words, there's probably room for improvement.
To check the number of logical reads per row, there are basically two methods. The first is to take advantage of the
execution statistics provided by dynamic performance views and displayed using the
dbms_xplan
package (Chapter 10
fully describes this technique). The following execution plan was generated using that method. From it, for each
