Re: My Whole World Fell Apart!!

What you've encountered here is 2 different Informix features: 1) Extent doubling. You'll notice that the smallest extent is 64 pages, or 128kb. After the (I think) 16th extent, Informix figures out that you're getting too big, so it doubles the size of the next extent (to 256). Each 16 after that, it does the same. 2) Extent concatenation. If there is a sufficiently big space available right after the last allocated extent (with some other restrictions), instead of making 2 extents, the engine just makes the last extent bigger. So, it's really allocating what you asked for, just saving some overhead. Both features are well documented. Vic Samuels David NTSE wrote: > I have a table called scpard it was created with an initial extent of > 620 Kb and a next extent size of 124 Kb. > > My problem is with rate at which extents are being added. Currently the > table is adding extents at a rate of 5 per week. There are 38 extents > and the calculated max extents is 225. As we're a 24x7 site I only have > a couple of scheduled downtimes a year. > > I ran the following SQL Query, but did not get the results I expected. > Baring in mind that PE_SIZE is in 2 Kb Pages. I thought the initial was > going to be 310, which it was. Then my whole world fell apart when the > next extent was not 62 and all other extent sizes seemed to be randomly > sized. I really do need to know why this is, in order to recalculate > the next extent size correctly. > > Is there some sort of weighted factor like Oracle's PCTINCREASE and > furthermore what is the rationale behind this bizarre extent allocation > sizes. Or why even specify a next extent as it seems to ignore it > anyway! > > SQL QUERY > > SELECT tabname, pe_extnum, pe_size > FROM systables t, sysmaster:sysptnext n > WHERE tabname="scpard" > AND n.pe_partnum=t.partnum > ORDER BY 2> > SQL QUERY RESULTS > > tabname pe_extnum pe_size > > scpard 0 310 > scpard 1 186 > scpard 2 3286 > scpard 3 9176 > scpard 4 62 > scpard 5 124 > scpard 6 248 > scpard 7 62 > scpard 8 62 > scpard 9 124 > scpard 10 62 > scpard 11 62 > scpard 12 124 > scpard 13 248 > scpard 14 62 > scpard 15 186 > scpard 16 124 > scpard 17 124 > scpard 18 248 > scpard 19 248 > scpard 20 124 > scpard 21 248 > scpard 22 248 > scpard 23 248 > scpard 24 124 > scpard 25 124 > scpard 26 124 > scpard 27 124 > scpard 28 248 > scpard 29 124 > scpard 30 124 > scpard 31 124 > scpard 32 248 > scpard 33 248 > scpard 34 248 > scpard 35 248 > scpard 36 248 > scpard 37 496 > scpard 38 248 > > I Look Forward to your replies. > > David L. Samuels > Informix Database Administrator > Siemens Microelectronics Ltd. > Tel: +44 191 280 4056 > E-Mail: David.Samuels@nts.hl.siemens.de -- Vic Goldberg Manager, DBA Team, Cornell University (607) 254-7441 (Voice) (607) 255-6982 (Fax)