RE: Defragmenting a database

Gaja wrote :-

The export and the import gets rid of "block-level fragmentation" Isn't a a Block Fully Compacted or Do u mean there exist Honey-comb Empty spaces scattered about in a Block too ?

Forgive the Ignorance as seem to be stumbling on Basics itself here

> -----Original Message-----
> From: Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
> Sent: Wednesday, October 11, 2000 2:35 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Defragmenting a database
>
> 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
>
>
> --- Ruth Gramolini <rgramolini_at_tax.state.vt.us> wrote:
> > When you import with compress=Y it puts all of the data in the
> > table into
> > one extent. If this is ever going to be a source database for
> > import into
> > another don't do this!!
> > RBG
> > ----- Original Message -----
> > To: "Multiple recipients of list ORACLE-L"
> > <ORACLE-L_at_fatcity.com>
> > Sent: Tuesday, October 10, 2000 12:04 PM
> >
> >
> > > Yes,
> > >
> > > I think it's the only option you have under 8.0.5. You
> > should export it
> > with COMPRESSION = ON and reimport it. As for indexes you'd
> > better rebuild
> > them in a separate tablespace to gain performance increase.
> > >
> > > Under 8.1.6 you have the option of rebuilding the table
> > rather than exp
> > and imp.
> > >
> > > good luck
> > > Mahmoud Reza Zare
> > >
> > > --- cemail_at_sprintmail.com
> > > > wrote:
> > > >
> > > >Some of our objects have up to 3000 extents and we are
> > starting
> > > >to have load issues and performance issues. I know that I
> > need
> > > >to do an export, recreate the object with a larger initial
> > extent
> > > >and then import the data back in. What do you do about the
> > constraints?
> > > > I don't want to go to import and then get constraint
> > violations.
> > > > Do you have to export the offending object and all of the
> > tables
> > > >related to it and then import them all back in at the same
> > time?
> > > >
> > > >Also some of the objects are indexes. This is Oracle 8.0.5
> > on
> > > >HPUX. After doing the export/import on the tables would it
> > be
> > > >easier to just rebuild the indexes with different storage
> > parameters
> > > >versus dropping and recreating?
> > > >
> > > >-----
> > > >Sent using MailStart.com (
> > http://MailStart.Com/welcome.html )
> > > >The FREE way to access your mailbox via any web browser,
> > anywhere!
> > > >
> > > >--
> > > >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > >--
> > > >Author:
> > > > INET: cemail_at_sprintmail.com
> > > >
> > > >Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > > >San Diego, California -- Public Internet access /
> > Mailing Lists
> > >
> >
> >--------------------------------------------------------------------
> > > >To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > > >to: ListGuru_at_fatcity.com (note EXACT spelling of
> > 'ListGuru') and in
> > > >the message BODY, include a line containing: UNSUB ORACLE-L
> > > >(or the name of mailing list you want to be removed from).
> > You may
> > > >also send the HELP command for other information (like
> > subscribing).
> > >
> > >
> > _____________________________________________________________
> > > Be someone_at_0racledba.com - get your free mail from Oriole
> > Corporation
> > > http://www.oriolecorp.com - Performance tools for Oracle
> > DBAs
> > >
> > > Get email for your site ---> http://www.everyone.net
> > >
> > > --
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > --
> > > Author: Mahmoud Reza Zare
> > > INET: mrezair_at_0racledba.com
> > >
> > > Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > > San Diego, California -- Public Internet access /
> > Mailing Lists
> > >
> >
> --------------------------------------------------------------------
> > > To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in
> > > the message BODY, include a line containing: UNSUB ORACLE-L
> > > (or the name of mailing list you want to be removed from).
> > You may
> > > also send the HELP command for other information (like
> > subscribing).
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Ruth Gramolini
> > INET: rgramolini_at_tax.state.vt.us
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858)
> > 538-5051
> > San Diego, California -- Public Internet access /
> > Mailing Lists
> >
> --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail
> > message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru')
> > and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).
> > You may
> > also send the HELP command for other information (like
> subscribing).
>
>
> =====
> 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"
>
> __________________________________________________
> Do You Yahoo!?
> Get Yahoo! Mail - Free email you can access from anywhere!
> http://mail.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Gaja Krishna Vaidyanatha
> INET: gajav_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Wed Oct 11 2000 - 13:25:34 CDT