- "A. Bardeen" <abardeen1_at_yahoo.com> wrote:
> Brijesh,
>
> Even though OPTIMIZER_MODE=RULE, the CBO will be
> used
> in the presence of other features (e.g.
> partitioning,
> degree of parallelism, etc...).
>
> In this case TEST is getting a better plan because
> the
> CBO is being used. You can tell this from several
> features in the explain plan:
> 1. Cost = 169
> Only the CBO calculates costs.
> 2. Only the CBO uses hash joins.
>
> The first thing I'd check is the degree of
> parallelism
> on the tables AND indexes. In 8.0.5 and 8.1.5 the
> CBO
> is used when the indexes have a degree of
> parallelism
> > 1 even if the tables don't (see note: 70008.1).
>
> If you follow the flow chart in Note: 66484.1 "Which
> Optimizer is Used ?" (looks best if you click on
> the
> "fixed font" above the heading for the note) you
> should find what's causing the CBO to be used on
> Test.
>
> HTH,
>
> -- Anita
>
> --- "Gupta, Brijesh" <Brijesh.Gupta_at_Airliquide.com>
> wrote:
> > Hi ALL
> >
> > I have a query with was running fast during the
> > test in the test instance
> > and
> > now when moved to production is running very
> slow.
> > On checking I found that same query is using
> > different explain plan on test
> > and production.
> >
> > Test is exact copy of production database only
> > little older data.
> >
> > Both database running Oracle Application with
> > optimizer mode = RULE .
> > Oracle 8.0.5
> > Platform AIX
> >
> > Can somebody give me a pointer why this is
> > happening.
> >
> > Thanks
> >
> > Here is explain plan from both instance and query
> >
> > ON PROD :
> >
> > Query Plan
> >
>
> > SELECT STATEMENT Cost =
> > SORT GROUP BY
> > NESTED LOOPS
> > NESTED LOOPS
> > TABLE ACCESS FULL DI_PRATN_CHARGE_ITEMS
> > TABLE ACCESS BY INDEX ROWID
> > CP_INVOICE_HEADERS_ALL
> > INDEX RANGE SCAN CP_INVOICE_HEADERS_N2
> > TABLE ACCESS BY INDEX ROWID
> > CP_INVOICE_ITEMS_ALL
> > INDEX RANGE SCAN CP_INVOICE_ITEMS_N25
> >
> >
> > ON TEST :
> >
> > Query Plan
> >
>
> > SELECT STATEMENT Cost = 169
> > SORT GROUP BY
> > HASH JOIN
> > NESTED LOOPS
> > TABLE ACCESS BY INDEX ROWID
> > CP_INVOICE_HEADERS_ALL
> > INDEX RANGE SCAN CP_INVOICE_HEADERS_N2
> > TABLE ACCESS BY INDEX ROWID
> > CP_INVOICE_ITEMS_ALL
> > INDEX RANGE SCAN CP_INVOICE_ITEMS_PK
> > TABLE ACCESS FULL DI_PRATN_CHARGE_ITEMS
> >
> >
> >
> > and Query is
> >
> > Select
> > Nvl(ih.sub_customer_number,
> ih.customer_number)
> > customer_number
> > ,ih.sub_account_number
> > ,ii.inventory_item_id
> > ,ii.organization_id
> > ,Sum(ii.total_line_amount) total_line_amount
> > From apps.CP_INVOICE_ITEMS ii,
> > apps.CP_INVOICE_HEADERS ih,
> > apps.DI_PRATN_CHARGE_ITEMS ci
> > where ih.billing_period_end_date =
> '31-jan-2000'
> > and ii.invoice_id = ih.invoice_id
> > and
> ii.inventory_item_id=ci.inventory_item_id+0
> > and ii.organization_id=ci.organization_id+0
> > and nvl(ii.total_line_amount, 0) != 0
> > group by
> > Nvl(ih.sub_customer_number,
> > ih.customer_number),
> > ih.sub_account_number,
> > ii.inventory_item_id,
> > ii.organization_id
> > /
> >
> >
> >
> >
> >
> >
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> > Brijesh Gupta
> > Oracle Production DBA
> >
>
>
> __________________________________________________
> Do You Yahoo!?
> Make international calls for as low as $.04/minute
> with Yahoo! Messenger
> http://phonecard.yahoo.com/
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: A. Bardeen
> INET: abardeen1_at_yahoo.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).
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sukumar Kurup
INET: sukuora_at_yahoo.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 Sat Aug 04 2001 - 20:37:51 CDT
