R
Rob Fitzpatrick
Guest
11.6.0 64-bit CentOS 6.9 I have been testing Update Statistics recently to investigate a problem at a client site. In 11.6.0 it seems to use excessive locks. I have a case open with TS for that. In my testing I tried to understand better what is happening under the covers, including what is being locked, so I can better predict whether it will run at a client site without a lock table overflow. As I understand it, Update Statistics updates the following: PUB._Syscolstat PUB._Sysdatestat PUB._Sysidxstat PUB._Sysintstat PUB._Sysnumstat PUB._Sysnvarcharstat PUB._Systblstat While it was running on a test DB, I queried the lock table: for each _lock no-lock: find _file no-lock where _file-number = _lock-table no-error. display _file-name when available( _file ) _lock-table format "->>>>9" _lock-type _lock-recid _lock-flags _lock-transid _lock-usr _lock-name no-error. end. I ran the query repeatedly and mostly got nothing. Occasionally I saw data, like this: File-Name Table Type RECID Flags Trans Id Usr Name 1057 REC 259355 X 932453851 24 sysprogres 1057 REC 260210 X 932453851 24 sysprogres _Sysdatestat -109 REC 258843 X 932453851 24 sysprogres _Sysdatestat -109 REC 259279 X 932453851 24 sysprogres _Sysnumstat -108 REC 262960 X 932453851 24 sysprogres _Sysintstat -107 REC 259440 X 932453851 24 sysprogres _Syscolstat -104 REC 261108 X 932453851 24 sysprogres 1047 REC 261392 X 932453851 24 sysprogres _Sysintstat -107 REC 260464 X 932453851 24 sysprogres _Sysnvarcharstat -119 REC 261871 X 932453851 24 sysprogres _Sysintstat -107 REC 260432 X 932453851 24 sysprogres 1048 REC 259188 X 932453851 24 sysprogres _Syscolstat -104 REC 258979 X 932453851 24 sysprogres _Sysnumstat -108 REC 263367 X 932453851 24 sysprogres _Sysnumstat -108 REC 258946 X 932453851 24 sysprogres 1050 REC 259009 X 932453851 24 sysprogres _Sysidxstat -94 REC 258401 X 932453851 24 sysprogres 1057 REC 260299 X 932453851 24 sysprogres _Syscolstat -104 REC 260889 X 932453851 24 sysprogres _Sysnumstat -108 REC 259008 X 932453851 24 sysprogres Most of this looks as I expect, but note that there are several records where "find _file" failed and the file name is blank and the file number > 1000. Those table numbers (_lock-table) don't exist, but there are _index records whose index numbers match, and they are indexes on the tables being updated: _idx-num _index-name 1044 _Idxsysidxstat 1047 _Idxsysdatestat 1048 _Idxsysintstat 1050 _Idxsysnumstat 1057 _Idxsysnvarcharstat What's more, for the "index" line items, the values in the RECID column are valid recids for the associated tables. Can anyone explain these records that have what appear to be index numbers in the _lock-table column? OE bug? Phantom promon data? A side-effect of _lock records changing so fast that the AVM can't do consistent reads?
Continue reading...
Continue reading...
