Re: is rebuilding indexes necessary after import?

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <sjaffarhussain@xxxxxxxxx>, "Jeremiah Wilton" <jwilton@xxxxxxxxxxxxx>
  • Date: Wed, 22 Dec 2004 11:23:08 -0000

Just to clarify -

    You have good performance on 8i - you checked
    the execution plan of a specific query and the statistics
    on all the tables and indexes involved ?

    You did a full import into 9i and performance was bad
    on repeated executions of the query. The execution plan 
    was identical and the statistics on the tables and indexes 
    (nearly) matched the 8i statistics ?

    You rebuilt one specific index, and the execution plan
    was identical and the statistics on the tables and indexes
    (nearly) matched the 8i statistics and performance was
    fine again ?


Amongst other things - what options for statistics did you take
when you imported, what did you do about statistics when you
did the rebuild ?

Take a closer look at the execution plans - it's not uncommon
for people to miss the differences between
    (range scan)
    (ful scan)
    (fast full scan)
when they are checking execution plans - especially if they
are really only looking for an index name.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated Sept 19th





----- Original Message ----- 
From: "Jaffar_DBA" <sjaffarhussain@xxxxxxxxx>
To: "Jeremiah Wilton" <jwilton@xxxxxxxxxxxxx>
Cc: "Active DBA" <oracle-l@xxxxxxxxxxxxx>
Sent: Wednesday, December 22, 2004 7:57 AM
Subject: Re: is rebuilding indexes necessary after import?


The reason, why I am asking question is, recently I have migrated  my
warehouse database from 8i to 9i on AIX machine using export/import.
The query which was taking 10 - 20 mins. was taking hours to finish I
tried looking into execution plan, taking 10046 event and taking
statspack. Query was reading index properly and I was going crazy. I
just rebuild the index and it when like before. I realized at that
time that we might have to rebuild indexes back once importing them.
But, technically, when you import indexes, it actually use create
idnex command and created indexes. So, why there was a problem with my
index at the point of time was a question mark. I face a lot of
performance degration after migration 8i to 9i on AIX. Then after
applying patch set 9.2.0.5, the situation come acceptable as oracle
says they have fixed a lot of optimizer bugs in 9.2.0.5.

JAFFAR



--
//www.freelists.org/webpage/oracle-l

Other related posts: