Table Extents Problem--No more Extents--
Hello All, I would like to know that how can i monitor the no of extents for the table, so that i could know in advance before the problem occurs. Also let me know the best possible way of handling the situation, where we are unable to insert new rows into a table due to no of extents. Also let me know that what is the maximum limit of extents for one table in the page size of 2k. I have verified that i have sufficient dbspace available for the allocation. I will be looking forward for your response. Regards, Omer Saeed Khan
This topic has been widely discussed, and should be in the FAQ, probably
isn't.
You can have as many extents for a table as you can describe in the page
header - which in your case is a single 2k page.
The amount of space it takes to describe the table will affect how much room
is left to describe extents. A complicated table structure will take more
space than a simple table structure (complex datatypes, etc).
Once you hit a max extents situation, you will not be able to add new
extents to the table until the table is reorganized - lots of ways to do
that.
There have been a number of scripts suggested over the years to determine
this situation and report it. Here is quick and dirty one I think I posted
years ago.
Usage: ext_warn.sh rem_ext Where rem_ext is the threshold of how many
extents you have left on any given table.
ext_warn.sh 8 will report all tables that can have 8 or fewer more
extents.
--- ext_warn.sh
echo "
select {+ORDERED,INDEX(a,syspaghdridx)}
trunc(pg_frcnt/8) frext, partaddr(dbsnum,pg_pagenum) partnum
from sysdbspaces b, syspaghdr a
where a.pg_partnum = partaddr(dbsnum,1)
and pg_flags = 2
and pg_frcnt < (8*$1)
into temp t1 with no log;
select dbsname, tabname, frext
from systabnames a, t1 b
where a.partnum = b.partnum
order by frext desc; " | dbaccess sysmaster -
---
I suggest googling the group for table reorg to find the dozen or so ways
people reorg their tables.
I don't know that the table page header has been widely discussed, it is
rather an esoteric topic. But you might google it as well.
Google also for max extents to find the half-dozen other scripts which check
for this situation.
Regards,
Jack Parker
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of
OMER KHAN
Sent: Thursday, June 21, 2007 4:49 AM
To: ids@iiug.org
Subject: Table Extents Problem--No more Extents-- [9397]
Hello All,
I would like to know that how can i monitor the no of extents for the table,
so that i could know in advance before the problem occurs.
Also let me know the best possible way of handling the situation, where we
are
unable to insert new rows into a table due to no of extents. Also let me
know
that what is the maximum limit of extents for one table in the page size of
2k.
I have verified that i have sufficient dbspace available for the allocation.
I will be looking forward for your response.
Regards,
Omer Saeed Khan
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Just to add to Jack's response: A table will hold somewhere in the neighborhood of 200 extents. The exact number, as Jack intimated, is dependent on the structure of the table and the server's pages size. The table's TABLESPACE TABLESPACE page aka partition header page contains the table's fixed vital stats (#rows, #pages, etc.), an index summary, the list of extents that make up the table (location and size), and the details of any 'special columns'. Special columns include Simple Large Objects, Smart Large Objects, and variable length columns such as varchars. The more special columns a table has the fewer extents the table can contain. SLOBS (BYTE & TEXT) columns are the worst offenders as one SLOB will take the space needed by six extent records. I might adjust Jack's script, he divides the free byte count (pg_frcnt) on the page by 8 as an extent entry requires 8 bytes, but IB that each extent takes up a slot in the page so that would mean that each extent entry requires 10 bytes not 8. Art ----- Original Message ----- From: Omer Khan <ids@iiug.org> At: 6/21 4:49:19 Hello All, I would like to know that how can i monitor the no of extents for the table, so that i could know in advance before the problem occurs. Also let me know the best possible way of handling the situation, where we are unable to insert new rows into a table due to no of extents. Also let me know that what is the maximum limit of extents for one table in the page size of 2k. I have verified that i have sufficient dbspace available for the allocation. I will be looking forward for your response. Regards, Omer Saeed Khan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Maximum number of extents? The maximum number of extents is derived from the maximum size of a table (32GB for versions 7 and 9 w/o enabling large chunks) associated with the Informix¢s feature of automatic extent doubling after every 16 extents. So, if using the default 1st and next extent sizes to be 8k (or 4pg on a 2K page size system), I interpret the calculation as: 1st next(auto) tableSize tablesize tot_num_ext 8k 8k (15*8) 120k 15 8k 16k (120+15*16) 360k 30 8k 32k (360+15*32) 840k 45 8k 64k (840+15*64) 1800k 60 8k 128k (1800+15*128) 3720k 75 8k 256k (3720+15*256) 7560k 90 8k 512k (7560+15*512) 15240k 105 8k 1024k (15240+15*1024) 30600k 120 8k 2048k (30600+15*2048) 61320k 135 8k 4096k (61320+15*4096) 122760k 150 8k 8192k (122760+15*8192) 245640k 165 8k 16384k (245640+15*16384) 491400k 180 8k 32768k (491400+15*32768) 982920k 195 8k 65536k (1048440+15*65536) 1965960k 210 8k 131072k (1965960+(15*131072) 3932040k 225 8k 262144k (3932040+15*262144) 7864200k 240 8k 524288k (7864200+15*524288) 15728520k 255 8k 1048576k (15728520+15*1048576) 31457160k 260 8k 2097152k 31457160+2097152 33554312k 261 Basically, for the 1st 15 extents before the next extent size is internally doubling, the tablesize is 120kb, after the 30th extents, the tablesize is 360kb, and so on. So, for the 2k page size system, the max number of extent is 260+1=261. IDS cannot grab any more extent because it has reached its limit (table limit=32GB). (My experiment shows that the maximum number of extents can go beyond 261 if my dbspace consists of many very small chunks) If your table (not fragmented) hits the 32gb limit, I suppose you can also get the message "no more extents" even though your table does not have a lot of extents -- so don't be confused, Two things you can monitor: number of extents and/or the table size. I welcome any comments. Kern -- ----- Original Message ---- From: OMER KHAN <oskhan@i2cinc.com> To: ids@iiug.org Sent: Thursday, June 21, 2007 4:48:54 AM Subject: Table Extents Problem--No more Extents-- [9397] Hello All, I would like to know that how can i monitor the no of extents for the table, so that i could know in advance before the problem occurs. Also let me know the best possible way of handling the situation, where we are unable to insert new rows into a table due to no of extents. Also let me know that what is the maximum limit of extents for one table in the page size of 2k. I have verified that i have sufficient dbspace available for the allocation. I will be looking forward for your response. Regards, Omer Saeed Khan ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks for the correction. j. -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org]On Behalf Of ART KAGEL, BLOOMBERG/ 731 LEXIN Sent: Thursday, June 21, 2007 9:40 AM To: ids@iiug.org Subject: Re: Table Extents Problem--No more Extents-- [9405] Just to add to Jack's response: A table will hold somewhere in the neighborhood of 200 extents. The exact number, as Jack intimated, is dependent on the structure of the table and the server's pages size. The table's TABLESPACE TABLESPACE page aka partition header page contains the table's fixed vital stats (#rows, #pages, etc.), an index summary, the list of extents that make up the table (location and size), and the details of any 'special columns'. Special columns include Simple Large Objects, Smart Large Objects, and variable length columns such as varchars. The more special columns a table has the fewer extents the table can contain. SLOBS (BYTE & TEXT) columns are the worst offenders as one SLOB will take the space needed by six extent records. I might adjust Jack's script, he divides the free byte count (pg_frcnt) on the page by 8 as an extent entry requires 8 bytes, but IB that each extent takes up a slot in the page so that would mean that each extent entry requires 10 bytes not 8. Art ----- Original Message ----- From: Omer Khan <ids@iiug.org> At: 6/21 4:49:19 Hello All, I would like to know that how can i monitor the no of extents for the table, so that i could know in advance before the problem occurs. Also let me know the best possible way of handling the situation, where we are unable to insert new rows into a table due to no of extents. Also let me know that what is the maximum limit of extents for one table in the page size of 2k. I have verified that i have sufficient dbspace available for the allocation. I will be looking forward for your response. Regards, Omer Saeed Khan **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.