Re: Tetra and Update Statistics
Andrew wrote: > 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 Except I still haven't seen which database server this is (I'm on the mailing list so may have missed it). If it's C-ISAM/SE, then indexes that grew or shrunk rapidly were prone to performance problems. Often a rebuild was the only way to produce a clean index file. If you are using a 'real' SE (not linked into C-ISAM files) or IDS database, then I would still follow Andrew's suggestion of running UPDATE STATS LOW at the point of the tail off and see if that restores performance before the rebuild. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list
Its IDS7.31 and HPUX11 "Mark D. Stock" wrote: > > Andrew wrote: > > 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 > > Except I still haven't seen which database server this is (I'm on the > mailing list so may have missed it). If it's C-ISAM/SE, then indexes > that grew or shrunk rapidly were prone to performance problems. Often a > rebuild was the only way to produce a clean index file. > > If you are using a 'real' SE (not linked into C-ISAM files) or IDS > database, then I would still follow Andrew's suggestion of running > UPDATE STATS LOW at the point of the tail off and see if that restores > performance before the rebuild. > > Cheers, > -- > Mark. > > +----------------------------------------------------------+-----------+ > | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| > | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| > | +-----------------------------------+//// / ///| > | |We value your comments, which have |/// / ////| > | |been recorded and automatically |// / /////| > | |emailed back to us for our records.|/ ////////| > +----------------------+-----------------------------------+-----------+ > > 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 #