Introducing the Query Optimizer

Abstract

The query optimizer is one of the building blocks of the SQL engine. Its purpose is to produce efficient execution plans in a timely manner. The time constraint is essential because in most situations it isn’t sensible to spend too much time on the optimization phase. What does “too much time” mean? In general, the parse phase, which contains the work performed by the query optimizer, should be much shorter than the execution phase. The only situation in which having a parse phase longer than the execution phase is acceptable is when a cursor can be reused for many executions. As discussed in Chapter 2, the ability to cache the shared SQL area associated with a cursor in the SGA was also introduced for this very same purpose.