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.