The following sections provide information on how to configure and use JDBC-accessible stores:
JDBC TLog Stores: to persist transaction logs (TLOGs) in a database. See Using a JDBC TLog Store.
JDBC Stores: to persist WebLogic Server instance services and subsystem information, excluding TLOGs, in a database. See Using a JDBC Store.
You can configure a JDBC TLOG store to persist transaction logs to a database, which provides the following benefits:
Leverages replication and HA characteristics of the underlying database.
Simplifies disaster recovery by allowing the easy synchronization of the state of the database and TLOGs.
Improved Transaction Recovery service migration as the transaction logs to do not need to be migrated (copied) to a new location.
The main steps for creating a JDBC TLOG store are as follows:
You can choose one of the following data source types, depending on your WebLogic Server license and application needs:
Generic Data Sources—See "Creating a JDBC Data Source" and "Using Connect-Time Failover with Oracle RAC" in Administering JDBC Data Sources for Oracle WebLogic Server.
GridLink Data Sources—See "Using GridLink Data Sources" in Administering JDBC Data Sources for Oracle WebLogic Server.
Multi data sources—Backed by a fully replicated, zero-latency database, such as Oracle RAC. See "Configuring JDBC Multi Data Sources" and "Using Multi Data Sources with Oracle RAC" in Administering JDBC Data Sources for Oracle WebLogic Server.
Here's an example of how a JDBC TLOG store may look in the configuration file, using the JDBC data source MyDataSource, and with a logical name specified:
<server>
<transaction-log-jdbc-store>
<data-source>MyDataSource</data-source>
<prefix-name>TLOG_MS1</prefix-name>
<create-table-ddl-file>myDDL/myCreateTable.sql</create-table-ddl-file>
<max-retry-seconds-before-tlog-fail>120</max-retry-seconds-before-tlog-fail>
</transaction-log-jdbc-store>
</server>
Table 6-1 describes the JDBC TLOG store configuration parameters that can be modified.
Table 6-1 JDBC TLOG Store Configuration Options
| Option | Required | What It Does |
|---|---|---|
|
Yes |
The JDBC data source or multi data source used by this JDBC store to access the store's database table ( Note: The JDBC store must use a JDBC data source that uses a non-XA JDBC driver and has Supports Global Transactions disabled. This limitation does not remove the XA capabilities of layered subsystems that use JDBC stores. For example, WebLogic JMS is fully XA-capable regardless of whether it uses a file store or any JDBC store. |
|
|
No |
The prefix for the JDBC store's table is generally entered in the following format: When using multiple JDBC stores, it is required to set this option to a unique value for each configured JDBC store. When no prefix is specified, the JDBC store table name is simply Modifying an existing JDBC store's prefix does not necessarily require a server restart, as described in Modifying Custom Persistent Store Parameters. |
|
|
No |
Optionally used with supported DDL (data definition language) files to create the JDBC store's database table ( |
|
|
Default is 20. |
The maximum number of table rows that are deleted per database call. |
|
|
Inserts Per Batch Maximum |
Default is 20. |
The maximum number of table rows that are inserted per database call. |
|
Default is 20 |
The maximum number of table rows that are deleted per database call. |
|
|
Default is 300. |
The maximum amount of time, in seconds, WebLogic Server tries to recover from a JDBC TLog store failure. If store remains unusable after this period, WebLogic Server set the health state to HEALTH_FAILED. A value of 0 indicates WebLogic Server does not conduct a retry and and immediately sets the health state as HEALTH_FAILED. |
|
|
Default is 60. |
The maximum amount of time, in seconds, WebLogic Server waits before trying to recover from a JDBC TLog store failure while processing a transaction. If store remains unusable after this amount of time, WebLogic Server rolls back the affected transaction. A value of 0 indicates WebLogic Server does not conduct a retry and rolls back the transaction immediately. The practical maximum value is a value less than the current value of |
|
|
Default is 5. |
The amount of time, in seconds, WebLogic Server waits before attempting to verify the health of the TLOG store after a store failure has occurred. |
|
|
1000 |
The amount of time, in milliseconds, a JDBC Store reconnect retry or a TLOG-in-DB Store attempts to re-establish a connection to a database, before the Store shuts down, and all the operations waiting on the Store are unblocked.The minimum value that can be configured through the |
|
|
200 |
The amount of time in milliseconds, between reconnect attempts, during the connection retry period. The minimum value that can be configured through the |
For instructions on configuring a JDBC TLOG store using the WebLogic Server Administration Console, see "Configure the Transaction Log Store" in the Oracle WebLogic Server Administration Console Online Help.]
The following section provides guidelines for configuring JDBC TLOG stores.
Only globally-scoped (not application-scoped) data sources can be targeted to a JDBC TLOG store.
Only one JDBC TLOG store can be configured per WebLogic Server. Conversely, multiple WebLogic Servers can not share a JDBC TLOG store.
You must configure a JDBC TLOG store. The default is to persist TLOG information to the server's default persistent store.
You cannot use a data source that is configured to use an XA JDBC driver or is configured to support global transactions. Use a non-XA data source.
For general rules on JDBC-accessible stores, see Guidelines for Configuring a JDBC Store.
The following section provides additional information on JDBC TLOG store behavior and limitations:
The database used to store the TLOG information must be available at server startup. If the database is not available, the WebLogic Server instance will fail to boot.
Only the JTA sub-system can use the JDBC TLOG store to persist information about committed transactions coordinated by the server that may not have been completed. No other systems can access the JDBC TLOG store.
Using a JDBC TLOG store does not change LLR behavior. A JDBC TLOG store can be used with or without LLR. When used in tandem with LLR transactions, the transaction committing information is stored in a LLR table but the checkpoint records and heuristic logs are stored in the JDBC TLOG store.
If the TLOG store is changed from one store type to another or from one location to another, the change takes effect only after reboot and all pending transactions in the old store are not be copied to the new store. You must ensure there are no pending transactions before changing the TLOG store type or location.
If the JDBC TLOG store becomes unavailable, the JTA health state transitions to FAILED and any global transactions will fail. In turn, the server life-cycle changes to FAILED. The JTA Transaction Recovery System then attempts to recover from transient runtime errors if possible and resolves any in-doubt transactions. See Server Migration when using a JDBC TLOG Store.
If the database used to store TLOG is corrupted and can not be restored, than all pending transaction information is lost.
If database tables or rows used by the JDBC TLOG store are locked for some reason in the database, the database administrator must resolve these locks manually. Otherwise, the JTA subsystem is blocked and will be suspended until the lock(s) are released, or encounters an exception due to lock. The JTA subsystem will remain unable to operate correctly until the lock(s) are released or the value of MaxRetrySecondsBeforeTLOGFail is exceeded.
Note:
Different databases have different features for locked local transactions. Some databases may have trouble resolving database locks in a timely manner. You may need to contact your database administrator for more information on basic row locking issues that may occur in your application environment.
WebLogic Server supports both manual and automatic migration of the Transaction Recovery Service when using a JDBC TLOG store. The data source used by the JDBC TLOG store must be targeted on both the primary server instance and a backup server instance. Oracle recommends targeting the data source to all the server instances of a cluster. For more information, see "Transaction Recovery After a Server Fails" in Developing JTA Applications for Oracle WebLogic Server.
You can monitor statistics for Transaction Log Store statistics and for each open store connection. For general information on how to monitor persistent stores, see Monitoring the WebLogic Persistent Store.
When you configure WebLogic Server to use a JDBC TLOG store, the store is registered with the Health system as a non-critical subsystem using a name with the following pattern:
PersistentStore.TLOG_servername
where servername is the name of the WebLogic Server instance hosting the primary TLOG store.
You can monitor the JDBC TLOG store health state in the WebLogic Server Administration Console, see "Monitor server health" in Oracle WebLogic Server Administration Console Online Help.
You can monitor Transaction Log Store statistics in the WebLogic Server Administration Console, see "View transaction log statistics for a server" in Oracle WebLogic Server Administration Console Online Help.
You can monitor Transaction Log Store connection statistics in the WebLogic Server Administration Console, see "View statistics for TLOG store connections" in Oracle WebLogic Server Administration Console Online Help.
Properly secure your application environment, including the JDBC TLOG store table. Failure to do so may allow a process to:
Delete information, maliciously or unintentionally. Such a deletion can cause transaction information to be lost and cause affected global transactions to be completed heuristically.
Modify information, maliciously or unintentionally. Such modification can cause unexpected behavior.
Read confidential transaction information, such as when transaction starts and what resources are involved.
Access the database instance or database machine.
Access the network between JTA and the database, potentially intercepting, viewing, or modifying data.
The following sections provide an example of a JDBC store, and information about creating a database table for a JDBC store, either using an existing DDL, a custom DDL, and using Oracle blob record columns in a DDL file.
To create a JDBC store, you can directly modify the default JDBCStoreMBean parameters. For instructions on using the WebLogic Server Administration Console to create a JDBC store, see "Create JDBC Stores" in the Oracle WebLogic Server Administration Console Online Help.
For configuration guidelines on using prefixes with JDBC stores and recommended JDBC data source settings, see Guidelines for Configuring a JDBC Store.
Here's an example of how a JDBC store may look in the configuration file, using the JDBC data source MyDataSource, and with a logical name specified:
<jdbc-store> <name>SampleJDBCStore</name> <target>yourserver</target> <data-source>MyDataSource</data-source> <logical-name>Baz</logical-name> </jdbc-store>
Table 6-2 describes the JDBC store configuration parameters that can be modified.
Table 6-2 JDBC Store Configuration Options
| Option | Required | What It Does |
|---|---|---|
|
Name |
Yes |
The name of the JDBC store, which must be unique across all stores in the domain. |
|
Targets |
Yes |
The server instance, cluster, or migratable target where a JDBC store is targeted. Multiple subsystems can share the same JDBC store, as long as they are all targeted to the same server instance or migratable target. Note:
|
|
Data Source |
Yes |
The JDBC data source or multi data source used by this JDBC store to access the store's database table ( Note: The JDBC store must use a JDBC data source that uses a non-XA JDBC driver and has Supports Global Transactions disabled. This limitation does not remove the XA capabilities of layered subsystems that use JDBC stores. For example, WebLogic JMS is fully XA-capable regardless of whether it uses a file store or any JDBC store. |
|
Prefix Name |
No |
The prefix for the JDBC store's table is generally entered in the following format: When using multiple JDBC stores, it is required to set this option to a unique value for each configured JDBC store. When no prefix is specified, the JDBC store table name is simply Modifying an existing JDBC store's prefix does not necessarily require a server restart, as described in Modifying Custom Persistent Store Parameters. |
|
Logical Name |
No |
Optionally used with WebLogic Server subsystems, like EJBs, when deploying a module to an entire cluster, but also require a different physical store on each server instance in the cluster. In such a configuration, each physical store would have its own name, but all the persistent stores would share the same logical name. |
|
Create Table from DDL File |
No |
Optionally used with supported DDL (data definition language) files to create the JDBC store's database table ( |
For instructions on configuring a JDBC store using the WebLogic Server Administration Console, see "Create JDBC Stores" in the Oracle WebLogic Server Administration Console Online Help.]
When using a JDBC store, the backing database can be any database that is accessible through a JDBC driver. WebLogic Server detects some drivers for supported databases.
For each of these databases, there are corresponding DDL (data definition language) files within the ORACLE_HOME\wlserver\modules\com.bea.core.store.jdbc_1.0.0.0.jar file, in the weblogic/store/io/jdbc/ddl directory, where ORACLE_HOME is the top-level installation directory of your WebLogic Server installation.
Table 6-3 Supported JDBC Drivers and Corresponding DDL Files
| Database | DDL Files |
|---|---|
|
IBM DB2 |
db2.ddl db2v6.ddl |
|
Informix |
informix.ddl |
|
Microsoft SQL (MSSQL) Server |
mssql.ddl |
|
MySQL |
mysql.ddl |
|
Oracle |
oracle.ddl oracle_blob.ddl oracle_blob_securefile.ddl |
|
Sybase |
sysbase.ddl |
The DDL files are actually text files containing the SQL commands (terminated by semicolons) that create the JDBC store's database table (WLStore). Therefore, if you are using a database that is not included in this list, you can copy and edit any one of the existing DDL files to suit your specific database, as described in Creating a JDBC Store Table Using a Custom DDL File.
The JDBC Store Configuration page provides an optional Create Table from DDL File option, through which you can access a pre-configured DDL file that is used to create the JDBC store's backing table (WLStore). This option is ignored when the JDBC store's backing table already exists. It is mainly used to specify a custom DDL file created for an unsupported database, or when upgrading JMS JDBC store tables from a prior release to a current JDBC Store table.
If a DDL file name is not specified in the Create Table from DDL File field, and the JDBC store detects that its backing table does not already exist, the JDBC store automatically creates the table by executing a pre-configured DDL file that is specific to the database vendor (see Supported JDBC Drivers).
If a DDL file name is specified in the Create Table from DDL File field, and the JDBC store detects that its backing table does not already exist, the JDBC store searches for the specified DDL file in the file path first, and then, if not found, searches for the DDL file in the CLASSPATH. Once found, the SQL within the DDL file is executed to create the JDBC store's backing table. If the configured file is not found and the table doesn't already exist, the JDBC store will fail to boot.
To use a different database from those listed in Supported JDBC Drivers, you can copy and edit any one of the existing DDL template files to suit your specific database.
For Oracle databases, you can use the oracle_blob.ddl or oracle_blob_securefile.ddl file to create a JDBC store table with a BLOB record column type rather than the default LONG RAW record column type. The oracle_blob.ddl is used to create Oracle basic file BLOBs and the oracle_blob_securefile.ddl file is used to create Oracle secure file BLOBs. Both files types are pre-configured and supplied in the WebLogic CLASSPATH, as described in Supported JDBC Drivers.
Oracle Database 11g Release 2 includes a zero copy I/O performance enhancement for Secure Files and a logical cache for BLOBs. Use of these enhancements can improve throughput with a JDBC store when message sizes are large and when network connections to the database are slow. The Oracle LONG RAW datatype is typically better performing than BLOBS when using a fast connection to the database.
Note:
If you need to preserve data already in a Oracle LONG RAW column, but still want to switch the column to BLOB, do not use this method. Instead, consult the Oracle documentation for the SQL ALTER TABLE command.
To use the Oracle BLOB DDL with a JDBC store:
Shut down the server instance that uses the JDBC store.
Delete the current JDBC table, as explained in Managing JDBC Store Tables.
Reboot the server instance.
Create a new JDBC store, as explained in "Create JDBC Stores" in the Oracle WebLogic Server Administration Console Online Help.
In the Create Table from DDL File field on the General Configuration page, enter the location of:
the oracle_blob.ddl file as: /oracle_blob.ddl
the oracle_blob_securefile.ddl file as: /oracle_blob_securefile.ddl
Click Finish to create the JDBC store's backing table.
When using Oracle BLOBS, you may be able to improve performance by tuning the ThreeStepThreshold value.
When the JDBC store schema contains an Oracle BLOB column (basic file or secure file), the JDBC store populates the BLOB data using one of the following implementations based on the size of the BLOB data:
The JDBC store inserts a row with BLOB data directly into the store table in a single step. Because only a single step is involved, JDBC batch insert is also adopted and performs best when the data size is small. This implementation is used when the BLOB data to be inserted is less than or equal to the value of the ThreeStepThreshold.
The JDBC store inserts a row with BLOB data into the store table in three steps using the Oracle LOB API. This implementation provides better performance when the data size is large. This implementation is used when the BLOB data to be inserted is greater than the value of the ThreeStepThreshold.
The default value of ThreeStepThreshold is 200K.
The JDBC utils.Schema utility allows you to regenerate a new JDBC store database table (WLStore) by deleting the existing version. Running this utility is usually not necessary, since WebLogic Server automatically creates this table for you. However, if your existing JDBC store database table somehow becomes corrupted, you can delete it using the utils.Schema utility.
The utils.Schema utility is a Java program that takes command-line arguments to specify the following:
JDBC driver
Database connection information
Name of a file containing the SQL Data Definition Language (DDL) commands that create the database table
Enter the utils.Schema command, as follows:
$ java utils.Schema url JDBC_driver [options] DDL_file
Note:
To execute utils.Schema, your CLASSPATH must contain the weblogic.jar file.
Table 6-4 lists the utils.Schema command-line arguments.
Table 6-4 Command-line arguments for utils.Schema
| Argument | Description |
|---|---|
url |
Database connection URL. This value must be a colon-separated URL as defined by the JDBC specification. |
JDBC_driver |
Full package name of the JDBC Driver class. |
options |
Optional command options. If required by the database, you can specify:
You can also specify the |
DDL_file |
The full pathname of the DDL text file containing the SQL commands that you want to execute. For more information, see Supported JDBC Drivers. |
For example, the following command deletes a JDBC table named MYWLStore in an Oracle server named DEMO, with the user name user1 and password foobar:
$ echo "drop MYWLStore;" > drop.ddl $ java utils.Schema jdbc:weblogic:oracle:DEMO \ weblogic.jdbc.oci.Driver -u user1 -p foobar -verbose \ drop.ddl
The JDBC Store reconnect retry period indicates the time period when a WebLogic JDBC Store or a TLOG in-DB Store retries to connect to a database, before the Store shuts down and requires a restart. You can configure the Store retry period through Command line system properties, MBeans and WLST.
The JDBC Store reconnect retry period controls the length of the time period required by a JDBC Store reconnect retry or a TLOG-in-DB Store to retry database requests before a Store shuts down, and requires a restart. The JDBC Store reconnect retry interval controls the length of the time in milliseconds between reconnect attempts during the connection retry period. You can configure the JDBC Store reconnect retry period and interval by using the ReconnectRetryPeriodMillis and ReconnectRetryIntervalMillis attributes available in the JDBCStoreMBean and TransactionLogJDBCStoreMBean. For more information about the Retry attributes, see MBean Reference for Oracle WebLogic Server.
You can configure the retry period and interval for custom JDBC Store reconnect retry and TLOG-in-DB Stores by setting system properties on the WebLogic Server command line. The -DwebLogic.store.jdbc.RecnnectRetryPeriodMillis=<millis> and -Dweblogic.store.jdbc.ReconnectRetryIntervalMillis=<millis> option specifies the JDBC Store reconnect retry in period and interval available in the WebLogic Server domain.
The -Dweblogic.store.jdbc.ReconnectRetryPeriodMillis=<millis> system property overrides legacy properties-Dweblogic.store.jdbc.IORetryDelayMillis=<millis> or -Dweblogic.jms.store.JMSJDBCIORetryDelay=<seconds>, if they are set on the same command line. If the retry period property is not set, then the legacy properties will take effect.
Deprecation Note: All JDBC Store reconnect retry command line properties are deprecated as of 12.2.1.0 and may be removed in a future release. In 12.2.1.0 and later. Oracle recommends setting these values through MBean attributes instead.
It is important to consider the following when configuring a JDBC Store reconnect retry period:
The total elapsed time before a Store fails may sometimes be more than double the configured retry period.
It is advisable to configure more tolerant retry periods of up to 15 seconds or more instead of the maximum, since a retry period that is set to too long may lock up WebLogic Server applications and client applications during this period.
JDBC Store reconnect retry tuning should be configured to align with transaction tuning.
Consider tuning JTA transaction time outs to be higher than the retry period so that the application transactions that involve a JDBC Store do not time out waiting for a JDBC Store to successfully recover from a database failure. The default transaction time out for a domain is 30 seconds and is tunable via the TimeoutSeconds attribute on the JTAMBean. In addition, transaction time outs are tunable on a per application basis.
WebLogic's transaction system will temporarily stop allowing a JDBC Store to participate in transactions if the JDBC Store is unresponsive for more than the JTAMBean MaxXACallMillis attribute (default is 1200000 millis/two minutes). Once JTA decides a Store is unresponsive, it will not attempt to allow the Store to participate in transactions until after MaxResourceUnavailableMillis has passed (default is 1800000 millis/30minutes). It is therefore advisable to ensure that MaxXACallMillis is at least twice the JDBC Store reconnect retry period.
If the retry period is configured on a TLOG-in-DB Store, it should be set to less than half of the TransactionLogJDBCStoreMBean MaxRetrySecondsBeforeTLOGFail setting. otherwise, the TLOG-in Store may delay failure longer than the TLOGFail setting.
A JDBC Store reconnect retry period is configured in milliseconds while some transaction settings are configured in seconds.
By default, a WebLogic JDBC Store obtains two JDBC connections from its Data Source, and caches these connections throughout a Store's lifetime. You can optionally tune the JDBC Store's Connection Caching Policy to reduce the number of cached JDBC connections.
The JDBC Store Connection Caching Policy setting controls how many JDBC connections it caches. The Connection Caching policy can be configured by using the ConnectionCachingPolicy attribute available in the JDBCStoreMBean and TransactionLogJDBCStoreMBean. The valid values for ConnectionCachingPolicy attribute are - DEFAULT, MINIMAL and NONE. For more information about the valid values, see MBean Reference for Oracle WebLogic Server and Table 6-5.
Note:
TheNONE policy requires additional tuning to avoid deadlocking a server. For more information about tuning the NONE JDBC Store Connection Caching Policy, see Important Tuning Considerations for the NONE Connection Caching Policy.You can configure the JDBC Store Connection Caching Policy by setting a system property on the WebLogic Server command line. The -Dweblogic.store.jdbc.ConnectionCachingPolicy option specifies the WebLogic JDBC Store Connections available in the WebLogic Server domain. For more information about the valid values that can be set for this Policy, see Table 6-5.
Note:
The weblogic.store.jdbc.ConnectionCachingPolicy system property has been deprecated as of 12.2.1.1.0, and may not remain available in future releases. Oracle recommends configuring a Connection Policy through WLST or MBeans instead. For more information about tuning the NONE JDBC Store Connection Caching Policy, see Important Tuning Considerations for the NONE Connection Caching Policy
The NONE policy requires additional tuning to avoid deadlocking a server. For more information about tuning the NONE JDBC Store Connection Caching Policy, see Important Tuning Considerations for the NONE Connection Caching Policy.
A JDBC Store's Connection Caching behavior is determined by the combination of its Connection Caching Policy setting and Worker Count setting. The table below describes the expected behavior for each combination:
Table 6-5 JDBC Store Connection Caching Policy behavior
|
Connection Caching Policy |
Cached Connections when Worker count=1 |
Cached Connections when WorkerCount>1 |
Description |
|
DEFAULT |
2 |
2+ Worker Count |
By default, each JDBC Store instance caches two database connections for the life of store. If the JDBC Store worker-count is configured to be more than two, the store opens an additional connection for each worker. |
|
MINIMAL |
1 |
1+WorkerCount |
Each JDBC store instance caches a single database connection for the life of the store. If the JDBC worker- count is configured to be two or higher, the store opens one connection for each worker. The performance of this setting may be less than |
|
NONE |
0 |
N/A |
Each JDBC store instance obtains a connection from its data source as needed, and releases the connection when finished. The Warning: To avoid the risk of dead-locking a WebLogic Server, Oracle strongly recommends configuring a dedicated data source for NONE connection-caching-policy JDBC stores. |
It is important to consider the following tuning considerations when a JDBC Store Connection Caching Policy is set to NONE:
Use a dedicated Data Source to avoid deadlocks - It is strongly advised to configure JDBC Stores to use a dedicated Data Source when the JDBC Store Connection Caching Policy is set to NONE. ANONE policy JDBC Store may deadlock a server or eventually fail if it is configured to share the Data Source with applications or non-store services.
For example, consider an application that performs the following steps:
Obtains a Data Source connection.
Sends a persistent JMS message through a JMS Server with a NONE policy, JDBC Store that uses the same Data Source.
Closes the Data Source connection.
It is possible that step 1 can obtain the last available connection in the Data Source connection pool, and therefore in step 2, the JMS send call will block until the NONE policy JDBC Store is able to get a connection from the same pool. This is a problem because step 2 will potentially never get a connection no matter how long it waits since it is possible that all other applications are also blocked in step 2 (and therefore no application can get to step 3 in order to free up a connection). This problem in turn can cause a server or client to have many stuck threads and/or cause a JDBC Store to ultimately shutdown once it waits too long to try and get a connection.
Tune a large enough Data Source connection pool - A JDBC Store uses multiple concurrent connections when its dependent services (such as JMS) first initialize. Hence, the Data Source pool must be configured so that it can grow somewhat larger than the number of JDBC Stores that are using the pool.
Enable and tune Data Source connection testing - Enabling Data Source connection testing helps provide JDBC Store resiliency during database access failures. But, note that if performance is a concern, then frequent Data Source connection testing should be avoided, since it lowers performance of a NONE policy JDBC Store. In general, it is advisable to enable the Data Source Test Connection on Reserve setting, and set the Data Source Seconds to Trust and Idle Pool Connection value higher than zero, and lower than the JDBC Store Connection Retry Interval Millis value. For more information, see Connection Testing Options for a Data Source in Administering JDBC Data Sources for Oracle WebLogic Server.
Monitor and tune Prepared Statement Caching performance - A JDBC Store configured with NONE may yield poor performance if its Data Source or JDBC driver Prepared Statement Cache size is too small. To check if cache misses are lowering performance, monitor your prepared statement cache activity when under load. This monitoring should show frequent cache hits and few cache misses, but if you see many cache misses then increase your cache size.
Monitor Oracle RAC with GridLink performance and potentially tune accordingly. If a NONE policy JDBC Store yields poor performance in comparison to other policies when using Oracle RAC with a GridLink driver, the potential workarounds are:
Use a Multi Data Source instead of GridLink Data Source.
Rebuild JDBC Store tables with a reverse index. For more information about Rebuilding, see section Rebuilding the Store Table Index for an Oracle Database.
Note:
TheNONE policy may yield measurably lower performance than the MINIMAL or DEFAULT policies even if all of the above considerations are carefully followed.The following sections provide guidelines for using JDBC store prefixes, recommended WebLogic JDBC data source settings for JDBC stores, and handling JMS transactions with JDBC stores.
The JDBC store database contains a database table, named WLStore, that is generated automatically and is used internally by WebLogic Server. The JDBC store provides an optional Prefix Name parameter, which can be used to provide more precise access to the database table.
It is always a best practice to configure a prefix for the JDBC WLStore table name, especially when:
The database requires fully-qualified names. (You should verify this with your database administrator.)
There is more than one JDBC store instance sharing a database, since no two JDBC stores can share the same table.
There are many tables in the database. Setting the prefix reduces the number of tables the JDBC store must search through to find its table during boot.
To avoid potential data loss, follow these rules:
Each JDBC store table name must be unique.
If multiple JDBC stores share a table, the behavior is undefined and data loss is likely.
There is no procedure for combining two database tables into a single table.
For most databases, the Prefix Name option for the JDBC store's backing database table should be set in the following format for each configured JDBC store, which will result in a valid table name when prepended to the JDBC store table name:
[[[catalog.]schema.]prefix]
Note that each period in the [[[catalog.]schema.]prefix] format is significant. Generally, catalog identifies the set of system tables being referenced by the DBMS, and schema generally corresponds to ID of the table owner (username). When no prefix is specified, the JDBC store table name is simply WLStore and the database implicitly determines the schema according the current user of the JDBC connection.
For example, in a production database, the database administrator could maintain a unique table for the Sales department, as follows:
[[[Production.]JMSAdmin.]Sales]
The resulting table will be created in the Production catalog, under the JMSAdmin schema, and will be named SalesWLStore.
For some DBMS vendors, such as Oracle, there is no catalog to set or choose, so the format simplifies to [[schema.]prefix]. For more information, refer to your DBMS documentation for instructions on fully-qualified table names, but note that the syntax specified by the DBMS may differ from the format required for this option.
Caution:
If the Prefix Name setting is changed, but the WLStore database table already exists in the database, take care to preserve existing table data. In this case, the existing database table must be renamed by a database administrator to match the new configured table name.
The following settings are recommended when you use a JDBC data source or multi data source for JDBC stores.
WebLogic Server provides robust JDBC data sources that can automatically reconnect to failed databases after they come back online, without requiring you to restart WebLogic Server. To take advantage of this capability, and make your use of JDBC stores more robust, configure the following options on the JDBC data source associated with the JDBC store:
TestConnectionsOnReserve="true" TestTableName="SYSTABLES" ConnectionCreationRetryFrequencySeconds="600"
For more information about JDBC default Test Table Names, see "Connection Testing Options for a Data Source" in the Administering JDBC Data Sources for Oracle WebLogic Server. For more information about setting the number of database reconnection attempts, see the "Enabling Connection Creation Retries" section in Administering JDBC Data Sources for Oracle WebLogic Server.
The JDBC store must use a JDBC data source that uses a non-XA JDBC driver and has Supports Global Transactions disabled. This limitation does not remove the XA capabilities of layered subsystems that use JDBC stores. For example, WebLogic JMS is fully XA-capable regardless of whether it uses a file store or any JDBC store.
Because the JDBC store implements the XAResource interface, it acts as it's own resource manager and handles the transactions above the JDBC driver level. That is, the store itself implements the XAResource and handles the transactions without depending on the database (even when the messages are stored in the database).
This means that whenever you are using a JDBC store and a database (even if it is the same database where the JMS messages are stored), then it is two-phase commit transaction.
For more information about using JMS transactions with a JDBC store, see "Using Transactions with WebLogic JMS" in Developing JMS Applications for Oracle WebLogic Server.
From a performance perspective, you may also boost your performance as follows:
Ensure that the JDBC data source used for the database work exists on the same server instance as the JMS destination—the transaction will still be two-phase, but it will be handled with less network overhead.
Use file stores rather than JDBC stores.
Configure multiple services to share the same store if they will commonly be invoked within the same transaction.
If an application directly performs database operations in addition to invoking store services (such as JMS) within the same transaction, consider using a JDBC data source with Logging Last Resource (LLR) enabled for the database operations.
With the LLR optimization, the transaction will follow the two-phase commit protocol, but the database operations will be handled in a single local transaction, which may improve overall transaction performance. For more information on using the LLR optimization, see "Understanding the Logging Last Resource Transaction Option" in Administering JDBC Data Sources for Oracle WebLogic Server.
Under heavy JDBC store I/O loads, you can improve performance by configuring a JDBC store to use multiple JDBC connections to concurrently process I/O operations.
Note:
Enabling I/O multithreading under light loads may actually reduce performance. Oracle recommends that you tune your applications appropriately.
To enable I/O multithreading, set the Worker Count attribute to an integer value greater than 1. The default value of this configuration property is 1 and disables this option. The Worker Count attribute specifies the number of worker threads the JDBC store uses to process store I/O. Each worker thread acquires one JDBC connection from the configured data source pool when the store is opened. In many cases, benefits of multithreading tends to decrease after 4 concurrent threads. When using a slow connection to the database, multithreading may not improve performance.
Note:
If you set the Worker Count to a value where there are not enough connections available in the connection pool, the JDBC store will fail to open.
You can tune the workload for each worker thread by changing the value of the Worker Preferred Batch Size attribute. Increasing the value of this attribute incrementally increases the workload assigned to each worker thread. The workload consists of store I/O requests, which are grouped and pushed to each JDBC worker thread for processing. If the size of individual I/O requests is commonly very large (for example, requests to store 1 MB JMS messages), then tune the value of Worker Preferred Batch Size to a smaller value for better performance.
When I/O multithreading is enabled, multiple JDBC connections are used to concurrently process store I/O operations which can result in database resource contention. To reduce contention on Oracle databases, Oracle recommends rebuilding the primary key index into a reverse key index when I/O multithreading is used. If you use and then disable I/O multithreading, Oracle recommends rebuilding the primary key index as a non-reverse index. For more information on reverse key indexes, see "Indexes and Index-Organized Tables" in Oracle Database Concepts.
Use the following basic steps to rebuild the Store table index for Oracle database:
Login to the Oracle database under the Store schema name. The Store schema name may or may not be the same as the data source user name.
Use the PL/SQL script found in Build a Reverse Index for an Oracle Database or Build a Non-Reverse Index for an Oracle Database to rebuild the Store table index as needed. Replace <Store Table Name> in each script with the Store table name as described in Using Prefixes with a JDBC Store. For more information on PL/SQL, see "Execution of PL/SQL Subprograms" in Oracle Database Concepts.
Note:
Oracle recommends reverse indexes for I/O multithreading and non-reverse indexes for single threaded I/O.
To rebuild the Store table index as a reverse index for an Oracle database, run the following PL/SQL block as the store database user:
declare
idx user_ind_columns.index_name%TYPE;
alter_stmt VARCHAR2(200);
begin
select index_name into idx from user_ind_columns where table_name =
<Store Table Name> and column_name = 'ID';
alter_stmt := 'alter index ' || idx || ' rebuild reverse';
dbms_output.put_line(alter_stmt);
execute immediate alter_stmt;
end;
/
To rebuild a reverse Store table index as a non-reverse index for Oracle database, run the following PL/SQL block as the store database user:
declare
idx user_ind_columns.index_name%TYPE;
alter_stmt VARCHAR2(200);
begin
select index_name into idx from user_ind_columns where table_name =
<Store Table Name> and column_name = 'ID';
alter_stmt := 'alter index ' || idx || ' rebuild noreverse';
dbms_output.put_line(alter_stmt);
execute immediate alter_stmt;
end;
/