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