CONNECTION POOLING AND CACHING in Font

Draw Code-39 in Font CONNECTION POOLING AND CACHING

poolConfigProps.put(OracleOCIConnectionPool.CONNPOOL_MIN_LIMIT, "2") ; poolConfigProps.put(OracleOCIConnectionPool.CONNPOOL_INCREMENT, "1") ; poolConfigProps.put(OracleOCIConnectionPool.CONNPOOL_MAX_LIMIT, "20") ; cpool.setPoolConfig(poolConfigProps); System.out.println ("Min poolsize Limit = " + cpool.getMinLimit()); System.out.println ("Max poolsize Limit = " + cpool.getMaxLimit()); System.out.println ("Connection Increment = " + cpool.getConnectionIncrement()); Up until this point, this program is the same as the single-threaded program, AnalyzeOCI ConnPoolSingleThread, which we saw in the previous section. After this, we create the number of threads as specified by the command-line parameter and start them. Thread [] threads = new Thread[ _numOfSessionsToOpen ]; for( int i = 0; i<threads.length; i++ ) { (threads[i] = new WorkerThread( cpool, i )).start(); } Finally, we wait for all threads to finish in a loop. This ends the main() program, which is followed by the definition of the _getNumOfSessionsToOpen() method at the end: // wait until all threads are done. for( int i = 0; i<threads.length; i++ ) { threads[i].join(); } cpool.close(); }// end of main private static int _getNumOfSessionsToOpen( String[] args ) { int numOfSessionsToOpen = 6; //by default open 6 sessions if( args.length == 1 ) { numOfSessionsToOpen = Integer.parseInt( args[0] ); } System.out.println("Total number of sessions to open for " + "scott and benchmark = " + numOfSessionsToOpen); return numOfSessionsToOpen; } private static int _numOfSessionsToOpen; }

We will run the program and then examine the database while the program is running. For some reason, the only reliable results our original query shows are the number of connections and the number of sessions. I modified the query to print out this information (note that the following query works only for the case in which we know that sessions are being independently created on top of connections, as is the case here). The program output is as follows: B:\> java AnalyzeOCIConnPoolMultiThread 12 Total number of sessions to open for scott and benchmark = 12 Min poolsize Limit = 2 Max poolsize Limit = 20 Connection Increment = 1 connecting as scott connecting as benchmark connecting as scott connecting as benchmark connecting as scott connecting as benchmark connecting as scott connecting as benchmark connecting as scott connecting as benchmark connecting as scott connecting as benchmark The program opens six sessions for SCOTT and six more for BENCHMARK. When I ran the modified query and executed it immediately again and again, it gave the following results: sys@ORA10G> select num_of_conns, (conns_plus_sess -num_of_conns) as num_of_seons 2 from 3 ( 4 select count(*) conns_plus_sess, 5 count( distinct p.spid) num_of_conns 6 from v$session s, v$process p 7 where s.type != 'BACKGROUND' 8 and s.username != 'SYS' 9 and p.addr(+) = s.paddr 10 ); NUM_OF_CONNS NUM_OF_SESSIONS ------------ --------------8 12 sys@ORA10G> / NUM_OF_CONNS NUM_OF_SESSIONS ------------ --------------8 12 sys@ORA10G> /

NUM_OF_CONNS NUM_OF_SESSIONS ------------ --------------12 12 <-- after some time --> sys@ORA10G> / NUM_OF_CONNS NUM_OF_SESSIONS ------------ --------------12 10 <-- after some time --> sys@ORA10G> / NUM_OF_CONNS NUM_OF_SESSIONS ------------ --------------0 0 This shows that the program had eight connections at the point I executed the query for the first time and 12 sessions. Ultimately, we had 12 sessions created on top of 12 connections. We can also see a stage where the number of sessions goes down to 10, while the number of connections open remains at 12. This experiment proves that each thread had a separate physical connection, in the case of multithreaded programs using OCI connection pooling.

Summary

In this chapter, you learned the difference between connections and sessions in Oracle. You learned why connection pooling is necessary, and you distinguished between connection pooling and caching. You examined how Oracle9i implements the connection pooling framework and provides a sample connection caching implementation. As you saw, in Oracle 10g, the Oracle9i connection caching has been deprecated and replaced by the more powerful implicit connection caching. Finally, you took a look at how Oracle s OCI connection pooling improves scalability and performance by creating lightweight sessions on top of a low number of physical connections.