Kbase P113776: Running UPDATE STATISTICS on all tables columns indexes overflows lock table
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
17/11/2009 |
|
Status: Verified
SYMPTOM(s):
Running UPDATE STATISTICS on all tables columns indexes
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS
Lock table overflow, increase -L on server (915)
FACT(s) (Environment):
Progress 9.x
OpenEdge 10.x
All Supported Operating Systems
CAUSE:
-L is not set high enough. At the same time, running UPDATE STATISTICS on all tables columns and indexes simultaneously is not recommended.
FIX:
There are three updates being made with the command
UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS.
It is better to break this up. Run these statements in this exact order.
update table statistics;
commit;
update index statistics;
commit;
update all column statistics;
commit;
The first 2 commands will only lock the system statistics tables we write to in this process and do not use many locks.
The UPDATE COLUMN STATISTICS uses 11 locks (for 11 records of info we write to the system statistics tables--10 values and 1 timestamp) for each column of indexed data. So if a table has 25 columns, and 12 are indexed, this means 12*11=132 locks.
This formula gives an idea of the number of locks needed and -L can be adjusted accordingly if necessary, when the commands are run separately (less locks will now be used than running the first command).