Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 






Track CPU and I/O cost with Oracle

September 2, 2015 - Donald Burleson

With each new release of Oracle, the database becomes more aware if its external environment. Oracle sets several important initialization parameters based on the number of CPUs on the Oracle server, and Oracle is now becoming more aware of the costs of CPU cycles and I/O operations.

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 )


#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:

Oracle full-table scan Illustration

Oracle Index access illustration


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.


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.


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.

Get the Complete
Oracle SQL Tuning Information 

The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational