Oracle Database Administration: The Essential Refe

Chapter 8. Query Optimization

In most environments, the database administrator is expected to help tune poorly performing queries. After all, the DBA is the expert and is responsible for overall database performance. Indeed, tuning a query to eliminate excessive disk I/O or CPU processing will generally buy more in performance than you can normally get by tuning the System Global Area (SGA) or by optimizing the placement of datafiles on disk.

This chapter is designed to help you optimize queries or, more accurately, to help you help the Oracle optimizers. The optimizer is that portion of the kernel that evaluates the SQL statement and determines the optimal way to retrieve the desired result set. We’ll begin by reviewing the various types of queries, then look at the various ways in which Oracle can perform a join. Next, we’ll discuss the cost-based optimizer and the rule-based optimizer. We present the information in this order so that when we get to the optimizers, we can better discuss how they handle the different types of queries and joins. Finally, we’ll discuss what you have to do to make the cost-based optimizer work, and how to provide it with query optimization hints, which override the normal processing of the cost-based optimizer. Hints are necessary because, after all is said and done, you still know more about your data and the application than the optimizer will ever be able to figure out.