We had similar things (performance issues) happening on our DB's that were upgraded from 8.1.7.4 to 9.2.0.4 (since patched to 9.2.0.5). The cause turned out to be different execution plans, as we also moved from RULE to COST based optimisation. Nick -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jaffar_DBA Sent: 22 December 2004 07:58 To: Jeremiah Wilton Cc: Active DBA 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 On Tue, 21 Dec 2004 23:43:31 -0800 (PST), Jeremiah Wilton <jwilton@xxxxxxxxxxxxx> wrote: > On Wed, 22 Dec 2004, Jaffar_DBA wrote: > > > My question is, do we really need to rebuild/recreate indexes after > > importing them into another database? > > Probably the reason they recommend this is that they hope you will > import without indexes, then build them in some more efficient way > (parallel, unrecoverable) with the export completed, saving yourself > time. > > Also, your nickname for this list - Active DBA (from the To: header) - > is this is contrast to Lazy DBA? If so I like the distinction! > > -- > Jeremiah Wilton > Independent Oracle Professional > Oracle Certified Master > Disaster Recovery - Seminars - Technical Interviews > http://www.speakeasy.net/~jwilton > -- Best Regards, Jaffar, OCP DBA Banque Saudi Fransi Saudi Arabia -- http://www.freelists.org/webpage/oracle-l ======================================================= This message is intended solely for the use of the individual or organisation to whom it is addressed. It may contain privileged or confidential information. If you have received this message in error, please notify the originator immediately. If you are not the intended recipient, you should not use, copy, alter, or disclose the contents of this message. All information or opinions expressed in this message and/or any attachments are those of the author and are not necessarily those of VarTecTelecom Europe Ltd or its affiliates. VarTec Telecom Europe Ltd accepts no responsibility for loss or damage arising from its use, including damage from virus. ======================================================= -- http://www.freelists.org/webpage/oracle-l