Oracle MVA

Tales from a Jack of all trades

JDBC statement cache setting

leave a comment »

Recently I was asked about the statement cache setting in WebLogic by a colleague. Reason he asked about it, was that documentation wasn’t making any sense to him in combination with advise given to him from an external expert. Here’s the doc he was referring to.

The tooltip documentation in WebLogic says:

WebLogic Server can reuse statements in the cache without reloading the statements, which can increase server performance. Each connection in the connection pool has its own cache of statements.

Now this suggests that WebLogic is maintaining some kind of cache, but really it isn’t (in combination with an Oracle database). All it is doing is opening a cursor on the Oracle database and reusing this cursor.

To demonstrate what is happening I created a small example. The example I use is an sqlauthenticator for WebLogic, allowing users in some database table to authenticate in WebLogic. In this presentation you can find the DDL and DML for the tables and a description how to setup this sql authenticator.

So, my initial database has a statement cache of 10 (default). When I restart the database and WebLogic and I login to the WebLogic console, I can find the following open cursors:

<br />select hash_value, cursor_type, sql_text<br />  from v$open_cursor<br /> where user_name = 'DEMO'<br />/<br />HASH_VALUE CURSOR_TYPE SQL_TEXT<br />---------- --------- ------------------------------------------------<br />32127143   OPEN      SELECT 1 FROM DUAL<br />238104037  OPEN      SELECT G_NAME FROM GROUPMEMBERS WHERE G_MEMBER = :1<br />3221480394 OPEN      SELECT U_PASSWORD FROM USERS WHERE U_NAME = :1<br /><br />3 rows selected.<br />

The minute I reconfigure the statement cache to 0 (=disabled), restart database and WebLogic and login to the console, I find the following open cursors:

<br />HASH_VALUE CURSOR_TYPE SQL_TEXT<br />---------- --------- ------------------------------------------------<br />238104037  OPEN      SELECT G_NAME FROM GROUPMEMBERS WHERE G_MEMBER = :1<br />1 row selected.<br />

This simple test teaches me that a cursor is kept open on the users table and on dual. The query that is running on dual is actually the test query for the datasource.

It would suggest that the statement-cache does keep an administration on which query has run over which connection. This test is too small to bring proof off that. Also I wonder what happens in combination with the pin to thread setting of the jdbc driver. Food for a new blogpost 🙂

So, in short: the statement cache of your datasource has a direct impact on the number of open cursors. This can (is) improving performance, you don’t have to create a new cursor when you reuse a statement. Setting the statement cache to 0 (disable the cache) is in my opinion not a best practice, by default every session to your 11.2 database can have 50 cursors so you got plenty to spare. You should tune open_cursors and session_cached_cursors on the database according to your applications need.

Hope this helps.

Advertisements

Written by Jacco H. Landlust

April 19, 2013 at 10:55 pm

Posted in RDBMS, Weblogic

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: