'Unknown SQL' nodes are visible in metric browser and agent log shows message regarding monitored Active Prepared & Callable statements exceeding a limit.

Document ID : KB000009252
Last Modified Date : 18/04/2018
Show Technical Document Details
Issue:

'Unknown SQL' nodes are visible in metric browser and agent log shows one of these messages:

10.5.1 HotFix 51 ERROR message:

Active Prepared & Callable statements that are monitoredexceeded the limit due to which 'Unknown SQL' metrics will get generated. Set a higher value than 128for the system property 'wily.PreparedAndCallableStatements.ActiveInMemory.maxMonitored' and restart the application. 

 

10.7 WARNing message:

Active Prepared & Callable statements that are monitored exceeded the limit due to which 'Unknown SQL' metrics may get generated. Set a higher value than 1024 for the hot property com.wily.introscope.agent.sqlagent.PreparedAndCallableStatementCache.maxsize

Environment:
CA APM Java Agent 10.5.1 HotFix 51, 10.7
Cause:

A new SQL cache for prepared and callable statements was developed for 10.5.1 HotFix 51 and has been further enhanced for APM 10.7. For applications that are executing a large number of such statements, this cache allows the agent to process more than the default number of statements at any point in time.

Resolution:

10.5.1 HotFix 51:

In the Application Server configuration add this Java property and set a larger number than the default value of 128:

-Dwily.PreparedAndCallableStatements.ActiveInMemory.maxMonitored

 

10.7:

The 10.5.1 implementation has changed to be via a dynamic/hot agent profile property and set a larger number than the default value of 1024:

com.wily.introscope.agent.sqlagent.PreparedAndCallableStatementCache.maxsize

 

The required value to ensure that all applications with large number of simultaneous SQL prepared and callable statements can be successfully monitored without "Unknown SQL" nodes will depend on the application and example values could be 3000, 10000. The value used will have no impact on resources required. Once the application closes a prepared statement the agent will remove it from the cache so aside from the specified size limit the cache is also being constantly cleaned.

Additional Information:
As a note of confirmation, we want to state that there is currently no 10.5.2 hotfix for this, if there is a requirement for this fix in 10.5.2, please raise a support request with us so we can investigate.