RE: SQL statement with hints or without hints

Tim,

Have you ever found yourself needing to use the DBMS_STATS.SET_XXX_STATS routines to get your desired plans? And if so, did you do this on a trial and error basis, or did you use something like a 10053 trace to see inside the CBO's head to help you determine what values to use?

Just curious. Something I've never done but am contemplating for a couple of cases.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
Sent: Friday, April 12, 2002 1:03 PM
To: Multiple recipients of list ORACLE-L

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...

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

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).