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
- 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.
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.
posted by David Aldridge @ 9:00 AM
5 Comments:
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.
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.
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.
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.
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....
Post a Comment
<< Home