VST _AreaStatus very slow access

Change the order of the tables:

FOR EACH _Areastatus NO-LOCK:

 FIND _Area WHERE _Area._Area-number = _Areastatus-Areanum no-lock.

END.

_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.

Check

promon/R&D/debghb/6/1. Status: Cache Entries

or

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): 

ftp.progress-tech.ru/.../LruShot.p

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.