Re: Oracle performace degradation problem

>Thanks. Indeed, I forgot to mention - Oracle 9i.

Not good enough. You need to post the exact version, exact platform and OS information, and an explain plan, or preferably real trace output, before and after.

The most likely explanation is the optimizer has flopped over to a slower access method (see Mladen's post), and in addition you may have decompressed your data - where before additions and updates fully packed your blocks full of data, now there is free space in the blocks.  So you have to physically read twice as much! You might consider adjusting pctfree when "defragmenting," depending on your expected future key distribution.

If it has become an issue that you want optimizer stability, check out plan stability - Chapter 7 in the 9.2 performance tuning guide.

jg

--
@home.com is bogus.
Active cookies hurt my stomach.
http://catless.ncl.ac.uk/Risks/24.17.html#subj4