Strange Performance Problem

Here's our situation:

We are using export & import to copy data & indexes for 13 tables from an Oracle 7 database (version 7.1.3.2.0) on a VAX to an Oracle 7 database (version 7.0.16.6.1) on an Ultrix machine.

We are truncating the tables on the target database and then doing the import. This has worked fine but has been extremely slow.

Recently we changed the procedure to also drop the indexes on the target db before we import. This has sped things up considerably (16 hours -> 3.5 hours!) but seems to have caused other problems. After the data has been imported, response time on queries to some views is incredibly high. Using the explain plan feature I find that a large table is being accessed via a full scan. The indexes are all there as they should be (the import put them back).

Strange thing is that if I shut down the instance and then start it up again, everything now runs fine - no performance problem, and explain plan now shows the indexes being used as expected, no full table scans.

Can anyone think what the problem is? I want to automate the data load so it runs overnight and I don't want to have to shut down & start up the instance each time if I can help it.

Thanks
Steve Turner   Received on Fri May 12 1995 - 00:00:00 CEST