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



Oracle Server Metrics and SQL Execution

When determining the best execution plan for an SQL query, the newly enhanced cost-based optimizer (CBO) considers external influences.  Since the Oracle database does not run in a vacuum, the CBO must have the ability to factor in the costs of external disk I/O as well as the cost of CPU cycles for each SQL operation.  This is a significant step forward in making the CBO one of the most sophisticated software packages in the world.  Choosing the best execution plan for any SQL statement is always the job of the CBO and is no small challenge.


In accordance with Oracle documentation, the I/O and CPU costs are estimated 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

The external costing is markedly influenced by the calculated cost of disk reads as measured by the v$ tables and the estimated CPU costs associated with each internal operation. By storing the details regarding the costs of many components of SQL execution, Oracle can use these average costs to influence the choices made by the cost-based SQL optimizer. Here are some examples:

Hash join costs: Oracle records the average amount of RAM memory consumed by a hash join.

Sort costs: Oracle tracks the RAM necessary for sorting and aggregation operations.

Table scan costs: Oracle keeps information about the amount of time that is essential to performing a multiblock read such as db file scatter reads .

Index block access costs: Oracle stores the average time required to fetch a single block such as db file sequential reads.

Depending on the optimizer_mode choice, Oracle costs are weighed differently.  If the all_rows optimizer mode is utilized for a data warehouse, the CBO will be heavily influenced by external factors due to the fact that the all_rows mode is designed to minimize resource consumption. On the other hand, if there is an OLTP system with the first_rows optimizer mode, the CBO deems it more imperative to return rows quickly than to minimize resource costs.



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