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 







Oracle SQL execution plan cost column tips

Oracle Database Tips by Donald BurlesonMay 3, 2015

Question:  I have a SQL explain plan (execution plan) and it shows a cost of 16.  What does this cost column mean?  Can I use these costs for SQL tuning?

Answer:  No, the cost column in an execution plan is not a reliable way to judge the real costs of a SQL statements response time.  Many people assume that an execution plan with a lower cost will run faster than a plan with a higher cost, but this is not true, especially in-light of the optimizer_mode parameter setting, which could be set to all_rows, which minimizes resource consumption, as opposed to first_rows_n, which minimizes response time.  These are very different costs, and not the costs that appear in the cost column of an explain plan.

The cost column is supposed to be a guess of the number of single block disk reads required, but it's not very useful for SQL tuning. 

What is the cost column in an explain plan?

Depending on your release and setting for the hidden parameter _optimizer_cost_model (cpu or io), the cost is taken from the cpu_cost and io_cost columns in the plan table (, in turn, estimates from sys.aux_stats$.  The "cost" column is not any particular unit of measurement, it is a weighted average of the costs derived from the cost-based decision tree generated when the SQL statement is bring processed.  The cost column is essentially an estimate of the run-time for a given operation.

In sum, the cost column is not valuable for SQL tuning, because the "best" execution plan may not be the one with the lowest cost.

As a review, there are dozens of conditions that go into the costing algorithm of the cost-based optimizer:

The costing is done via a mathematical model that builds a decision tree, and Oracle chooses the plan with the lowest cost.  However, this algorithm is constantly changing as Oracle refines their software and you can only make very general statements about it's behavior.
The cost-based optimizer was originally written back when disk was super-expensive and minimizing disk I/O was a major goal in SQL tuning.   Today, of course, many systems use super-fast flash memory (solid-state disks), and I/O is not so much of an issue.  But nonetheless, estimated disk "costs" are behind the optimizer's estimates of cost.
In the example below, we see that the optimizer estimates four physical disk reads to service a nested loops join, and hence we see a cost=4:

| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT             |         |     1 |    57 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    57 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |     1 |    37 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |

If we estimate this "cost" based on the average sampled I/O latency (gathered by dbms_stats.gather_system_stats), we might be able to estimate the run-time for a query.  In this example, assume that our average disk I/O delay is 15 milliseconds.  A "cost" of 4 would mean that the optimizer expects the query to take roughly 60 milliseconds to complete.
This feature of the optimizer to do costing in terms of physical disk reads is one reason why the optimizer buffering parameters like optimizer_index_caching are important when tuning a SQL workload. 
The best vs. the cheapest plan
The "cost" figures that are displayed in a SQL execution plan do not always reflect the "real" costs of a query.  Oracle may choose a plan with a higher cost, depending upon the optimizer's goal (e.g. first_rows vs. all_rows).
Remember, the "Cost" figures are very misleading and should not be used as guidelines for SQL tuning for several reasons:
  • The optimizers costing can be wrong because of stale or missing metadata (especially histograms)

  • The lowest cost value does not always indicate the "real" lowest cost that is select by the optimizer.

  • In first_rows optimization, extra I/O is required to access the data via indexes and get the row back quickly. Hence, the optimizer may choose a more expensive plan because it will return rows faster than a "cheaper" plan that uses less machine resources.

  • Even with complete metadata it is impossible for any optimizer to always "guess" the accurate size of a result set, especially when  the where clause has complex column calculations.

In other words, Oracle does not always choose the lowest real "cost" value, and Oracle does not disclose the exact behavior of their optimizer, a prized competitive edge over other competing databases. 
For example, consider a million row table that takes 30 seconds to full scan.  Now consider a SQL that returns 10,000 rows in sorted order via a index where clustering_factor approaches num_rows, and two possible execution plans:

  • OPTION 1 - Do an index scan - More I/O (and cost) to traverse the index top pull the rows in sorted order, but the query starts delivering rows immediately.

  • OPTION 2 - Do a full table scan and back-end sort - his may involve far less work (and less cost), but we will not see any results for 30 seconds

See, in this example, the optimizer cannot always assume that the least cost plan is the "best" plan when optimizing for fast response time.

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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.