Re: Explain Plan

Hi Rob,

    A couple of things you could try:

1. Double check and make sure that ALL of the desired indexes were reapplied.
2. Check and see which optimizer they are using. If they are using the COST BASED OPTIMIZER run ANALYZE TABLE <tablename> COMPUTE STATISTICS
3. If neither of the above help run ANALYZE TABLE <tablename> VALIDATE STRUCTURE CASCADE
4. If none of the above help I am sure someone will have some other things you could try.

regards

Jerry Gitomer

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 - 15:11:19 CDT