extents primer - what to wory about and when to worry?

Is there a good primar on this subject? I see a lot of posts on the subject and I know I have at least one table at 150 extents and climbing. Sorry for the inexperianced question, but the info I have found in the manual is more about how to calc and set extent size more so than it is how to administer/what to watch and worry about type info and I inherited this DB.

"freakyfreak" <emebohw2@netscape.net> wrote > Is there a good primar on this subject? I see a lot of posts on the > subject and I know I have at least one table at 150 extents and > climbing. Sorry for the inexperianced question, but the info I have > found in the manual is more about how to calc and set extent size more > so than it is how to administer/what to watch and worry about type > info and I inherited this DB. You did not mention the version. If it is 7.x or pre 9.40, then you have to worry about 2 things: (a) number of extents should not be too close to max number of extents. Max number of extents is dependent on the table size and some other extents. Usually it maxes out around 230 extents, though for large tables it can be lower also. We have one table which can take only 167 extents. It can easily be solved by changing EXTENT SIZE and NEXT SIZE of a table. However the table will be off-line if you have to reorg the table to reduce the number of extents. It can't be done on-line. (b) number of pages in a table. Since Informix uses a 4 byte rowid, no table can have more than 16 million pages. There is no solution to this except fragment the table. In that case each fragment has 16 million pages restriction. IIRC (a) has been resolved in 9.40 but not (b). If you are using pre 9.40 version or 7.x, I can give you a script which can tell how many extents can the table take. Or the number of pages of that table. Mail me.

On Mon, 11 Oct 2004 13:12:53 -0400, rkusenet wrote: Correcting some mis-conceptions below: > "freakyfreak" <emebohw2@netscape.net> wrote > >> Is there a good primar on this subject? I see a lot of posts on the subject >> and I know I have at least one table at 150 extents and climbing. Sorry for >> the inexperianced question, but the info I have found in the manual is more >> about how to calc and set extent size more so than it is how to >> administer/what to watch and worry about type info and I inherited this DB. > > You did not mention the version. If it is 7.x or pre 9.40, then you have to > worry about 2 things: > > (a) number of extents should not be too close to max number of > extents. Max number of extents is dependent on the table size and some > other extents. Usually it maxes out around 230 extents, though for large > tables it can be lower also. We have one table which can take only 167 > extents. It can easily be solved by changing EXTENT SIZE and NEXT SIZE > of a table. However the table will be off-line if you have to reorg the > table to reduce the number of extents. It can't be done on-line. Max extents for a table is dependent on the number of 'special' columns the table contains. Specials include Blobs, SBlobs, VARCHAR, LVARCHAR, Opaque type columns. The space remaining on the table's TABLESPACE or partition page after the header information is shared by extent entries and special column type details. > (b) number of pages in a table. Since Informix uses a 4 byte > rowid, no table can have more than 16 million pages. There is no > solution to this except fragment the table. In that case each fragment > has 16 million pages restriction. This restriction is per partition of fragment not per table, that's not clear above. > IIRC (a) has been resolved in 9.40 but not (b). > > If you are using pre 9.40 version or 7.x, I can give you a script which can > tell how many extents can the table take. Or the number of pages of that > table. Mail me. The solution is to compress the table to fewer extents which can also help performance if there is not good locality in the data (ie very old records are frequently accessed). There are several ways to compress or reorg a table: 1 - Export the data, drop and recreate the table with a larger EXTENT SIZE and NEXT SIZE, reload the data (you can use dbexport/dbimport or the hploader for this). 2 - Create a new table with larger EXTENT SIZE and NEXT SIZE, copy the data from old table to new, drop old table, rename new table (you can use my dbcopy utility to do the copying quickly). 3 - After altering the NEXT SIZE of the table, LOCK TABLE ... IN EXCLUSIVE MODE, ALTER FRAGMENT ... INIT IN (this can be done for fragmented or non-fragmented tables and even into the same dbspace that contains the table currently. This tends to be fastest if you have the logical log space (make the table RAW and lock the table). Art S. Kagel

freakyfreak wrote: > Is there a good primar on this subject? I see a lot of posts on the > subject and I know I have at least one table at 150 extents and > climbing. Sorry for the inexperianced question, but the info I have > found in the manual is more about how to calc and set extent size more > so than it is how to administer/what to watch and worry about type > info and I inherited this DB. All replies so far are good, but I think you are also wanting to know when to worry? [see subject:-] In the olde dayes, we had to start getting concerned about performance deterioration after 8 extents, because the engine used to allocate only 8 extent-pointers within the TBLspace of the table, and then it allocated another indirect page which contained extent-pointers for the additional extents in the table. This means that accessing extents 9 and above often involved a double-page read just to find the address of the extent. Of course, caching in the buffers tended to soften the blow but it depended on how busy the table and engine is. Now, the pointers to the extents are not limited so severely to only 8 direct pointers and the rest being indirect pointers. Frankly, I've never learned the new technique, but it's better. Therefore we need to worry less about performance impacts from double-page reads for control information. However, there is still the old performance effect of scattered pages in a file/table - if the contents of a disk object are scattered badly, and the file/table is often being read sequentially then the disk heads must jump around a lot, and that causes a physical slowdown. This effect is more noticable when the machine and disks get loaded up with work. You should probably strive for less than 20 extents merely to improve performance due to this effect. Other people might have more rigourous opinions on this; I still tend to allocate for small numbers of extents so I really haven't "felt" much and have little experience on when the number of extents becomes bad for performance. This effect is worse if the table is being used for a lot of sequential scanning across significant parts of the table - and that can include "sequential reads" of the index itself during evaluation of things like "BETWEEN 10000 and 20000". If the table is mostly being probed to get a very small number of rows during SELECTS etc, then the scattering of the extents probably makes little difference.

Just to add a couple of points to Art's write-up: > 2 - Create a new table with larger EXTENT SIZE and NEXT SIZE, copy the data > from old table to new, drop old table, rename new table (you can use my dbcopy > utility to do the copying quickly). If you use SQL to do the copy it will go vastly more quickly if you create the target table as type RAW. You'll have to ALTER it back to STANDARD before you can put any indexes on it, though. It'll also go a lot faster if the various objects are on different disks. dbcopy comes as source code and on the platform I tried it on (HP-UX) the attempt to make it barfed because we didn't have the ANSI version of the C compiler. Good luck, Andy