Re: Query running extremely slow after create/drop index

On Fri, 23 May 2003 07:04:03 +0000, Christian Svensson wrote:

> Greeetings !
>
> For a batchjob with a select query joining two large tables (~220.000
> and ~160 millions of rows), this query that normally took ~50 min to
> finish now takes 6-8 longer time to finish.
>
> This after I have created an index on one of the tables and later
> dropped the same index (to try tune another query).
>
> I have flushed the sga so it does not reuse the plan as when the index
> was created, I have also changed the pl/sql code for the statement
> (added a space mark) to also eliminate the reuse.
>
> This is on a Preproduction environment so we have opportunity to
> restart the database to see if it helps (you never know). After
> comparing against the production database the table/index settings is
> the same, there are also same amount of data on both pre and prod, the
> explain plan when running this query is strangely almost the same on
> pre and prod. So I am very confused why the query takes so much longer
> time, it smells like a bug...
>
> Sun Solaris 6 (I know...) / Oracle 8.1.7.3.0
>
> But if anyone have encountered this same situation and have some kind
> of hints, please send a reply on this group.
>
> Thanks.
>
> Cheers !
>
> /Christian Svensson

Just a few suggestions . i hope it helps . Have the tables / indexes analyzed after the index was dropped . Did you check if the explain plan has changed . Since it is a pre production did you try turning on tracing with level 8 to see if there are waits and what is it waiting on . Received on Fri May 23 2003 - 11:32:30 CDT