Re: Explain Plan

Check if the tables involved are analysed on production instance PROD. I guess that could be the problem.

Prasad.

Rob Woods wrote:
>
> 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 - 16:01:43 CDT