Running the code below takes between 700ms and 800ms on a database with 69 areas (64 ai areas included) and this long response time is caused by the find _areastatus.
FOR EACH _Area NO-LOCK:
FIND _Areastatus WHERE _Areastatus-Areanum = _Area._Area-number no-lock.
A for each of the entire _areastatus vst takes max 15ms.
Is it possible to rewrite this sample code in a way that performs better?
Change the order of the tables:
FOR EACH _Areastatus NO-LOCK:
FIND _Area WHERE _Area._Area-number = _Areastatus-Areanum no-lock.
_Area has an index on the _Area-number field.
_Areastatus does not have an index on the _Areastatus-Areanum field and your FIND (especially without FIRST) does a full table scan.
Also _Areastatus reads the ACO (Area Control Object) blocks and Progress uses LRU latch to access them. Check if you have a bottleneck on the LRU latch. I would suggest to put all ACO blocks into dynamically allocated alternate buffer pool.
Thanks for the Obvious change of order of the tables (i should have checked that myself - was thinking about non-existing indexes). I will check LRU latch bottleneck.
What do you mean by "put all ACO blocks into dynamically allocated alternate buffer pool"?
You can remove any blocks in buffer pool out of the LRU chain. Each buffer header has a field called "Skips" - it's a "countdown" for the block before the block's access will again change LRU chain. We can set the "countdown" equal to its maximum value (two billions minus one). In most cases it will mean "never change LRU chain". For a short period of time set the -lruskips to 2147483647, read the blocks and reset the -lruskips back to zero.
promon/R&D/debghb/6/1. Status: Cache Entries
promon/R&D/debghb/6/4. Status: Lru Chains
Num DBKEY Area Hash T S Usect Flags Updctr Lsn Chkpnt Lru Skips 33 64 1 139 O 0 L 4 0 0 0 2147483647 34 64 6 824 O 0 L 1385 0 0 0 2147483647 35 64 7 74 O 0 L 43 0 0 0 2147483647 36 64 8 211 O 0 L 10 0 0 0 2147483647 37 2 9 348 O 0 L 6 0 0 0 2147483647 38 2 10 485 O 0 L 6 0 0 0 2147483647 39 64 11 622 O 0 L 6 0 0 0 2147483647
Last column marks the blocks "who want live forever" off the LRU chain.
LruShot.p does this trick for a few special blocks (including the ACO blocks):
For example, it would be good idea to put all metachema data in such "alternate buffer pool" because all sessions read a lot of blocks during db connection and sometime the connections should be as fast as possible.
It's easy to create the dynamically allocated alternate buffer pools right after db startup.
You can do as George P recommends but: why do you care?
Wouldn't you get the same result with less fiddling and diddling by simply putting the schema area into B2?
I asked about it because I did not know what he meant with ACO blocks, but after looking at the program, I think I don't care anymore. I would probably care if we were suffering from bad performance but excpept for this peculiar query we don't. So I will just change the _area _areastatus order.
> Wouldn't you get the same result with less fiddling and diddling by simply putting the schema area into B2?
Yes, if the schema area contains only metaschema and db is using the -B2 and LRU mechanism is not enabled for alternate buffer pool - in other words, if a database is maintained in a right way. Dynamically allocated alternate buffer pool is just an alternate and "dynamic" solution (we can do it at any time when needed).
And answer is NO for ACO blocks: we can't assign all of them to B2.
BTW, I saw the cases when the blocks had the incorrect Lru flag in the buffer headers. The blocks that should be in the primary buffers had Lru 1. The blocks that belong to a table assigned to B2 had Lru 0. It was on sports db but I can't reproduce this behaviour right now.
> why do you care?
Reading ACO blocks (through _AreaStatus or while updating activity counters in promon) is not an issue when db is working smoothly. But the contention on LRU latch may cause the delays in the seconds - exactly at the time when we need to check what is going wrong in db.
Do we need "flu shot" while we are healthy? Yes, because it will be too late to do it when we get ill.