RE: Optimizer Puzzler

However, this is using rule based optimizer so none of that should really matter, except for db_file_multiblock_read_count which is also an operational parameter, not just for the optimizer. Do any of the table have a parallel degree by any chance. That would override the rule based optimizer. Can you run with event 10053 set to verify that the RBO is used. And you checked the obvious: the index does exist in dev and is valid and usable. Generally the RBO jumps on any usable index in sight so it's odd that it wouldn't use it.

At 12:37 PM 4/22/2003 -0800, you wrote:
> >Good day all,
> >I have a query that has been a stumper for the past
> >couple of days -
> >Oracle 8.0.5
> >HPUX 11
> >When running it in Dev environment, it does a full
> >table scan on a large
> >table and the performance is terrible. But when
> >running it in prod
> >environment, it uses the index and the performance
> >is great. Both Dev and
> >Prod are running with Rule-based ( this is Oracle
> >Apps so we don't have a
> >choice ). Also Dev is a 'clone' of prod from about
> >2 weeks ago.
> >The only thing I have a funny feeling about is that
> >while Rule-based
> >optimizer is set in init.ora, all the tables are
> >analyzed with a 2001 date (
> >done by previous DBA ). But since both environment
> >have the same statistics,
> >I don't think this can make a difference. I deleted
> >statistics from the
> >table in Dev and the plan remains the same.
> >
> >TIA
> >
> >Dennis
> >
>
>Dennis,
>
>Interesting case. Usually the performance is good in dev and terrible in
prod.
>When you cloned your database, did you copy the init.ora file as well or
>just the database files? Because even with identical statistics,
>parameters such as sort_area_size, hash_area_size,
>db_block_multiread_count and the like can have a huge influence on what
>the optimizer believes to be the decent thing to do.
>
>Regards,
>
>Stephane Faroult
>Oriole
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Stephane Faroult
> INET: sfaroult_at_oriolecorp.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>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).

Wolfgang Breitling
Centrex Consulting Corporation
http://www.centrexcc.com

This email communication is intended as a private communication for the sole use of the primary addressee and those individuals listed for copies in the original message. The information contained in this email is private and confidential and if you are not an intended recipient you are hereby notified that copying, forwarding or other dissemination or distribution of this communication by any means is prohibited. If you are not specifically authorized to receive this email and if you believe that you received it in error please notify the original sender immediately. We honour similar requests relating to the privacy of email communications.

Cette communication par courrier electronique est une communication privee a l'usage exclusif du destinataire principal ainsi que des personnes dont les noms figurent en copie. Les renseignements contenus dans ce courriel sont confidentiels et si vous n'etes pas le destinataire prevu, vous etes avise, par les presentes que toute reproduction, tout transfert ou toute autre forme de diffusion de cette communication par quelque moyen que ce soit est interdit. Si vous n'etes pas specifiquement autorise a recevoir ce courriel ou si vous croyez l'avoir recu par erreur, veuillez en aviser l'expediteur original immediatement. Nous respectons les demandes similaires qui touchent la confidentialite des communications par courrier electronique.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).