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

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

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.

 

SEE CODE DEPOT FOR FULL SCRIPTS


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:

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

 

 

   

 Copyright © 1996 -2016 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