Re: Strange Performance Problem

In article <3p06o7$1et_at_senator-bedfellow.MIT.EDU> sturner_at_athena.mit.edu (Stephen Turner) writes:
>From: sturner_at_athena.mit.edu (Stephen Turner)
>Subject: Strange Performance Problem
>Date: 12 May 1995 17:43:35 GMT
 

>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
>

Steve,

This sure is strange.

The only thing I can think of is that the SQL-statement is kept in the SGA en is not invalidated when you drop the indexes. Subsequently, when you use it again, it isn't parsed again and uses the old, bad execution plan.

The moment you restart your database, the SQL-statement is out of the SGA and thus being parsed upon first execution.

Try using the ALTER SYSTEM FLUSH SHARED_POOL;-command after an import and before your users acces the database again. This should drop the SQL-statement from the SGA.

Greetings,
Frank Received on Sat May 13 1995 - 00:00:00 CEST