RE: CBO irregularity
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 7 Jun 2004 15:08:38 -0500
It's what CBO is built to do: take into account a tremendous number of
variables, each of which has a right to influence the optimizer's choice for
best plan. CBO tends to work pretty well when (a) you give it the
information it needs, and (b) you let it do its job (okay, and maybe also
"(c) you wait 'til 9.2 to use it").
By (a), I mean things like:
- Make sure that your schema statistics reasonably represent your
data. For example, if you tell CBO that a million-row table has 100 rows in
it, then CBO will make dumb decisions about what to do with the table.
- Make sure that your system statistics reasonably represent the
operational characteristics of your system. For example, if your system
really averages 3.7 blocks per multi-block read, then CBO will make dumb
decisions about whether to do full-table scans on systems were
db_file_multiblock_read_count=128.
- Collect histograms for skewed data. For example, if you tell CBO
that an attribute with domain cardinality 2 has uniformly distributed
values, then CBO will make dumb decisions if the real distribution is 99/1
instead of 50/50.
- Use informative hints. For example (from a Tom Kyte training
course I attended), use ALL_ROWS, FIRST_ROWS(n), FIRST_ROWS, CHOOSE,
(NO)REWRITE, DRIVING_SITE, (NO)PARALLEL, (NO)APPEND, CURSOR_SHARING_EXACT,
DYNAMIC_SAMPLING, and CARDINALITY.
By (b), I mean things like:
- Don't use plan-restricting hints, except in test situations where
you're trying to cause bad performance. For example, don't use hints like
RULE, ORDERED, USE_NL, INDEX, USE_HASH, FULL, AND_EQUAL, etc. in production.
If you really want plan stability, stored outlines can give you that. But
the adaptive intelligence of a well-informed and properly functioning CBO is
a smarter long-term decision except in rare cases.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
Nullius in verba
Upcoming events:
- Performance <http://www.hotsos.com/courses/PD101.php> Diagnosis 101: 6/22
Pittsburgh, 7/20 Cleveland, 8/10 Boston
- SQL Optimization <http://www.hotsos.com/courses/OP101.php> 101: 5/24 San
Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Rick Stephenson
Sent: Monday, June 07, 2004 2:36 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: CBO irregularity
The CBO has been nothing short of a pain in the butt to me. Going from
Development to QA to a live environment achieves unexpected results. It
seems that you never know what you are going to get when it comes to an
execution plan. The developers run Oracle on their Windows box and the
execution path is one way, but when it gets moved to a QA environment it
chooses another way. At least with the RULE base optimizer you know what
you are going to get.
Sometimes I think I am the only one with this problem. How do you work
this? Do you always use hints, do you use stored outlines..?
Thanks,
Rick Stephenson
Other related posts:
