update statistics - large amount of log space

update statistics - large amount of log space

Post by malcolm we » Wed, 23 Feb 2005 00:47:42



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
 
 
 

update statistics - large amount of log space

Post by Marco Grec » Wed, 23 Feb 2005 02:33:02


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.




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

Informix faq http://www.yqcomputer.com/
4glworks http://www.yqcomputer.com/
Informix on Linux http://www.yqcomputer.com/

sending to informix-list

 
 
 

update statistics - large amount of log space

Post by Art S. Kag » Thu, 24 Feb 2005 03:39:32


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
 
 
 

update statistics - large amount of log space

Post by roefer » Thu, 24 Feb 2005 05:17:15


Hi folks

I user to run a statement:

SET ISOLATION TO DIRTY READ ;

before 'update statistics [high | medium} for table ' using or not
'distributions only'.

could be a good idea to don't generate logs.

BR,

R Ferronato
 
 
 

update statistics - large amount of log space

Post by John Mille » Fri, 25 Feb 2005 16:08:05

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