Re: Space Allocation for Indexes

mohitanchlia@gmail.com wrote: > On Feb 7, 10:16 am, mohitanch...@gmail.com wrote: > >>On Feb 7, 9:46 am, jpren...@yahoo.com wrote: >> >> >> >> >> >> >>>On Feb 6, 7:49 pm, mohitanch...@gmail.com wrote: >> >>>>Version IDS 10: >> >>>>I run below query to get free pages in index tblspace for individual >>>>indexes: >> >>>>select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname >>>>database, st2.tabname partition, nptotal, npused, >>>> npdata, (npused - npdata) npindex , nptotal-npused freepages >>>>from systabnames st1, systabnames st2, sysptnhdr sph >>>>where st1.partnum = sph.lockid and st2.partnum = sph.partnum >>>> and st1.dbsname = 'dbname' >>>> and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' >>>> group by 1,2,3,4,5,6,7,8 >>>>order by 2, 3, 1; >> >>>>But what I am seeing is that nptotal and npused pages are same. What I >>>>don't understand is that this index that I am looking at grows >>>>everyday. Question is that if free pages is 0 and index grows everyday >>>>then how and where it's getting the space from. I consistently see >>>>same number of npused and nptotal pages. For example if npused is 100 >>>>and nptotal is 100, I see the same number even if I run this query >>>>after a week. But I know for sure that 1000s of rows have been added >>>>in between. This index is a serial value and is unique. >> >>>Are rows also being deleted, if not currently, then in the past? For >>>example, I created a small table with a detached index. I inserted >>>1000 rows. This set my nptotal to 10 and npused to 8. I then deleted >>>every row in my table, made sure the btscanner cleaned my index, and >>>again checked npused and nptotal. While obviously nptotal shouldn't >>>change, npused also didn't change. However, oncheck -pT for that >>>index partition now showed 8 free pages (1 was the bit map page and >>>then there is still 1 page for the empty root node of the index). So >>>my guess is you are deleting rows in your table, but npused doesn't go >>>down. So nptotal - npused is not an accurate way to check for free >>>pages in a detached index. >> >>>I believe the product use to decrement npused if pages got totally >>>free, however I think there were some bugs with that, and I think now >>>npused doesn't get decremented anymore. It just relies on the bitmap >>>pages to find totally "free/empty" pages. So nptotal - npused is >>>really only an accurate count of pages that have been allocated to the >>>table's extent list, but haven't ever been used yet. Once a page gets >>>used, and then all the data on it deleted (either row data in tables >>>or key values in indices) the only way to tell if it's "free" is to >>>look at the bitmap page. >> >>>Jacques- Hide quoted text - >> >>>- Show quoted text - >> >>That's part of the puzzle. These rows are never deleted because it's >>supposed to hold historic data.- Hide quoted text - >> >>- Show quoted text - > > > I think I found the answer. As you mentioned, even for Index pages I > need to look at bitmap page. So I am running a query now that takes > all the partitions in index dbspace and looks for bitmap = 0. This > gives me all the free pages in the partition for that index. I'll run > it after few days to see if the unused page count is decreasing. It > should decrease as we add more rows. What are you trying to achieve from a Business / Function perspective. Having absolute finite details about the number of pages used is going to provide you with??? From a slightly different perspective : What sort of activity happens with the table that the indexes support (i.e. mass insert / mass deletes / OLTP etc. blah de blah). What is your FILLFACTOR when you create the index??? That will have a big impact on your calculations. Why not just set up the index to be "roughly 80%" full, and then when you allocate another extent, you .. .need to do some maintenance. KISS. Just my (devaluing) 2 cents worth

On Feb 8, 5:53 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > mohitanch...@gmail.com wrote: > > On Feb 7, 10:16 am, mohitanch...@gmail.com wrote: > > >>On Feb 7, 9:46 am, jpren...@yahoo.com wrote: > > >>>On Feb 6, 7:49 pm, mohitanch...@gmail.com wrote: > > >>>>Version IDS 10: > > >>>>I run below query to get free pages in index tblspace for individual > >>>>indexes: > > >>>>select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > >>>>database, st2.tabname partition, nptotal, npused, > >>>>       npdata, (npused - npdata) npindex , nptotal-npused freepages > >>>>from systabnames st1, systabnames st2, sysptnhdr sph > >>>>where st1.partnum = sph.lockid and st2.partnum = sph.partnum > >>>>    and st1.dbsname = 'dbname' > >>>>    and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' > >>>>    group by 1,2,3,4,5,6,7,8 > >>>>order by 2, 3, 1; > > >>>>But what I am seeing is that nptotal and npused pages are same. What I > >>>>don't understand is that this index that I am looking at grows > >>>>everyday. Question is that if free pages is 0 and index grows everyday > >>>>then how and where it's getting the space from. I consistently see > >>>>same number of npused and nptotal pages. For example if npused is 100 > >>>>and nptotal is 100, I see the same number even if I run this query > >>>>after a week. But I know for sure that 1000s of rows have been added > >>>>in between. This index is a serial value and is unique. > > >>>Are rows also being deleted, if not currently, then in the past?  For > >>>example, I created a small table with a detached index.  I inserted > >>>1000 rows.  This set my nptotal to 10 and npused to 8.  I then deleted > >>>every row in my table, made sure the btscanner cleaned my index, and > >>>again checked npused and nptotal.  While obviously nptotal shouldn't > >>>change, npused also didn't change.  However, oncheck -pT for that > >>>index partition now showed 8 free pages (1 was the bit map page and > >>>then there is still 1 page for the empty root node of the index).  So > >>>my guess is you are deleting rows in your table, but npused doesn't go > >>>down.  So nptotal - npused is not an accurate way to check for free > >>>pages in a detached index. > > >>>I believe the product use to decrement npused if pages got totally > >>>free, however I think there were some bugs with that, and I think now > >>>npused doesn't get decremented anymore.  It just relies on the bitmap > >>>pages to find totally "free/empty" pages.  So nptotal - npused is > >>>really only an accurate count of pages that have been allocated to the > >>>table's extent list, but haven't ever been used yet.  Once a page gets > >>>used, and then all the data on it deleted (either row data in tables > >>>or key values in indices) the only way to tell if it's "free" is to > >>>look at the bitmap page. > > >>>Jacques- Hide quoted text - > > >>>- Show quoted text - > > >>That's part of the puzzle. These rows are never deleted because it's > >>supposed to hold historic data.- Hide quoted text - > > >>- Show quoted text - > > > I think I found the answer. As you mentioned, even for Index pages I > > need to look at bitmap page. So I am running a query now that takes > > all the partitions in index dbspace and looks for bitmap = 0. This > > gives me all the free pages in the partition for that index. I'll run > > it after few days to see if the unused page count is decreasing. It > > should decrease as we add more rows. > > What are you trying to achieve from a Business / Function perspective. > > Having absolute finite details about the number of pages used is going to provide you with??? > >  From a slightly different perspective : > > What sort of activity happens with the table that the indexes support (i.e. mass insert / mass deletes / OLTP etc. blah de blah). > > What is your FILLFACTOR when you create the index??? That will have a big impact on your calculations. > > Why not just set up the index to be "roughly 80%" full, and then when you allocate another extent, you .. .need to do some > maintenance. KISS. > > Just my (devaluing) 2 cents worth- Hide quoted text - > > - Show quoted text - I am trying to calculate how much space is currently being used and over the period how much more it will grow. This is being done to calculate if current index dbspace is enough to house all the indexes. How can I find out the fillfactor set for an Index ? I looked at sysindexes tables

mohitanchlia@gmail.com wrote: > On Feb 8, 5:53 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > >>mohitanch...@gmail.com wrote: >> >>>On Feb 7, 10:16 am, mohitanch...@gmail.com wrote: >> >>>>On Feb 7, 9:46 am, jpren...@yahoo.com wrote: >> >>>>>On Feb 6, 7:49 pm, mohitanch...@gmail.com wrote: >> >>>>>>Version IDS 10: >> >>>>>>I run below query to get free pages in index tblspace for individual >>>>>>indexes: >> >>>>>>select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname >>>>>>database, st2.tabname partition, nptotal, npused, >>>>>> npdata, (npused - npdata) npindex , nptotal-npused freepages >>>>> >>>>>>from systabnames st1, systabnames st2, sysptnhdr sph >>>>> >>>>>>where st1.partnum = sph.lockid and st2.partnum = sph.partnum >>>>>> and st1.dbsname = 'dbname' >>>>>> and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' >>>>>> group by 1,2,3,4,5,6,7,8 >>>>>>order by 2, 3, 1; >> >>>>>>But what I am seeing is that nptotal and npused pages are same. What I >>>>>>don't understand is that this index that I am looking at grows >>>>>>everyday. Question is that if free pages is 0 and index grows everyday >>>>>>then how and where it's getting the space from. I consistently see >>>>>>same number of npused and nptotal pages. For example if npused is 100 >>>>>>and nptotal is 100, I see the same number even if I run this query >>>>>>after a week. But I know for sure that 1000s of rows have been added >>>>>>in between. This index is a serial value and is unique. >> >>>>>Are rows also being deleted, if not currently, then in the past? For >>>>>example, I created a small table with a detached index. I inserted >>>>>1000 rows. This set my nptotal to 10 and npused to 8. I then deleted >>>>>every row in my table, made sure the btscanner cleaned my index, and >>>>>again checked npused and nptotal. While obviously nptotal shouldn't >>>>>change, npused also didn't change. However, oncheck -pT for that >>>>>index partition now showed 8 free pages (1 was the bit map page and >>>>>then there is still 1 page for the empty root node of the index). So >>>>>my guess is you are deleting rows in your table, but npused doesn't go >>>>>down. So nptotal - npused is not an accurate way to check for free >>>>>pages in a detached index. >> >>>>>I believe the product use to decrement npused if pages got totally >>>>>free, however I think there were some bugs with that, and I think now >>>>>npused doesn't get decremented anymore. It just relies on the bitmap >>>>>pages to find totally "free/empty" pages. So nptotal - npused is >>>>>really only an accurate count of pages that have been allocated to the >>>>>table's extent list, but haven't ever been used yet. Once a page gets >>>>>used, and then all the data on it deleted (either row data in tables >>>>>or key values in indices) the only way to tell if it's "free" is to >>>>>look at the bitmap page. >> >>>>>Jacques- Hide quoted text - >> >>>>>- Show quoted text - >> >>>>That's part of the puzzle. These rows are never deleted because it's >>>>supposed to hold historic data.- Hide quoted text - >> >>>>- Show quoted text - >> >>>I think I found the answer. As you mentioned, even for Index pages I >>>need to look at bitmap page. So I am running a query now that takes >>>all the partitions in index dbspace and looks for bitmap = 0. This >>>gives me all the free pages in the partition for that index. I'll run >>>it after few days to see if the unused page count is decreasing. It >>>should decrease as we add more rows. >> >>What are you trying to achieve from a Business / Function perspective. >> >>Having absolute finite details about the number of pages used is going to provide you with??? >> >> From a slightly different perspective : >> >>What sort of activity happens with the table that the indexes support (i.e. mass insert / mass deletes / OLTP etc. blah de blah). >> >>What is your FILLFACTOR when you create the index??? That will have a big impact on your calculations. >> >>Why not just set up the index to be "roughly 80%" full, and then when you allocate another extent, you .. .need to do some >>maintenance. KISS. >> >>Just my (devaluing) 2 cents worth- Hide quoted text - >> >>- Show quoted text - > > > I am trying to calculate how much space is currently being used and > over the period how much more it will grow. This is being done to > calculate if current index dbspace is enough to house all the indexes. > So ... What sort of activity happens with the table that the indexes support (i.e. mass insert / mass deletes / OLTP etc. blah de blah). > How can I find out the fillfactor set for an Index ? I looked at > sysindexes tables Only relevant at creation time, and not recorded (see previous posts).

On Feb 8, 8:26 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > mohitanch...@gmail.com wrote: > > On Feb 8, 5:53 am, "TBP (The Big Potato)" <T...@NotHere.Co.Uk> wrote: > > >>mohitanch...@gmail.com wrote: > > >>>On Feb 7, 10:16 am, mohitanch...@gmail.com wrote: > > >>>>On Feb 7, 9:46 am, jpren...@yahoo.com wrote: > > >>>>>On Feb 6, 7:49 pm, mohitanch...@gmail.com wrote: > > >>>>>>Version IDS 10: > > >>>>>>I run below query to get free pages in index tblspace for individual > >>>>>>indexes: > > >>>>>>select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname > >>>>>>database, st2.tabname partition, nptotal, npused, > >>>>>> npdata, (npused - npdata) npindex , nptotal-npused freepages > > >>>>>>from systabnames st1, systabnames st2, sysptnhdr sph > > >>>>>>where st1.partnum = sph.lockid and st2.partnum = sph.partnum > >>>>>> and st1.dbsname = 'dbname' > >>>>>> and dbinfo( 'dbspace', sph.partnum ) like 'dbspacename' > >>>>>> group by 1,2,3,4,5,6,7,8 > >>>>>>order by 2, 3, 1; > > >>>>>>But what I am seeing is that nptotal and npused pages are same. What I > >>>>>>don't understand is that this index that I am looking at grows > >>>>>>everyday. Question is that if free pages is 0 and index grows everyday > >>>>>>then how and where it's getting the space from. I consistently see > >>>>>>same number of npused and nptotal pages. For example if npused is 100 > >>>>>>and nptotal is 100, I see the same number even if I run this query > >>>>>>after a week. But I know for sure that 1000s of rows have been added > >>>>>>in between. This index is a serial value and is unique. > > >>>>>Are rows also being deleted, if not currently, then in the past? For > >>>>>example, I created a small table with a detached index. I inserted > >>>>>1000 rows. This set my nptotal to 10 and npused to 8. I then deleted > >>>>>every row in my table, made sure the btscanner cleaned my index, and > >>>>>again checked npused and nptotal. While obviously nptotal shouldn't > >>>>>change, npused also didn't change. However, oncheck -pT for that > >>>>>index partition now showed 8 free pages (1 was the bit map page and > >>>>>then there is still 1 page for the empty root node of the index). So > >>>>>my guess is you are deleting rows in your table, but npused doesn't go > >>>>>down. So nptotal - npused is not an accurate way to check for free > >>>>>pages in a detached index. > > >>>>>I believe the product use to decrement npused if pages got totally > >>>>>free, however I think there were some bugs with that, and I think now > >>>>>npused doesn't get decremented anymore. It just relies on the bitmap > >>>>>pages to find totally "free/empty" pages. So nptotal - npused is > >>>>>really only an accurate count of pages that have been allocated to the > >>>>>table's extent list, but haven't ever been used yet. Once a page gets > >>>>>used, and then all the data on it deleted (either row data in tables > >>>>>or key values in indices) the only way to tell if it's "free" is to > >>>>>look at the bitmap page. > > >>>>>Jacques- Hide quoted text - > > >>>>>- Show quoted text - > > >>>>That's part of the puzzle. These rows are never deleted because it's > >>>>supposed to hold historic data.- Hide quoted text - > > >>>>- Show quoted text - > > >>>I think I found the answer. As you mentioned, even for Index pages I > >>>need to look at bitmap page. So I am running a query now that takes > >>>all the partitions in index dbspace and looks for bitmap = 0. This > >>>gives me all the free pages in the partition for that index. I'll run > >>>it after few days to see if the unused page count is decreasing. It > >>>should decrease as we add more rows. > > >>What are you trying to achieve from a Business / Function perspective. > > >>Having absolute finite details about the number of pages used is going to provide you with??? > > >> From a slightly different perspective : > > >>What sort of activity happens with the table that the indexes support (i.e. mass insert / mass deletes / OLTP etc. blah de blah). > > >>What is your FILLFACTOR when you create the index??? That will have a big impact on your calculations. > > >>Why not just set up the index to be "roughly 80%" full, and then when you allocate another extent, you .. .need to do some > >>maintenance. KISS. > > >>Just my (devaluing) 2 cents worth- Hide quoted text - > > >>- Show quoted text - > > > I am trying to calculate how much space is currently being used and > > over the period how much more it will grow. This is being done to > > calculate if current index dbspace is enough to house all the indexes. > > So ... > > What sort of activity happens with the table that the indexes support (i.e. mass insert / mass deletes / OLTP etc. blah de blah). > > > How can I find out the fillfactor set for an Index ? I looked at > > sysindexes tables > > Only relevant at creation time, and not recorded (see previous posts). Just inserts 10000s every day. No deletes. Delete is performed only once a year. This table grows to have 60-80M rows