Re: Update statistics on 7.11 Online

KENDRICKS.CHERYL@heb.com wrote: > > OS: UNIX System V/386/486 Release 4.0 Version 3. > Platform: Single Processor NCR > Software: Informix Online 7.11UC2 > Does anyone know of a bug with update statistics? > > Here is my problem: > Table XYZ is 240,000 pages in size (124,477 data, 49,959 index, > 62,534 free and 30 in Bit map pages). Table is an highly updated > table and update stat occurs only once a week for this table. > Yet when I issue the following: > update statistics medium for table XYZ;> PROBLEM ------> It is taking over 10 hours to complete > update statistics high for table XYZ;> PROBLEM " It is taking over 8 hours to complete. > > What I have noticed: > Using the SMI sysptprof table that it gets to a certain point and > then starts to do a lot of buffer waits!!!!! By the completion it has > done over 515,818,452 buffer reads and has only 59,234 pagreads > (page reads) with NO seqscan (sequential scans) > > During the early processing and prior to the start of accumulating > the massive amount of buffer reads the pagreads is increasing by 3 to > 4 thousand pages per min. Once it starts accumulating buffer waits > (as seen via onstat -p (bufwaits), the pagereads is only increasing > by 10 to 20 pages per min. > > What I have tried/done: > 1. oncheck -cDI shows no problems with the table. > 2. Increasing the number of buffers, went to the max and got down > to 2 hours (yet, this is the only thing executing, attempt anything > else and my system TIES UP). > In general my ONCONFIG looks good via all performance specifications > that I have reviewed (Sutto, this list and Informix Manuals of course). > > So does ANYONE have an IDEA of what the problem could be OR if there is > an Informix BUG that I just do not know about. Feel free to e-mail me > directly if you have any idea. > > Thanks in advance! > Cheryl L. Kendricks > Informix DBA: vnibs2@heb.com There certainly are some bugs with UPDATE STATISTICS MEDIUM in OnLine 7, ironed out allegedly in 7.14. I feel that you probably know this, becuase when my database server crashed executing such a statement, I think you were one of my respondents. Anyway, it's possible that there are other related bugs. The one I encountered was sporadic, related to whether or not specific data conditions were satisfied. Could you unload/reload the table to change its physical structure and see if that makes a difference? Neil Truby Chase Bank Bournemouth, UK