New Oracle9i execution plan columns   Donald K. Burleson

The Oracle9i cost-based optimizer (cost-based SQL optimizer) has been enhanced in Oracle9i to gather and use external statistics when evaluating the cost of an SQL execution plan.  These new statistics are estimated from Oracles knowledge on the external environment, and these include:

• The mapping of tablespaces to disk files

• The tablespaces blocksize

• The number of CPUs on the database server

• Space required for sorting the result set

Oracle has now developed a way to estimate the CPU and I/O costs and include these estimates into the cost for an SQL query.

• CPU_COST - The CPU cost of the operation as estimated by the cost-based SQL optimizer based on a secret algorithm. The value of this column does not have any particular unit of measurement; it is merely a weighted value used to compare costs of execution plans.

• IO_COST – Oracle estimates the I/O cost of the SQL based upon its knowledge of the settings for db_file_multiblock_read_count, the tablespace blocksize and the presence of indexes.  Oracle does NOT use data buffer statistics because Oracle cannot have an a-priori knowledge of whether a desired data block is already cached in the RAM data buffers.

• TEMP_SPACE - The sort-work area for any ORDER BY or GROUP BY operations are also estimated by the cost-based SQL optimizer.  In Oracle9i the pga_aggregate_target parameter controls the RAM temporary segments (deprecating the obsolete sort_area_size parameter), and the cost-based SQL optimizer also knows about the usage of temporary segments in the TEMP tablespace.

Oracle claims to use this information to make more intelligent choices of execution plans, but there is a serious problem with this approach.

• The cost-based SQL optimizer has no prior knowledge of the data buffer contents – Because of this shortcoming, the cost-based SQL optimizer cannot know if the data blocks are already in the RAM data buffers.

• CPU costs depend upon system load – The CPU costs associated with servicing an Oracle query depend upon the server load, and CPU costs are generally not important unless the entire Oracle instance is using excessive CPU resources.

• Temporary segment space is transient – Even though the cost-based SQL optimizer estimates the TEMP_SPACE costs at optimization time, this execution plan will become permanent for the SQL query until the executable is flushed from the library cache.  Hence, obsolete execution plans may be used to execute a query.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

http://www.rampant-books.com/book_2003_1_oracle9i_sga.htm