Professional Web Applications Themes

Any ideas - Informix

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

  1. #1

    Default 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
    ysis 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 Guest

  2. #2

    Default Re: Any ideas

    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" <mpruetcomcast.net> wrote in message
    news:vJoPa.27062$wk6.6615rwcrnsc52.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.trubyardenta.com> wrote in message
    > news:bekth2$6fb16$1ID-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
    > > ysis 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 Guest

  3. #3

    Default Re: Any ideas

    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" <mpruetcomcast.net> wrote in message
    > news:vJoPa.27062$wk6.6615rwcrnsc52.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.trubyardenta.com> wrote in message
    >> news:bekth2$6fb16$1ID-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 ysis 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 Guest

  4. #4

    Default Re: Any ideas

    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" <kagelbloomberg.net> wrote in message
    > news:pan.2003.07.11.11.12.53.681631.15473bloomber 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" <mpruetcomcast.net> wrote in message
    > > > news:vJoPa.27062$wk6.6615rwcrnsc52.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.trubyardenta.com> wrote in message
    > > >> news:bekth2$6fb16$1ID-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 ysis 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] #
    Paul Watson Guest

Similar Threads

  1. PLEASE help with ideas
    By mikethecoder in forum Macromedia ColdFusion
    Replies: 1
    Last Post: April 10th, 12:46 PM
  2. Any ideas?
    By Shane IRL in forum Macromedia ColdFusion
    Replies: 12
    Last Post: March 4th, 12:19 AM
  3. other ideas
    By MJ in forum ASP Database
    Replies: 1
    Last Post: November 9th, 12:38 AM
  4. X ideas
    By Arron in forum Linux Setup, Configuration & Administration
    Replies: 4
    Last Post: August 11th, 02:47 PM
  5. Any ideas on how this is done?
    By Seth Meranda in forum Macromedia Dreamweaver
    Replies: 2
    Last Post: July 15th, 01:29 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139