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?
But recids of those locks are NOT dbkey of index blocks (they are not devided by 32 or 64).
Use dbrpr to dump the blocks. You will see their type. If they are data blocks then you will see the tables the recids belong to.
> But recids of those locks are NOT dbkey of index blocks (they are not devided by 32 or 64).
That's right, they are RM blocks. My point was that the recid is valid for the table that has that index; there is a correlation between the two values. They aren't just random garbage values.
The confusion is about index numbers appearing in the _lock-table column. What are these lock table entries? Are they valid records? Are they artifacts of dirty reads?
> there is a correlation between the two values.
You can also display _Lock-Chain and check a correlation with this third value:
REC/RGET: Hash# = (Table# + Recid) modulo (-lkhash)
TAB: Hash# = Table# modulo (-lkhash)
PURG: Hash# = User# modulo (-lkhash)
> They aren't just random garbage values.
Indeed!
rob,
there are tables such that _file-number matches some index numbers and also _file-numbers that match index numbers for the same table. easy to get them mixed up.
there should not be any lock table entries for indexes.
anyhow, the update statistics function will update index statistics as well as table statistics, depending on what options you used.
Thanks George; interesting info.
But this doesn't answer my underlying question. ;) If _lock contains record locks, table locks, schema locks; what are these additional records?
Thanks Gus. My confusion is that, in this database, there are no _file-numbers > 1000. I don't /think/ I'm mixed up.
In this case the SQL statements were generated from code like this:
put unformatted "update table statistics and index statistics and all column statistics for PUB." quoter( _file._file-name )";" skip.
put unformatted "commit work;" skip.
possibly there is a bug such that sql is erroneosly taking out locks such that the lock id is the same as an index number. or not, i can’t tell from this distance.
I can't be sure at this time but I suspect this is a bug that has been fixed. Running this SQL script against sports in 11.6.0, I get a lock table HWM of 2034. If I break it into separate statements/transactions for TABLE, INDEX, and ALL COLUMN, it goes down to 1944; still very high.
In 11.6.3, the same tests give HWMs of 90 and 86; a significant difference.
Sorry, my numbers were a little off. 90 and 86 was for an empty sports DB in 11.6.0. The numbers for sports on 11.6.3 were 77 and 58.