The cost-based SQL optimizer (CBO) has been enhanced to consider external influences when determining the best execution plan for a SQL query. Because the Oracle database does not run in a vacuum, the CBO must be able to factor in the costs of external disk I/O and the cost of CPU cycles for each SQL operation. This is an important step forward in making the CBO one of the most sophisticated software packages in the world. The job of the CBO is to always choose the best execution plan for any SQL statement, and this is no small challenge.

According to the Oracle documentation, the I/O and CPU costs are evaluated as shown below:

Cost =  (#SRds * sreadtim + #MRds * mreadtim + #CPUCycles
         -------------------------------------------------
                          cpuspeed )
         -------------------------------------------------
                          sreadtim
where:

#SRDs - number of single block reads
#MRDs - number of multi block reads
#CPUCycles - number of CPU Cycles *)

sreadtim  - single block read time
mreadtim - multi block read time
cpuspeed  -  CPU cycles per second

Note that the external costing is heavily influenced by the estimated cost of disk reads (as measured by the v$ tables), and the estimated CPU costs associated with each internal operation. Oracle keeps details about the costs of many components of SQL execution and uses these average costs to influence the choices made by the cost-based SQL optimizer. Here are some examples:
 

• Hash join costs—Oracle knows the average amount of RAM memory consumed by a hash join.
   
• Sort costs—Oracle keeps track of the RAM required for sorting and aggregation operations.
   
• Table scan costs—Oracle keeps information about the time required to perform a multiblock read (db file scatter reads).
   
• Index block access costs—Oracle knows the average time required to fetch a single block (db file sequential reads).

Remember that these costs are weighted differently depending on your choice of Oracle optimizer_mode. If you have an OLTP system with the first_rows optimizer mode, the CBO makes it more important to return rows quickly than to minimize resource costs. On the other hand, if you're using the all_rows optimizer mode for a data warehouse, the CBO will be heavily influenced by these external factors because the all_rows mode is designed to minimize resource consumption.

The all_rows optimizer mode is designed to minimize computing resources and it favors full-table scans.  Index access (first_rows) adds additional I/O overhead, but they return rows faster, back to the originating query:

To fully understand external costing within Oracle, let’s take a closer look at these new external influences and see how the Oracle CBO uses external costs.

cpu_cost

The CBO is now capable of estimating the number of machine cycles required for an operation, and factors this cost into the execution plan calculation. The CPU costs associated with servicing an Oracle query depend on the current server load (which Oracle cannot see). CPU costs are generally not as important unless the entire Oracle instance is using excessive CPU resources.

io_cost

The CBO had been enhanced to estimate the number of physical block reads required for an operation. The I/O cost is proportional to the number of physical data blocks read by the operation. However, the CBO has no a priori knowledge of the data buffer contents and cannot distinguish between a logical read (in-buffer) and a physical read. Because of this shortcoming, the CBO cannot know if the data blocks are already in the RAM data buffers.

Not quite perfect yet

Note that the costs are a function of the number of reads and the relative read times, plus the CPU cost estimate for the query. Also note that the external costing doesn't consider the number of data blocks that reside in the RAM data buffers, but a future release of the CBO is likely to consider this factor.

Here we see that Oracle uses both the CPU and I/O cost estimations in evaluating the execution plans. This equation becomes even more complex when we factor in parallel query, where many concurrent processes are servicing the query.

The best benefit for using CPU costing is for all_rows execution plans, where cost is more important than with first_rows optimization.

In my next column, I'll look at how the CBO is influenced by statistics. To make an intelligent decision about the best execution plan, the CBO must use information about all of the data objects that are involved in the query. Because you control how the statistics are collected, this is a critical aspect of CBO tuning.

If you like Oracle tuning, check-out my latest book "Oracle Tuning: The Definitive Reference", the best deal at 30% off, buying directly from the publisher.  Packed with almost 1,000 pages of Oracle performance tuning techniques, it's the foolproof way to find and correct Oracle bottlenecks.

Need an Oracle Health Check? Do you have bad performance after an upgrade?   Need to certify that your database follows best practices? BC Oracle performance gurus can quickly certify every aspect of your Oracle database and provide a complete verification that your database is fully optimized.