This content has been marked as final. Show 7 replies
You made a good job.
Now you should focus on partition your big tables and indexes.
You possibly reduced the height of some of your indexes and reduced the logical reads, and consequently the physical reads also -- maybe reduced or increased the size of table segments and eliminated migrated rows.
You should watch for a performance degradation on DML operations though -- inserts and updates may now have a higher chance of causing an index block split and there may be increased contention.
Queries that use Full table scans will now perform better because of less blocks to scan (you have eliminated fragmentation). So this is one area that you will see tremendous improvements.
Have you also considered "Index Monitoring" feature ? If yours is an OLTP database, i suggest you implement this feature too when you do your reorgs. My guess you can further reduce your database size after "Index Monitoring". I have an article on that.
In which areas would you expect performanceI'd also like to expand that question: how long does one expect those performance improvements?
improvements after the described reorganization? I’ve
found some changes, but maybe there are areas that I
haven’t focused on?
Or - if you are going to document improvements, it would be interesting to get a second snapshot a few weeks and/or months later.
I have anecdotal evidence, but have never documented it, that a re-org can improve things for a short time. But normal data manipulation seems to eventually degrade the performance to a 'steady state' rate. I think you are in a fairly unique position to be able to document or debunk that phenomenon.
If you are comparing total segment size (and not datafile size) before and after, then there should be significant savings in logical I/O due to the re-packing of previously sparse index and table blocks. Can you post those numbers before and after?
Indexes may grow again depending on what you set the PCTFREE to, but the 'steady state' effects alone cannot account for the total reduction in size you are seeing.
Thank you very much for all replies. Due to some issues I haven't been able to focus on the mentioned database.
As soon as I gather and compare statistics, I'll publish here my findings. Of course I'll take into consideration all your suggestions.
I remember reading a thread on asktom regarding index rebuilds and it was interesting and it change my perspective on index rebuilds.. you could also check that in the below link.