Re: SQL statement with hints or without hints

Instructing people to put hints in all of their code is the same as using CBO full-time, because CBO hints automatically enable the CBO. Using CBO full-time and not analyzing everything is asking for trouble...

Using RBO is unnecessary if you are using Oracle8 v8.0 or above. The CBO outperforms RBO in any situation except queries against the data dictionary (because you cannot analyze the data dictionary). Please read my paper "Search for Intelligent Life in the CBO" (online at www.evdbt.com/library.htm) for an explanation as to why it is crucial to set OPTIMIZER_MODE=CHOOSE (not FIRST_ROWS or ALL_ROWS) and use the OPTIMIZER_INDEX_CACHING and (cautiously!) the OPTIMIZER_INDEX_COST_ADJ parameters. Especially in Oracle8i, the CBO chooses dramatically superior access plans over the RBO...

Embedding hints in SQL will eventually cripple your system as conditions change, as will continued use of the RBO. Instructing people to embed hints on a wholesale basis is not the correct approach, in my opinion. Instead, I'd recommend setting the above-mentioned parameters appropriately (read the paper!), analyzing everything (use the GATHER_xxx_STATS procedures in the DBMS_STATS package if using Oracle8i or above), and trust the CBO. The myths about it "not working" or "not being trustworthy" have not been true for years. For situations where it doesn't choose an index where you think it should (or mistakenly chooses one that you think it should't), consider analyzing involved columns also to deal with any possible data skew problems by creating "histograms".

As Dr. Evil says, "I'm the boss. I need the info". Give the CBO the info and it will choose the right plan. Like that analogy? I do... :-)

I know the CBO works because I have made a living out of tuning Oracle-based applications for the past 6-7 years straight, working for Oracle and later as an independent. Lately (i.e. past 2 years or so), SQL tuning has mainly consisted of *removing* hints (both the old "pre-CBO" hints like "+0" and "||''" as well as CBO hints) and analyzing appropriately (i.e. "need the info!"). Nothing gets your attention like something that affects your wallet, so I've been betting my hourly income on these facts. I would have noticed if I was wrong by now... :-) "Would you like fries with that, ma'am?"

Hints should be used only as they were originally intended: infrequent use to deal with the extremely rare circumstances when the CBO cannot choose the best path. Nothing is perfect, but the CBO in Oracle8i and above is the best there is...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Friday, April 12, 2002 8:03 AM

> Hi all,
>
> We are running a kind of hybrid mode, mainly RBO with some tables analyzed
> (mainly for intermedia). Last week we had an interesting situation, when
> tuning a huge SQL, we created an index and the query worked fine, but
later
> in the day developers complained that their queries are running slower.
>
> We finally took out the new index and things were fine after that, but
this
> brought out few important issues,
>
> 1. Developers do not put hints in their SQL statements.
> 2. They implicitly reply on a set of indexes that have worked for them.
> 3. Due to reasons of 1 and 2, no new indexes can be created because it
will
> make their queries run slower.
> 4. As an effect of 3, new queries that can't make use of these set indexes
> will always be slower. Some of these queries can't be rewritten to make
use
> of the existing indexes due to business logic involved.
>
> My question is, do you, in your organization recommend putting hints in
the
> SQLs all the time, some times or not at all ?? It doesn't really matter
if
> you use RBO or CBO.
>
> When the developers questioned me, I told them 'Oracle optimizer is not an
> exact science, especially in a mixed RBO/CBO mode, so it is bound to make
> some wrong choices and that is precisely why Oracle calls these things as
> "hints", so we tell the optimizer to do the right thing."
>
> I am in a good mood to write a short note explaining developers why they
> (must) use hints in their SQL statements.
>
> Your input is greatly appreciated
> TIA
> Raj
> ______________________________________________________
> Rajendra Jamadagni MIS, ESPN Inc.
> Rajendra dot Jamadagni at ESPN dot com
> Any opinion expressed here is personal and doesn't reflect that of ESPN
Inc.
>
> QOTD: Any clod can have facts, but having an opinion is an art!
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Tim Gorman
  INET: Tim_at_SageLogix.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).