Too many extents
A DBA on IDS 10.0 (Linux) had most tables created with default extent sizes, leaving some with 100+ extents and hurting performance. He asked whether to drop/recreate, do an in-place alter, or dbexport/dbimport with adjusted sizes. Replies suggested checking fragmentation with oncheck -pe first; noted dropped tables' extents are reusable but may not be contiguous; offered ALTER TABLE MODIFY NEXT SIZE plus ALTER FRAGMENT ... INIT as the simplest in-place fix, or onunload/onload or dbexport/dbimport after editing first/next extent sizes in the .sql file. Art Kagel corrected a claim that dbimport recalculates extents, saying it instead benefits from extent coalescing into empty space, and mentioned his myschema utility for pre-calculated extent sizes. The poster thanked everyone but didn't state which option he chose.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi All,
IDS 10.0.UC6, RHL AS4
DB Size 20 GB
One of database in production has got more than 60% tables with default
extents. These tables now have got too many extents (some have more than 100
extents) and causing performance issue, as expected. Yep, very bad. I suppose,
there are 3 options:
1. drop & recreate these tables and dependent objects
2. In place alter
3. dbexport and adjust extents and then do dbimport
questions:
1. What would be the best option (any other option if I missed)?
2. Once these too many extents tables have been dropped, can space used by
them be
reclaimed when creating new objects?
Thanks.
---------------------------------
Never miss a thing. Make Yahoo your homepage.
>
> 1. drop & recreate these tables and dependent objects
> 2. In place alter
> 3. dbexport and adjust extents and then do dbimport
>
Depends on the amount of space you have for these tables to be
re-created and how fragemented your disk space is. Also performance
could be better depending on size with high performance loader.
> questions:
>
> 1. What would be the best option (any other option if I missed)?
> 2. Once these too many extents tables have been dropped, can space used by
> them be
>
> reclaimed when creating new objects?
Once the orignal table is dropped all of the extents can be reused.
Issue is that if you have 100 tables fragmented together you may not
have enough contigous space to store the table. You may have to
remove more then on at a time.
>
> Thanks.
>
> ---------------------------------
> Never miss a thing. Make Yahoo your homepage.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
HI,
Probably the simplest one is to use the following two sample steps.
--alter table file_receipt_info modify next size 2048;
--alter fragment on table file_receipt_info init in dbdata01;
make sure the locking time and impact to online activities.
Frank
On Nov 21, 2007 2:50 PM, H.G <hariog@yahoo.com> wrote:
> Hi All,
>
> IDS 10.0.UC6, RHL AS4
> DB Size 20 GB
>
> One of database in production has got more than 60% tables with default
> extents. These tables now have got too many extents (some have more than 100
> extents) and causing performance issue, as expected. Yep, very bad. I
suppose,
> there are 3 options:
>
> 1. drop & recreate these tables and dependent objects
> 2. In place alter
> 3. dbexport and adjust extents and then do dbimport
>
> questions:
>
> 1. What would be the best option (any other option if I missed)?
> 2. Once these too many extents tables have been dropped, can space used by
> them be
>
> reclaimed when creating new objects?
>
> Thanks.
>
> ---------------------------------
> Never miss a thing. Make Yahoo your homepage.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
Eric stated it nicely. How much trouble is(are) your dbspace(s) in? do an
oncheck -pe and see what sort of mess you've got on your hands first.
j.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of H.G
Sent: Wednesday, November 21, 2007 2:51 PM
To: ids@iiug.org
Subject: Too many extents [10444]
Hi All,
IDS 10.0.UC6, RHL AS4
DB Size 20 GB
One of database in production has got more than 60% tables with default
extents. These tables now have got too many extents (some have more than 100
extents) and causing performance issue, as expected. Yep, very bad. I
suppose,
there are 3 options:
1. drop & recreate these tables and dependent objects
2. In place alter
3. dbexport and adjust extents and then do dbimport
questions:
1. What would be the best option (any other option if I missed)?
2. Once these too many extents tables have been dropped, can space used by
them be
reclaimed when creating new objects?
Thanks.
---------------------------------
Never miss a thing. Make Yahoo your homepage.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
it will recount the extent size and next size for tables when you execute the
dbexport command.
onunload
onload
It has some pre requisites .. .you need to check if it useful for your
situation.
----- Mensagem original ----
De: H.G <hariog@yahoo.com>
Para: ids@iiug.org
Enviadas: Quarta-feira, 21 de Novembro de 2007 16:50:44
Assunto: Too many extents [10444]
Hi All,
IDS 10.0.UC6, RHL AS4
DB Size 20 GB
One of database in production has got more than 60% tables with default
extents. These tables now have got too many extents (some have more
than 100
extents) and causing performance issue, as expected. Yep, very bad. I
suppose,
there are 3 options:
1. drop & recreate these tables and dependent objects
2. In place alter
3. dbexport and adjust extents and then do dbimport
questions:
1. What would be the best option (any other option if I missed)?
2. Once these too many extents tables have been dropped, can space used
by
them be
reclaimed when creating new objects?
Thanks.
---------------------------------
Never miss a thing. Make Yahoo your homepage.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Abra sua conta no Yahoo! Mail, o único sem limite de espaço para armazenamento!
http://br.mail.yahoo.com/
Hi,
If was me ... I would use dbexport / dbimport if you have enought time to do
it ... or unload and dbload.If the choice is dbexport/import, you should
modify the <DB>.sql file, changing first extent and next size.B regards... R
Ferronato
> To: ids@iiug.org> From: cesar_inacio_martins@yahoo.com.br> Subject: Res: Too
many extents [10452]> Date: Wed, 21 Nov 2007 20:39:57 -0500> > onunload >
onload > > It has some pre requisites .. .you need to check if it useful for
your > situation. > > ----- Mensagem original ---- > De: H.G
<hariog@yahoo.com> > Para: ids@iiug.org > Enviadas: Quarta-feira, 21 de
Novembro de 2007 16:50:44 > Assunto: Too many extents [10444] > > Hi All, > >
IDS 10.0.UC6, RHL AS4 > DB Size 20 GB > > One of database in production has
got more than 60% tables with default > > extents. These tables now have got
too many extents (some have more > than 100 > extents) and causing performance
issue, as expected. Yep, very bad. I > suppose, > there are 3 options: > > 1.
drop & recreate these tables and dependent objects > 2. In place alter > 3.
dbexport and adjust extents and then do dbimport > > questions: > > 1. What
would be the best option (any other option if I missed)? > 2. Once these too
many extents tables have been dropped, can space used > by > them be > >
reclaimed when creating new objects? > > Thanks. > >
--------------------------------- > Never miss a thing. Make Yahoo your
homepage. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. > > Abra
sua conta no Yahoo! Mail, o único sem limite de espaço para > armazenamento! >
http://br.mail.yahoo.com/ > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Connect to the next generation of MSN Messenger
http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmai
ltagline
Thanks everyone for great advise. Cheers.
R Fo <roeferr@hotmail.com> wrote: Hi,
If was me ... I would use dbexport / dbimport if you have enought time to do
it ... or unload and dbload.If the choice is dbexport/import, you should
modify the .sql file, changing first extent and next size.B regards... R
Ferronato
> To: ids@iiug.org> From: cesar_inacio_martins@yahoo.com.br> Subject: Res: Too
many extents [10452]> Date: Wed, 21 Nov 2007 20:39:57 -0500> > onunload >
onload > > It has some pre requisites .. .you need to check if it useful for
your > situation. > > ----- Mensagem original ---- > De: H.G
> Para: ids@iiug.org > Enviadas: Quarta-feira, 21 de
Novembro de 2007 16:50:44 > Assunto: Too many extents [10444] > > Hi All, > >
IDS 10.0.UC6, RHL AS4 > DB Size 20 GB > > One of database in production has
got more than 60% tables with default > > extents. These tables now have got
too many extents (some have more > than 100 > extents) and causing performance
issue, as expected. Yep, very bad. I > suppose, > there are 3 options: > > 1.
drop & recreate these tables and dependent objects > 2. In place alter > 3.
dbexport and adjust extents and then do dbimport > > questions: > > 1. What
would be the best option (any other option if I missed)? > 2. Once these too
many extents tables have been dropped, can space used > by > them be > >
reclaimed when creating new objects? > > Thanks. > >
--------------------------------- > Never miss a thing. Make Yahoo your
homepage. > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. > > Abra
sua conta no Yahoo! Mail, o único sem limite de espaço para > armazenamento! >
http://br.mail.yahoo.com/ > > >
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Connect to the next generation of MSN Messenger
http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmai
ltagline
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
---------------------------------
Get easy, one-click access to your favorites. Make Yahoo! your homepage.
Hi,
Dbimport will automatically recalculate extends because it knows how
many rows are in the table actually. Based on this knowledge, it will
set the initial extent size.
If you expect the database to grow very fast, you should calculate the
estimated amount of space which is used per year and set the extent size
manually as stated below in the sql file before loading data again.
Marcus
-----Original Message-----
From: H.G [mailto:hariog@yahoo.com]
Sent: Thursday, November 22, 2007 8:48 PM
To: ids@iiug.org
Subject: RE: Res: Too many extents [10456]
Thanks everyone for great advise. Cheers.
R Fo <roeferr@hotmail.com> wrote: Hi,
If was me ... I would use dbexport / dbimport if you have enought time
to do it ... or unload and dbload.If the choice is dbexport/import, you
should modify the .sql file, changing first extent and next size.B
regards... R Ferronato
> To: ids@iiug.org> From: cesar_inacio_martins@yahoo.com.br> Subject:
> Res: Too
many extents [10452]> Date: Wed, 21 Nov 2007 20:39:57 -0500> > onunload
> onload > > It has some pre requisites .. .you need to check if it
useful for your > situation. > > ----- Mensagem original ---- > De: H.G
> Para: ids@iiug.org > Enviadas: Quarta-feira, 21 de
Novembro de 2007 16:50:44 > Assunto: Too many extents [10444] > > Hi
All, > > IDS 10.0.UC6, RHL AS4 > DB Size 20 GB > > One of database in
production has got more than 60% tables with default > > extents. These
tables now have got too many extents (some have more > than 100 >
extents) and causing performance issue, as expected. Yep, very bad. I >
suppose, > there are 3 options: > > 1.
drop & recreate these tables and dependent objects > 2. In place alter >
3.
dbexport and adjust extents and then do dbimport > > questions: > > 1.
What would be the best option (any other option if I missed)? > 2. Once
these too many extents tables have been dropped, can space used > by >
them be > > reclaimed when creating new objects? > > Thanks. > >
--------------------------------- > Never miss a thing. Make Yahoo your
homepage. > > >
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum. >
> > Abra
sua conta no Yahoo! Mail, o nico sem limite de espao para >
armazenamento! > http://br.mail.yahoo.com/ > > >
************************************************************************
*******
> Forum Note: Use "Reply" to post a response in the discussion forum. >
_________________________________________________________________
Connect to the next generation of MSN Messenger
http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmai
ltagline
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
---------------------------------
Get easy, one-click access to your favorites. Make Yahoo! your homepage.
************************************************************************
*******
Forum Note: Use "Reply" to post a response in the discussion forum.
Marcus Haarmann wrote:
> Hi,
>
> Dbimport will automatically recalculate extends because it knows how
> many rows are in the table actually. Based on this knowledge, it will
> set the initial extent size.
>
Actually, AFAIK dbimport doesn't actually calculate the first extent
size. However, since it loads only one table at a time, extent
compression (extending an existing extent with contiguous disk instead
of adding another extent) causes the tables to tend towards few extents
and if the dbspace you are loading into is empty (or at least all of the
free space is contiguous) you will tend to end up with one large extent
if the table fits within a single chunk.
The only way to get a pre-calculated set of extent sizes would be to use
my dbschema replacement utility, myschema, with its dbexport
compatibility option (-l) and its auto-extent sizing features to
generate a replacement schema for dbimport to use.
Art S. Kagel
> If you expect the database to grow very fast, you should calculate the
> estimated amount of space which is used per year and set the extent size
> manually as stated below in the sql file before loading data again.
>
> Marcus
>
>
<SNIP>