RE: OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING

The reason is that the 'FIRST_ROWS' parameter didn't give the expected results. We wanted index access strongly favored and setting OPTIMIZER_INDEX_COST_ADJ to 30 (we tried with 75 and 50, too) gave us the plans that we were expecting. The problem with cost based optimizer are the bind variables, when you're doing a range scan (COL1 between :a and :b). CBO assumes, even with the histograms that you're searching through the 25% of the table and generates a full scan. OPTIMIZER_INDEX_COST_ADJ set to 30 made all of our PRO*C and Oracle*Forms modules behave as expected. To tell the truth, I do not have any conclusions that I can generalize, because
my task was only to make sure that we do not suffer a huge performance hit when migrating to Oracle8i (from 7.3.4.4). There is also another way of doing
things, namely, setting CURSOR_SHARING to FORCE, in which case all of the bind
variables will be forcibly replaced by constants. This, I'm told, does not sit
very well with Oracle's own CASE products. I still have CURSOR_SHARING set to
EXACT, which is the default.

-----Original Message-----
Sent: Wednesday, June 06, 2001 9:36 PM
To: Multiple recipients of list ORACLE-L

> My goal was to strongly favor indexes and make CBO behave like RBO.

Mladen,

I would have considered setting optimizer_goal=first_rows and would appreciate hearing your ideas on why you decided to set OPTIMIZER_INDEX_COST_ADJ to 30 instead. Thank you.

• Greg

Sorry if this is a duplicate ... the mail server bounced the original.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Greg Moore
  INET: sqlgreg_at_pacbell.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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Gogala, Mladen
  INET: MGogala_at_oxhp.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).