This discussion is archived
7 Replies Latest reply: Jul 2, 2008 3:11 AM by 553937 RSS

Database reorganization - looking for performance improvements

505417 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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
    639056 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    602378 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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