The COST column of EXPLAIN PLAN

The COST column cannot be used to compare execution plans. Punkt.

A discussion I have had any number of times is what the COST column of EXPLAIN PLAN means. Many people are under the impression that if a plan has a higher cost than another, it is worse. That is simply not true. The COST column is meaningful only within a plan: it shows the relative cost of each operation. It is meaningless to compare the COST of different plans. I came across an example recently, here is a simplified version, comparing two equivalent SQLs:

orcl> create table foo as select * from all_objects;

Table created.

orcl> explain plan for select count(*) from (select * from foo union all select * from foo);

Explained.

orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------

Plan hash value: 242850384

-----------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |     1 |   512   (2)| 00:00:09 |
|   1 |  SORT AGGREGATE              |      |     1 |            |          |
|   2 |   VIEW                       |      |   149K|   512   (2)| 00:00:09 |
|   3 |    UNION-ALL                 |      |       |            |          |
|   4 |     TABLE ACCESS STORAGE FULL| FOO  | 74552 |   256   (2)| 00:00:05 |
|   5 |     TABLE ACCESS STORAGE FULL| FOO  | 74552 |   256   (2)| 00:00:05 |
-----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

16 rows selected.

orcl> explain plan for select sum(c) from (select count(*) c from foo union all select count(*) c from foo);

Explained.

orcl> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------

Plan hash value: 3291583229

--------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |     1 |    13 |   508   (1)| 00:00:09 |
|   1 |  SORT AGGREGATE               |      |     1 |    13 |            |          |
|   2 |   VIEW                        |      |     2 |    26 |   508   (1)| 00:00:09 |
|   3 |    UNION-ALL                  |      |       |       |            |          |
|   4 |     SORT AGGREGATE            |      |     1 |       |            |          |
|   5 |      TABLE ACCESS STORAGE FULL| FOO  | 74552 |       |   254   (1)| 00:00:05 |
|   6 |     SORT AGGREGATE            |      |     1 |       |            |          |
|   7 |      TABLE ACCESS STORAGE FULL| FOO  | 74552 |       |   254   (1)| 00:00:05 |
--------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

18 rows selected.

orcl>

The COSTs of the plans are virtually identical. So the executions will be identical too, right? Not at all. Look at what actually happens:
orcl> alter session set statistics_level=all;

Session altered.

orcl> select count(*) from (select * from foo union all select * from foo);

  COUNT(*)
----------
    145808

orcl> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------

SQL_ID  5wn0myv3kcsvs, child number 0
-------------------------------------
select count(*) from (select * from foo union all select * from foo)

Plan hash value: 242850384

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |      1 |00:00:02.02 |    2086 |   2080 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |      1 |00:00:02.02 |    2086 |   2080 |
|   2 |   VIEW                       |      |      1 |    149K|    145K|00:00:01.79 |    2086 |   2080 |
|   3 |    UNION-ALL                 |      |      1 |        |    145K|00:00:01.30 |    2086 |   2080 |
|   4 |     TABLE ACCESS STORAGE FULL| FOO  |      1 |  74552 |  72904 |00:00:00.20 |    1043 |   1040 |
|   5 |     TABLE ACCESS STORAGE FULL| FOO  |      1 |  74552 |  72904 |00:00:00.14 |    1043 |   1040 |
--------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


21 rows selected.

orcl> select sum(c) from (select count(*) c from foo union all select count(*) c from foo);

    SUM(C)
----------
    145808

orcl> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------

SQL_ID  gq7ca01amc88s, child number 0
-------------------------------------
select sum(c) from (select count(*) c from foo union all select
count(*) c from foo)

Plan hash value: 3291583229

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |      1 |00:00:00.57 |    2086 |   2080 |
|   1 |  SORT AGGREGATE               |      |      1 |      1 |      1 |00:00:00.57 |    2086 |   2080 |
|   2 |   VIEW                        |      |      1 |      2 |      2 |00:00:00.57 |    2086 |   2080 |
|   3 |    UNION-ALL                  |      |      1 |        |      2 |00:00:00.57 |    2086 |   2080 |
|   4 |     SORT AGGREGATE            |      |      1 |      1 |      1 |00:00:00.30 |    1043 |   1040 |
|   5 |      TABLE ACCESS STORAGE FULL| FOO  |      1 |  74552 |  72904 |00:00:00.18 |    1043 |   1040 |
|   6 |     SORT AGGREGATE            |      |      1 |      1 |      1 |00:00:00.27 |    1043 |   1040 |
|   7 |      TABLE ACCESS STORAGE FULL| FOO  |      1 |  74552 |  72904 |00:00:00.15 |    1043 |   1040 |
---------------------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


24 rows selected.

orcl>
Check out the A-Time column. The second SQL was nearly four times as quick as the first (if you want to know why, think about the number of rows being returned through the various steps of the plan.)

Here's another example where the SQLs aren't merely equivalent: they are identical. Changing a parameter that has no effect on execution doubles the cost:

orcl> sho parameter optimizer_index_cost_adj

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_index_cost_adj             integer     100
orcl> set autot on exp
orcl> select * from emp where empno=1000;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=1000)

orcl> alter session set optimizer_index_cost_adj=10000;

Session altered.

orcl> select * from emp where empno=1000;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    38 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPNO"=1000)

orcl>

So no more debates about COST, please: it cannot be used to compare plans. It's purpose is to identify the relative cost of steps within a plan.

Comments

The Oracle documentation seems to suggest that the COST column actually "is useful for comparing execution plans"[1].

[1] Oracle Database Performance Tuning Guide, Customizing PLAN_TABLE Output: http://docs.oracle.com/cd/E11882_01/server.112/e16638/ex_plan.htm#autoId13

I don't entirely agree - I believe your test case is flawed.

It is true that the UNION ALL and VIEW steps of the plan process more rows in the longer example, but these are pass-through steps in the plan with negligible overhead. Both examples perform exactly the same amount of IO and with some very small variations they will have nearly identical performance. In fact, I think Oracle has got it spot on: there is a tiny cost to the UNION ALL and VIEW steps with all of those extra rows, and Oracle reports a tiny increase in the COST value.

What I believe has happened in your test case is a case of Observer Effect: the use of ALTER SESSION SET STATISTICS_LEVEL=ALL has blown out the cost of those pass-through steps because of all of the internal stats capturing that is going on. Those steps have gone from low-cost pass-throughs to massive IO bottlenecks - just because we are observing their effect.

Here is an example without the statistics captured. I have run the UNION 20 times to magnify any effect it might have. We can see that the "Fast" version (run first here) is actually a fraction slower, however the difference is negligible (4.23s vs 4.20s). Also note that the IO for the two is identical.

Scroll down to see a 2nd test case with the STATISTICS_LEVEL applied

    SUM(A)                                                                      
----------                                                                      
  18376860                                                                      

Elapsed: 00:00:04.23

Execution Plan
----------------------------------------------------------                      
Plan hash value: 2263678124                                                     
                                                                                
------------------------------------------------------------------------------  
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT      |      |     1 |    13 | 24626   (1)| 00:04:56 |  
|   1 |  SORT AGGREGATE       |      |     1 |    13 |            |          |  
|   2 |   VIEW                |      |    20 |   260 | 24626   (1)| 00:04:56 |  
|   3 |    UNION-ALL          |      |       |       |            |          |  
|   4 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|   5 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|   6 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|   7 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|   8 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|   9 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  10 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  11 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  12 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  13 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  14 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  15 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  16 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  17 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  18 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  19 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  20 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  21 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  22 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  23 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  24 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  25 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  26 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  27 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  28 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  29 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  30 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  31 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  32 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  33 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  34 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  35 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  36 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  37 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  38 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  39 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  40 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  41 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  42 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  43 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
------------------------------------------------------------------------------  
                                                                                
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement (level=2)                         


Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
      88740  consistent gets                                                    
      88660  physical reads                                                     
          0  redo size                                                          
        481  bytes sent via SQL*Net to client                                   
        419  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     


  COUNT(*)                                                                      
----------                                                                      
  18376860                                                                      

Elapsed: 00:00:04.20

Execution Plan
----------------------------------------------------------                      
Plan hash value: 3024108014                                                     
                                                                                
---------------------------------------------------------------------           
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     |           
---------------------------------------------------------------------           
|   0 | SELECT STATEMENT     |      |     1 | 24718   (1)| 00:04:57 |           
|   1 |  SORT AGGREGATE      |      |     1 |            |          |           
|   2 |   VIEW               |      |    15M| 24718   (1)| 00:04:57 |           
|   3 |    UNION-ALL         |      |       |            |          |           
|   4 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   5 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   6 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   7 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   8 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   9 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  10 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  11 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  12 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  13 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  14 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  15 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  16 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  17 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  18 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  19 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  20 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  21 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  22 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  23 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
---------------------------------------------------------------------           
                                                                                
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement (level=2)                         


Statistics
----------------------------------------------------------                      
          0  recursive calls                                                    
          0  db block gets                                                      
      88740  consistent gets                                                    
      88660  physical reads                                                     
          0  redo size                                                          
        483  bytes sent via SQL*Net to client                                   
        419  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     

In the next example, I have run the ALTER SESSION SET STATISTICS_LEVEL=ALL

Remember previously both were running in about 4.2s? Now the "fast" version is 2m13s, and the "slow" version is a whopping 8m37s.

I don't think this tells us anything meaningful about the COST in Explain Plan, except that enabling STATISTICS_LEVEL can be expected to massively affect performance.

    SUM(A)                                                                      
----------                                                                      
  18376860                                                                      

Elapsed: 00:02:13.47

Execution Plan
----------------------------------------------------------                      
Plan hash value: 2263678124                                                     
                                                                                
------------------------------------------------------------------------------  
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |  
------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT      |      |     1 |    13 | 24626   (1)| 00:04:56 |  
|   1 |  SORT AGGREGATE       |      |     1 |    13 |            |          |  
|   2 |   VIEW                |      |    20 |   260 | 24626   (1)| 00:04:56 |  
|   3 |    UNION-ALL          |      |       |       |            |          |  
|   4 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|   5 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|   6 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|   7 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|   8 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|   9 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  10 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  11 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  12 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  13 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  14 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  15 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  16 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  17 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  18 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  19 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  20 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  21 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  22 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  23 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  24 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  25 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  26 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  27 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  28 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  29 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  30 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  31 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  32 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  33 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  34 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  35 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  36 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  37 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  38 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  39 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  40 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  41 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
|  42 |     SORT AGGREGATE    |      |     1 |       |            |          |  
|  43 |      TABLE ACCESS FULL| FOO  |   771K|       |  1231   (1)| 00:00:15 |  
------------------------------------------------------------------------------  
                                                                                
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement (level=2)                         


Statistics
----------------------------------------------------------                      
         62  recursive calls                                                    
          0  db block gets                                                      
      89960  consistent gets                                                    
      88660  physical reads                                                     
          0  redo size                                                          
        481  bytes sent via SQL*Net to client                                   
        419  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed                                                     


  COUNT(*)                                                                      
----------                                                                      
  18376860                                                                      

Elapsed: 00:08:37.17

Execution Plan
----------------------------------------------------------                      
Plan hash value: 3024108014                                                     
                                                                                
---------------------------------------------------------------------           
| Id  | Operation            | Name | Rows  | Cost (%CPU)| Time     |           
---------------------------------------------------------------------           
|   0 | SELECT STATEMENT     |      |     1 | 24718   (1)| 00:04:57 |           
|   1 |  SORT AGGREGATE      |      |     1 |            |          |           
|   2 |   VIEW               |      |    15M| 24718   (1)| 00:04:57 |           
|   3 |    UNION-ALL         |      |       |            |          |           
|   4 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   5 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   6 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   7 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   8 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|   9 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  10 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  11 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  12 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  13 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  14 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  15 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  16 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  17 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  18 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  19 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  20 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  21 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  22 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
|  23 |     TABLE ACCESS FULL| FOO  |   771K|  1236   (1)| 00:00:15 |           
---------------------------------------------------------------------           
                                                                                
Note                                                                            
-----                                                                           
   - dynamic sampling used for this statement (level=2)                         


Statistics
----------------------------------------------------------                      
         61  recursive calls                                                    
          0  db block gets                                                      
      89960  consistent gets                                                    
      88660  physical reads                                                     
          0  redo size                                                          
        483  bytes sent via SQL*Net to client                                   
        419  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                  
          0  sorts (memory)                                                     
          0  sorts (disk)                                                       
          1  rows processed              

For the record: my opinion on the COST column of Explain Plan - even though I don't use it - is that it is comparable when evaluating two plans for the SAME SQL at the SAME TIME on the SAME DATABASE with the SAME parameter settings.

With 2 different SQLs, the COST is somewhat comparable, but I would be wary of using it.
However if you change the database, move the database to a different machine, or change either of the Index Cost parameters, all bets are off. I would be wary of comparing changing costs over time as well, although I have no evidence to back this up.


Ross Leishman

The COST article I keep referring to ==> http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/

It is true that the UNION ALL and VIEW steps of the plan process more rows in the longer example, but these are pass-through steps in the plan with negligible overhead. Both examples perform exactly the same amount of IO and with some very small variations they will have nearly identical performance. In fact, I think Oracle has got it spot on: there is a tiny cost to the UNION ALL and VIEW steps with all of those extra rows, and Oracle reports a tiny increase in the COST value.

What I believe has happened in your test case is a case of Observer Effect: the use of ALTER SESSION SET STATISTICS_LEVEL=ALL has blown out the cost of those pass-through steps because of all of the internal stats capturing that is going on. Those steps have gone from low-cost pass-throughs to massive IO bottlenecks - just because we are observing their effect.

Isn't that what I said? Verbatim?

bobbydurrettdba's picture

Thanks for your post. I often cringe when someone (dba or developer) tells me that a plan's cost is lower so it should be be better. I think what people don't understand is that there are a lot of reasons why the cost could not relate to the actual run time. Your first example is a little funky in terms of why the two run times are so different but of course it does show that runtimes and costs do not always match up. I guess your first example shows that there can be factors at work that the optimizer isn't taking into consideration such as the STATISTICS_LEVEL if that is really the source of the difference. The second example is better because it shows that the CBO's estimates of the cost of a given operation such as an index scan or full scan can be way off. Also, the CBO's cardinality estimates can be way off.

I can't prove it but my contention is that if you are trying to tune a slow query there is a good chance that either the CBO is wrong about the cardinality in some step or the cost of operating that type of operation per row is off. Of course some queries just need an index or something simple. But a query that looks like it should run fast and doesn't seems like there is usually something that is throwing the cost way off. I think cardinality mis estimates are much more frequent than people generally realize because the where clause predicates are not truly independent and human developers don't understand that the relationships between the conditions in their queries can't be understood by the optimizer.

- Bobby

My blog post on this topic: http://www.bobbydurrettdba.com/2013/03/01/dont-focus-on-cost-of-execution-plan/

It is wrong if say: "COST column is meaningful only within a plan: it shows the relative cost of each operation. It is meaningless to compare the COST of different plans". The Cost is an virtual column, the Cost is not a simply issue from which CPU used or magic SQL identified by value of piece. In same configuration parameter, same logical object contents or structure, same plan but less Cost is useful than more Cost value.