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" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
>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: [EMAIL PROTECTED] (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).
_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
