RE: Update Stats slowing.....
How are you running update statistics low?
If you are running a single update statistics low that contains all of
the indexed columns, you will walk the leaf pages of all 23 indexes
sequentially. I could see that taking 3 hours against a large table.
You might get better performance if you break your low run into multiple
update statistics statements and kick them off in parallel.
For example, you have:
Index 1 (a, b, c)
Index 2 (b, d, a)
Index 3 (x, y, z)
Instead of
Update statistics low for table tablename (a, b, c, d, x, y, z);
Try running the following in parallel
update statistics low for table tablename (a, b, c);
update statistics low for table tablename (b, d, a);
update statistics low for table tablename (x, y, z);
If your indexes are fragmented, setting PDQPRIORITY to 10 will let the
engine scan all of your index fragments in parallel.
PSORT_NPROCS, DBUPSPACE and PSORT_DBTEMP should have no effect on update
statistics low but will have an impact on medium and high.
Andrew Ford
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org] On Behalf Of
malc_p@btinternet.com
Sent: Friday, March 17, 2006 5:41 AM
To: informix-list@iiug.org
Subject: Update Stats slowing.....
IDS9.30HC5, HP-UX 11i 4cpu
Morning all!
Our overnight update stats is slowing up on us on one table - should it
be taking over 3 hours to run UPDATE STATISTICS LOW on a table with
40,000,000 rows (85 columns, no special columns (text, byte etc), 23
indexes, 2 fragments and 29 extents) in overnight quiet time? OK I know
I need to address the extenting issue but the thing is the table hasn't
really changed greatly except for day-to-day growth of about 500 rows
over the last year or so; it's just suddenly gone up from an hour or
so.
(We do MEDIUM DISTRIBUTIONS ONLY for the whole table beforehand and it
takes 6 minutes!)
We have MAX PDQ set to 10, PSORT_NPROCS=4 and DBUPSPACE=10000.
PSORT_DBTEMP is not used.
Any input gratefully received.
Cheers
Malc
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list