Re: Oracle import - indexes not being used?!

Russell Fray wrote:

> My main database is Oracle 8.1.6.4.0
>
> I am migrating to a new server which is running Oracle 8.1.7.4.0 enterprise.
>
> I used a full export on the 8.1.6.4 DB, and used a full import on the
> 8.1.7.4 DB. It appeared to work fine - a few small errors in the import log
> but nothing significant.
>
> Now, when I run a particular select on the new DB, it takes about 5 minutes
> compared to 5 seconds on the old machine. Hardware is identical. I used
> explain plan, and the migrated DB isn't using the indexes for the query.
> But, the indexes DO exist! I even dropped them and recreated them and it
> simply doesn't use them.
>
> Is there a known bug, or something that I am obviously doing wrong? The
> indexes have definitely both been exported and imported, and I'm not doing
> anything clever with indexfile= or indexes=y/n etc.
>
> My 8.1.7.4 DB has the extra features of that release, namely partitioning
> and extra index functions (ie bitmaps etc), global/local indexes, but if I
> am doing a straight import without anything clever, what could possibly be
> going wrong?
>
> Thanks for any pointers/clues...!!
> Russ.

Did you run DBMS_STATS to the cost based optimizer would have something to work with?

Have you looked at the parameters in the init.ora and compare?

Daniel Morgan Received on Fri Feb 07 2003 - 19:09:48 CST