OCI Connection Pooling
The OCI connection pooling feature is an Oracle-designed extension. The connection pooling provided by the JDBC OCI driver enables applications to have multiple logical connections, all of which are using a small set of physical connections. Each call on a logical connection is routed on to the physical connection that is available at the given time.(有点像OS为线程分配的时间片)
Client Result Cache
Client result cache feature enables client-side caching of SQL query result sets in client memory. In this way, OCI applications can use client memory to take advantage of the client result cache to improve response times of repetitive queries.
Benefits of Client Result Cache
The benefits of the OCI client-side result set cache are the following:
■The JDBC OCI client-side result set cache is completely transparent to OCI applications and its cache of result set data is kept consistent with any session or database changes that affect its result set.
■Since the result cache is on the client-side, a cache hit causes SQL query execute and fetch calls to be processed locally, instead of making server round trips. This can result in huge performance savings for server resources, for example, server CPU and server I/O.
■The result cache on JDBC OCI client is per-process, so multiple client sessions can simultaneously use matching cached result sets.
■The result cache on JDBC OCI client minimizes the need for each OCI application to have its own custom result set cache.
■The result cache on JDBC OCI client uses OCI client memory that is cheaper than server memory.
Usage Guidelines in JDBC
You can enable result caching in the following three ways:
■ RESULT_CACHE_MODE Parameter
■ Table Annotations
■ SQL Hints
Note:
■You must use JDBC statement caching or cache statements at the application level when using the JDBC OCI client result cache.
■The SQL hints take precedence over the session parameter RESULT_CACHE_MODE and table annotations. The table annotation FORCE takes precedence over session parameter.(也就是说可以使用SQL hints来覆盖之前的设置)
RESULT_CACHE_MODE Parameter
You can use the RESULT_CACHE_MODE parameter to decide the result cache mode
across tables in your queries. Use this clause with the ALTER SESSION and ALTER
SYSTEM statements, or inside the server parameter file (init.ora) to determine result
caching. You can set the RESULT_CACHE_MODE parameter to control whether the SQL
query result cache is used for all queries, or only for the queries that are annotated
with the result cache hint using SQL hints or table annotations.
Table Annotations
You can use table annotations to enable result caching without making changes to the
code. The ALTER TABLE and CREATE TABLE statements enable you to annotate
tables with result cache mode. The syntax is:
CREATE|ALTER TABLE [<schema>.]<table> ... [RESULT_CACHE (MODE {FORCE|DEFAULT})]
Following example shows how to use table annotations with CREATE TABLE
statements:
CREATE TABLE foo (a NUMBER, b VARCHAR2(20)) RESULT_CACHE (MODE FORCE);
ollowing example shows how to use table annotations with ALTER TABLE
statements:
ALTER TABLE foo RESULT_CACHE (MODE DEFAULT);
SQL Hints
You can use SQL hints to specify the queries to be cached by annotating the queries
with a /*+ result_cache */ or /*+ no_result_cache */ hint. For example, look at
the following code snippet:
String query = "select /*+ result_cache */ * from emp where empno < : 1";
((oracle.jdbc.OracleConnection)conn).setImplicitCachingEnabled(true);
((oracle.jdbc.OracleConnection)conn).setStatementCacheSize(10);
PreparedStatement pstmt;
ResultSet rs;
for (int j = 0 ; j < 10 ; j++ )
{
pstmt = conn.prepareStatement (query);
pstmt.setInt(1,7500);
rs = pstmt.executeQuery();
while (rs.next( ) )
{
// see the values }
rs.close;
pstmt.close( ) ;
}
}
In the preceding example, the client result cache hint /*+ result_cache */ is annotated to the actual query, that is, select * from emp where empno < : 1.So, the first execution of the query goes to the database and the result set is cached for the remaining nine executions of the query. This improves the performance of your application significantly. This is primarily useful for read-only data.
Following are some more examples of SQL hints. All the following examples assume that the dept table is annotated for result caching by using the following command:
ALTER TABLE dept result_cache (MODE FORCE);
Examples
■SELECT * FROM emp
The result set will not be cached.
■SELECT * FROM dept
The result set will be cached.
■SELECT /*+ result_cache */ empno FROM emp
The result set will be cached.
■SELECT /*+ no_result_cache */ deptno FROM dept
The result set will not be cached.
■SELECT /*+ result_cache */ * FROM dept
The result set will be cached though query hint is not necessary.