Batch processes that are in the process of exiting are blocked in promon on STCA associated with Statement Caching
Blocked Clients screen in promon showing 200+ processes blocked on STCA
01/09/14 Status: Blocked Clients
08:50:05
Usr Name Type Wait Wait Info Trans id Login time
1031 SELF/ABL STCA 0 0 01/09/14 06:28
1032 SELF/ABL STCA 0 0 01/09/14 06:44
1033 SELF/ABL STCA 0 0 01/09/14 06:26
1038 _auto-bu SELF/ABL STCA 36864 2118133877 01/08/14 23:38
1039 SELF/ABL STCA 0 0 01/09/14 06:42
1040 SELF/ABL STCA 0 0 01/09/14 06:46
1042 _auto-bu SELF/ABL STCA 9493056 2118140397 01/08/14 23:03
1044 SELF/ABL STCA 0 0 01/09/14 06:02
1053 _auto-bu SELF/ABL STCA 163685824 2118136016 01/08/14 23:09
1064 _auto-bu SELF/ABL STCA 13112256 2118135287 01/08/14 23:03
1067 _auto-bu SELF/ABL STCA 17273472 2118136018 01/08/14 23:03
1069 _auto-bu SELF/ABL STCA 3 2118135067 01/08/14 23:03
Batch processes are not showing in proshut screen.
Batch processes appear to have an active transaction started in promon.
Database is extremely slow for all users.
Stack trace from _progres of one of the blocked clients on Solaris shows:
61885: /usr/dlc/bin/_progres -b -pf parameterfile.pf -p code.p -param
ffffffff7e5e7b70 semsys (2, 20000e9, ffffffff7fffd788, 1, 0)
0000000100755380 latWaitOnSem (100d88260, ffffffffffffffff, 7fff6801cfaa0, 1a, 0, 7fff6800070e8) + 88
00000001007550e8 latUsrWait (100d88260, 7fff6801d7d38, 3, d0, 7fff680008400, 7fff6801cfaa0) + 58
00000001006b25b0 dbUserStmtCacheLock (100d88260, 7fff6801d00d8, f1bd, ffff000 7fff6801cfaa0, 1a) + 120
00000001006b1c6c dbUserGetStatementCache (100d88260, fffffffffffcd0cc, 0, ffffffff7fffdc24, fffffffffffff3a6, ff
0000000100746e88 vstConnectFetch (100d88260, 41f, 10d6a0f10, 520, 0, 7fff6801d00d8) + 93c
000000010072f85c rmFetchVirtual (100d88260, ffffffffffffbfff, 41f, 10d6a0f10, 520, ffffffffbfff0000) + 38
00000001006abb94 dbRecordGet (100d88260, ffffffff7fffe0c0, ffffffffffffbfff, 0, ffffffffffffc13f, 0) + ec
00000001006cd6a0 dsmRecordGet (100d88260, ffffffff7fffe0c0, 0, 1, 1, ffffb000) + e4
0000000100198344 slrmget (7f38, ffffffff7fffede0, 5c8, 100d07390, 0, 520) + e0
0000000100199a58 dbrmget (ffffffff7fffe270, ffffffff7fffede0, 7f38, 0, ffffffffffffbfff, 100e270c0) + 7c
000000010019a828 dbqry (10e5a6680, 0, 8, ffffffff7fffe270, ffffeea4, ffffffffffffffff) + 328
0000000100475eb0 proqry (100e26ed0, 100e270c0, 100800, 10e5a6718, 10dabcc70, 100b17998) + bc
00000001004755d4 profnd (7f38, 0, ffffffff7fffeda0, 10e5a65c0, 0, 100e27078) + 29c
000000010011addc fdfnd (0, 100b54368, 100b18610, 0, 100f4a590, 10e5a6680) + 138
00000001004e913c bfFindRow (10dabcc70, ffffffff7fffeda0, 100b19, 1, 0, 100800) + 4d0
0000000100562a28 rnbfnxtDoit (10e5a6600, 100cb1df0, 100b18390, 10e3b60f8, 10b7e2c68, 10b7e2ae0) + 448
0000000100562414 rnbfnxtBody (100cb1df0, 37, 10e3b60f8, 10b7e2c68, 10dabcc70, 10e5a6620) + 518
0000000100561c1c rnbfnxt (100cb1df0, ff00, 10e3b60f8, ff000000, 10dabcc70, 1c00) + 370
000000010056a5f4 rnexec_entry (100c00, 100cb1df0, 100ca7, 0, 100b18390, 10b7e2bd0) + 79c
000000010056d524 rninterpret (100cb1df0, 1006, 1000, ffffffffffffffff, 0, 0) + 5c
000000010055c170 rnqget_rcsv (10d0d4200, 0, 0, 10b7e29e0, 100caf5b8, 100cb1df0) + 150
0000000100550e04 rnDynQryGetNext (4, 4, 84, 0, 10d0d4200, 1) + fc
0000000100332dac umDynqGetAttr (100d33080, 0, 100d33080, 0, 0, 1a40) + 844
0000000100399b70 umSuperGetAttr (c8, 100000, 100d33080, 100332568, 0, 348) + 160
00000001002c0a98 ioGetAttribute (10e4e14f8, fedcb800, 100d33080, 0, 0, c87) + c8
0000000100155804 fmEWDAX (9d, 100d33080, 0, 10e154c40, 10e154c38, 100d33080) + 50
00000001001570ac fmeval (3, 10e154c38, 9d, 0, 100d33080, 1) + 59c
000000010056cefc rnexpstmt (10e08f908, 100cb1df0, b00, 100d33080, 10056ce58, 0) + a4
000000010056a5f4 rnexec_entry (100c00, 100cb1df0, 100ca7, 0, 100b18390, 10b7e29f8) + 79c
000000010056d524 rninterpret (100cb1df0, 1006, 1000, ffffffffffffffff, 1, 0) + 5c
00000001001750c4 rnrq (100d058f0, a, 100800, 100000, 100b19000, 100cb1df0) + 13c
000000010010db4c main (0, 100b17000, 0, 100800, 100b18790, 100800) + 648
00000001000d627c _start (0, 0, 0, 0, 0, 0) + 17c
Stack trace of _mprosrv on Solaris is showing:
33895: /usr/dlc/bin/_mprosrv dbname -m1 -N tcp -Nd /d
ffffffff7e5e7b70 semsys (2, 20000d3, ffffffff7fffcdb8, 1, 0)
0000000100203890 latWaitOnSem (1005dfcf0, ffffffffffffffff, 7fff68091f820, 1a, 0, 7fff6800070e8) + 88
00000001002035f8 latUsrWait (1005dfcf0, 7fff680866e38, 3, d0, 7fff680008400, 7fff68091f820) + 58
0000000100156178 dbUserStmtCacheLock (1005dfcf0, 7fff6801c8ab0, 8467, ffff000 7fff68091f820, 1a) + 120
0000000100155834 dbUserGetStatementCache (1005dfcf0, fffffffffffcdf7e, 100676e80, ffffffff7fffd254, fffffffffffff3df, 100501) + 5c
00000001001f53e4 vstConnectFetch (1005dfcf0, 40c, 1006a6320, 6f83, 0, 1c) + 988
00000001001ddd6c rmFetchVirtual (1005dfcf0, ffffffffffffbfff, 40c, 1006a6320, 6f83, ffffffffbfff0000) + 38
000000010014f75c dbRecordGet (1005dfcf0, ffffffff7fffd6f0, ffffffffffffbfff, 2, ffffffffffffc13f, 0) + ec
000000010017bbb0 dsmRecordGet (1005dfcf0, ffffffff7fffd6f0, 0, 6f83, 100506000, 100506) + e4
00000001002131d8 esvrmget (ffffffff7fffd8a0, 100506f28, 7f68, 0, 100400, ffffb1e0) + 320
00000001000e7cb4 dbrmget (ffffffff7fffd8a0, 100506f28, 7f68, 0, ffffffffffffbfff, 0) + a8
00000001000e8a58 dbqry (10065dd48, 0, 8, ffffffff7fffd8a0, ffffeea4, 3) + 328
0000000100223a34 nsadoix (7fff68091f820, 10065dc48, 10, 10065dc48, 1005069f0, 1005069f8) + 15b4
000000010022c428 nsaloop (7fff68091f820, 7c7, ffffffffffff, 7fff79b3d6858, 1005069f8, 1004f5958) + 854
00000001000e65d4 doserve (2096, 10052faf8, 1004f51e8, 100400, 1005dfcf0, 2) + 8e4
00000001000e5cd4 main (1d, ffffffff7ffff768, 1004f5, 100400, 1004f5000, 1004f5) + 138
000000010006e27c _start (0, 0, 0, 0, 0, 0) + 17c
Batch processes received an 8812 error sometime prior to getting blocked on the STCA.
Client Database Statement Caching has been enabled for all users on the database.
Issue did not occur prior to adding the client database statement caching for the additional 360 batch processes.
