_TableStat and _IndexStat data

[jdpjamesp]

I've been doing some work looking at _TableStat and _IndexStat data and I have noticed that these tables only hold 50 records in them. Is there a way to increase the data collected here?

Thanks.

 

[TomBascom]

Use -tablerangesize and -indexrangesize

define variable i as integer no-undo.
for each _file no-lock where _file-num > 0 and _file-num < 10000:
  i = i + 1.
end.
message "set -tablerangesize to at least " i.
i = 0.
for each _index no-lock:
  i = i + 1.
end.
message "set -indexrangesize to at least " i.

 

[jdpjamesp]

That's fabulous thanks Tom.

Just showing my ignorance, but where do I set this?

 

[TomBascom]

DB startup.

If you're using some god-awful "GUI" management tool like exploder it goes in the "other args" section.

 

[Rob Fitzpatrick]

Use -tablerangesize and -indexrangesize

define variable i as integer no-undo.
for each _file no-lock where _file-num > 0 and _file-num < 10000:
  i = i + 1.
end.
message "set -tablerangesize to at least " i.
i = 0.
for each _index no-lock:
  i = i + 1.
end.
message "set -indexrangesize to at least " i.

That will set -indexrangesize higher than you need, as it counts system indexes as well as application indexes. Not that it matters, the only expense is a bit of shared memory. Alternatively, you could count indexes like:

...
<snip>
i = 0.
for each _index no-lock where not _index-name begins "_":
  i = i + 1.
end.
message "set -indexrangesize to at least " i + 7.

This assumes your application doesn't have any indexes whose names begin with an underscore, and -baseindex is 1 (the default). I add 7 because indexes 1 to 7 (_idx-num) are system indexes, and if you don't leave enough slots in _IndexStat then you won't get stats for your last (highest-numbered) seven indexes. I learned that one the hard way.

 

[TomBascom]

Yes.

I have found some fascinating things by noticing unexpected activity in the system indexes.

 

[Rob Fitzpatrick]

Last night (and, well, today) I was checking table and index stats for a long-running batch job and was surprised by the number of reads on the meta-schema indexes (tens of thousands). I wasn't expecting that. I guess I expected that the schema would be read once (i.e. enough reads to get the lay of the land for the application tables/indexes referenced in the code) and that's it.

The more I learn, the less I seem to know.

 

[jdpjamesp]

Thanks gentlemen - I will get out DBA to look at changing this