PK
\Eoa, mimetypeapplication/epub+zipPK
\E iTunesMetadata.plisti
This chapter describes how to design and configure WebLogic Server to get the best performance from JDBC applications:
WebLogic has several features that enhance performance for JDBC applications.
Establishing a JDBC connection with a DBMS can be very slow. If your application requires database connections that are repeatedly opened and closed, this can become a significant performance issue. Connection pools in WebLogic data sources offer an efficient solution to this problem.
When WebLogic Server starts, connections in the data sources are opened and are available to all clients. When a client closes a connection from a data source, the connection is returned to the pool and becomes available for other clients; the connection itself is not closed. There is little cost to opening and closing pooled connections.
Most performance gains or losses in a database application is not determined by the application language, but by how the application is designed. The number and location of clients, size and structure of DBMS tables and indexes, and the number and types of queries all affect application performance.
The following are general hints that apply to all DBMSs. It is also important to be familiar with the performance documentation of the specific DBMS that you use in your application.
Most serious performance problems in DBMS applications come from moving raw data around needlessly, whether it is across the network or just in and out of cache in the DBMS. A good method for minimizing this waste is to put your logic where the data is—in the DBMS, not in the client —even if the client is running on the same box as the DBMS. In fact, for some DBMSs a fat client and a fat DBMS sharing one CPU is a performance disaster.
Most DBMSs provide stored procedures, an ideal tool for putting your logic where your data is. There is a significant difference in performance between a client that calls a stored procedure to update 10 rows, and another client that fetches those rows, alters them, and sends update statements to save the changes to the DBMS.
Also review the DBMS documentation on managing cache memory in the DBMS. Some DBMSs (Sybase, for example) provide the means to partition the virtual memory allotted to the DBMS, and to guarantee certain objects exclusive use of some fixed areas of cache. This means that an important table or index can be read once from disk and remain available to all clients without having to access the disk again.
SQL is a set processing language. DBMSs are designed from the ground up to do set-based processing. Accessing a database one row at a time is, without exception, slower than set-based processing and, on some DBMSs is poorly implemented. For example, it will always be faster to update each of four tables one at a time for all the 100 employees represented in the tables than to alter each table 100 times, once for each employee.
Many complicated processes that were originally thought too complex to do any other way but row-at-a-time have been rewritten using set-based processing, resulting in improved performance. For example, a major payroll application was converted from a huge slow COBOL application to four stored procedures running in series, and what took hours on a multi-CPU machine now takes fifteen minutes with many fewer resources used.
Frequently customers ask how to tell how many rows will be coming back in a given result set. The only way to find out without fetching all the rows is by issuing the same query using the count keyword:
SELECT count(*) from myTable, yourTable where ...
This returns the number of rows the original query would have returned, assuming no change in relevant data. The actual count may change when the query is issued if other DBMS activity has occurred that alters the relevant data.
Be aware, however, that this is a resource-intensive operation. Depending on the original query, the DBMS may perform nearly as much work to count the rows as it will to send them.
Make your application queries as specific as possible about what data it actually wants. For example, tailor your application to select into temporary tables, returning only the count, and then sending a refined second query to return only a subset of the rows in the temporary table.
Learning to select only the data you really want at the client is crucial. Some applications ported from ISAM (a pre-relational database architecture) will unnecessarily send a query selecting all the rows in a table when only the first few rows are required. Some applications use a 'sort by' clause to get the rows they want to come back first. Database queries like this cause unnecessary degradation of performance.
Proper use of SQL can avoid these performance problems. For example, if you only want data about the top three earners on the payroll, the proper way to make this query is with a correlated subquery. Table 4-1 shows the entire table returned by the SQL statement
select * from payroll
Table 4-1 Full Results Returned
Name | Salary |
---|---|
Joe |
10 |
Mike |
20 |
Sam |
30 |
Tom |
40 |
Jan |
50 |
Ann |
60 |
Sue |
70 |
Hal |
80 |
May |
80 |
A correlated subquery
select p.name, p.salary from payroll p where 3 >= (select count(*) from payroll pp where pp.salary >= p.salary);
returns a much smaller result, shown in Table 4-2.
This query returns only three rows, with the name and salary of the top three earners. It scans through the payroll table, and for every row, it goes through the whole payroll table again in an inner loop to see how many salaries are higher than the current row of the outer scan. This may look complicated, but DBMSs are designed to use SQL efficiently for this type of operation.
Whenever possible, collect a set of data operations and submit an update transaction in one statement in the form:
BEGIN TRANSACTION UPDATE TABLE1... INSERT INTO TABLE2 DELETE TABLE3 COMMIT
This approach results in better performance than using separate statements and commits. Even with conditional logic and temporary tables in the batch, it is preferable because the DBMS obtains all the locks necessary on the various rows and tables, and uses and releases them in one step. Using separate statements and commits results in many more client-to-DBMS transmissions and holds the locks in the DBMS for much longer. These locks will block out other clients from accessing this data, and, depending on whether different updates can alter tables in different orders, may cause deadlocks.
Caution: If any individual statement in the preceding transaction fails, due, for instance, to violating a unique key constraint, you should put in conditional SQL logic to detect statement failure and to roll back the transaction rather than commit. If, in the preceding example, the insert failed, most DBMSs return an error message about the failed insert, but behave as if you got the message between the second and third statement, and decided to commit anyway! Microsoft SQL Server offers a connection option enabled by executing the SQL set xact_abort on
, which automatically rolls back the transaction if any statement fails.
If an application sends a 'BEGIN TRAN'
and some SQL that locks rows or tables for an update, do not write your application so that it must wait on the user to press a key before committing the transaction. That user may go to lunch first and lock up a whole DBMS table until the user returns.
If you require user input to form or complete a transaction, use optimistic locking. Briefly, optimistic locking employs timestamps and triggers in queries and updates. Queries select data with timestamp values and prepare a transaction based on that data, without locking the data in a transaction.
When an update transaction is finally defined by the user input, it is sent as a single submission that includes time-stamped safeguards to make sure the data is the same as originally fetched. A successful transaction automatically updates the relevant timestamps for changed data. If an interceding update from another client has altered data on which the current transaction is based, the timestamps change, and the current transaction is rejected. Most of the time, no relevant data has been changed so transactions usually succeed. When a transaction fails, the application can refetch the updated data to present to the user to reform the transaction if desired.
Changing a data row in place is much faster than moving a row, which may be required if the update requires more space than the table design can accommodate. If you design your rows to have the space they need initially, updates will be faster, although the table may require more disk space. Because disk space is cheap, using a little more of it can be a worthwhile investment to improve performance.
Some applications store operational data in the same table as historical data. Over time and with accumulation of this historical data, all operational queries have to read through lots of useless (on a day-to-day basis) data to get to the more current data. Move non-current data to other tables and do joins to these tables for the rarer historical queries. If this can't be done, index and cluster your table so that the most frequently used data is logically and physically localized.
DBMSs are designed to work best when very busy with lots of different things to do. The worst way to use a DBMS is as dumb file storage for one big single-threaded application. If you can design your application and data to support lots of parallel processes working on easily distinguished subsets of the work, your application will be much faster. If there are multiple steps to processing, try to design your application so that subsequent steps can start working on the portion of data that any prior process has finished, instead of having to wait until the prior process is complete. This may not always be possible, but you can dramatically improve performance by designing your program with this in mind.
This chapter describes how you use the WebLogic Server Administration Console to enable, configure, and monitor features of WebLogic Server, including JDBC data sources and multi data sources. You can do the same tasks programmatically using the JMX API and the WebLogic Scripting Tool (WLST). After configuring JDBC connectivity components, you can use them in your applications.
The following sections describe how to use the JDBC connectivity in your applications.
For more information about configuring JDBC data sources and multi data sources, see Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.
The following sections provide details about requesting a database connection from a DataSource object - either a data source or a multi data source:
To use the DataSource objects in your applications, import the following classes in your client code:
import java.sql.*; import java.util.*; import javax.naming.*;
To obtain a connection for a JDBC client, use a Java Naming and Directory Interface (JNDI) lookup to locate the DataSource object, as shown in this code fragment.
Note: When using a JDBC connection in a client-side application, the exact same JDBC driver classes must be in the |
Context ctx = null; Hashtable ht = new Hashtable(); ht.put(Context.INITIAL_CONTEXT_FACTORY, "weblogic.jndi.WLInitialContextFactory"); ht.put(Context.PROVIDER_URL, "t3://hostname:port"); Connection conn = null; Statement stmt = null; ResultSet rs = null; try { ctx = new InitialContext(ht); javax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup ("myDataSource"); conn = ds.getConnection(); // You can now use the conn object to create // Statements and retrieve result sets: stmt = conn.createStatement(); stmt.execute("select * from someTable"); rs = stmt.getResultSet(); ... //Close JDBC objects as soon as possible stmt.close(); stmt=null; conn.close(); conn=null; } catch (Exception e) { // a failure occurred log message; } finally { try { ctx.close(); } catch (Exception e) { log message; } try { if (rs != null) rs.close(); } catch (Exception e) { log message; } try { if (stmt != null) stmt.close(); } catch (Exception e) { log message; } try { if (conn != null) conn.close(); } catch (Exception e) { log message; } }
(Substitute the correct hostname and port number for your WebLogic Server.)
Note: The code above uses one of several available procedures for obtaining a JNDI context. For more information on JNDI, see Programming JNDI for Oracle WebLogic Server. |
The weblogic.jdbc.extensions package includes the following exceptions that can be thrown when an application request fails. Each exception extends java.sql.SQLException
.
ConnectionDeadSQLException
—generated when an application request to get a connection fails because the connection test on the reserved connection failed. This typically happens when the database server is unavailable.
ConnectionUnavailableSQLException
—generated when an application request to get a connection fails because there are currently no connections available in the pool to be allocated. This is a transient failure, and is generated if all connections in the pool are currently in use. It can also be thrown when connections are unavailable because they are being tested.
PoolDisabledSQLException
—generated when an application request to get a connection fails because the JDBC Data Source has been administratively disabled.
PoolLimitSQLException
—generated when an application request to get a connection fails due to a configured threshold of the data source, such as HighestNumWaiters
, ConnectionReserveTimeoutSeconds
, and so forth.
PoolPermissionsSQLException
—generated when an application request to get a connection fails a (security) authentication or authorization check.
Note: Certain Oracle JDBC extensions, and possibly other non-standard methods available from other drivers may durably alter a connection's behavior in a way that future users of the pooled connection will inherit. WebLogic Server attempts to protect connections against some types of these calls when possible. |
When using pooled connections in a data source, it is possible to execute DBMS-specific SQL code that will alter the database connection properties and that WebLogic Server and the JDBC driver will be unaware of. When the connection is returned to the data source, the characteristics of the connection may not be set back to a valid state. For example, with a Sybase DBMS, if you use a statement such as "set rowcount 3 select * from y"
, the connection will only ever return a maximum of 3 rows from any subsequent query on this connection. When the connection is returned to the data source and then reused, the next user of the connection will still only get 3 rows returned, even if the table being selected from has 500 rows.
In most cases, there is standard JDBC code that can accomplish the same result. In this example, you could use setMaxRows()
instead of set rowcount
. Oracle recommends that you use the standard JDBC code instead of the DBMS-specific SQL code. When you use standard JDBC calls to alter the connection, WebLogic Server returns the connection to a standard state when the connection is returned to the data source.
If you use DBMS-specific SQL code that alters the connection, you must set the connection back to an acceptable state before returning the connection to the data source.
To get a connection from an application-scoped data source, see "Getting a Database Connection from a Packaged JDBC Module" in Configuring and Managing JDBC Data Sources for Oracle WebLogic Server.