Release 1 (v1.0.2.2)
Part Number A95201-01
 Library |
 Solution Area |
 Contents |
 Index |
| Oracle9i Application Server Best Practices Release 1 (v1.0.2.2) Part Number A95201-01 |
|
Pooling mechanisms increase application scalability by allowing many users to access the same application without an exorbitant tax on server resources.
This chapter contains these topics:
This section gives an overview of the different pooling mechanisms provided with Oracle9iAS:
Most Web applications have a much higher number of browsing than active users. Browsing users have accessed a Web page and are either looking at it or working in it (for example, filling in form elements). Active users have clicked on one of the links in the Web page and are waiting for a reply to come back. We often say that these are in a call or submitted a request.
Users spend much more time looking at or working in a page than waiting for the next page to come up, at least under normal load. Pooling takes advantage of this difference to reduce the amount of server-side resources necessary to serve a large user population. This reduction in necessary resources keeps servers in a well-behaved response zone and avoids sharp degradation of response time due to swapping, network contentions, or other server overheads.
A pool is a collection of instances of a resource--such as a database connection, Java object, or server process--that see serial reuse across clients. Instead of having one instance of the resource per client, you create a pool of a small number of instances. You then have each client borrow an instance from the pool when needed and return it when finished.
The resource is typically borrowed at the beginning of a call and returned at the end. Different clients sequentially use the same instance of the resource at different times, but two clients never use the same resource at the same time. When a resource is borrowed, the borrower gets exclusive use of it.
Pooling allows you to:
The concept and benefits are better understood with an example. At any given time a typical e-commerce application may have 10,000 browsing users. Suppose each user spends 10 seconds looking at or working in a page, and each request for the next page takes 100ms on the server side. So each user is active only one percent of the time, or looked at the other way, only one percent of the 10,000 browsing users (= 100 users) are concurrently active. If each request has to go to the database, then only 100 database connections/sessions are needed to service the 10,000 users.
From the server perspective, this is much better than using one connection/session for each user, in which case the database would have to support 10,000 of them at the same time. It is also better than creating and closing a connection at each call, in which case the database would be dominated by the overhead of creating and destroying these sessions. The pool of 100 connections is enough to support 10,000 users, and at the same time it leaves resources available on the server to do other things. Everyone wins.
Pooling is different from sharing. Multiple clients use shared resources at the same time; pooled resources are used by only one client at a time. Shared resources lower server usage but can add contention points. Pooled resources lower server usage but add queuing points. Consider the following two examples:
Multiple client threads run at the same time in the Java VM. The clients can impact each other's performance, for example in the case where one client triggers a long garbage collection that prevents another from allocating new Java objects.
A client being served by a shared server has full use of the process while the request is executed. If all shared servers are in use, then additional clients will be put in a queue until a server becomes available. (Other mechanisms also automatically start new servers up to a maximum number.)
Oracle9iAS provides pools at many levels in the request processing pipeline. At the very front end, Apache uses a pool of processes called HTTPD demons to receive and handle HTTP requests. In the backend, the database (when configured in Microsoft Transaction Server mode) uses a pool of processes called shared servers to handle SQL requests.
In between, the Apache module mod_plsql uses pools of database sessions to handle requests, and mod_perl effectively has a pool of Perl interpreters (one per Apache demon).
When running Java programs, you can pool your access mechanisms to the database. If you are using JDBC from servlets or JavaServer Pages (JSPs), then you can use JDBC Connection Caches. If you are using BC4J, then you can use Application Module pools. In addition, you can tune two internal pooling mechanisms used by the servlet engine: threads and servlets pools.
The rest of this chapter provides details on these built-in pools in Oracle9iAS, and it provides recommendations for building custom pools for your own expensive resources.
The main entry point into Oracle9iAS is the Apache Web server. Apache version 1.x (Oracle9iAS release 1.0.2 is shipping Apache 1.3.12) uses a pool of processes called HTTPD demons to handle the client requests. Apache launches an initial set of processes when it starts up. These processes are used in sequence to handle incoming HTTP requests.
At any given time, each process is handling only one client. When the process is done with that client, it goes back in the queue and waits for a request from another client. If the number of clients waiting exceeds the number of available HTTPD demons, then Apache can automatically create new processes to service them. If the load later goes down, then Apache will kill any extra HTTPD processes.
Control the number of Apache client-request processes.
You can easily control the minimum and maximum numbers of demon processes that Apache uses. Apache starts the minimum number when it comes up. You want to set it to handle your server's normal load.
The maximum number can be used to throttle the Web server, by forcing extra clients to wait a bit instead of saturating the machine with HTTPD processes.
The Oracle JDBC 2.0 driver provides a mechanism called connection caching that effectively implements pools of connections to the database. This pooling mechanism follows the JDBC 2.0 standard APIs. A JDBC connection cache is a collection of JDBC connections to database sessions. All the sessions are in the same database and are authenticated as the same user. A connection cache can be used from servlets, JSPs, or any Java code running in the JDK.
Set up a JDBC connection cache.
A connection cache is represented by an instance of the OracleConnectionCacheImpl class, provided with the JDBC libraries. Your application will usually instantiate this class on startup, keep a reference to the instance in a well-known place, and use it when connections are needed. Full J2EE applications will prefer to bind connection caches within the JNDI namespace, which will avoid having to create them on startup. In that case, the application would just use JNDI APIs to lookup() the connection cache when needed. The JNDI layer will take care of instantiating the connection cache the first time and reuse it on subsequent calls.
Authenticate connections on the fly.
All connections managed by a given connection cache use the same username and password when accessing the database. This is one of the features that make the connections reusable across clients. You can provide the username and password when you create the connection cache instance or pass it on the fly each time you ask the cache for a connection. Some developers see a security risk in passing the username and password when creating the cache, because the password is then held in a global variable in the Java VM where the cache was instantiated. Our examples reflect this concern and pass the information only when requesting a connection.
To use a connection cache from servlets running in the JDK, you first create it in the initialization phase of your application (for example in the init method of your servlet) and then hold it in a Java static variable. To borrow a connection from the cache, you call one of the getConnection() methods of the connection cache. When you are done with the connection, release it by using its close() method. This method has been specialized by the connection cache to put the database connection back in the pool instead of terminating it. Another client will be able to borrow that connection as needed.
The following example shows how to initialize a connection cache and make it accessible from a single servlet:
public class MyServlet extends HttpServlet{ // The connection cache is held in a private static attribute of the servlet. static private OracleConnectionCacheImpl cache = null; // This function initializes the cache if needed and returns it. static OracleConnectionCacheImpl getConnectionCache() throws SQLException { if (cache == null) { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); cache = new OracleConnectionCacheImpl(); cache.setURL("jdbc:oracle:thin:@dlsun57:1521:ORCL2"); cache.setMaxLimit (3); cache.setCacheScheme (OracleConnectionCacheImpl.FIXED_WAIT_SCHEME); } return cache; } // The doGet method of the servlet is what handles the HTTP requests. public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { response.setContentType("text/html"); PrintWriter out = response.getWriter(); out.println ("<html><body>MyServlet<p>Employee List<ul>"); Connection conn = null; try { // Borrow a connection from the cache. conn = getConnectionCache().getConnection("scott", "tiger"); // Use it as a regular JDBC Connection. PreparedStatement stmt = conn.prepareStatement ("select ename from emp"); ResultSet rset = stmt.executeQuery (); while (rset.next()) { out.println ("<li>" + rset.getString (1)); } stmt.close (); out.println ("</body></html>"); // This catch block handles SQL exceptions. } catch (SQLException e) { ... // The finally block ensures that the connection is returned back to the // cache even if an exception is raised. } finally { if (conn != null) { // The close() method returns the connection back to the cache. try { conn.close (); } catch (SQLException e) {} } } } }
Most often, your application uses multiple servlets, and you should share a global connection cache across them all. To achieve this, wrap the connection cache within a singleton class accessed by all servlets, as shown in the following example:
// Separate singleton class to manage the cache public class MyCache { static private OracleConnectionCacheImpl cache = null; public static OracleConnectionCacheImpl getConnectionCache() throws SQLException { if (cache == null) { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); cache = new OracleConnectionCacheImpl(); cache.setURL("jdbc:oracle:thin:@dlsun57:1521:ORCL2"); cache.setMaxLimit (3); cache.setCacheScheme (OracleConnectionCacheImpl.FIXED_WAIT_SCHEME); } return cache; } } // Any servlet of the application can borrow a connection from the cache. public class MyServlet2 extends HttpServlet { public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ... try { // Get a connection from the cache conn = MyCache.getConnectionCache().getConnection("scott", "tiger"); ... } finally { if (conn != null) { try { conn.close (); } catch (SQLException e) {} } } } }
Commit or roll back database updates before returning a connection to the cache.
We recommend that you borrow a JDBC connection at the beginning of a call and return it at the end of the call. You need to make sure that you have committed or rolled back all updates to the database before returning the connection to the cache. If you do not, then the next thread that borrows the connection may see the non-committed changes of the first thread, which would be wrong. A best practice is to include a call to the rollback() method in the finally block that returns the connection to the cache:
try { // Get a connection from the cache. conn = MyCache.getConnectionCache().getConnection("scott", "tiger"); ... } finally { if (conn != null) { // The rollback ensures that all updates are reverted before returning // the connection to the cache. try { conn.rollback (); } catch (SQLException e) {} try { conn.close (); } catch (SQLException e) {} }
Pass connections across linked servlets.
In a Servlets 2.2 compliant container you can call a servlet from another servlet using a HttpRequestDispatcher object. If the servlets in the chain all have to access the database, then they should share the same JDBC connection. An easy way to achieve that is to have the first servlet borrow a connection from the cache and store it in an attribute of the HttpRequest that gets passed to the other servlets in the chain. Do not forget to release the connection in a finally block in your first servlet:
try { // Get a connection from the cache. conn = MyCache.getConnectionCache().getConnection("scott", "tiger"); // Hold it in the HttpRequest object. request.setAttribute ("myConnection", conn); ... // Call otherServlet that will get the connection from the HttpRequest. request.getRequestDispatcher("otherServlet").include(request, response); ... } finally { if (conn != null) { // The rollback ensures that all updates are reverted before returning // the connection to the cache. try { conn.rollback (); } catch (SQLException e) {} try { conn.close (); } catch (SQLException e) {} }
If you do not always chain the servlets in the same order, then it may be a good idea to check if a connection is already available in the HttpRequest before borrowing one from the cache. The borrowing code would then look like the following:
boolean borrowed = false; conn = (Connection)request.getAttribute("myConnection"); try { // Is there a connection in the HttpRequest? if (conn == null) { conn = MyCache.getConnectionCache().getConnection("scott", "tiger"); request.setAttribute("myConnection", conn); borrowed = true; } ... } finally { if (conn != null && borrowed) { // Only return the connection if we borrowed it ourselves. try { conn.rollback (); } catch (SQLException e) {} try { conn.close (); } catch (SQLException e) {} } }
You will not usually hold borrowed connections across calls, because this would defeat the purpose of the cache. Your cache would have to be larger than necessary. In the worst case you would end up with one connection per client, which renders the cache useless.
One example where it would be necessary to hold a connection is a transaction that spans multiple calls. Suppose the first call locks a row with a SELECT FOR UPDATE, and the next call modifies it with an UPDATE statement before committing the transaction. Most applications avoid these situations, but they are still sometimes necessary. You can hold a connection by storing it in the HttpSession associated with your client:
// Holding a connection across calls public class MyServlet3 extends HttpServlet { public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ... if (<locking-a-row>) { // Get a connection from the cache HttpSession session = request.getSession (true); conn = session.getAttribute ("connection"); if (conn == null) conn = MyCache.getConnectionCache().getConnection("scott", "tiger"); session.setAttribute ("connection", conn); <lock-the-row> ... } finally { // The connection is not returned to the cache here if (stmt != null) stmt.close (); } else if (<updating-locked-row>) { HttpSession session = request.getSession (true); conn = session.getAttribute ("connection"); <update-the-row> session.removeAttribute ("connection"); } finally { // Here we do return the connection to the cache. if (conn != null) conn.close(); } }
If you hold connections in the HttpSession, then you have to ensure that you eventually return the borrowed connections. The most common mistake is to let the session time out without returning the connection. You can avoid this with the HttpSessionBindingListener mechanism, which lets you bind objects in the session and have these objects notified (execute some code) when they get bound or unbound. See the documentation of the HttpSession class for more information.
Size the connection cache with min and max parameters.
If you use a connection cache, then you will have to tune the min and max parameters, which control the size of the cache. You will also have to choose one of several policies for when you run out of connections in the cache. Most often you will use the FIXED_WAIT_SCHEME policy, which causes additional clients to wait when the pool is exhausted.
The min parameter controls the warm-up time of your application. The connection cache will create min connections when it is constructed. A large min will slow down the startup of your application. But after startup the application will be responsive to a high load, because connections will be available immediately.
If min is small, then the application will start up faster. But the initial users will experience delays until the connection cache is filled with enough connections. You should set min to a low value (one, for example) during development and to a higher value in production.
The max parameter provides a throttle on the database load that your application creates. If the database is dedicated to the application, then you will want to configure the cache to just saturate the database when all the connections are used. You will have to do experiments to find the saturation point.
Use a test harness that simulates many clients hitting your application without any think time between requests. That is, each client executes request after request without any delay between them. As the number of clients increases, you will notice degradation in the response time. At some point you will decide that the response time is too high: you will have reached the scalability limit of your system.
The number of clients at this point is your system's maximum number of database connections. If you have only one Java VM, then it is also the max size of the connection cache that you can use. But if you deploy your application on multiple Java VMs, then you will have to divide this number by the number of VMs: each VM will have a fraction of the total number of database connections.
Suppose your nominal response time is 100ms, and it degrades with the number of clients to a maximum-tolerable response time of 2s at 200 clients. In this case, you need a total of 200 maximum connections to your database. If you deploy your application on five Java VMs, then you need a connection cache max size of 40 in each VM (40x5=200).
If the database is shared with other applications, then you can follow a similar technique to measure the correct max value. However, instead of increasing the number of clients until the application reaches its scalability limit, increase the number of clients until the database reaches the load that you choose to allocate for your application. For example, if you decide that your application will use only 50% of the database resources, then the max size of your connection cache will be the number of clients when the database usage reaches 50%.
Monitor cache usage to tune its performance.
The previous best practice explains how to estimate the min and max parameters. To ensure that your application performs optimally, you will have to monitor the usage of the cache and tune these parameters. You may have decided that a min size of ten was appropriate, only to find that you never use more than three connections from the pool. Or you may have set a max of 30, only to find that the application becomes too sluggish long before all the connections are used.
To help you tune the cache, you should report information on the cache usage to your servlet log files. You can use the getActiveSize() and getCacheSize() methods of the connection cache for this. Active size will tell you how many connections are actually used at a given time. Cache size will tell you how many connections have been opened, which reflects the peak size of your cache.
Set your min parameter below the average active size and your max parameter somewhere between the active and peak size.
public class MyCache { ... public static OracleConnectionCacheImpl getConnectionCache(HttpServlet servlet) throws SQLException { if (ods == null) { ... } // This log call will report cache usage information in the servlet engine // log file. servlet.log ("Connection Cache: " + ods.getCacheSize() + " connections, " + ods.getActiveSize() + " active."); return ods; } }
You will often have multiple applications running together in multiple Java VMs. If you want tight control over database resources usage, then you should have one connection cache for each application and Java VM. This enables you to size the caches independently for each application.
If you use multithreaded servlets, then you should be careful not to keep references to the borrowed connection in one of the servlet attributes. This would risk having the same connection used by two calls at the same time, which would just not work. You should keep references to borrowed connections only in local variables, or pass them as arguments to the other Java objects called by the servlet code. In general it is easier and wiser to write single-threaded servlets.
public class MyServlet extends HttpServlet { // Do not use a servlet attribute to hold the connection. Connection myConn; public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { // The code below will be problematic for multithreaded servlets. // Do not do that. myConn = getConnectionCache().getConnection("scott", "tiger");
Use SQLJ to reduce database access code.
SQLJ is a pre-processor that greatly reduces the amount of code that you write to access the database from Java. SQLJ can easily take advantage of connection caches. You only have to instantiate the SQLJ DefaultContext from a connection obtained from the connection cache, as described in the following code sample:
import sqlj.runtime.ref.DefaultContext; public class MyServlet extends HttpServlet { // This SQLJ statement defines an iterator for EMP names. #sql public static iterator EmpIter (String ename); public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ... // In SQLJ a JDBC connection is represented by a DefaultContext object. DefaultContext ctx = null; try { // The DefaultContext is initialized from a connection borrowed from the // cache. Connection conn = getConnectionCache().getConnection("scott", "tiger"); ctx = new DefaultContext(conn); EmpIter iter = null; #sql [ctx] iter = { select ename from emp }; while (iter.next()) { out.println ("<li>" + iter.ename()); } ... } catch (SQLException e) { ... // The finally block ensures that the connection is returned to the cache // by closing the DefaultContext. } finally { if (ctx != null) { try { ctx.close (); } catch (SQLException e) {} } }
Use JDBC statement cache.
In addition to the connection cache, Oracle JDBC drivers provide a statement cache. This mechanism enables you to hold prepared statements in JDBC connections and avoid parsing the statements at every call. In Oracle8i release 8.1.7.1 and in Oracle9i this feature can be combined with connections borrowed from a connection cache. (The Oracle8i release 8.1.7 JDBC drivers have a bug that prevents caching statements for cached connections.) We recommend that you use the statement cache together with the connection cache.
The JDBC OCI driver provides a mechanism called connection pools, which provides a different functionality than the JDBC connection cache. Where the JDBC connection cache mechanism is pooling connection/session pairs, the JDBC OCI mechanism is pooling only network connections; it still uses a large number of database sessions.
If you were to use the JDBC OCI connection pools mechanism in our example of 10,000 users using a pool of 100 database connections, then you would end up using 100 network connections to access 10,000 database sessions. The mechanism enables you to lower the number of network connections, but not the number of database sessions.
Usually, you want to reduce both connections and sessions. Reducing only the number of network connections is useful if you need to use one database session for each client but cannot afford the network connection overhead. In other words, the kind of pooling provided by the JDBC OCI connection pools is useful if you want each database session to be authenticated differently.
Oracle provides other mechanisms to reduce network connections (such as the Oracle Connection Manager--CMAN), and you can also reduce the footprint of sessions in the database by configuring the server in shared server mode. The JDBC OCI connection pools mechanism is just another way of achieving the savings provided by the combination of CMAN and shared server mode.
Some Oracle9iAS applications access the database with Oracle Business Components for Java (BC4J) instead of JDBC or SQLJ. With BC4J you can define Java classes that represent the contents of tables or the results of a query, thereby creating an object-oriented view of the database. The BC4J runtime takes care of calling the database through JDBC, generating all the SQL statements necessary, and opening and closing connections as needed. The main BC4J object that represents a connection to the database is called an application module. In addition to the database connection, the application module manages a cache of Java objects materialized from database tables.
Configure BC4J to use pools of application modules.
When running in a multithreaded server (a servlet engine, for example), the BC4J runtime can be configured to use a pool of application module objects, similar in effect to a JDBC connection cache. You will typically create a pool of application modules in the initialization phase of your program, then borrow and return modules to the pool on call boundaries.
The following example shows how a servlet would borrow application modules.
public class MyServlet extends HttpServlet { public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { ... // In BC4J a pool is called ApplicationPool. ApplicationPool pool = null; EmpModuleImpl am = null; try { // Here we get access to the pool from a singleton class MyCache. pool = MyCache.getPool(); // And we check-out one application module from the pool. am = (EmpModuleImpl)pool.checkout(); // We use the application module as we would usually do. EmpNamesImpl vo = am.getEmpNames (); vo.executeQuery(); while (vo.hasNext ()) { EmpNamesRowImpl row = (EmpNamesRowImpl)vo.next (); String name = row.getEname(); out.println ("<li> " + name); } } catch (Exception e) { ... // The finally block ensures that we return the application module to the // pool when we are done. } finally { if (am != null) pool.checkin (am); } } }
Just as in the case of the JDBC connection cache, we use a wrapper class to encapsulate the initialization and creation of the pool of application modules.
import oracle.jbo.*; import oracle.jbo.common.ampool.*; public class MyCache { public static ApplicationPool getPool() throws Exception { // Ask the BC4J runtime if it knows about the pool. ApplicationPool pool = PoolMgr.getInstance().getPool("EmpAMPool"); if (pool == null) { // If not create the pool here. Hashtable env = new Hashtable (10); env.put (Context.INITIAL_CONTEXT_FACTORY, JboContext.JBO_CONTEXT_FACTORY); env.put (JboContext.DEPLOY_PLATFORM, JboContext.PLATFORM_LOCAL); PoolMgr.getInstance().createPool("EmpAMPool", "pools.bc4j.emp.EmpModule" , "jdbc:oracle:thin:scott/tiger@localhost:1521:orcl2", env); pool = PoolMgr.getInstance().getPool("EmpAMPool"); } return pool; } }
The servlet engine is a Java program that receives requests from Apache and handles them by invoking servlets. The servlet engine can be configured to use pools of Java threads and pools of Java servlet instances to serve the application better. Configuring these pools is not easy, and the procedure varies depending on whether you are using single-threaded or multithreaded servlets.
By definition, a single-threaded servlet implements the SingleThreadModel interface. A servlet that does not implement this interface is a multithreaded servlet. Single-threaded servlets will be in only one thread (or call) at a time, so the Java instances of a given single-threaded servlet class constitute a pool of resources used by the servlet engine.
The number of instances determines the number of clients that will be able to execute the servlet concurrently. If clients want to execute the servlet, but no instances are available, then they will wait for one of the other clients to be done. You can control the min and max number of instances used by the servlet engine for single-threaded servlets.
Multithreaded servlets require only one instance. The number of clients executing the servlet at the same time is controlled by the size of the thread pool used by the engine.
The Oracle HTTP Server Performance Guide, in the Oracle9i Application Server Platform-specific Documentation, explains how to tune the thread pool and the servlet instance pool. In general you can always start with the same size for the two pools and tune from there. Be aware that you will have to measure the performance of your application under different loads to do any tuning at all. Trying to tune without any performance measurement is a waste of time and resources.
Do not pool resources by holding them in servlet attributes.
When you decide to use SingleThreadModel servlets, it is tempting to employ the pool of servlet instances for pooling. The servlet engine will serially reuse instances of your servlet class. So if you hold reusable resources in the servlets attributes, then these resources will automatically be serially reused with the servlets. Typically, you might be tempted to allocate one JDBC connection for each servlet instance, holding it in an attribute of the servlet. As the servlet instances get serially reused across clients, so will the JDBC connections. You can also be tempted to hold prepared statements or other objects that are expensive to create.
You should resist these temptations, however, because this kind of pooling is often shortsighted. As your application grows it will use more and more different servlet classes, and the servlet engine will have pools of instances for each of them. If all these servlets require a JDBC connection, then you will end up using more connections than you would have used with a regular JDBC connection cache.
For example, if you have 10 different servlet classes and configure the servlet engine for 10 instances, then you will end up with 100 JDBC connections. Furthermore the connections will not be passed across servlets or JavaServer Pages as described earlier. For these reasons we do not recommend that you pool resources by holding them in servlet attributes.
// Wrongly storing a JDBC connection in a servlet instance attribute public class MyServlet extends HttpServlet implements SingleThreadModel { // Instance variables private Connection dbConn; // Will wrongly be used to hold connection public void init(ServletConfig config) throws ServletException { // Create a connection and hold it in a private attribute. dbConn = DriverManager.getConnection ("jdbc:oracle:oci:..."); } public void doGet (HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { try { // Use the connection from the private attribute. PreparedStatement stmt = dbConn.prepareStatement ("select ename from emp"); ... } finally { // Here you do not close the connection, because you keep it in an attribute. } } }
Developers often prefer JSPs to servlets, especially for pages that do not have much dynamic content. You can access JDBC connection caches from JSPs by using an application-scoped instance of the predefined Oracle ConnBean class. This bean is a wrapper for a JDBC connection cache and will let you easily borrow and return connections from a JSP.
Use application-scoped ConnBeans with JSPs.
Note that the example provided with the Oracle JSP 1.1 installation is using a session-scoped ConnBean, which defeats the purpose of the cache. If you store the ConnBean in the session scope, then you will have one connection pool for each HttpSession object. This means that the connections will not be reused across clients. To reuse the connections across all clients you must have an application-scoped ConnBean bean.
Here is the correct code sample:
<%@ page import="java.sql.* , oracle.jsp.dbutil.*" %> <!-- This creates an application scoped connection bean and initializes it. --!> <jsp:useBean id="cbean" class="oracle.jsp.dbutil.ConnBean" scope="application"> <jsp:setProperty name="cbean" property="User" value="scott"/> <jsp:setProperty name="cbean" property="Password" value="tiger"/> <jsp:setProperty name="cbean" property="URL" value="jdbc:oracle:thin:@dlsun57:1521:orcl2"/> <jsp:setProperty name="cbean" property="PreFetch" value="5"/> <jsp:setProperty name="cbean" property="StmtCacheSize" value="2"/> </jsp:useBean> <html><body> MyJSP<p> Employee List <% try { // Make the connection. cbean.connect(); String sql = "SELECT ename, sal FROM scott.emp ORDER BY ename"; // Get a cursor bean. CursorBean cb = cbean.getCursorBean (CursorBean.PREP_STMT, sql); out.println (cb.getResultAsHTMLTable()); // Close the cursor bean. cb.close(); } catch (SQLException e) { out.println ("<pre>" + e + "</pre>"); // The finally block returns the connection to the cache by closing the // connection bean. } finally { // Close the bean to close the connection. cbean.close(); } %> </body></html>
As in the case of servlets, you would likely have several JSPs using the same connection cache. In that case you would not directly initialize the connection bean from within any of the JSPs. You would instead implement your own singleton class as a wrapper of the connection bean, as we did for the servlets examples. Each JSP would borrow connections from that class.
Do not use single-threaded JSPs.
JSPs are compiled to servlets by the JSP compiler, which by default generates multithreaded servlets. At runtime there will be only one Java instance of the servlets, and multiple clients will be executing it in different threads. The number of clients simultaneously executing the JSP is controlled by the thread pool parameters of the servlet engine.
You have the option to generate a single-threaded servlet from a JSP. But the Oracle JSP engine does not manage pools of single-threaded instances; it only ever creates one instance of a single-threaded JSP. Therefore, all clients will be serialized when accessing a single-threaded JSP. So we do not recommend single-threaded JSPs.
This may seem at odds with our recommendation that you use single-threaded servlets. Why do we not also recommend single-threaded JSPs? In general, we prefer single-threaded servlets because it is often difficult to write servlets that function well in a multithreaded environment. With JSPs it is much more difficult to write a non-thread safe page, so it is OK always to use a multithreaded model.
Perl programs run in memory with the Apache HTTPD demons. This means that there is effectively a pool of Perl interpreters that are serially reused across clients, as the HTTPD demons are.
Pool database connections from Perl programs.
The global variables of Perl interpreters survive calls and can be used by different clients. You will be able to pool database connections from Perl programs by simply keeping a reference to the database connection in a Perl global variable. You will end up with a pool of the same size as the pool of HTTPD demons.
The mod_plsql module delegates the handling of HTTP requests to the backend database. URLs contain the names of PL/SQL programs to execute in response to a request. Module mod_plsql uses one database connection/session for each Apache HTTPD demon. Because the HTTPD demons are serially reused across clients, so are the sessions used by mod_plsql. If you configure Apache with a maximum of 100 HTTPD demons, then you will use a maximum of 100 database connections to execute PL/SQL.
Apache will quickly start new HTTPD demons when the HTTP load goes up, which will result in corresponding database sessions also starting quickly if the requests are handled by mod_plsql. To make the session start up faster and control the load on the backend database, we recommend that you configure it in shared server mode. In shared server mode, the creation of a new session does not require the creation of a new process or thread on the backend server.
So far we have described Oracle9iAS pooling support at the Apache, Java, PL/SQL, and Perl levels. One additional important pooling mechanism is the database shared server mode, which provides pools of database server processes that are serially reused across clients. Each client still has a database session and a connection to the server, but the session is only a piece of shared memory instead of a full-fledged server process (on Windows machines server processes are actually OS threads, while on Unix machines they are processes).
Configure the database in shared server mode.
When you configure the database in shared server mode, you can decide to pre-spawn processes and have the database automatically start new processes up to a maximum number. You end up with fewer database processes than in the default dedicated server mode, but you have to allocate more memory to the shared pool in the init.ora parameters. Re-using processes across clients increases database scalability and makes session creation cheaper and faster.
So far we have described the built-in pooling mechanisms in Oracle9iAS. Advanced programmers may also be interested in implementing pools for the expensive resources used by their applications. For example, if your application accesses a legacy system through some proprietary network protocol, then you may want to define a pool of connections to this system that you will serially reuse across clients. The important points to consider when pooling resources are the following:
You should pool resources only if they do not keep per-client residual state after being returned to the pool.
It is worthwhile to pool your own resources only if they are indeed reusable across different clients. For example, resources that require client-specific authentication (that is, a distinct login for different clients) are not good candidates for pooling.
A pool is beneficial if the resources are expensive to create or initialize and if the cost can be amortized across many reuses. There will be little benefit from a pool of inexpensive Java objects, such as small footprint Java Beans that perform auxiliary calculations for JSPs. Indeed, the pooling overhead may be more expensive than recreating and dropping these beans at each call. Only measurements of your particular application can tell you if a pool is beneficial or not.
Pooling is intended to provide serial access to a resource, and during access the resource is fully owned by the calling program. This is to be contrasted with sharing, where multiple calling programs or threads access the same resource at the same time. Some objects are better shared than pooled. For example, pricing rules for an e-business site should be a cache of sharable data in memory, rather than a pooled resource.
If you decide to pool your own resources, then it is quite easy to write pooling code similar to what the JDBC connection cache provides. If you are accessing the resource from a JSP, then it is a good idea to gain access to the resource from a page-scoped Bean and release the resource at the end of the page.
|
 Copyright © 2001 Oracle Corporation. All Rights Reserved. |
|
