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.
>
>