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"
Received on Tue Oct 10 2000 - 15:03:04 CDT
