Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING

RE: OPTIMIZER_INDEX_COST_ADJ & OPTIMIZER_INDEX_CACHING

From: Thapliyal, Deepak <DThapliyal_at_ea.com>
Date: Thu, 07 Jun 2001 12:25:36 -0700
Message-ID: <F001.00320DC5.20010607114715@fatcity.com>

we hit a ora-600 related to cursor sharing=force and wws advised us an unusual fix --> setting sort area = sort retained .. funny thing is that this fix solved our problem ..

Deepak

-----Original Message-----
Sent: Thursday, June 07, 2001 11:20 AM
To: Multiple recipients of list ORACLE-L

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



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: 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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thapliyal, Deepak
  INET: DThapliyal_at_ea.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 - 14:25:36 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US