 | |
Re: Question on Extent Size
comp.databases.informix comp.databases.informix
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Re: Question on Extent Size -
09-11-2003
, 01:55 AM
|
Hi All We are running informix 9.21 on Solaris, the page size is 2k . The db was setup by prev DBA, as i am new to Informix (from oracle) I have few queries on extent size. We have around 100 tables , every table has created with default extent size (16k and next 16k). Some of the tables are showing extent size like table1 : 1208787 next ext 120878 table2: 2472 next 247 56723 next 5672 etc, Some small tables shows still 16k and next extent 16 , But when i run the query to find the no of extents for each table still they show les than 8 extetns some of them even 1 or 2 extents. 1. Is above extents size are ok or will it affect any queries? 2. Do they have to be dropped and recreated? 3. The tables with what extent size needs to be recreated? 3. If i need to recreate , what is the first and next extent size i should give? |
in their own dbspace, so they're increasing contiguously when new extents
are added. So basically you're OK.
#2
| |||
| |||
|
|
Hi All We are running informix 9.21 on Solaris, the page size is 2k . The db was setup by prev DBA, as i am new to Informix (from oracle) I have few queries on extent size. We have around 100 tables , every table has created with default extent size (16k and next 16k). Some of the tables are showing extent size like table1 : 1208787 next ext 120878 table2: 2472 next 247 56723 next 5672 etc, Some small tables shows still 16k and next extent 16 , But when i run the query to find the no of extents for each table still they show les than 8 extetns some of them even 1 or 2 extents. 1. Is above extents size are ok or will it affect any queries? 2. Do they have to be dropped and recreated? 3. The tables with what extent size needs to be recreated? 3. If i need to recreate , what is the first and next extent size i should give? Any suggestions, guidelines highly appreciated. Thanks in Advance Kalpana |
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
sending to informix-list
#3
| |||
| |||
|
|
There are two options.... Fragmented Table : First extent size = (no. of rows * rowsize)/# of fragments. Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments this way you only need to rebuild the table once every quarter. Non Fragmented table : First extent size = (no. of rows * rowsize) Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments As a general rule, make sure you don't exceed more than 8extents per fragment for a table, this helps performance. Here is a sql for obtaining the extent report. select t.tabname,count(*) n_ext from sysmaster:sysextents e, <"database name">:systables t where e.tabname = t.tabname and dbsname = <"database name" and t.tabname not matches 'sys*' group by 1 having count(*) > 1 order by 2 desc; EOF Hope it helps. -Abraham --- KalpanaPai <kalpanapai (AT) hotmail (DOT) com> wrote: Hi All We are running informix 9.21 on Solaris, the page size is 2k . The db was setup by prev DBA, as i am new to Informix (from oracle) I have few queries on extent size. We have around 100 tables , every table has created with default extent size (16k and next 16k). Some of the tables are showing extent size like table1 : 1208787 next ext 120878 table2: 2472 next 247 56723 next 5672 etc, Some small tables shows still 16k and next extent 16 , But when i run the query to find the no of extents for each table still they show les than 8 extetns some of them even 1 or 2 extents. 1. Is above extents size are ok or will it affect any queries? 2. Do they have to be dropped and recreated? 3. The tables with what extent size needs to be recreated? 3. If i need to recreate , what is the first and next extent size i should give? Any suggestions, guidelines highly appreciated. Thanks in Advance Kalpana __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com sending to informix-list |
#4
| |||
| |||
|
|
There are two options.... Fragmented Table : First extent size = (no. of rows * rowsize)/# of fragments. Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments this way you only need to rebuild the table once every quarter. Non Fragmented table : First extent size = (no. of rows * rowsize) Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments As a general rule, make sure you don't exceed more than 8extents per fragment for a table, this helps performance. Here is a sql for obtaining the extent report. select t.tabname,count(*) n_ext from sysmaster:sysextents e, <"database name">:systables t where e.tabname = t.tabname and dbsname = <"database name" and t.tabname not matches 'sys*' group by 1 having count(*) > 1 order by 2 desc; EOF Hope it helps. -Abraham --- KalpanaPai <kalpanapai (AT) hotmail (DOT) com> wrote: Hi All We are running informix 9.21 on Solaris, the page size is 2k . The db was setup by prev DBA, as i am new to Informix (from oracle) I have few queries on extent size. We have around 100 tables , every table has created with default extent size (16k and next 16k). Some of the tables are showing extent size like table1 : 1208787 next ext 120878 table2: 2472 next 247 56723 next 5672 etc, Some small tables shows still 16k and next extent 16 , But when i run the query to find the no of extents for each table still they show les than 8 extetns some of them even 1 or 2 extents. 1. Is above extents size are ok or will it affect any queries? 2. Do they have to be dropped and recreated? 3. The tables with what extent size needs to be recreated? 3. If i need to recreate , what is the first and next extent size i should give? Any suggestions, guidelines highly appreciated. Thanks in Advance Kalpana __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com sending to informix-list |
sending to informix-list
#5
| |||
| |||
|
|
There are two options.... Fragmented Table : First extent size = (no. of rows * rowsize)/# of fragments. Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments this way you only need to rebuild the table once every quarter. Non Fragmented table : First extent size = (no. of rows * rowsize) Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments As a general rule, make sure you don't exceed more than 8extents per fragment for a table, this helps performance. Here is a sql for obtaining the extent report. select t.tabname,count(*) n_ext from sysmaster:sysextents e, <"database name">:systables t where e.tabname = t.tabname and dbsname = <"database name" and t.tabname not matches 'sys*' group by 1 having count(*) > 1 order by 2 desc; EOF Hope it helps. -Abraham --- KalpanaPai <kalpanapai (AT) hotmail (DOT) com> wrote: Hi All We are running informix 9.21 on Solaris, the page size is 2k . The db was setup by prev DBA, as i am new to Informix (from oracle) I have few queries on extent size. We have around 100 tables , every table has created with default extent size (16k and next 16k). Some of the tables are showing extent size like table1 : 1208787 next ext 120878 table2: 2472 next 247 56723 next 5672 etc, Some small tables shows still 16k and next extent 16 , But when i run the query to find the no of extents for each table still they show les than 8 extetns some of them even 1 or 2 extents. 1. Is above extents size are ok or will it affect any queries? 2. Do they have to be dropped and recreated? 3. The tables with what extent size needs to be recreated? 3. If i need to recreate , what is the first and next extent size i should give? Any suggestions, guidelines highly appreciated. Thanks in Advance Kalpana __________________________________ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com sending to informix-list |
sending to informix-list
sending to informix-list
#6
| |||
| |||
|
| Hi Kalpana, What has happened is "extent concatenation" ...when Informix engine tries to allocate space for the table ( in extents) it first tries to find space (if available) right next to the current extent of the table ... it looks like for your table you are in pretty good shape ... Actually, after Online 5 version, the number of extents rule of thumb does not matter too much ..(although its good to have a clean table) ... but in your case you don't really require to do anything since your table is existing in physically contiguous space (even though the extent is big) .. HTH Thanx much, Rajib Sarkar Advisory Software Engineer (RAS) IBM Data Management Group Ph : (602)-217-2100 Fax: (602)-217-2100 T/L : 667-2100 As long as you derive inner help and comfort from anything, keep it -- Mahatma Gandhi kalpanapai@hotmai l.com To: informix-list (AT) iiug (DOT) org (KalpanaPai) cc: Sent by: Subject: Re: Question on Extent Size owner-informix-li st (AT) iiug (DOT) org 09/11/2003 09:23 PM Please respond to kalpanapai Hi Abraham Thanks very much for your reply. As we are setting up a new database Server, i thought if i need to recreate any of the tables of having more extents. When i run the same query it does it return any rows, it means all the tables are having single extent correct me if i am wrong. And also if i run the dbschema and oncheck the following is the result . with dbschema for one table which is everyday growing table extent size 1208787 next size 120878 with oncheck foll is the output TBLspace Report for vam2:informix.asset Physical Address 3deb94 Creation date 09/05/2003 12:45:36 TBLspace Flags 901 Page Locking TBLspace contains VARCHARS TBLspace use 4 bit bit-maps Maximum row size 24249 Number of special columns 30 Number of keys 0 Number of extents 1 Current serial value 189122 First extent size 604393 Next extent size 60439 Number of pages allocated 565682 Number of pages used 14737 Number of data pages 14726 Number of rows 51347 Partition partnum 3146009 Partition lockid 3146009 Extents Logical Page Physical Page Size 0 3491a9 565682 Could anyone suggest me with ref to the above output, do i need recreate such tables? If so what is the allowable growth of extent at present it is showing 1208787 next size 120878 . If i get the approximate extent size i can check rest of the tables and recreate them in the new server. Also for first and next extent size cal ,rowsize you mean (Maximum row size )(24249) to be considered? Please advise me which are the other areas i need to consider, as i am importing the whole db to new server dbexport/dbimport. Any suggestions much appreciated Thanks Kalpana Abraham Kirubakaran <bull_informix (AT) yahoo (DOT) com> wrote in message news:<bjqtle$so2$1 (AT) terabinaries (DOT) xmission.com>... There are two options.... Fragmented Table : First extent size = (no. of rows * rowsize)/# of fragments. Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments this way you only need to rebuild the table once every quarter. Non Fragmented table : First extent size = (no. of rows * rowsize) Next extent size = ( .01* First extent size) Or to be more precise = ((no of rows expected per month *4)*rowsize)/#of fragments As a general rule, make sure you don't exceed more than 8extents per fragment for a table, this helps performance. Here is a sql for obtaining the extent report. select t.tabname,count(*) n_ext from sysmaster:sysextents e, <"database name">:systables t where e.tabname = t.tabname and dbsname = <"database name" and t.tabname not matches 'sys*' group by 1 having count(*) > 1 order by 2 desc; EOF Hope it helps. -Abraham === message truncated === |
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
sending to informix-list
#7
| |||
| |||
|
|
As a general rule, make sure you don't exceed more than 8extents per fragment for a table, this helps performance. |
From version 6, all extents are cached, so there is no real drop in
performance in terms of tracking extents after 8 extents. However, 1 extent
per fragment is best. If you get any were near 200 then you run the risk of
running out and getting errors. Any where in between and it depends on the
type of user access as to the affect on performance.
Cheers,
--
Mark.
+----------------------------------------------------------+-----------+
Quote:
|
Mark D. Stock mailto:mdstock (AT) MydasSolutions (DOT) com |//////// /| Mydas Solutions Ltd http://MydasSolutions.com |///// / //| +-----------------------------------+//// / ///| |We value your comments, which have |/// / ////| |been recorded and automatically |// / /////| |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ |
sending to informix-list
#8
| |||
| |||
|
|
Abraham Kirubakaran wrote: As a general rule, make sure you don't exceed more than 8extents per fragment for a table, this helps performance. The 8 extents general rule applies to OnLine up to and including version 5. From version 6, all extents are cached, so there is no real drop in performance in terms of tracking extents after 8 extents. However, 1 extent per fragment is best. If you get any were near 200 then you run the risk of running out and getting errors. Any where in between and it depends on the type of user access as to the affect on performance. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock (AT) MydasSolutions (DOT) com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.