The JWP process is inactive in AWI, and when checking for the process itself on the AE server, it appears to be running.
However when using AWI, it is clear that the JWP is unresponsive. Any AWI function that relies on the JWP such as the process monitoring and the search functionality do not work.
When the JWP process is restarted, it is working temporarily with the message eventually appearing in the AWI as JWP is inactive.
0200219/192235.144 - 35 U00003524 UCUDB: ===> Time critical DB call! OPC: 'EXEC' time: '249895ms'
20200219/192235.144 - 35 U00003525 UCUDB: ===> 'DELETE FROM PMIAM WHERE PMIAM_PMMA_IDNR IN ( SELECT * FROM (SELECT PMMA_Idnr FROM PMMA WHERE PMMA_Timestamp <= ? ORDER BY PMMA_Idnr) WHERE ROWNUM <= 1000 )'
20200219/192552.607 - 35 U00003524 UCUDB: ===> Time critical DB call! OPC: 'EXEC' time: '197463ms'
20200219/192552.607 - 35 U00003525 UCUDB: ===> 'DELETE FROM PMMA WHERE PMMA_Idnr IN ( SELECT * FROM (SELECT PMMA_Idnr FROM PMMA WHERE PMMA_Timestamp <= ? ORDER BY PMMA_Idnr) WHERE ROWNUM <= 1000 )'
20200219/192558.482 - 36 U00003524 UCUDB: ===> Time critical DB call! OPC: 'EXEC' time: '459055ms'
20200219/192558.482 - 36 U00003525 UCUDB: ===> 'DELETE FROM PMMAV WHERE PMMAV_PMMA_Idnr IN ( SELECT * FROM (SELECT PMMA_Idnr FROM PMMA WHERE PMMA_Timestamp <= ? ORDER BY PMMA_Idnr) WHERE ROWNUM <= 1000 )'
The JWP dropping does indeed appear to be caused by the delete of the PM* table records. These tables are used to keep track of performance within the system. The JWPs automatically cleanup (delete) the PM* records - by default, they will delete 1000 records that are related to each other throughout the PM* tables (much like the AH table is the header table for all A* tables for statistics) and the JWPs will delete anything over 30 days. These delete statements are taking too long and causing a bottleneck for other traffic (AWI traffic for example) that uses the JWPs.
Release : 12.1
Component : AUTOMATION ENGINE
The ultimate fix to this issue will be to update to 12.2 or 12.3. The reason is there were optimizations to the PM* tables and routines that were built into those two versions where there was too much risk to update the code in 12.1.
Workaround to stay in v12.1
CREATE INDEX NK_PMMA_Timestamp ON PMMA(PMMA_Timestamp DESC, PMMA_PMMD_Idnr) tablespace UC4_INDEX;If problems after the index is added persists, please try the following:
https://docs.automic.com/documentation/webhelp/english/AWA/12.1/DOCU/12.1/AWA%20Guides/help.htm#Installation_Common/install_AWA_ARA_sys_requirements_sizing.htm?Highlight=JWP- Lower the batch size that is deleted at one time. By default the JWP deletes 1000 records at a time. This number can be lowered and tuned to what is best for your system (a few different options may need to be tried, maybe start with 800). Lowering the number may cause the overall runtime for cleaning up the PM* tables to take longer as the load is spread out, lowering the amount of time the JWPs are busy running one statement, but increase the overall time the cleanup takes. To do this: