Thoughts on Cost Based Optimization

Just a brief note.

In general terms there are three elements to getting a good execution plan out of Oracle’s Cost Based Optimizer (CBO).

The first of these is to give Oracle sufficient information about the data – “metadata”. This means, for example:

  • Using constraints where possible to indicate uniqueness, nullability, foreign key relations
  • Using statistics to indicate data volumes and distributions

The second of these is to give Oracle sufficient information about the system on which the instance is running. This means, for example:

  • Correct values for initiation parameters such as db_file_multiblock_read_count, cpu_count.
  • Before 9i, appropriate values for optimizer_index_cost_adj
  • From 9i upwards, use of DBMS_Stats.Gather_System_Stats() to benchmark the hardware.

The third of these is to write SQL that the optimizer can work with. I have no examples of this, but I have an instinctive feeling that it is possible to write suboptimal SQL that is “difficult” to optimize. If I think of anything more substantial then I’ll update.

So given that you believe that you have a CBO-related problem, I would propose that there are three categories of investigation: Metadata, Configuration, and SQL.

I can’t decide whether this is blindingly obvious to everyone or a sudden flash of meaningful insight. Is there a way of conducting a poll in this blog? Just post a comment. “Tautology” or “Genius”.

As always, thoughts are welcome, particularly on that last point but on anything herein of course.

Advertisements

5 thoughts on “Thoughts on Cost Based Optimization

  1. For your last point… Now it is true this is a single user test and with 10,000 concurrent users, the second query might somehow perform better (maybe when 10,000 people do it, the IO’s go down — kidding)…

    but not knowing what SQL functionality exists (analytics keep popping up over and over and over again) — you are killing yourself. Both the following queries do the same thing:

    create table emp
    as
    select mod(rownum,100)+1 deptno, object_id sal, rpad(‘*’,60,’*’) data
    from all_objects;
    create index emp_deptno_idx on emp(deptno);

    exec dbms_stats.gather_table_stats( user, ‘EMP’, cascade=>true );

    set autotrace traceonly statistics
    alter session set sql_trace=true;

    select deptno,
    count(*),
    percentile_cont(0.5) within group (order by sal) med
    from emp
    group by deptno
    /

    SELECT deptno, AVG(DISTINCT sal)
    FROM (SELECT cp1.deptno, CP1.sal
    FROM emp CP1, emp CP2
    where cp1.deptno = cp2.deptno
    GROUP BY cp1.deptno, CP1.sal
    HAVING SUM(DECODE(CP1.sal, CP2.sal, 1, 0)) >=
    ABS(SUM(SIGN(CP1.sal – CP2.sal))))
    group by deptno
    /

    but go ahead and run them and see what you see :)

    I saw 28.43 cpu seconds for the second query and 0.13 for the first…

    Yes, knowing the full capability of SQL is vital.

    Or you can just buy faster cpus, more ram, solid state disks and get a faster network.

  2. take a look at http://help.blogger.com/bin/answer.py?answer=760&topic=40 if you are sad enough to want a poll!

    How would we know if the CBO struggles to find the best plan – I have seen (today even) user queries on a DWH that get a poor plan because they asked the WRONG question. Like selecting from master/detail range partitioned tables without joining the tables by the range key or adding redundant joins.

  3. TK, yup those analytics are the bees knees. And I’m also thinking of some other cases which might be more subtle, shall we say.

    I have this thought that partition pruning is one (strongly version dependent) example of this. I think it’s possible to rewrite a query (particularly in the dreaded snowflake schema) so that it does and doesn’t use partition pruning against the fact table. I’m sure that I recall instances in 8iR2 where placing a predicate on a column of a dimension table would not provoke partition pruning, where pushing the predicate into, or “closer to” the fact table would.

    Or maybe not — darned if I have the time to go test this right now. Maybe it was just a general difference in the execution plan caused by the optimizer not inferring correct cardinality in the fact table … um … lemme think about that.

  4. Pete,

    I think you have two excellent blog topics there …

    i) The sadness of poles (the opinion sort, not the nationality).

    ii) That other thing about databases that I don’t understand.

  5. Ok, a more common one that I see:

    select a.key,
    b.value obs1,
    c.value obs2,
    d.value obs3,

    z.value obs25
    from t A,
    (select * from t where obs=1) b,
    (select * from t where obs=2) c,

    (select * from t where obs=25) z

    where a.key = b.key(+)
    and a.key = c.key(+)
    and a.key = …
    and a.key = z.key(+)
    /

    instead of

    select key,
    max(decode(obs,1,value)),
    max(decode(obs,2,value)),
    ..
    max(decode(obs,25,value)),
    from t
    group by key;

    The infamous outer join the table to itself as many times as possible trick.

    But I think the biggest problem is our algorithms. It’ll be quite a while before an optimizer can fix:

    for x in (select * from t)
    loop
    insert into t2 values T;
    end loop;

    or a do it yourself nested loops join…. thinking in SETS :) I swear I’ve dreamt in SQL with sets once or twice….

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s