update statistics - large amount of log space

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

malcolm weallans - 21 Feb 2005 15:47 GMT

I am getting problems with update statistics and the
amount of log space used. We are filling 14 logs of
20Mbytes each time that we run update statistics
medium. The system has a number of databases and
their are 44,554 tables in the whole system. I also
checked and their are a total of 36,000 sysdistrib
records. The row size for systables is 469 so the
amount of log space needed for systables is 44,554 * 2
* 469 plus about 10% overhead. The row size for
sysdistrib is 305 so the amount of log space needed
for that is 36,000 *2 *305 plus about10% overhead.
The calculation can account for about 70 Mbytes
whereas we ar using 280 Mbytes. What else does update
statistics change?

regards

Malcol,m
sending to informix-list

Reply to this Message

Marco Greco - 21 Feb 2005 17:33 GMT

are you doing an update statistics medium distributions only?
if yes the only table involved is sysdistrib
if not, systables, syscolumns, sysindexes also to name a few.
also sysdistrib does not get updated, rather update statistics deletes the old
distributions for each table/column pair it is invoked on, and then inserts
new distributions.

> I am getting problems with update statistics and the
> amount of log space used. We are filling 14 logs of
[quoted text clipped - 15 lines]
> Malcol,m
> sending to informix-list

Signature

Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!

Informix faq http://www.iiug.org/techinfo/faq/informix.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm

sending to informix-list

Reply to this Message

Art S. Kagel - 22 Feb 2005 18:39 GMT

And if you're only getting 36000 sysdistrib records for 44,554 tables, then
you are generating data distributions for less than on column per table on
average. Something's not right with your update stats procedures, if
nothing else.

But to your direct question, what else gets updated? Systables, syscolumns,
sysindexes (sysindices for 9.xx), and sysdistrib for tables, if you also
update statistics on stored procedures, then: sysprocedures and sysprocplan
as well.

I'd suggest increasing logical log space and making sure logs are archived
as soon as they fill either with onbar or ontape -c or through the
ALARMPROGRAM handling of class 23 events.

Art S. Kagel

> I am getting problems with update statistics and the
> amount of log space used. We are filling 14 logs of
[quoted text clipped - 15 lines]
> Malcol,m
> sending to informix-list

Reply to this Message

John Miller - 24 Feb 2005 07:08 GMT

Not sure which version you are running, but if you
do not add the "DISTRIBUTIONS ONLY" clause then your
update statistics command will submit work to the btree
cleaner (or btree scanner) which will cause the indexes
to be compressed and re-balanced.

John Miller

> I am getting problems with update statistics and the
> amount of log space used. We are filling 14 logs of
[quoted text clipped - 15 lines]
> Malcol,m
> sending to informix-list

Reply to this Message