Use of JDBC Performance Enhancement Features

Nel documento Oracle9AS Containers for J2EE (pagine 114-117)

EJB Calls from JSP Pages

JSP Support for Oracle SQLJ

Oracle XML Support

Use of JDBC Performance Enhancement Features

You can use the following performance enhancement features, supported through Oracle JDBC extensions, in JSP applications in OC4J:

caching database connections

caching JDBC statements

batching update statements

prefetching rows during a query

caching rowsets

Most of these performance features are supported by the ConnBean and ConnCacheBean data-access JavaBeans (but not by DBBean). These beans are described in the Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference.

Database Connection Caching

Creating a new database connection is an expensive operation that you should avoid whenever possible. Instead, use a cache of database connections. A JSP application can get a logical connection from a pre-existing pool of physical connections, and return the connection to the pool when done.

You can create a connection pool at any one of the four JSP scopes—application, session, page, or request. It is most efficient to use the maximum possible

Note: For information about additional OC4J JSP data-access features—portable JavaBeans and tags for SQL functionality—see the Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference.

JSP Data-Access Considerations and Features

scope—application scope if that is permitted by the Web server, or session scope if not.

The Oracle JDBC connection caching scheme, built upon standard connection pooling as specified in the JDBC 2.0 standard extensions, is implemented in the ConnCacheBean data-access JavaBean provided with OC4J. Alternatively, you can use standard data source connection pooling functionality, which is supported by the ConnBean data-access JavaBean. These beans are described in the Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference.

It is also possible to use the Oracle JDBC OracleConnectionCacheImpl class directly, as though it were a JavaBean, as in the following example (although all OracleConnectionCacheImpl functionality is available through

ConnCacheBean):

<jsp:useBean id="occi" class="oracle.jdbc.pool.OracleConnectionCacheImpl"

scope="session" />

The same properties are available in OracleConnectionCacheImpl as in ConnCacheBean. They can be set either through jsp:setProperty tags or directly through the class setter methods.

Refer to the OC4J demos for examples of using OracleConnectionCacheImpl directly. For information about the Oracle JDBC connection caching scheme and the OracleConnectionCacheImpl class, see the Oracle9i JDBC Developer’s Guide and Reference.

JDBC Statement Caching

Statement caching, an Oracle JDBC extension, improves performance by caching executable statements that are used repeatedly within a single physical connection, such as in a loop or in a method that is called repeatedly. When a statement is cached, the statement does not have to be re-parsed, the statement object does not have to be recreated, and parameter size definitions do not have to be recalculated each time the statement is executed.

The Oracle JDBC statement caching scheme is implemented in the ConnBean and ConnCacheBean data-access JavaBeans that are provided with OC4J. Each of these beans has a stmtCacheSize property that can be set through a

jsp:setProperty tag or the bean setStmtCacheSize() method. The beans are described in the Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities

Reference.

Statement caching is also available directly through the Oracle JDBC OracleConnection and OracleConnectionCacheImpl classes. For

information about the Oracle JDBC statement caching scheme and the OracleConnection and OracleConnectionCacheImpl classes, see the Oracle9i JDBC Developer’s Guide and Reference.

Update Batching

The Oracle JDBC update batching feature associates a batch value (limit) with each prepared statement object. With update batching, instead of the JDBC driver executing a prepared statement each time its "execute" method is called, the driver adds the statement to a batch of accumulated execution requests. The driver will pass all the operations to the database for execution once the batch value is reached.

For example, if the batch value is 10, then each batch of ten operations will be sent to the database and processed in one trip.

OC4J supports Oracle JDBC update batching directly, through the executeBatch property of the ConnBean data-access JavaBean. You can set this property through a jsp:setProperty tag or through the setter method of the bean. If you use ConnCacheBean instead, you can enable update batching through Oracle JDBC functionality in the connection and statement objects you create. These beans are described in the Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference.

For more information about Oracle JDBC update batching, see the Oracle9i JDBC Developer’s Guide and Reference.

Row Prefetching

The Oracle JDBC row prefetching feature allows you to set the number of rows to prefetch into the client during each trip to the database while a result set is being populated during a query, reducing the number of round-trips to the server.

OC4J supports Oracle JDBC row prefetching directly, through the preFetch property of the ConnBean data-access JavaBean. You can set this property through a jsp:setProperty tag or through the setter method of the bean. If you use ConnCacheBean instead, you can enable row prefetching through Oracle JDBC functionality in the connection and statement objects you create. These beans are

Important: Statements can be cached only within a single physical connection. When you enable statement caching for a connection cache, statements can be cached across multiple logical connection objects from a single pooled connection object, but not across multiple pooled connection objects.

JSP Data-Access Considerations and Features

described in the Oracle9iAS Containers for J2EE JSP Tag Libraries and Utilities Reference.

For more information about Oracle JDBC row prefetching, see the Oracle9i JDBC Developer’s Guide and Reference.

Rowset Caching

A cached rowset provides a disconnected, serializable, and scrollable container for retrieved data. This feature is useful for small sets of data that do not change often, particularly when the client requires frequent or continued access to the

information. By contrast, using a normal result set requires the underlying connection and other resources to be held. Be aware, however, that large cached rowsets consume a lot of memory on the client.

In Oracle9i, Oracle JDBC provides a cached rowset implementation. If you are using an Oracle JDBC driver, use code inside a JSP page to create and populate a cached rowset, as follows:

CachedRowSet crs = new CachedRowSet();

crs.populate(rset); // rset is a previously created JDBC ResultSet object.

Once the rowset is populated, the connection and statement objects used in obtaining the original result set can be closed.

For more information about Oracle JDBC cached rowsets, see the Oracle9i JDBC Developer’s Guide and Reference.

Nel documento Oracle9AS Containers for J2EE (pagine 114-117)