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" <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 > > >> > 943