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

 

 
 

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 )

         -------------------------------------------------

                          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

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.

 

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