The COST column of EXPLAIN PLAN
Submitted by John Watson on Sun, 2012-09-16 01:48
The COSTs of the plans are virtually identical. So the executions will be identical too, right? Not at all. Look at what actually happens:
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.
»
- John Watson's blog
- Login to post comments
COST is useful for comparing execution plans
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
Observer Effect
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 processedIn 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 processedFor 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
COST caculation
The COST article I keep referring to ==> http://jonathanlewis.wordpress.com/2006/12/11/cost-is-time/
ALTER SESSION SET STATISTICS_LEVEL=ALL
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.
Is there an echo in here?
Isn't that what I said? Verbatim?