RE: Optimizer Puzzler

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