Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



CPU Based Optimizer Costing

The new CPU Costing  feature, controlled by the _optimizer_cost_model=cpu  hidden parameter, enhances the CBO  capabilities by allowing it to estimate the number of machine cycles necessary for an operation.  This cost subsequently counts in the execution plan calculation. The CPU costs affiliated with servicing an Oracle query hinge on the current server load, which Oracle cannot see. Generally, CPU costs are not considered significant unless the entire Oracle instance is using excessive CPU resources.

I/O Costing

While the CBO   is now enhanced to figure the number of physical block reads required for an operation, it has not yet been quite perfected. For example, the CBO is not yet aware of the percentage of  a table’s blocks that reside in the data buffer.


The I/O cost is proportional to the number of physical data blocks read by the operation. However, the CBO has no prior information on the data buffer contents and cannot distinguish between a logical read (in-buffer) and a physical read. Due to this shortcoming, the CBO cannot know if the data blocks are already in the RAM data buffers.


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


External costing does not take into account the number of data blocks residing in the RAM data buffers; however, a future release of the CBO   is likely to incorporate this element.  Additionally, costs are a function of the number of reads and the relative read times plus the CPU cost estimate for the query.


In evaluating the execution plan , Oracle uses both the CPU and I/O cost estimations. This equation becomes even more complex when parallel querying is factored in when several concurrent processes are servicing the query.


Oracle Network affects Oracle performance and the AWR data can help users detect and fix data transmission issues.



This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference". 

You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts:




 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks