RE: How to improve the performance of update statistics

That resembles SAP's approach to identifying tables for updstats too. However it's caveat is when tables do now change in row count but undergo massive updates, or deletes / inserts. Several of the tables in our SAP databases do this and the num row delta methodology fell short in targeting the tables for updstats. When we began our audit we found tables that hadn't received updstats in months yet had a high percentile of change per day. We do not rely on the numrow delta method for day to day updstats. We run a full updstats weekly and play catch-up on a daily basis on the tables that require it between weekly runs. We don't do an onstat -z unless the onstat -p metrics roll over. -Chris Bill Dare <dareb@jevic.com> on 06/16/2003 03:00:33 PM To: Chris St. Aubin/REEBOK@REEBOK, hause011@garnet.tc.umn.edu cc: informix-list@iiug.org Subject: RE: How to improve the performance of update statistics One caveat with Chris's method of calculating the percentage change in the table. The results will be affected by an onstat -z. The values in sysptntab are being compared to systables.nrows. The values in sysptntab will be zeroed on an onstat -z. If this causes you problems, you should get Art Kagel's dostats utility. The latest version has an option ([-b [-B BrowseThreshold]]) to check for a percentage change in number of rows since the last update statistics. Art compares YOUR_DATABASE:systables.nrows (updated on the last update statistics) against sysmaster:sysptnhdr.nrows (the current number of rows in the table, not reset on onstat -z) to determine the percentage change. Regards, Bill > -----Original Message----- > From: chris.staubin@reebok.com [SMTP:chris.staubin@reebok.com] > Sent: Monday, June 16, 2003 10:38 AM > To: hause011@garnet.tc.umn.edu > Cc: informix-list@iiug.org > Subject: Re: How to improve the performance of update statistics > > > > Greetings, > > Here's a script that we use on a daily basis to discover tables that have > a > certain percentage of change. It's used for both reporting as this > attached > script is written to do. And it's used to send a list of tablenames to a > utility that runs updstats. > > We sum the writes, updates, and deletes and weigh that against the number > of rows in the table. It's not an exact science as a single row could be > affected by each action... but it's tried and true for over a year. > > round(((b.pf_iswrite+b.pf_isrwrite+b.pf_isdelete)/c.nrows) * 100) pchange > from systabnames a, sysptntab b, SID:systables c <<< Replace SID with > your database identifier. > >

On Mon, 16 Jun 2003 15:10:22 -0400, chris.staubin wrote: Unless the updates alter the key distribution in important indexes it's not as important as the rowcount. In addition dostats include the -a/-A option pair which specified a maximum number of days which are permitted to pass without updating stats on the table. Using these two features of dostats (-b -B5.0 and -a -A7) together we run dostats nightly and each night one to four tables are updated due to rowcount changes and every other table is updated on the weekend (I initialized everything to W/E update by running dostats without the -b & -a options over a weekend so the -A7 expires on the quieter tables every Saturday night). Art S. Kagel > > That resembles SAP's approach to identifying tables for updstats too. > However it's caveat is when tables do now change in row count but > undergo massive updates, or deletes / inserts. > > Several of the tables in our SAP databases do this and the num row delta > methodology fell short in targeting the tables for updstats. > > When we began our audit we found tables that hadn't received updstats in > months yet had a high percentile of change per day. > > We do not rely on the numrow delta method for day to day updstats. We > run a full updstats weekly and play catch-up on a daily basis on the > tables that require it between weekly runs. > > We don't do an onstat -z unless the onstat -p metrics roll over. > > -Chris > > > > > Bill Dare <dareb@jevic.com> on 06/16/2003 03:00:33 PM > > To: Chris St. Aubin/REEBOK@REEBOK, hause011@garnet.tc.umn.edu cc: > informix-list@iiug.org > Subject: RE: How to improve the performance of update statistics > > > One caveat with Chris's method of calculating the percentage change in > the table. The results will be affected by an onstat -z. The values in > sysptntab are being compared to systables.nrows. The values in > sysptntab will be zeroed on an onstat -z. > > If this causes you problems, you should get Art Kagel's dostats utility. > The latest version has an option ([-b [-B BrowseThreshold]]) to check > for a percentage change in number of rows since the last update > statistics. Art compares YOUR_DATABASE:systables.nrows (updated on the > last update statistics) against sysmaster:sysptnhdr.nrows (the current > number of rows in > the table, not reset on onstat -z) to determine the percentage change. > > Regards, > Bill > >> -----Original Message----- >> From: chris.staubin@reebok.com [SMTP:chris.staubin@reebok.com] >> Sent: Monday, June 16, 2003 10:38 AM To: hause011@garnet.tc.umn.edu >> Cc: informix-list@iiug.org >> Subject: Re: How to improve the performance of update statistics >> >> >> >> Greetings, >> >> Here's a script that we use on a daily basis to discover tables that >> have a >> certain percentage of change. It's used for both reporting as this >> attached >> script is written to do. And it's used to send a list of tablenames to >> a utility that runs updstats. >> >> We sum the writes, updates, and deletes and weigh that against the >> number of rows in the table. It's not an exact science as a single row >> could be affected by each action... but it's tried and true for over a >> year. >> >> round(((b.pf_iswrite+b.pf_isrwrite+b.pf_isdelete)/c.nrows) * 100) >> pchange from systabnames a, sysptntab b, SID:systables c <<< Replace >> SID with your database identifier. >> >>