RE: Extents maxed out???
The January 15, 1999 topic at the following Web page provides a good
explanation
of how many extents a table can have:
http://members.tripod.com/markscranton/monthly_topic.htm#January 15, 1999
-----Original Message-----
From: Sandeep Singh [mailto:ss64@cornell.edu]
Sent: Thursday, March 04, 1999 15:14
To: informix-list@iiug.org
Subject: Re: Extents maxed out???
I fixed the problem by unloading and then loading back again, but the
question
that is still looming large is that why did the Extents get maxed out at 88?
The other question that I have been thinking about is that why is there a
range
for limits on Extents (200-500)? What is the logic behind that? Is it just
that
contiguous space gets to be a limiting factor? Thanks for your time...
Sandeep
"Art S. Kagel" wrote:
> Sandeep Singh wrote:
>
> > Hello all,
>
> > I am facing a strange incidence of a table that has stopped growing
> > and am writing this in the hope that someone can give me any tips on
> > what might be going on.
>
> > I am adminitering an OnLine (7.2) instance working as a backend for
> > BPCS (6.004) in which a table is not allowing any more inserts from
> > BPCS. I tried to insert a row manually and it gave me an ISAM error
> > 136, which means that the maximum number of extents have been
> > allocated for the table.
>
> > According to the Informix Error listing, this could be caused either
> > because of the dbspace getting full (which I am certain is not the
> > case, besides all the other tables are growing) or that too many extents
>
> > have been allocated to that table -which is also not the case because
> > the "nextns" value from onstat -t for that table is merely 88 and
> > although the nextns value may not be an accurate index of the total
> > number of extents for that table, so I eye-balled the oncheck -pT
> > listing for that table and 88 appears to be the ball-park figure.
>
> It is entirely possible that 88 extents is too many. As finderr -136
> reports the maximum number of extents for a table is betwee 50 and 200
> depending on many factors including the system pagesize (usually 2K).
> You need to reorganize the table into fewer extents. Execute the
> following commands:
>
> ALTER TABLE mytable NEXT SIZE 7000; -- Make sure no more than 2 extents
> ALTER FRAGMENT FOR TABLE mytable INIT IN same-or-different-dbspace;
> ALTER TABLE mytable NEXT SIZE 1000; -- Or any other large reasonable #>
> Your 6500+ page table will now reside in only one or two extents,
> assuming there are 7000 contiguous pages available, and there will
> be a few hundred pages unused for data/index expansion. Something else
> you can do to improve performance and prevent degradation over time is
> to detach some or all of those many indexes so that data pages will be
> contiguous (as will index pages) and not interleaved with index pages
> from several different indexes.
>
> > The table schema does not have a FIRST EXTENT/NEXT EXTENT
> > defined and so the defaults are:
>
> > FIRST EXTENT: 8
> > (CURRENT) NEXT EXTENT: 128
>
> Actually the NEXT EXTENT started out as 8 pages but extent doubling has
> increased it over time to 128. You should explicitely set it to hold
> all the data for a determined time period (like 1 month or 1 year) in
> a single extent. The period can be determined by any of several
> popular rules various DBAs use:
>
> o Typical time period of records normally scanned so no more than 2
> extents will need to be accessed by a typical query.
> o Large enough so that I will not see the table grow all year/qtr/month.
> o The size of a typical chunk I add to this dbspace (or 1/2, or 1/4 of
> one).
> o Time period that corresponds to how often I clean out old data so
> that as I clean up a contiguous extent that extent will be reused by
> the next period's data contiguously.
>
> Have fun.
>
> Art S. Kagel