Extents Calculation
A DBA on IDS 11.10 wanted to size a table's first extent so all rows fit in one extent, but his page-count calculation came out ~2,000 pages short of the actual pages used. Art Kagel pointed out the error: each row also needs 4 bytes for its slot-table entry, so rows per page = usable page bytes / (rowsize + 4) — here 2020/337 = 5, giving 14,693 data pages — plus bitmap pages (one per ~4032 pages, assuming 4-bit bitmaps), totalling the observed 14,697. With that correction the arithmetic matched.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, IDS 11.10 FC3 on Solaris 10 I am doing a Reorg activity on few tables for which i need to calculate the first extent size. I tried the calculations below & got it right for some tables and not so accurate for others. ROWs to be inserted in new table to be created: 73462 Maximum row size: 333 Page size : 2K Usable bytes: 2020 ROWs/Page : 6 Pages required for 73462 rows = 12243 # trunc(73462\\\\6) Pages required for 73462 rows = 12243 + 8 more pages = 12251 extent size required to hold 73462 rows = 24502 So i give first extent size as 24502 and found that more than 1 extent is requred to hold the initial data as Number of pages used : 14697 this is more than what i calculated. No more rows expected in this table hence i want the first extent as the only extent to be allocated to hold the entire 73462 rows. The above calculations hold good for some tables but not for all as in above example. none of the table is fragmented. Any corretion for above calculations? Regards, Vikas
Hi, I tried the following formula from the documentation data_pages = rows / trunc(pageuse/(rowsize + 4)) 73462/trunc(2020/(333+4)) 73462/trunc(2020/337) 73462/5 14692 first extent = 14692 * 2 = 29384 The above calculation takes me closer to the first extent but not exact.. Maximum row size 333 Number of special columns 0 Number of keys 0 Number of extents 2 Current serial value 1 Current SERIAL8 value 1 Current REFID value 1 Pagesize (k) 2 First extent size 14692 Next extent size 8 Number of pages allocated 14700 Number of pages used 14697 Number of data pages 14693 Number of rows 73462 Intentionally i have kept the next extnet at 16. And there a difference between no of pages calculated (14692) and Number of pages used(14697). How do i get the exact size of first extent size to add with create table in order to have just one extent used for no of rows. Thanks Vikas. ******************************************************************************* Hi, IDS 11.10 FC3 on Solaris 10 I am doing a Reorg activity on few tables for which i need to calculate the first extent size. I tried the calculations below & got it right for some tables and not so accurate for others. ROWs to be inserted in new table to be created: 73462 Maximum row size: 333 Page size : 2K Usable bytes: 2020 ROWs/Page : 6 Pages required for 73462 rows = 12243 # trunc(73462\\\\6) Pages required for 73462 rows = 12243 + 8 more pages = 12251 extent size required to hold 73462 rows = 24502 So i give first extent size as 24502 and found that more than 1 extent is requred to hold the initial data as Number of pages used : 14697 this is more than what i calculated. No more rows expected in this table hence i want the first extent as the only extent to be allocated to hold the entire 73462 rows. The above calculations hold good for some tables but not for all as in above example. none of the table is fragmented. Any corretion for above calculations? Regards, Vikas
You forgot to add 4 bytes to the maximum length of each row to allow for the row's slot table entry. So: 2020 / (333 + 4) = 5.99 = 5 73462 / 5 = 14692.4 = 14693 14692 / 4096 = 3.5 = 4 bitmap pages 14693 + 4 = 14697! Voila! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 16, 2009 at 3:16 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hi, > > IDS 11.10 FC3 on Solaris 10 > > I am doing a Reorg activity on few tables for which i need to calculate the > first extent size. > > I tried the calculations below & got it right for some tables and not so > accurate for others. > > ROWs to be inserted in new table to be created: 73462 > Maximum row size: 333 > Page size : 2K > Usable bytes: 2020 > ROWs/Page : 6 > Pages required for 73462 rows = 12243 # trunc(73462\\\\6) > Pages required for 73462 rows = 12243 + 8 more pages = 12251 > extent size required to hold 73462 rows = 24502 > > So i give first extent size as 24502 and found that more than 1 extent is > requred to hold the initial data as > > Number of pages used : 14697 > this is more than what i calculated. > > No more rows expected in this table hence i want the first extent as the > only > extent to be allocated to hold the entire 73462 rows. > > The above calculations hold good for some tables but not for all as in > above > example. none of the table is fragmented. > > Any corretion for above calculations? > > Regards, > Vikas > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0023545bdaac0a4a6a04787b4ba6
Hello Mr Kagel! Yes, I forgot untill i found a doc By Oninit Tech Team under my Informix repository :) Every 4032nd logical page allocated to the table will automatically be a bitmap page. The equation is: (TRUNC((2048244) / 16)) * 32 = 4032 So i divided the total no of datapages with 4032 roundedup ROUNDUP((datapages/4032),0) I think thats exactly what you have mentioned! Thanks, Vikas ******************************************************************************* You forgot to add 4 bytes to the maximum length of each row to allow for the row's slot table entry. So: 2020 / (333 + 4) = 5.99 = 5 73462 / 5 = 14692.4 = 14693 14692 / 4096 = 3.5 = 4 bitmap pages 14693 + 4 = 14697! Voila! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 16, 2009 at 3:16 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hi, > > IDS 11.10 FC3 on Solaris 10 > > I am doing a Reorg activity on few tables for which i need to calculate the > first extent size. > > I tried the calculations below & got it right for some tables and not so > accurate for others. > > ROWs to be inserted in new table to be created: 73462 > Maximum row size: 333 > Page size : 2K > Usable bytes: 2020 > ROWs/Page : 6 > Pages required for 73462 rows = 12243 # trunc(73462\\\\6) > Pages required for 73462 rows = 12243 + 8 more pages = 12251 > extent size required to hold 73462 rows = 24502 > > So i give first extent size as 24502 and found that more than 1 extent is > requred to hold the initial data as > > Number of pages used : 14697 > this is more than what i calculated. > > No more rows expected in this table hence i want the first extent as the > only > extent to be allocated to hold the entire 73462 rows. > > The above calculations hold good for some tables but not for all as in > above > example. none of the table is fragmented. > > Any corretion for above calculations? > > Regards, > Vikas
No - Art is saying that when you are determining your row length you need to allow for an extra 4 bytes for each row. Every row on a data page has a 4 byte entry in the slot table at the bottom of the page. So if you have a row size of 330 bytes, add 4 to that for calculating how many rows will fit on a page. MM
Mike's right, the main point was that the OP didn't count the slot entry in the row length which caused him to be 2000 pages off. He's right though, I did also include the bitmap calculation without explicitely mentioning it, which was a little off (too lazy to look up the number of bits each holds). Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 16, 2009 at 12:52 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > No - Art is saying that when you are determining your row length you need > to > allow for an extra 4 bytes for each row. Every row on a data page has a 4 > byte > entry in the slot table at the bottom of the page. So if you have a row > size > of 330 bytes, add 4 to that for calculating how many rows will fit on a > page. > > MM > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --0015174766d622cd60047881175d
Hello, Yes Mike, you are right and I have taken 4 bytes in to consideration while calulating the no of rows that would fit on a page (2020 / rowsize + 4). I wanted to confirm the calculation of "bit map pages" needed for total no of rows and adding them to the "data pages" for extent calculation. For ex. Number of rows : 23701200 Maximum row size : 333 Using the above values I calculated: Number of data pages : 1394189 Bit Map pages : 346 Total no of pages : 1394535 Extent Size : 2789070 I hope the calculation is correct? Its ok if there is 1 or 2 pages more but less would add another extent.. Regards, Vikas ******************************************************************************* Mike's right, the main point was that the OP didn't count the slot entry in the row length which caused him to be 2000 pages off. He's right though, I did also include the bitmap calculation without explicitely mentioning it, which was a little off (too lazy to look up the number of bits each holds). Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 16, 2009 at 12:52 PM, MIKE MAGIE <jmmagie@yahoo.com> wrote: > No - Art is saying that when you are determining your row length you need > to > allow for an extra 4 bytes for each row. Every row on a data page has a 4 > byte > entry in the slot table at the bottom of the page. So if you have a row > size > of 330 bytes, add 4 to that for calculating how many rows will fit on a > page. > > MM Hello Mr Kagel! Yes, I forgot untill i found a doc By Oninit Tech Team under my Informix repository :) Every 4032nd logical page allocated to the table will automatically be a bitmap page. The equation is: (TRUNC((2048244) / 16)) * 32 = 4032 So i divided the total no of datapages with 4032 roundedup ROUNDUP((datapages/4032),0) I think thats exactly what you have mentioned! Thanks, Vikas ******************************************************************************* You forgot to add 4 bytes to the maximum length of each row to allow for the row's slot table entry. So: 2020 / (333 + 4) = 5.99 = 5 73462 / 5 = 14692.4 = 14693 14692 / 4096 = 3.5 = 4 bitmap pages 14693 + 4 = 14697! Voila! Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Nov 16, 2009 at 3:16 AM, VIKAS HIVARKAR <vikas.hivarkar@gmail.com>wrote: > Hi, > > IDS 11.10 FC3 on Solaris 10 > > I am doing a Reorg activity on few tables for which i need to calculate the > first extent size. > > I tried the calculations below & got it right for some tables and not so > accurate for others. > > ROWs to be inserted in new table to be created: 73462 > Maximum row size: 333 > Page size : 2K > Usable bytes: 2020 > ROWs/Page : 6 > Pages required for 73462 rows = 12243 # trunc(73462\\\\6) > Pages required for 73462 rows = 12243 + 8 more pages = 12251 > extent size required to hold 73462 rows = 24502 > > So i give first extent size as 24502 and found that more than 1 extent is > requred to hold the initial data as > > Number of pages used : 14697 > this is more than what i calculated. > > No more rows expected in this table hence i want the first extent as the > only > extent to be allocated to hold the entire 73462 rows. > > The above calculations hold good for some tables but not for all as in > above > example. none of the table is fragmented. > > Any corretion for above calculations? > > Regards, > Vikas
For most tables the bitmap page can track (including itself) 4032 pages; if
your oncheck -pt <database>:<table> indicates that the table uses 4 bit
bitmaps.