Thanks to all, ANALYZE fixed it..
--
rwoods.not_at_canright.com
remove .not for email reply
Rob Woods <rswoods_at_nwlink.com> wrote in message
news:3756d815_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 Fri Jun 04 1999 - 12:32:30 CDT
