CHAPTER 7

image

System Statistics

The query optimizer used to base its cost estimations on the number of physical reads needed to execute SQL statements. That method is known as the I/O cost model. The main drawback of this method is that single-block reads and multiblock reads are equally costly.1 Consequently, multiblock read operations, such as full table scans, are artificially favored. Before system statistics were introduced, especially in OLTP systems, the optimizer_index_caching and optimizer_index_cost_adj initialization parameters were used to work around this problem (see Chapter 9 for coverage of both parameters). In fact, the default values ...

Get Troubleshooting Oracle Performance, Second Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.