Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Defragmenting a database

Re: Defragmenting a database

From: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
Date: Tue, 10 Oct 2000 13:03:04 -0700 (PDT)
Message-Id: <10645.118902@fatcity.com>


Ruth,

On the money! Just want to add a few points to your note.

The core issue that we are dealing here, is the stage that is being set, for the effects of "honey combing" and fragmentation of space within the tablespace, that will occur as a result of using "COMPRESS=Y". Having just 1 huge extent (and having many of those in your database) is not better performing that even a few hundred smaller extents.

The better option will be to create 4 types of tablespaces - SMALL, MEDIUM, LARGE and XLARGE and provide decent "default storage" clauses for the extents for each of these tablespaces and not put any storage clause at the object level. Yes, you may waste a few megabytes of space, but in the bigger scheme of things, the cost of that is near to nothing.

By having "uniform extents within a given tablespace", all objects in a given tablespace will have the same extent sizes, and by design one will prevent fragmentation. This also obfuscates the need for "free space coalescing" and the "brilliant event" of SMON coming around and doing that job for us. I'd rather wait for Haley's Comet!

One of the core myths that has been around for a while now is that "defragmenting a table from hundreds or thousands of extents to 1 extent" provides better performance. That is so not true. The export and the import gets rid of "block-level fragmentation" and a lot of "row-level fragmentation (in the form of chained and migrated rows)", which in turn provides the better performance. It also re-fills each block upto to PCTFREE and hence there is better "block compaction and utilization".

So ther possibility of 1 extent providing any performance benefit is close to 0, instead the benefit arises from each block filled with as many rows as possible, which are not "chained" or "migrated". It is important to adjust PCTFREE as you are adjusting the INITIAL & NEXT of your tables, to eliminate any/all row-level fragmentation.

Apart from what is mentioned above, the other down side of having a table with a few hundred or a few thousand extents is the time it takes to "truncate" or "drop" the table and the potential fragmentation that this operation inflicts on the uet$ and the fet$ data dictionary tables.

Cheers,

Gaja


Gaja Krishna Vaidyanatha
Director, Storage Management Products, Quest Software Inc. Office : (972)-304-1170, E-mail : gajav_at_yahoo.com

Author - Oracle Tuning 101 by Osborne McGraw-Hill "Opinions and views expressed are my own and not of Quest" Received on Tue Oct 10 2000 - 15:03:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US