Re: Tetra and Update Statistics
Hi Paul, I ran into the same problem at two clients using the same combination of IDS 7.31 and Tetra CS/3 3.1. After running update statistics high on columns heading an index etc. Tetra became extremely slow especially posting batches and the like. My conclusion was that the query that Tetra does on the database in some or other way "bypasses" the IDS optimizer :-( (I realise it sounds funny). I tried the same query through isql with sqexplain on and it was just as slow. I solved the immediate problem by dropping the distribution on the Tetra tables and running a standard update statistics on a regular basis on those tables. The rest of my database works fine with the "tuned" update statistics script. Skip Paul Watson <paul@oninit.com> wrote in message news:<3F337B63.BB6C959B@oninit.com>... > Malcolm > > the Tetra runs on 7.31 IDS. > > The hassle with Tetra back in 1995 (ish) was they were starting to > look at Online but it was a ground floor re-write AFAIK > > Paul > > "malcolm.iiug" wrote: > > > > Paul, > > Old versions of Tetra use C-ISAM to access the files as well as SQL. > > The Tetra database is really a set of flat files mapped into SQL. It's not > > really a database. > > Folk-lore/Legend says that running Update Statistics against Tetra has > > caused problems but in the only case that I looked at it turned out to have > > other causes. > > You say it's Informix 7.31 but is it SE or IDS? Originally Tetra was SE. I > > know they were developing a "proper" system that was IDS but that would not > > have had the same C-ISAM access. > > > > regards > > > > Malcolm > > > > ----- Original Message ----- > > From: "Paul Watson" <paul@oninit.com> > > To: <informix-list@iiug.org> > > Sent: Thursday, August 07, 2003 4:57 PM > > Subject: Tetra and Update Statistics > > > > > I haven't used Tetra Accounts for a number of years now and I've > > > recently inherited a site that uses Tetra 3.1 and it's running > > > slow, everything points to update statistcs but the Tetra supplier > > > is saying "don't run update statistics". AFAIR this never used to be > > > the case. Anybody got any upto date views on this. > > > > > > BTW Informix 7.31 HPUX 11 > > > > > > > > > -- > > > Paul Watson # > > > Oninit Ltd # Growing old is mandatory > > > Tel: +44 1436 672201 # Growing up is optional > > > Fax: +44 1436 678693 # > > > Mob: +44 7818 003457 # > > > www.oninit.com # > > > > sending to informix-list
What I'm seeing is a gradual tailoff of performance, and then the client drops and rebuilds all the indexes and it's fine again for a few weeks. Skip wrote: > > Hi Paul, > > I ran into the same problem at two clients using the same combination > of IDS 7.31 and Tetra CS/3 3.1. After running update statistics high > on columns heading an index etc. Tetra became extremely slow > especially posting batches and the like. My conclusion was that the > query that Tetra does on the database in some or other way "bypasses" > the IDS optimizer :-( (I realise it sounds funny). I tried the same > query through isql with sqexplain on and it was just as slow. > > I solved the immediate problem by dropping the distribution on the > Tetra tables and running a standard update statistics on a regular > basis on those tables. The rest of my database works fine with the > "tuned" update statistics script. > > Skip > > Paul Watson <paul@oninit.com> wrote in message news:<3F337B63.BB6C959B@oninit.com>... > > Malcolm > > > > the Tetra runs on 7.31 IDS. > > > > The hassle with Tetra back in 1995 (ish) was they were starting to > > look at Online but it was a ground floor re-write AFAIK > > > > Paul > > > > "malcolm.iiug" wrote: > > > > > > Paul, > > > Old versions of Tetra use C-ISAM to access the files as well as SQL. > > > The Tetra database is really a set of flat files mapped into SQL. It's not > > > really a database. > > > Folk-lore/Legend says that running Update Statistics against Tetra has > > > caused problems but in the only case that I looked at it turned out to have > > > other causes. > > > You say it's Informix 7.31 but is it SE or IDS? Originally Tetra was SE. I > > > know they were developing a "proper" system that was IDS but that would not > > > have had the same C-ISAM access. > > > > > > regards > > > > > > Malcolm > > > > > > ----- Original Message ----- > > > From: "Paul Watson" <paul@oninit.com> > > > To: <informix-list@iiug.org> > > > Sent: Thursday, August 07, 2003 4:57 PM > > > Subject: Tetra and Update Statistics > > > > > > > I haven't used Tetra Accounts for a number of years now and I've > > > > recently inherited a site that uses Tetra 3.1 and it's running > > > > slow, everything points to update statistcs but the Tetra supplier > > > > is saying "don't run update statistics". AFAIR this never used to be > > > > the case. Anybody got any upto date views on this. > > > > > > > > BTW Informix 7.31 HPUX 11 > > > > > > > > > > > > -- > > > > Paul Watson # > > > > Oninit Ltd # Growing old is mandatory > > > > Tel: +44 1436 672201 # Growing up is optional > > > > Fax: +44 1436 678693 # > > > > Mob: +44 7818 003457 # > > > > www.oninit.com # > > > > > > sending to informix-list -- Paul Watson # Oninit Ltd # Growing old is mandatory Tel: +44 1436 672201 # Growing up is optional Fax: +44 1436 678693 # Mob: +44 7818 003457 # www.oninit.com #
Paul Watson wrote: > What I'm seeing is a gradual tailoff of performance, and then the > client drops and rebuilds all the indexes and it's fine again for > a few weeks. Two things spring to mind 1) if the indexes have cluster attributes, then the index rebuild will cause clustering. 2) extent deterioration. Perhaps, just before the index rebuild, the customer needs to count the extents on the tables and indexes before and after. Perhaps they are getting simple coalescence