Behaviour of statement cache for Oracle Database in UCP

tapj

UCP documentation says that statement cache for oracle database can be enabled by setting below:

pds.setMaxStatements(10)

How do we get performance enhancements here. As when we create a connection we do not provide statement. May be some other connection in the pool have the statement cached (which we will prepare later). lets say pds is a PoolDataSource

Connection conn = pds.getConnection();

This connection object returned from the pool may or may not have statement cached. So how do we achieve performance enhancements by enabling statement cache.

Unknown

UCP documentation says that statement cache for oracle database can be enabled by setting below:

pds.setMaxStatements(10)

How do we get performance enhancements here. As when we create a connection we do not provide statement. May be some other connection in the pool have the statement cached (which we will prepare later).

You get performance enhancements when the SAME executable statement is used repeatedly by your code. Each connection has its own cache so if 'some other connection' has the statement cached it won't help the connection you are using at all.

If you have 10 different executable statements that your code will use for a connection you should set the cache size to 10.

There will be NOTHING in the cache for a connection until a statement is created. So there will be NO performance improvements until that SAME statement is executed for the second (or third, etc) time.

The section in the doc 'above' the section you are referring to should have answered your question

http://docs.oracle.com/cd/E18283_01/java.112/e12265/optimize.htm#CFHEDJDC

Caching SQL Statements in UCP

Statement caching makes working with statements more efficient. Statement caching improves performance by caching executable statements that are used repeatedly and makes it unnecessary for programmers to explicitly reuse prepared statements. Statement caching eliminates overhead due to repeated cursor creation, repeated statement parsing and creation and reduces overhead of communication between applications and the database. Statement caching and reuse is transparent to an application. Each statement cache is associated with a physical connection. That is, each physical connection will have its own statement cache.
The match criteria for cached statements are as follows:

  The SQL string in the statement must be the same (case-sensitive) to one in the cache.
  The statement type must be the same (prepared or callable) to the one in the cache.
  The scrollable type of result sets produced by the statement must be the same (forward-only or scrollable) as the one in the cache.

Until you create a statement there will be nothing in the cache. The second time you execute that same statement for the connection the one in the cache will be used.

lets say pds is a PoolDataSource  

Connection conn = pds.getConnection();

This connection object returned from the pool may or may not have statement cached. So how do we achieve performance enhancements by enabling statement cache.

And again, the same document you used for your info should have answered that question. See this section that is just below the section you used:

Statement Cache Size Resource Issues
Each connection is associated with its own statement cache. 
. . . 

Each connection in the pool has its own cache. Only when the same statement is executed MULTIPLE times using the SAME connection will the caching have an effect.

Unknown

UCP documentation says that statement cache for oracle database can be enabled by setting below:

pds.setMaxStatements(10)

How do we get performance enhancements here. As when we create a connection we do not provide statement. May be some other connection in the pool have the statement cached (which we will prepare later).

You get performance enhancements when the SAME executable statement is used repeatedly by your code. Each connection has its own cache so if 'some other connection' has the statement cached it won't help the connection you are using at all.

If you have 10 different executable statements that your code will use for a connection you should set the cache size to 10.

There will be NOTHING in the cache for a connection until a statement is created. So there will be NO performance improvements until that SAME statement is executed for the second (or third, etc) time.

The section in the doc 'above' the section you are referring to should have answered your question

http://docs.oracle.com/cd/E18283_01/java.112/e12265/optimize.htm#CFHEDJDC

Caching SQL Statements in UCP

Statement caching makes working with statements more efficient. Statement caching improves performance by caching executable statements that are used repeatedly and makes it unnecessary for programmers to explicitly reuse prepared statements. Statement caching eliminates overhead due to repeated cursor creation, repeated statement parsing and creation and reduces overhead of communication between applications and the database. Statement caching and reuse is transparent to an application. Each statement cache is associated with a physical connection. That is, each physical connection will have its own statement cache.
The match criteria for cached statements are as follows:

  The SQL string in the statement must be the same (case-sensitive) to one in the cache.
  The statement type must be the same (prepared or callable) to the one in the cache.
  The scrollable type of result sets produced by the statement must be the same (forward-only or scrollable) as the one in the cache.

Until you create a statement there will be nothing in the cache. The second time you execute that same statement for the connection the one in the cache will be used.

lets say pds is a PoolDataSource  

Connection conn = pds.getConnection();

This connection object returned from the pool may or may not have statement cached. So how do we achieve performance enhancements by enabling statement cache.

And again, the same document you used for your info should have answered that question. See this section that is just below the section you used:

Statement Cache Size Resource Issues
Each connection is associated with its own statement cache. 
. . . 

Each connection in the pool has its own cache. Only when the same statement is executed MULTIPLE times using the SAME connection will the caching have an effect.

tapj

Thanks for you reply. What I could understand that, the fate of getting a cached statement depends on the connection returned from the pool. We do not have any control to get the specific connection which has the statement cached.

Joe Weinstein-Oracle

Correct. A statement, once created can only be cached/reused in association with the

connection that made it. So until every connection in the pool has been used to run

the same statement, you will not be sure to gain the performance benefit of reusing

a cached statement. Also note that if your application uses 20 different prepared statements

and the statement cache can only hold 10, depending on how the cache purges and populates,

the cache may not retain a given statement between the time it is created (and cached)

to when it is next needed and looked for in the cache.

dsurber-Oracle

Everything rp048 and Joe said is correct. Let's go a little farther, though....

Suppose your app has 100 distinct SQLs that it uses frequently and 100 more that it might use occasionally, once or twice a day. Further suppose your app runs in limited memory. How big should you make the statement cache?

1) Since there are 200 distinct SQLs set it to 200. Since the app has limited memory this may not be a good plan.

2) Since there are 100 distinct frequently used SQLs set it to 100. This is not such a good plan because when one of the infrequently used SQLs is returned to the cache it will force out one of the frequently used ones. When the frequently used one is recreated it will force out another frequently used one. Rinse. Repeat. Until you have recreated all of the frequently used SQLs.

3) Set it to 101. That leaves room for 100 frequently used SQLs and one extra for an infrequently used one. But what if you use two infrequently used ones? Or 3? What should you set the size to? 105? 110? The infrequently used SQLs are just wasting space in the cache as they are unlikely to be reused before they are aged out.

An alternative is to set the cache size to 100 and call Statement.setPoolable(false) on the infrequently used SQLs. The frequently used SQLs will stay in the cache and the infrequently used SQLs will be tossed, never taking up space in the cache.

If you have plenty of memory 200 is good. But if not and if you have some very infrequently used SQLs then use setPoolable(false). Note: the Oracle drivers do not cache plain Statement objects. Only Prepared and Callable Statements. So calling setPoolable on a plain Statement doesn't do anything either way.

Unknown

d6b58d85-49d8-4164-b459-c3001c787563 wrote:

Thanks for you reply. What I could understand that, the fate of getting a cached statement depends on the connection returned from the pool. We do not have any control to get the specific connection which has the statement cached.

That is true. But for some use cases it may be better to have some connections that are NOT pooled. Then you can set the cache size for those connections individually.

So for the use cases that dsurber provided you could use one connection (pooled or unpooled) with a cache size of 100 and an unpooled connection with a small cache size like 5 or so. Then the statements that use the unpooled connection will NOT purge the statements from the other pooled/unpooled connection since each connection cache is distinct.

Whether to use pooled or unpooled connections, or a mix of the two, depends on your use case.

For a single-threaded app there may not be much point in creating a connection pool.

tapj

Thanks rp0428 and dsurber for your insights, I have much better understanding now.

We had our home grown connection pool till now in our application. We are in the process of replacing it by UCP. We had a class StatementHandler which had connection and statement. While creating preparedStatement we used to loop on all available connections in the pool and replace the current connection with the one which has the statement cached. I think UCP lags here from our home grown connection pool, but as dsurber suggested there could be other ways to increase the hit ratio.

jschellSomeoneStoleMyAlias

> So how do we achieve performance enhancements

Presumably before you started looking into this you profiled your application, removed processing code bottlenecks, looked at queries themselves and optimized them, looked at the implementation and design and optimized that as well.  And you are now investigating caching to get the last bit of performance out of the system.