G
George Potemkin
Guest
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 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?
Continue reading...
Continue reading...