RE: CBO irregularity

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 7 Jun 2004 15:08:38 -0500
Message-ID: <016f01c44ccb$3b7e6df0$6801a8c0@CVMLAP02>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rick Stephenson
Sent: Monday, June 07, 2004 2:36 PM
To: oracle-l_at_freelists.org
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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Jun 07 2004 - 15:07:17 CDT