JDBC drivers and implicit statement caching

http://www.oracle.com/technetwork/database/enterprise-edition/memory.pdf

11.2 JDBC Developers Guide HTML

11.2 JDBC Developers Guide - cache configuration and use PDF

The property can either be set as a System property via -D or as a connection property on the getConnection method. Note that setting freeMemoryOnEnterImplicitCache does not cause the parameter value buffers to be released, only the row data buffers.



oracle.jdbc.freeMemoryOnEnterImplicitCache=true/false


The default is Integer.MAX_VALUE. This is the maximum size for a buffer which will be stored in the internal buffer cache. A single size is used for both the char[] and byte[] buffers. The size is in chars for the char[] buffers and bytes for the byte[] buffers. It is the maximum buffer size, not a predefined size.


oracle.jdbc.maxCachedBufferSize=Integer.MAX_VALUE

oracle.jdbc.useThreadLocalBufferCache=true/false

With the 11.2 driver setting maxCachedBufferSize can sometimes improve performance in very large systems with large statement caches and SQL with widely divergent buffer size requirements. In 11.2 the value of maxCachedBufferSize is interpreted as the log base 2 of the maximum buffer size. For example if maxCachedBufferSize is set to 20 the max  size buffer that is cached is 2^20 = 1048576.  For backwards compatibility, values larger than 30 are interpreted as the actual size rather than log2 of the size, but using powers of 2 is recommended.


It is usually the case that setting maxCachedBufferSize to a reasonable value has no impact. If you need to set maxCachedBufferSize, start with 18. If you have to set the value to less than 16, you probably need more memory.

maxCachedBufferSize=

Initial size of the statement cache. Setting the property to a positive value enables the Implicit Statement Cache. The default is “0”. The property can be set as a System property via -D or as a connection property via getConnection. Calling OracleConnection.setStatementCacheSize and/or OracleConnection.setImplicitCachingEnabled overrides the value of implicitStatementCacheSize. This property may make it easier to enable the Implicit Statement Cache in cases where enabling it in code is impractical.

oracle.jdbc.implicitStatementCacheSize=

Comments

Post a Comment

Popular posts from this blog

Sites, Newsletters, and Blogs

BLOGS Java.net Weblogs Martin Fowler's blog Paul Graham Ruminations of a Programmer Form Follows Function Newsletters Core Java Tech Tips Enterprise Java Tech Tips JavaSpecialists.EU TheServerSide.com Sites Java.net Today Hot Java Sites CSS Zen Garden Evernote Hacker News Pragmatic Programmer
Read more

Oracle JDBC ReadTimeout QueryTimeout

If a query exceeds the oracle.jdbc.ReadTimeout without receiving any data, an exception is thrown and the connection is terminated by the Oracle driver on the client. Unfortunately the session will still be queued on the database and continue to wait for locks, hold any current locks, and complete any DML/PL*SQL procedures that are pending on the server-side of the orphaned connection. If an application, on a another connection, due to ReadTimeout exception, retries DML/PL*SQL  which requires locks, those queries will queue behind the initial DML/PL*SQL. This will increase the workload exacerbating the situation. On applications with retries, this can be observed by querying the v$session table  or gv$session on RAC and noting new sessions started periodically based on the ReadTimeout interval. The sessions may often have the same SQL_ID and/or SQL_HASH_VALUE. If stmt.setQueryTimeout(Seconds) is issued and the statement exceeds the timeout, it will atte...
Read more

Locks held on Oracle for hours after sessions abnormally terminated by node failure

If a session holds db locks and is abnormally terminated (no fin/ack), the locks will persist until the db session is closed, typically around 2 hours and 12 minutes with default network tcp_keepalive settings. Abnormally terminated does not include CTRL C or kill -9. The ojdbc6 driver apparently has a shutdown hook thread that closes the connections in graceful shutdowns. The OS apparently closes the connections when processes are killed. Abnormally terminated might include power failure, firewall failures, Out Of Memory, OS/kernel crash, network connection failure, JBoss or other server node failure, etc. This was easily reproduced by creating a  process that connected to the db and updated a record but did not commit. While the first process was waiting, a second process with a contending update was started which blocked on the first update. The first clients network cable was then disconnected. The second clients transaction waited for approximately 2 hours until the databa...
Read more