Methods for Reducing Extents
Hi all,
I'm aware this subject has been discussed at nauseum for many years,
but I just want to make sure the info I have is still up to date.
The methods I know about are:
alter table <tablename> modify next 100000; (where 100000 = a number
roughly the size of the table)
alter fragment on table <tablename> init in <same dbspace>;
or:
alter table <tablename> modify next 100000; (where 100000 = a number
roughly the size of the table)
alter index <an index for the table in question> to cluster;
or:
unload to filename
select * from <tablename>;dbschema -t tablename -d databasename >> tablename.sql (alter .sqlfile to increase initial/next extents)
drop table tablename;recreate table with tablename.sql
load from filenameinsert into <tablename>;
or:
dbexport the entire database (if wishing to do many tables)
drop database;
modify the dbexport.sql file to change the initial/next extents for
large tables
dbimport the database
or:
onunload database (or just a table)
drop/recreate the table or entire database
onload the database or table
Does that sound right?
Questions:
1. The onunload method seems to work. That is, I onunload from prod
and insert into the test instance, and almost all tables in test are
in single extents (whereas prod has many extents for the large
tables). This also seems to be the fastest method. However, I read a
post from '98 that said that onunload/method (at least for the entire
database) is not a good idea because:
"Since onunload is taking a binary dump of the table
(i.e. page images), you are really not "defragmenting" it when you
onload it
back in. That is, if all the pages were not full when you onunloaded,
they will
remain not full when you onload (it will, as mentioned above, probably
result in
a single extent, though). "
Is that still true? Will there still be wasted space in each page,
even the extents are merged into one? And if so, will it be
detrimental to performance?
2. I've tried the other methods with varying degrees of success. After
I modify the extent, and run, say, an alter fragment on it, often
times the number of extents don't change at all or by very little
(like 204 to 171, in one case). I added a 10GB chunk to the dbspace,
just to make sure I had plenty of contiguous space, and still had very
inconsistent results. And thats with initial/next extents about as
large as the whole table. (The table size for these tables were less
then about 3.5GB's each, and I'm using 10.00.FC6 and FC7).
Does anyone know what could be wrong? Am I missing a step or two?
Thanks for ANY advice regarding this, and feel free to correct any
mistakes I've made. I/We have many, many instances to do, and I'd like
to get the methodology down pat!
Nate
The "traditional" way to reorganise the table was to create a cluster index Any method of reducing the number of extents does requrie you have adequate contiguous disk space So unloading a table, dropping it and then not doing anything with the other tables that are in the dbsapce will mean you do not have sufficient contiguous space to reload the data
Hi.
Firstly, thanks for this comprehensive list of reorganization methods, you really included every option.
I had just reorganized a lot of very fragmented tables (from 80 extents to a hundred and something), and I used either unload-drop-create-load or alter fragment paths. Both worked for me, except that methods which don't recreate original table can't increase first extent size (major suggestion for coming version, in my opinion) and they used to create at least two extents - a tiny one and the important-big one.
I got same results than you - that is to say, more extents than I expected - when I failed calculating next extent size or when I didn't have enough contiguous space on disk. My favourite method to don't fail with calculation is using oncheck -pt to see actual size of every fragment, convert it to kilobytes, increasing 10% (to support growing, percentage may vary according with grow ratio on your database or table) applying it, make all the stuff and finally set next size to a reasonable value (I mean, if oncheck says my table used 100K, I set next size to 100K just while I was defragmenting, and then restore former value or another acconding with new size and growing expectations) When I have enough free space (you can verify it with oncheck -pt on the dbspace of choice) It never fail.
I hope you find this useful. Later
Omar
--- On Thu, 6/19/08, natebsi@gmail.com <natebsi@gmail.com> wrote:
> From: natebsi@gmail.com <natebsi@gmail.com>
> Subject: Methods for Reducing Extents
> To: informix-list@iiug.org
> Date: Thursday, June 19, 2008, 7:06 PM
> Hi all,
>
> I'm aware this subject has been discussed at nauseum
> for many years,
> but I just want to make sure the info I have is still up to
> date.
>
> The methods I know about are:
>
> alter table <tablename> modify next 100000; (where
> 100000 = a number
> roughly the size of the table)
> alter fragment on table <tablename> init in <same> dbspace>;
>
> or:
>
> alter table <tablename> modify next 100000; (where
> 100000 = a number
> roughly the size of the table)
> alter index <an index for the table in question> to
> cluster;
>
> or:
>
> unload to filename
> select * from <tablename>;> dbschema -t tablename -d databasename >>
> tablename.sql (alter .sql> file to increase initial/next extents)
> drop table tablename;> recreate table with tablename.sql
> load from filename> insert into <tablename>;
>
> or:
>
> dbexport the entire database (if wishing to do many tables)
> drop database;
> modify the dbexport.sql file to change the initial/next
> extents for
> large tables
> dbimport the database
>
> or:
>
> onunload database (or just a table)
> drop/recreate the table or entire database
> onload the database or table
>
>
> Does that sound right?
>
> Questions:
>
> 1. The onunload method seems to work. That is, I onunload
> from prod
> and insert into the test instance, and almost all tables in
> test are
> in single extents (whereas prod has many extents for the
> large
> tables). This also seems to be the fastest method. However,
> I read a
> post from '98 that said that onunload/method (at least
> for the entire
> database) is not a good idea because:
>
> "Since onunload is taking a binary dump of the table
> (i.e. page images), you are really not
> "defragmenting" it when you
> onload it
> back in. That is, if all the pages were not full when you
> onunloaded,
> they will
> remain not full when you onload (it will, as mentioned
> above, probably
> result in
> a single extent, though). "
>
> Is that still true? Will there still be wasted space in
> each page,
> even the extents are merged into one? And if so, will it be
> detrimental to performance?
>
> 2. I've tried the other methods with varying degrees of
> success. After
> I modify the extent, and run, say, an alter fragment on it,
> often
> times the number of extents don't change at all or by
> very little
> (like 204 to 171, in one case). I added a 10GB chunk to the
> dbspace,
> just to make sure I had plenty of contiguous space, and
> still had very
> inconsistent results. And thats with initial/next extents
> about as
> large as the whole table. (The table size for these tables
> were less
> then about 3.5GB's each, and I'm using 10.00.FC6
> and FC7).
> Does anyone know what could be wrong? Am I missing a step
> or two?
>
> Thanks for ANY advice regarding this, and feel free to
> correct any
> mistakes I've made. I/We have many, many instances to
> do, and I'd like
> to get the methodology down pat!
>
> Nate
> _______________________________________________
> Informix-list mailing list
> Informix-list@iiug.org
> http://www.iiug.org/mailman/listinfo/informix-list
On 20 Jun, 01:06, nate...@gmail.com wrote:
> Hi all,
>
> I'm aware this subject has been discussed at nauseum for many years,
> but I just want to make sure the info I have is still up to date.
>
> The methods I know about are:
>
> alter table <tablename> modify next 100000; (where 100000 = a number
> roughly the size of the table)
> alter fragment on table <tablename> init in <same dbspace>;>
> or:
>
> alter table <tablename> modify next 100000; (where 100000 = a number
> roughly the size of the table)
> alter index <an index for the table in question> to cluster;
>
> or:
>
> unload to filename
> select * from <tablename>;> dbschema -t tablename -d databasename >> tablename.sql (alter .sql> file to increase initial/next extents)
> drop table tablename;> recreate table with tablename.sql
> load from filename> insert into <tablename>;
>
> or:
>
> dbexport the entire database (if wishing to do many tables)
> drop database;
> modify the dbexport.sql file to change the initial/next extents for
> large tables
> dbimport the database
>
> or:
>
> onunload database (or just a table)
> drop/recreate the table or entire database
> onload the database or table
>
> Does that sound right?
>
> Questions:
>
> 1. The onunload method seems to work. That is, I onunload from prod
> and insert into the test instance, and almost all tables in test are
> in single extents (whereas prod has many extents for the large
> tables). This also seems to be the fastest method. However, I read a
> post from '98 that said that onunload/method (at least for the entire
> database) is not a good idea because:
>
> "Since onunload is taking a binary dump of the table
> (i.e. page images), you are really not "defragmenting" it when you
> onload it
> back in. That is, if all the pages were not full when you onunloaded,
> they will
> remain not full when you onload (it will, as mentioned above, probably
> result in
> a single extent, though). "
>
> Is that still true? Will there still be wasted space in each page,
> even the extents are merged into one? And if so, will it be
> detrimental to performance?
>
> 2. I've tried the other methods with varying degrees of success. After
> I modify the extent, and run, say, an alter fragment on it, often
> times the number of extents don't change at all or by very little
> (like 204 to 171, in one case). I added a 10GB chunk to the dbspace,
> just to make sure I had plenty of contiguous space, and still had very
> inconsistent results. And thats with initial/next extents about as
> large as the whole table. (The table size for these tables were less
> then about 3.5GB's each, and I'm using 10.00.FC6 and FC7).
> Does anyone know what could be wrong? Am I missing a step or two?
>
Yes, alter fragement init into a new dbspace (then move the other
tables into that dbspace as well).
That way you have contiguous free space to move the table into.
> Thanks for ANY advice regarding this, and feel free to correct any
> mistakes I've made. I/We have many, many instances to do, and I'd like
> to get the methodology down pat!
>
> Nate
When you use any of the techniques for increasing the next extent size you
need to be sure that you currently have enough contiguous free space to
build the table into. If you haven't then the table will still be
fragmented. There are a number of ways to check for contiguous free space.
One is to use oncheck for the dbsapce. Another is to use sysmaster and to
find the free extents from that.
Regards
Malcolm
-----Original Message-----
From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]
On Behalf Of natebsi@gmail.com
Sent: 20 June 2008 01:07
To: informix-list@iiug.org
Subject: Methods for Reducing Extents
Hi all,
I'm aware this subject has been discussed at nauseum for many years,
but I just want to make sure the info I have is still up to date.
The methods I know about are:
alter table <tablename> modify next 100000; (where 100000 = a number
roughly the size of the table)
alter fragment on table <tablename> init in <same dbspace>;
or:
alter table <tablename> modify next 100000; (where 100000 = a number
roughly the size of the table)
alter index <an index for the table in question> to cluster;
or:
unload to filename
select * from <tablename>;dbschema -t tablename -d databasename >> tablename.sql (alter .sqlfile to increase initial/next extents)
drop table tablename;recreate table with tablename.sql
load from filenameinsert into <tablename>;
or:
dbexport the entire database (if wishing to do many tables)
drop database;
modify the dbexport.sql file to change the initial/next extents for
large tables
dbimport the database
or:
onunload database (or just a table)
drop/recreate the table or entire database
onload the database or table
Does that sound right?
Questions:
1. The onunload method seems to work. That is, I onunload from prod
and insert into the test instance, and almost all tables in test are
in single extents (whereas prod has many extents for the large
tables). This also seems to be the fastest method. However, I read a
post from '98 that said that onunload/method (at least for the entire
database) is not a good idea because:
"Since onunload is taking a binary dump of the table
(i.e. page images), you are really not "defragmenting" it when you
onload it
back in. That is, if all the pages were not full when you onunloaded,
they will
remain not full when you onload (it will, as mentioned above, probably
result in
a single extent, though). "
Is that still true? Will there still be wasted space in each page,
even the extents are merged into one? And if so, will it be
detrimental to performance?
2. I've tried the other methods with varying degrees of success. After
I modify the extent, and run, say, an alter fragment on it, often
times the number of extents don't change at all or by very little
(like 204 to 171, in one case). I added a 10GB chunk to the dbspace,
just to make sure I had plenty of contiguous space, and still had very
inconsistent results. And thats with initial/next extents about as
large as the whole table. (The table size for these tables were less
then about 3.5GB's each, and I'm using 10.00.FC6 and FC7).
Does anyone know what could be wrong? Am I missing a step or two?
Thanks for ANY advice regarding this, and feel free to correct any
mistakes I've made. I/We have many, many instances to do, and I'd like
to get the methodology down pat!
Nate
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list