Any ideas

IDS 9.21 FC4 on HP-UX. Application: Lawson I've never seen this before. The application is inserting some rows into table dsinvlines, according to apps support and onstat -g sql. IToday, it has started to insert these very, very slowly, If I stop the app and re-build the table (alter fragment ... init in) the problem is immediately cured. Likewise if i run the alter fragment before starting the insert app. A few points: 1. The table is big, but far from the biggest in this half terabyte database (oncheck listing below) 2. The problem is reproducable on a test server by EMC Timefinder copy, so a hardware or disk problem can be discounted. 3. Via Timefinder I have a regression copy of the database to carry out analysis on. 4. I note that the no of pages allocated = number used exactly Any ideas gratefully received. thanks neil TBLspace Report for live:root.dsinvlines Physical Address e0002e Creation date 04/04/1999 05:01:41 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 219 Number of special columns 0 Number of keys 0 Number of extents 35 Current serial value 1 First extent size 4 Next extent size 100000 Number of pages allocated 2375004 Number of pages used 2375004 Number of data pages 1348673 Number of rows 11174221 Partition partnum 9437227 Partition lockid 9437227 Extents dsinvlines.onc (5%)

It might have to do with how we search for space in a tablespace. You have probably had some deletes in the table and/or have varchars, so you do have some empty space within the allocated extents, but not clumped in any one spot. Rather they are scattered throughout the tablespace. When we insert a row, we keep track of the last page that we inserted into and try to insert the next row near it. When we reach the end of the extent, we make a quick scan via the bit map pages to see where we might find space to insert the next row. I'm guessing that your table has varchars, which means that it is a bit of a 'hit & miss' in trying to find enough space to store the row. So it is taking a bit of time to find a space to store the row. Since NPUSED = number of pages allocated, you are not able to expand into the 'next page' of allocation and thus are scanning the partially filled pages. M.P. "Neil Truby" <neil.truby@ardenta.com> wrote in message news:bekth2$6fb16$1@ID-162943.news.uni-berlin.de... > IDS 9.21 FC4 on HP-UX. Application: Lawson > > I've never seen this before. The application is inserting some rows into > table dsinvlines, according to apps support and onstat -g sql. IToday, it > has started to insert these very, very slowly, If I stop the app and > re-build the table (alter fragment ... init in) the problem is immediately > cured. Likewise if i run the alter fragment before starting the insert app. > > A few points: > > 1. The table is big, but far from the biggest in this half terabyte database > (oncheck listing below) > 2. The problem is reproducable on a test server by EMC Timefinder copy, so a > hardware or disk problem can be discounted. > 3. Via Timefinder I have a regression copy of the database to carry out > analysis on. > 4. I note that the no of pages allocated = number used exactly > > Any ideas gratefully received. > > thanks > neil > > TBLspace Report for live:root.dsinvlines > > Physical Address e0002e > Creation date 04/04/1999 05:01:41 > TBLspace Flags 802 Row Locking > TBLspace use 4 bit bit-maps > Maximum row size 219 > Number of special columns 0 > Number of keys 0 > Number of extents 35 > Current serial value 1 > First extent size 4 > Next extent size 100000 > Number of pages allocated 2375004 > Number of pages used 2375004 > Number of data pages 1348673 > Number of rows 11174221 > Partition partnum 9437227 > Partition lockid 9437227 > > Extents > dsinvlines.onc (5%) > >

Thanks guys. I'd wondered about this. The npused thing caught my eye immediately. But why would it suddenly happen: I did a Timefinder restore back to the previous day's image, and the problem didn't manifest itself at all? There are no varchars. I list the table's schema below. One thing that might just be relevant, sparked into my mind by something Mark Denham asked: the table has never been reloaded since the database was a 7.24 one, and therefore it has attached indexes. regards Neil DBSCHEMA Schema Utility INFORMIX-SQL Version 9.21.FC4 Copyright (C) Informix Software, Inc., 1984-1997 Software Serial Number AAD#J130447 { TABLE "root".dsinvlines row size = 219 number of columns = 25 index size = 174 } create table "root".dsinvlines ( company smallint not null , vendor_num char(9) not null , batch_num integer not null , invoice char(22) not null , seq_nbr smallint not null , product_num char(30) not null , line_desc char(40) not null , unit_order decimal(12,3) not null , quantity_inv decimal(12,3) not null , unit_meas char(6) not null , list_price decimal(14,4) not null , net_price decimal(14,4) not null , amnt_disc decimal(14,4) not null , amnt_net decimal(14,4) not null , vat_code char(1) not null , qty_weight_01 decimal(10,3) default 0.000 not null , qty_weight_02 decimal(10,3) default 0.000 not null , qty_weight_03 decimal(10,3) default 0.000 not null , qty_weight_04 decimal(10,3) default 0.000 not null , qty_weight_05 decimal(10,3) default 0.000 not null , qty_weight_06 decimal(10,3) default 0.000 not null , qty_weight_07 decimal(10,3) default 0 not null , qty_weight_id char(1) default '' not null , l_index char(4) not null , l_atdil_ss_sw char(1) not null ); revoke all on "root".dsinvlines from "public"; create unique index "root".dilset1 on "root".dsinvlines (company, vendor_num,batch_num,invoice,seq_nbr) using btree ; create index "root".dilset2 on "root".dsinvlines (company) using btree ; create index "root".dilset3 on "root".dsinvlines (company,invoice, product_num) using btree ; create index "root".l_atdil on "root".dsinvlines (l_atdil_ss_sw, l_index) using btree ; "Madison Pruet" <mpruet@comcast.net> wrote in message news:vJoPa.27062$wk6.6615@rwcrnsc52.ops.asp.att.net... > It might have to do with how we search for space in a tablespace. > > You have probably had some deletes in the table and/or have varchars, so you > do have some empty space within the allocated extents, but not clumped in > any one spot. Rather they are scattered throughout the tablespace. When we > insert a row, we keep track of the last page that we inserted into and try > to insert the next row near it. When we reach the end of the extent, we > make a quick scan via the bit map pages to see where we might find space to > insert the next row. > > I'm guessing that your table has varchars, which means that it is a bit of a > 'hit & miss' in trying to find enough space to store the row. So it is > taking a bit of time to find a space to store the row. Since NPUSED = > number of pages allocated, you are not able to expand into the 'next page' > of allocation and thus are scanning the partially filled pages. > > M.P. > > "Neil Truby" <neil.truby@ardenta.com> wrote in message > news:bekth2$6fb16$1@ID-162943.news.uni-berlin.de... > > IDS 9.21 FC4 on HP-UX. Application: Lawson > > > > I've never seen this before. The application is inserting some rows into > > table dsinvlines, according to apps support and onstat -g sql. IToday, it > > has started to insert these very, very slowly, If I stop the app and > > re-build the table (alter fragment ... init in) the problem is immediately > > cured. Likewise if i run the alter fragment before starting the insert > app. > > > > A few points: > > > > 1. The table is big, but far from the biggest in this half terabyte > database > > (oncheck listing below) > > 2. The problem is reproducable on a test server by EMC Timefinder copy, so > a > > hardware or disk problem can be discounted. > > 3. Via Timefinder I have a regression copy of the database to carry out > > analysis on. > > 4. I note that the no of pages allocated = number used exactly > > > > Any ideas gratefully received. > > > > thanks > > neil > > > > TBLspace Report for live:root.dsinvlines > > > > Physical Address e0002e > > Creation date 04/04/1999 05:01:41 > > TBLspace Flags 802 Row Locking > > TBLspace use 4 bit bit-maps > > Maximum row size 219 > > Number of special columns 0 > > Number of keys 0 > > Number of extents 35 > > Current serial value 1 > > First extent size 4 > > Next extent size 100000 > > Number of pages allocated 2375004 > > Number of pages used 2375004 > > Number of data pages 1348673 > > Number of rows 11174221 > > Partition partnum 9437227 > > Partition lockid 9437227 > > > > Extents > > dsinvlines.onc (5%) > > > > > >

On Fri, 11 Jul 2003 03:46:35 -0400, Neil Truby wrote: Perhaps the indexes have just gotten inefficient? This can be important especially with attached indexes. Art S. Kagel > Thanks guys. > > I'd wondered about this. The npused thing caught my eye immediately. > But why would it suddenly happen: I did a Timefinder restore back to the > previous day's image, and the problem didn't manifest itself at all? > > There are no varchars. I list the table's schema below. > > One thing that might just be relevant, sparked into my mind by something > Mark Denham asked: the table has never been reloaded since the database > was a 7.24 one, and therefore it has attached indexes. > > regards > Neil > > DBSCHEMA Schema Utility INFORMIX-SQL Version 9.21.FC4 Copyright > (C) Informix Software, Inc., 1984-1997 Software Serial Number > AAD#J130447 > { TABLE "root".dsinvlines row size = 219 number of columns = 25 index > size = 174 > } > create table "root".dsinvlines > ( > company smallint not null , > vendor_num char(9) not null , > batch_num integer not null , > invoice char(22) not null , > seq_nbr smallint not null , > product_num char(30) not null , > line_desc char(40) not null , > unit_order decimal(12,3) not null , > quantity_inv decimal(12,3) not null , unit_meas char(6) not null , > list_price decimal(14,4) not null , > net_price decimal(14,4) not null , > amnt_disc decimal(14,4) not null , > amnt_net decimal(14,4) not null , > vat_code char(1) not null , > qty_weight_01 decimal(10,3) > default 0.000 not null , > qty_weight_02 decimal(10,3) > default 0.000 not null , > qty_weight_03 decimal(10,3) > default 0.000 not null , > qty_weight_04 decimal(10,3) > default 0.000 not null , > qty_weight_05 decimal(10,3) > default 0.000 not null , > qty_weight_06 decimal(10,3) > default 0.000 not null , > qty_weight_07 decimal(10,3) > default 0 not null , > qty_weight_id char(1) > default '' not null , > l_index char(4) not null , > l_atdil_ss_sw char(1) not null > ); > revoke all on "root".dsinvlines from "public";> > > > create unique index "root".dilset1 on "root".dsinvlines (company, > vendor_num,batch_num,invoice,seq_nbr) using btree ; > create index "root".dilset2 on "root".dsinvlines (company) using > btree ; > create index "root".dilset3 on "root".dsinvlines (company,invoice, > product_num) using btree ; > create index "root".l_atdil on "root".dsinvlines (l_atdil_ss_sw, > l_index) using btree ; > "Madison Pruet" <mpruet@comcast.net> wrote in message > news:vJoPa.27062$wk6.6615@rwcrnsc52.ops.asp.att.net... >> It might have to do with how we search for space in a tablespace. >> >> You have probably had some deletes in the table and/or have varchars, >> so > you >> do have some empty space within the allocated extents, but not clumped >> in any one spot. Rather they are scattered throughout the tablespace. >> When > we >> insert a row, we keep track of the last page that we inserted into and >> try to insert the next row near it. When we reach the end of the >> extent, we make a quick scan via the bit map pages to see where we >> might find space > to >> insert the next row. >> >> I'm guessing that your table has varchars, which means that it is a bit >> of > a >> 'hit & miss' in trying to find enough space to store the row. So it is >> taking a bit of time to find a space to store the row. Since NPUSED = >> number of pages allocated, you are not able to expand into the 'next >> page' of allocation and thus are scanning the partially filled pages. >> >> M.P. >> >> "Neil Truby" <neil.truby@ardenta.com> wrote in message >> news:bekth2$6fb16$1@ID-162943.news.uni-berlin.de... >> > IDS 9.21 FC4 on HP-UX. Application: Lawson >> > >> > I've never seen this before. The application is inserting some rows > into >> > table dsinvlines, according to apps support and onstat -g sql. >> > IToday, > it >> > has started to insert these very, very slowly, If I stop the app and >> > re-build the table (alter fragment ... init in) the problem is > immediately >> > cured. Likewise if i run the alter fragment before starting the >> > insert >> app. >> > >> > A few points: >> > >> > 1. The table is big, but far from the biggest in this half terabyte >> database >> > (oncheck listing below) >> > 2. The problem is reproducable on a test server by EMC Timefinder >> > copy, > so >> a >> > hardware or disk problem can be discounted. 3. Via Timefinder I have >> > a regression copy of the database to carry out analysis on. 4. I note >> > that the no of pages allocated = number used exactly >> > >> > Any ideas gratefully received. >> > >> > thanks >> > neil >> > >> > TBLspace Report for live:root.dsinvlines >> > >> > Physical Address e0002e Creation date >> > 04/04/1999 05:01:41 TBLspace Flags 802 >> > Row Locking >> > TBLspace use 4 bit > bit-maps >> > Maximum row size 219 >> > Number of special columns 0 >> > Number of keys 0 >> > Number of extents 35 >> > Current serial value 1 >> > First extent size 4 >> > Next extent size 100000 Number of pages allocated >> > 2375004 Number of pages used 2375004 Number of data >> > pages 1348673 Number of rows 11174221 >> > Partition partnum 9437227 Partition lockid >> > 9437227 >> > >> > Extents >> > dsinvlines.onc (5%) >> > >> > >> > >> >

Thanks. This could be it. I could establish this by dropping and re-creating the indexes on the mirror copy. But whether you're right - or whether Madison's hunch about it being a data problem is on the mark - it's still difficult to understand why this problem occurred all of a sudden. in fact, if it was an index problem, wouldn't you expect a very gradual degradation? cheers Neil "Art S. Kagel" <kagel@bloomberg.net> wrote in message news:pan.2003.07.11.11.12.53.681631.15473@bloomberg.net... > On Fri, 11 Jul 2003 03:46:35 -0400, Neil Truby wrote: > > Perhaps the indexes have just gotten inefficient? This can be important > especially with attached indexes. > > Art S. Kagel > > > Thanks guys. > > > > I'd wondered about this. The npused thing caught my eye immediately. > > But why would it suddenly happen: I did a Timefinder restore back to the > > previous day's image, and the problem didn't manifest itself at all? > > > > There are no varchars. I list the table's schema below. > > > > One thing that might just be relevant, sparked into my mind by something > > Mark Denham asked: the table has never been reloaded since the database > > was a 7.24 one, and therefore it has attached indexes. > > > > regards > > Neil > > > > DBSCHEMA Schema Utility INFORMIX-SQL Version 9.21.FC4 Copyright > > (C) Informix Software, Inc., 1984-1997 Software Serial Number > > AAD#J130447 > > { TABLE "root".dsinvlines row size = 219 number of columns = 25 index > > size = 174 > > } > > create table "root".dsinvlines > > ( > > company smallint not null , > > vendor_num char(9) not null , > > batch_num integer not null , > > invoice char(22) not null , > > seq_nbr smallint not null , > > product_num char(30) not null , > > line_desc char(40) not null , > > unit_order decimal(12,3) not null , > > quantity_inv decimal(12,3) not null , unit_meas char(6) not null , > > list_price decimal(14,4) not null , > > net_price decimal(14,4) not null , > > amnt_disc decimal(14,4) not null , > > amnt_net decimal(14,4) not null , > > vat_code char(1) not null , > > qty_weight_01 decimal(10,3) > > default 0.000 not null , > > qty_weight_02 decimal(10,3) > > default 0.000 not null , > > qty_weight_03 decimal(10,3) > > default 0.000 not null , > > qty_weight_04 decimal(10,3) > > default 0.000 not null , > > qty_weight_05 decimal(10,3) > > default 0.000 not null , > > qty_weight_06 decimal(10,3) > > default 0.000 not null , > > qty_weight_07 decimal(10,3) > > default 0 not null , > > qty_weight_id char(1) > > default '' not null , > > l_index char(4) not null , > > l_atdil_ss_sw char(1) not null > > ); > > revoke all on "root".dsinvlines from "public";> > > > > > > > create unique index "root".dilset1 on "root".dsinvlines (company, > > vendor_num,batch_num,invoice,seq_nbr) using btree ; > > create index "root".dilset2 on "root".dsinvlines (company) using > > btree ; > > create index "root".dilset3 on "root".dsinvlines (company,invoice, > > product_num) using btree ; > > create index "root".l_atdil on "root".dsinvlines (l_atdil_ss_sw, > > l_index) using btree ; > > "Madison Pruet" <mpruet@comcast.net> wrote in message > > news:vJoPa.27062$wk6.6615@rwcrnsc52.ops.asp.att.net... > >> It might have to do with how we search for space in a tablespace. > >> > >> You have probably had some deletes in the table and/or have varchars, > >> so > > you > >> do have some empty space within the allocated extents, but not clumped > >> in any one spot. Rather they are scattered throughout the tablespace. > >> When > > we > >> insert a row, we keep track of the last page that we inserted into and > >> try to insert the next row near it. When we reach the end of the > >> extent, we make a quick scan via the bit map pages to see where we > >> might find space > > to > >> insert the next row. > >> > >> I'm guessing that your table has varchars, which means that it is a bit > >> of > > a > >> 'hit & miss' in trying to find enough space to store the row. So it is > >> taking a bit of time to find a space to store the row. Since NPUSED = > >> number of pages allocated, you are not able to expand into the 'next > >> page' of allocation and thus are scanning the partially filled pages. > >> > >> M.P. > >> > >> "Neil Truby" <neil.truby@ardenta.com> wrote in message > >> news:bekth2$6fb16$1@ID-162943.news.uni-berlin.de... > >> > IDS 9.21 FC4 on HP-UX. Application: Lawson > >> > > >> > I've never seen this before. The application is inserting some rows > > into > >> > table dsinvlines, according to apps support and onstat -g sql. > >> > IToday, > > it > >> > has started to insert these very, very slowly, If I stop the app and > >> > re-build the table (alter fragment ... init in) the problem is > > immediately > >> > cured. Likewise if i run the alter fragment before starting the > >> > insert > >> app. > >> > > >> > A few points: > >> > > >> > 1. The table is big, but far from the biggest in this half terabyte > >> database > >> > (oncheck listing below) > >> > 2. The problem is reproducable on a test server by EMC Timefinder > >> > copy, > > so > >> a > >> > hardware or disk problem can be discounted. 3. Via Timefinder I have > >> > a regression copy of the database to carry out analysis on. 4. I note > >> > that the no of pages allocated = number used exactly > >> > > >> > Any ideas gratefully received. > >> > > >> > thanks > >> > neil > >> > > >> > TBLspace Report for live:root.dsinvlines > >> > > >> > Physical Address e0002e Creation date > >> > 04/04/1999 05:01:41 TBLspace Flags 802 > >> > Row Locking > >> > TBLspace use 4 bit > > bit-maps > >> > Maximum row size 219 > >> > Number of special columns 0 > >> > Number of keys 0 > >> > Number of extents 35 > >> > Current serial value 1 > >> > First extent size 4 > >> > Next extent size 100000 Number of pages allocated > >> > 2375004 Number of pages used 2375004 Number of data > >> > pages 1348673 Number of rows 11174221 > >> > Partition partnum 9437227 Partition lockid > >> > 9437227 > >> > > >> > Extents > >> > dsinvlines.onc (5%) > >> > > >> > > >> > > >> >