Re: Question on Extent Size
Oops !! did not look at the fact that you have less
than 8 extents per table.
Rajib, thanks for pointing it.
Just keep in mind , that the numbers you see from
dbschema is in Kbytes and the ones you see in the
Oncheck output is in pages.
All that you give to the engine is in Kbytes and all
that you get back from the engine is in Pages.
-Abraham.
--- Rajib Sarkar <rsarkar@us.ibm.com> wrote:
>
>
>
>
> 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@iiug.org
>
> (KalpanaPai) cc:
>
>
> Sent by:
> Subject: Re: Question on Extent Size
>
> owner-informix-li
>
>
> st@iiug.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@yahoo.com> wrote
> in message
> news:<bjqtle$so2$1@terabinaries.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