| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING
About CURSOR_SHARING - EXACT means that SQL statements are not changed, and FORCE means that constants are changed to bind variables.
Alex Hillman
-----Original Message-----
Sent: Thursday, June 07, 2001 11:52 AM
To: Multiple recipients of list ORACLE-L
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.
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).
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).
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). Received on Thu Jun 07 2001 - 12:36:37 CDT
 |
 |