Any ideas

[Neil Truby]

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%)

[Neil Truby]

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.ne t...

> 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%)
> >
> >

>
>

[Art S. Kagel]

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.ne t...

>> 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%)
>> >
>> >
>> >
>> >

[Paul Watson]

Neil Truby wrote:

>
> 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.

The machine date is wrong, it thinks it's Friday the 13th?

> 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@bloomber g.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.ne t...
> > >> 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%)
> > >> >
> > >> >
> > >> >
> > >> >

--
Paul Watson #
Oninit Ltd # Growing old is mandatory
Tel: +44 1436 672201 # Growing up is optional
Fax: +44 1436 678693 #
Mob: +44 7818 003457 #
[url]www.oninit.com[/url] #