RE: Defragmenting a database

Gaja wrote :-

> > 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!

        Qs. How does it BY DESIGN Prevent Fragmentation ?

	-----Original Message-----
	From:	Gaja Krishna Vaidyanatha [SMTP:gajav_at_yahoo.com]
	Sent:	Thursday, October 12, 2000 4:46 AM
	To:	Multiple recipients of list ORACLE-L
	Subject:	RE: Defragmenting a database

	Vivek,

	What I mean is that when the import is done, brand new blocks
	are allocated for the table and each block is filled upto
	PCTFREE, thereby utilizing each block to its maximum potential. 
	Which means, if previously there were 10000 blocks with an
	average of 10 rows in each block, assuming that 100 rows can fit
	in 1 block, the new table after the import will contain 100
	blocks.  The result is that you have a much smaller table
	(decreased high water mark) and also any chained/migrated rows
	would be fixed (within reason).  You are getting rid of both
	block-level and row-level fragmentation here.

	The honeycombing effect I was referring to, usually relates to
	the "free space" in the datafile(s), but if you think about it,
	if a table has undergone multiple iterations of deletes and
	inserts, it is possible (based on its PCTUSED value),that
	honeycombing could occur even within a block.

	Hope that helps,

	Gaja


	--- VIVEK_SHARMA <VIVEK_SHARMA_at_infy.com> wrote:


	> 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

	> > 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: VIVEK_SHARMA

	>   INET: VIVEK_SHARMA_at_infy.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).