Understanding Prepared Statement Caching metrics in JBoss

Solution Unverified - Updated

Environment

  • Red Hat JBoss Enterprise Application Platform 6
  • prepared-statements-cache-size as defined in
    • jboss-as-datasources_1_0.xsd
    • jboss-as-datasources_1_1.xsd
    • jboss-as-datasources_1_2.xsd

Issue

  • understand what these CLI stats mean
  • Does it even make sense to have this caching if caching is enabled on DB side?
  • How does the client leverage these settings?

Resolution

Please refer to the JDBC Statistics table in the documentation.

Note that statistics-enabled needs to be true for metrics to be gathered.

<xa-datasource jndi-name="java:jboss/xa-datasources/OracleDS" pool-name="oracle-test-pool" enabled="true" statistics-enabled="true">

The prepared-statements-cache-size will only be leveraged by clients that are using a Content from docs.oracle.com is not included.PreparedStatement or Content from docs.oracle.com is not included.CallableStatement

prepared-statement-cache-size declared inside of a statement block in the datasource or xa-datasource definition.

<statement>
    ...
    <prepared-statement-cache-size>30</prepared-statement-cache-size>
    ...
</statement>

such that...

[standalone@localhost:9999 /] /subsystem=datasources/xa-data-source=oracle-test-pool:read-resource(recursive=true)
{
    "outcome" => "success",
    "result" => {
        ...
        "prepared-statements-cache-size" => 30L,
        "share-prepared-statements" => <true or false>,
        "statistics-enabled" => true,
        "track-statements" => <true or false or "nowarn">,
        ...
    }
}

A reading of the metrics from below follows.

[standalone@localhost:9999 /] /subsystem=datasources/xa-data-source=oracle-test-pool/statistics=jdbc:read-resource(include-runtime=true)
{
    "outcome" => "success",
    "result" => {
        "PreparedStatementCacheAccessCount" => "380",
        "PreparedStatementCacheAddCount" => "266",
        "PreparedStatementCacheCurrentSize" => "266",
        "PreparedStatementCacheDeleteCount" => "0",
        "PreparedStatementCacheHitCount" => "114",
        "PreparedStatementCacheMissCount" => "0",
        "statistics-enabled" => true
    }
}

"PreparedStatementCacheAccessCount" => "380"
The statement cache was accessed 380 times.

"PreparedStatementCacheAddCount" => "266"
266 statements were added to the statement cache.

"PreparedStatementCacheCurrentSize" => "266"
There are 266 prepared and callable statements currently cached in the statement cache.

"PreparedStatementCacheDeleteCount" => "0"
0 statements were discarded from the cache.

"PreparedStatementCacheHitCount" => "114"
114 statements from the cache were used.

"PreparedStatementCacheMissCount" => "0"
0 statement requests could not be satisfied with a statement from the cache.

PreparedStatementCacheAddCount indicates that a statement was not found in the cache and was therefore added to the cache.

PreparedStatementCacheMissCount indicates that a statement was in the cache but could not be used for the current request (Content from github.com is not included.Reference) as the PreparedStatement is already in use within a Transaction (either explicitly or via autocommit).

SBR
Product(s)
Category
Tags

This solution is part of Red Hat’s fast-track publication program, providing a huge library of solutions that Red Hat engineers have created while supporting our customers. To give you the knowledge you need the instant it becomes available, these articles may be presented in a raw and unedited form.