7 Replies Latest reply: Jul 2, 2008 5:11 AM by 553937 RSS

    Database reorganization - looking for performance improvements

    505417
      Hi,

      I've just reorganized a 1TB production database. Currently the database is 10g R1, but some time ago was migrated from 8i. During the migration, all tablespaces were migrated to locally managed (using DBMS_SPACE_ADMIN.MIGRATE_TO_LOCAL) but extents were still USER managed.

      During the reorganization tables and indexes were moved to 6 new, locally managed tablespaces with ASSM. For the cause I created 6 new tablespaces:
      - one for small tables (<500MB), uniform extent size 1MB
      - one for small indexes (<500MB), uniform extent size 1MB.
      - one for medium tables (> 500MB, < 5GB), uniform extent size 10MB
      - one for medium indexes (>500MB, <5GB), uniform extent size 10MB
      - one for big tables (> 5GB), uniform extent size 100MB
      - one for big indexes (> 5GB), uniform extent size 100MB

      Tables were moved using ALTER TABLE … MOVE TABLESPACE… and indexes using ALTER INDEX … REBUILD TABLESPACE …

      I suppose the indexes were not rebuilt for the last 5 years. The indexes often span many columns and before the reorganization the biggest one had had about 40GB!

      After The reorganization the size of the whole database decreased about 250GB.

      I'd like to point out performance improvements achieved after the reorganization. I created two statspack reports, one for the week before the reorganization and the second one for the week after the operation. There are some differences, but I’d like to ask you for help. The question is:

      In which areas would you expect performance improvements after the described reorganization? I’ve found some changes, but maybe there are areas that I haven’t focused on?

      Any suggestions are welcome!
      Tim,
        • 1. Re: Database reorganization - looking for performance improvements
          428027
          You made a good job.

          Now you should focus on partition your big tables and indexes.

          Regards

          Helio Dias
          http://heliodias.com
          • 2. Re: Database reorganization - looking for performance improvements
            635471
            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.
            • 3. Re: Database reorganization - looking for performance improvements
              Srinivas.R
              Hello Tim,

              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.

              http://www.dailydba.com/2008/02/benefit-from-index-monitoring.html
              • 4. Re: Database reorganization - looking for performance improvements
                546612
                In which areas would you expect performance
                improvements after the described reorganization? I’ve
                found some changes, but maybe there are areas that I
                haven’t focused on?
                I'd also like to expand that question: how long does one expect those performance improvements?

                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.

                /Hans
                • 5. Re: Database reorganization - looking for performance improvements
                  ben23
                  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.
                  • 6. Re: Database reorganization - looking for performance improvements
                    505417
                    Hi All,

                    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.

                    Tim
                    • 7. Re: Database reorganization - looking for performance improvements
                      553937
                      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.

                      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6601312252730


                      Cheers,
                      Sbs