Customer complained about strong performance degradation. For example, the total records reads per sec dropped down by 4-5 times. It's turned out that someone enabled Statement Cache for all clients on one of the databases.
12/21/17 Activity: Resource Queues
Time Queue - Requests - ------- Waits ------- --- Wait usec ---
Total /Sec Total /Sec Pct /Req /Wait
10:57:46 Statement Cache 0 0 0 0 0.00 0 0
11:03:03 Statement Cache 0 0 78354 260 0.00 0 0
11:08:17 Statement Cache 0 0 988817 3285 0.00 0 0
11:13:31 Statement Cache 0 0 1310546 4354 0.00 0 0
11:18:43 Statement Cache 0 0 1152035 3840 0.00 0 0
11:23:56 Statement Cache 0 0 775776 2577 0.00 0 0
11:29:08 Statement Cache 0 0 721655 2406 0.00 0 0
11:34:20 Statement Cache 0 0 629053 2097 0.00 0 0
11:39:33 Statement Cache 0 0 440273 1463 0.00 0 0
11:44:46 Statement Cache 0 0 570785 1896 0.00 0 0
11:49:58 Statement Cache 0 0 473506 1573 0.00 0 0
11:55:09 Statement Cache 0 0 445112 1484 0.00 0 0
12/21/17 Activity: Other Time Total Per Min Per Sec Per Tx 10:57:46 Semaphore latch waits 9 2 0.03 0.00 11:03:03 Semaphore latch waits 620304 123649 2060.81 4.38 11:08:17 Semaphore latch waits 10620960 2117135 35285.58 601.07 11:13:31 Semaphore latch waits 10935842 2179902 36331.70 308.82 11:18:43 Semaphore latch waits 10350649 2070130 34502.16 80.82 11:23:56 Semaphore latch waits 8825084 1759153 29319.22 37.71 11:29:08 Semaphore latch waits 8036457 1607291 26788.19 33.49 11:34:20 Semaphore latch waits 8502416 1700483 28341.39 24.75 11:39:33 Semaphore latch waits 8257009 1645915 27431.92 20.61 11:44:46 Semaphore latch waits 8383099 1671050 27850.83 16.53 11:49:58 Semaphore latch waits 8934889 1781041 29684.02 20.51 11:55:09 Semaphore latch waits 8599188 1719838 28663.96 21.89
12/21/17 Activity: Latch Counts => USR Latch Time Owner Locks/Sec Naps/Sec 10:57:46 -- 153 0 11:03:03 6585 2630 44 11:08:17 3291 35735 730 11:13:31 6598 36671 553 11:18:43 4888 34850 581 11:23:56 6447 29684 637 11:29:08 4982 27132 619 11:34:20 5720 28718 676 11:39:33 5332 27876 766 11:44:46 6385 30535 818 11:49:58 6017 30180 814 11:55:09 5911 29113 794
Is it possible to find out who enabled Statement Cache?
There were no promon sessions connected to the database between 10:57:46 and 11:03:03 when Statement Cache supposed to be enabled. So I guess it was done in program.
All the time promon shows the zero requests for Statement Cache but Waits are not zero. Is it a bug?
Progress 10.2B0817 on HP-UX 11.31 ia64
Also note that USR latch was 100% busy (we see the latch's owners during every snapshot). USR latch was locked 30,000 times per second. In other words, the duration of latch lock was about 30 /micro/seconds (= 1 / 30,000) but a normal duration of the most latch locks is about 30-100 /nano/seconds. I guess the processes hold the USR latch while they read or write the .cst files on disk. The microseconds are typical for disk I/O.
If a query of the _Connect VST will use the EXCEPT phrase for the _Connect-CacheLineNumber and _Connect-CacheInfo fields will Progress still read the .cst files of the sessions where the cache statement is enabled?
Update: Tested. EXCEPT phrase does not help.
>Is it possible to find out who enabled Statement Cache?
George, is that reflected in the AI file ? Try to enable Statement Cache on sports db and scan the AI to see if it is recorded there. If it is not recorded I guess you are out of luck.
> is that reflected in the AI file ?
No, Stmt Caching Type (_Connect-CachingType) is a flag in Usrctl Table. Its changes do not create the recovery notes.
> If it is not recorded I guess you are out of luck.
In our case we had found out that statement cache was enabled by a program rather than through promon. The rest was simple. ;-)
Makes sense. _Connect is VST, i.e. memory structure. There is no need for recovery as VST will start from scratch each time database goes up.
still possible to write an AI note though. other memory structures (e.g. transaction table) get notes. also, there are “no action” or “comment" notes that can be written. they just have so text but no do or undo subroutines.