Re: is rebuilding indexes necessary after import?

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_at_gmail.com> To: "Jeremiah Wilton" <jwilton_at_speakeasy.net> Cc: "Active DBA" <oracle-l_at_freelists.org> 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

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