| 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
I have heard alot of people complain that CURSOR_SHARING force causes 600 errors, I haven't seen it but I stay away from that setting.
-----Original Message-----
To: Multiple recipients of list ORACLE-L
Sent: 6/7/01 1:06 PM
I have cursor_sharing set to FORCE (but we don't run Case)
we found that the programmers had coded statements with constants....
and we
were hitting shared pool problems. So I set it to FORCE and suddenly
things
are nice and quiet
It does sometimes change queries that are non-repeating (which causes a slight performance hit on those) but since those are ad-hoc queries as opposed to the ones that run most of the time, I can live with a (barely
noticeable) performance hit.
Rachel
>From: "Gogala, Mladen" <MGogala_at_oxhp.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING
>Date: Thu, 07 Jun 2001 07:51:31 -0800
>
>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).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: carmichr_at_hotmail.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Christopher Spence INET: cspence_at_FuelSpot.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).Received on Thu Jun 07 2001 - 13:21:53 CDT
 |
 |