Trouble with update statistics
We had/have some trouble with update statistics here. Because it's not so
simple to describe in short I send the whole story:
- IDS 9.40.FC4W2
- Solaris 5.9 Generic_117171-12 sun4u sparc SUNW,Netra-T12
- Table contains about 800,000,000 rows
- Fragmented by expression 20 fragments plus remainder, strategy (id >x1 and
id <x2), id is serial and primary key
- at regular intervals dropping of older fragments and creating of new
fragments
- the last has been done on Friday,12 th of October
- Update Statistics for the table in the night, lasts about 9 hours usually
- Crash of database server after about 3 hours since update statistics
started and restart of database server
- Running update statistics where killed by the crash
- next day certain queries behaved very slow
- Optimizer chose wrong index for those queries
- Generated update statistics commands with dostats, newest version and ran
them
DATABASE xxxxx;
SET PDQPRIORITY 100;
UPDATE STATISTICS LOW FOR TABLE foo (bar1, bar2);
UPDATE STATISTICS LOW FOR TABLE foo (bar3);
UPDATE STATISTICS LOW FOR TABLE foo (bar4, bar5, bar6, bar7, bar8);
UPDATE STATISTICS LOW FOR TABLE foo (bar9, bar2);
UPDATE STATISTICS LOW FOR TABLE foo (bar11, bar12, bar2);
UPDATE STATISTICS HIGH FOR TABLE foo (bar3, bar4, bar1, bar9, bar11)DISTRIBUTIONS ONLY
;
UPDATE STATISTICS MEDIUM FOR TABLE foo (bar5, bar6, bar7, bar13, bar12,
bar14, bar15,
bar8, bar16, bar17, bar18, bar19, bar20, bar2, bar21, bar22, bar23);
- Update Statistics low succeeded
- Update Statistics high continued running after about 24 hours
- Having a look in sysdistrib I found "high" entries for bar3, bar4, bar1,
bar9. For bar11 I found only medium entries.
- bar11 was exactly the leading column of the incorrect choosen index (the
problem lasts in this state, BTW)
- All "high" entries where from the day before. Consequential update
statistics ran on that column bar11 since at least 10 hours.
- I assumed it hanging, tried to kill update statistics and ran it again
only against column bar11
- BTW: the killed (onmode -z ses_id) update statistics disappeared after a
while though update statistics cannot be killed as I learned yesterday.
Obviously the new update statistics on the same column had some influence.
- Because I saw no progress I tried to kill the new update statistics, too.
But without any success.
- I tried an "update statistic low on table foo (bar11) drop distributions"
- That ran in a second and the medium entries for bar11 vanished from
sysdistrib
- From this moment on the problem (Optimizer chose wrong index) vanished,
too. Those medium entries seem to have hinted the optimizer the wrong way.
- The "UPDATE STATISTICS HIGH FOR TABLE slda_ergebnis (bar11) DISTRIBUTIONS
ONLY" ran to an end (3 hours later) and I found new entries in sysdistrib
(four rows "high" for bar11)
- Finally I ran the UPDATE STATISTICS MEDIUM (not as shown above but column
by column). To my astonishment ist ran very fast in only a few minutes.
- The problem with the optimizer was fixed
Now I have some questions:
- What could be the reason for the first hanging "UPDATE STATISTICS HIGH FOR
TABLE slda_ergebnis (bar3, bar4, bar1, bar9, bar11) DISTRIBUTIONS ONLY"
- Why does "UPDATE STATISTICS HIGH FOR TABLE slda_ergebnis (bar11)
DISTRIBUTIONS ONLY" last more than 6 hours
- Why does UPDATE STATISTICS MEDIUM FOR TABLE run so fast. My experience is
that "...DISTRIBUTIONS ONLY" runs fast but without that clause it last long
with such big tables
- Can it be that medium entries in sysdistrib of the leading column of an
index hints the optimizer the wrong way
- In sysindices I found that for all five indexes the column nrows is "0.0".
Is that ok (rows below)?
idxname foo_31
owner informix
tabid 999
idxtype U
clustered
levels 4
leaves 5089001
nunique 780894232
clust 31239720
nrows 0,00
indexkeys 1 [1] (bar3, primary key)
amid 1
amparam
collation en_US.819
idxname foo_32
owner informix
tabid 999
idxtype D
clustered
levels 5
leaves 5942378
nunique 12294372
clust 31248612
nrows 0,00
indexkeys 8 [1], 19 [1]
amid 1
amparam
collation en_US.819
idxname foo_33
owner informix
tabid 999
idxtype D
clustered
levels 5
leaves 11722572
nunique 3327737
clust 369534328
nrows 0,00
indexkeys 2 [1], 3 [1], 4 [1], 5 [1], 13 [1]
amid 1
amparam
collation en_US.819
idxname foo_34
owner informix
tabid 999
idxtype D
clustered
levels 5
leaves 10746294
nunique 3605
clust 631618369
nrows 0,00
indexkeys 12 [1], 7 [1], 8 [1] (column 12 = bar11, the "problem" column)
amid 1
amparam
collation en_US.819
idxname foo_35
owner informix
tabid 999
idxtype D
clustered
levels 5
leaves 3736413
nunique 2004
clust 48293889
nrows 0,00
indexkeys 11 [1], 8 [1]
amid 1
amparam
collation en_US.819
- For IDS 9.40FC4W2 could it be better to ran update statistics high
seperately for each column?
- In my first attempt I set PDQPRIORITY to 100 and environment variable
PSORT_NPROCS=4 and exported it.
In onconfig following is set:
MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority
DS_MAX_QUERIES 20 # Maximum number of decision supportqueries
DS_TOTAL_MEMORY 300000 # Decision support memory (Kbytes)
DS_MAX_SCANS 100 # Maximum number of decision supportscans
BUFFERS 2100000
SHMVIRTSIZE 1376256 # initial virtual shared memory segmentsize
SHMADD 98304 # Size of new shared memory segments
(Kbytes)
SHMTOTAL 0 # Total shared memory (Kbytes).
0=>unlimited
- Are these settings helpful and do they fit one another?
Sorry for my longsome story but there are so many obscurities with update
statistics.
Thanks for your help in advance.
Regards,
Reinhard.