If you've checked the tables and indexes and they match, take Jerry's
suggestion to check the optimizer_mode and whether your tables/indexes are
analyzed,
Rob Woods wrote in message <3756d689_at_news.nwlink.com>...
>Hi,
>
>We have a client with a problem..
>
>They're running 7.1.4, and have two instances, DEMO & PROD.
>
>Last weekend, their admin deleted old data from several tables in PROD,
then
>exported & re-imported the tables, and re-applied all the indexes. The
>problem is that reports that used to take < 5 minutes on PROD now take over
>3 hours to run. I have run explain plan in both instances for the
identical
>sql statement(from one of the reports), and get different results(below).
>Obviously the full table scan in PROD is the killer here, but I don't know
>what I am missing. I have examined the indexes in both DEMO & PROD, and
>they look to be identical.
>
>Any suggestions?
>
>TIA,
>
>Rob Woods
>
>
>
>DEMO>@ex1
>
>QUERY_PLAN
>---------------------------------------------------------------------------
-
>----
> NESTED LOOPS
> NESTED LOOPS
> TABLE ACCESS BY ROWID QUOTE_MODEL_DETAIL
> INDEX RANGE SCAN QUOTE_MODEL_DETAIL_IDX2
> TABLE ACCESS BY ROWID OEDETL
> INDEX RANGE SCAN OEDETL_PRIME
> TABLE ACCESS BY ROWID OEHEAD
> INDEX UNIQUE SCAN OEHEAD_PRIME
>
>8 rows selected.
>
>PROD>@ex1
>
>QUERY_PLAN
>---------------------------------------------------------------------------
-
>----
> NESTED LOOPS
> MERGE JOIN
> SORT JOIN
> TABLE ACCESS FULL OEDETL
> SORT JOIN
> TABLE ACCESS BY ROWID QUOTE_MODEL_DETAIL
> INDEX RANGE SCAN QUOTE_MODEL_DETAIL_IDX2
> TABLE ACCESS BY ROWID OEHEAD
> INDEX UNIQUE SCAN OEHEAD_PRIME
>
>9 rows selected.
>
>PROD>
>
>
>
>
>*********************************************
>rwoods.not_at_canright.com
>remove .not for email reply
>*********************************************
>
>
>
Received on Thu Jun 03 1999 - 18:31:43 CDT
