Thanks for the question, Robert.

Asked: November 09, 2009 - 2:22 pm UTC

Last updated: November 24, 2009 - 10:00 am UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hi Tom,

I'm from the Tom Kyte school of ignoring CBO query COSTS in favor of good old fashioned analytical tuning, knowing the data, logical i/o, clock time, and so forth.
However I'm wondering now in 10g and even more in 11g... is the simplistic method of tuning a query by COST becoming more valid/accurate?

Your thoughts?

Thanks!

Robert.

and Tom said...

As always - "it depends"

Comparing costs across queries can be quite tricky. You have to presume/assume the estimated card= values are absolutely dead on (major contributor to the cost). You have to assume that the cpu costing was perfect. Within a set of plans for the same query - we can compare the costs because these things are sort of uniform - the same - for those plans. But change the syntax and the optimizer might well estimate a card= value radically different - causing the cost to be influenced (negatively or postively). If the rewrite resulted in a better set of estimated cardinalities - the plan cost might well go up (to reflect more rows perhaps) - but the plan would probably be better (because the better the estimated card=values, the better the plan in general)

Consider this example:


ops$tkyte%ORA11GR2> create table t
  2  as select decode( mod(rownum,2), 0, 'N', 'Y' ) flag1,
  3            decode( mod(rownum,2), 0, 'Y', 'N' ) flag2, a.*
  4    from all_objects a
  5   where rownum <= 50000
  6  /

Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(flag1,flag2);

Index created.

ops$tkyte%ORA11GR2> begin
  2          dbms_stats.gather_table_stats
  3          ( user, 'T',
  4            method_opt=>'for all indexed columns size 254' );
  5  end;
  6  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where flag1='N' and flag2='N';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 12499 |  1232K|   208   (1)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    | 12499 |  1232K|   208   (1)| 00:00:03 |
--------------------------------------------------------------------------

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

   1 - filter("FLAG2"='N' AND "FLAG1"='N')

ops$tkyte%ORA11GR2> select /*+ index(t t_idx) */ * from t where flag1='N' and flag2='N';

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       | 12499 |  1232K|   397   (0)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     | 12499 |  1232K|   397   (0)| 00:00:05 |
|*  2 |   INDEX RANGE SCAN          | T_IDX | 12499 |       |    27   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("FLAG1"='N' AND "FLAG2"='N')

Now, you know and I know that the second plan there is better - we know that because WE KNOW that flag1=n and flag2=n never happens - but the optimizer doesn't (yet). The optimizer knows that flag1=n is 50% of the data, flag2=n is 50% of the data and it assumes (using high school statistics) that flag1=n and flag2=n = 50%*50% = 25% of the data (12,499 is about 25% of 50,000)

So, let's fix the card= value and re-evaluate

ops$tkyte%ORA11GR2> select /*+ dynamic_sampling(t 3) */ * from t where flag1='N' and flag2='N';

Execution Plan
----------------------------------------------------------
Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     4 |   404 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     4 |   404 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     4 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access("FLAG1"='N' AND "FLAG2"='N')

Now the cost is much lower and we can see this is the right plan. But, in the first run through, the full scan would have been the "right plan" given the cost compared to the index range scan.

It still comes back to "are the estimated cardinalities close to reality"

If they are not, the cost is going to be thrown off.
If they are - for all of the queries you compare - then they are probably comparable (assuming the cpu costing is good as well)

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Are the estimated cardinalities close reality

Matt T, November 10, 2009 - 2:16 pm UTC

Good paper here by Wolfgang Breitling. Tuning by cardinality feedback.
http://www.centrexcc.com/Tuning%20by%20Cardinality%20Feedback.pdf

Great example of an 'exception'

Robert, November 11, 2009 - 2:27 pm UTC

Tom,

That is a great black and white example of how the cost can be deceiving.

But do I detect a little bit of a relaxing of your previous hard stance on using the CBO cost as a valid tuning tool? Now that Oracle CBO is getting more and more sophisticated?

Thank you,

Robert.

November 15, 2009 - 12:36 pm UTC

how so? what did I say to convey that sense?

strange 11.2 behavior

Chris, November 16, 2009 - 10:36 pm UTC

Tom,

I took your example from above and tried using extended statistics (instead of dynamic_sampling) to fill the optimizer in on the correct cardinality. This works for me in 11.1, however, the cbo does not seem to pick up the extended stats in 11.2. Here's exactly what I'm running:

drop table t purge;

create table t as
select  decode(mod(rownum, 2), 0, 'N', 'Y') flag1,
        decode(mod(rownum, 2), 0, 'Y', 'N') flag2,
        a.*
from all_objects a
where rownum <= 50000
/

create index t_idx on t(flag1, flag2)
/

declare
        l_sgrp user_stat_extensions.extension_name%type;
begin
        l_sgrp := dbms_stats.create_extended_stats(user, 'T', '(flag1, flag2)');
end;
/


exec dbms_stats.gather_table_stats(user, 'T', method_opt => 'for all columns size skewonly')


begin
  for x in ( select /*+ gather_plan_statistics */ * from t
             where flag1 = 'N' and flag2 = 'N' ) loop
    null;
  end loop;
  dbms_output.put_line('===========');
  for x in ( select plan_table_output from table(
             dbms_xplan.display_cursor(null, null,
                        'allstats last')) ) loop
    dbms_output.put_line( chr(9) || x.plan_table_output );
  end loop;
  dbms_output.put_line('===========');
end;
/

And here's the 11.2 (Linux) output:

Table dropped.


Table created.


Index created.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

===========
 SQL_ID 7rpa4jjqr0qka, child number 0
 -------------------------------------
 SELECT /*+ gather_plan_statistics */ * FROM T WHERE FLAG1 = 'N' AND
 FLAG2 = 'N'
 
 Plan hash value: 1601196873
 
 ------------------------------------------------------------------------------------
 | Id  | Operation   | Name | Starts | E-Rows | A-Rows | A-Time  | Buffers |
 ------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT  |  | 1 |    |   0 |00:00:00.01 |     744 |
 |*  1 |  TABLE ACCESS FULL| T  | 1 |  12453 |   0 |00:00:00.01 |     744 |
 ------------------------------------------------------------------------------------
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 
    1 - filter(("FLAG2"='N' AND "FLAG1"='N'))
 
===========

PL/SQL procedure successfully completed.

Is there something else going on here, or is this a bug?

Thanks,
Chris

November 24, 2009 - 10:00 am UTC

I'm investigating....

Ok, the fix for bug 5483301 introduced this issue:

...
Details:
This fix introduce BUG:6082745
If a column has a frequency histogram and a query uses an equality predicate over
the column looking for a value that does not exists in the histogram then
the CBO was estimating a cardinality of 1.
This could favor Nested Loops too much.
The fix introduces a notable change in behavior:
instead of estimating a cardinality of 1 then with this fix CBO
estimates a cardinality as 0.5 times the cardinality of the least
popular value in the histogram.
Workaround:
Drop the frequency histogram that is on the predicate column.
Note: One off patches for this bug can introduce the problem described in bug 6082745.
This fix can be disabled by setting "_fix_control"='5483301:off'
....

The optimizer group is re-looking at this now.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Are the estimated cardinalities close reality

Great example of an 'exception'

strange 11.2 behavior

More to Explore

PL/SQL demos

PL/SQL docs

CBO Cost in 10g and 11g -- Valid Tuning Tool?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Are the estimated cardinalities close reality

Great example of an 'exception'

strange 11.2 behavior

More to Explore

PL/SQL demos

PL/SQL docs