Much performance testing has been done in the area of JMS running on a JDBC store on an Oracle RAC Cluster. The goal of this article is to point to some
existing documentation, point out a new, related feature introduced in WLS 12.1.3, and summarize the various approaches.
First, let me point out suggestions in optimization of the Oracle database table that is used for the JMS backing store. The current JMS documentation proposes using a reverse index when enabling "I/O Multi-threading", which in turn is only recommended for heavy loads. If you have licensed the Oracle database partitioning option, you can use global hash partition indexes for the table. This cuts down the contention on the index, reduces waiting on the global cache buffer, and can significantly improve the response time of the application. Partitioning works well in all cases, some of which will not see significant improvements with a reverse index. See http://www.oracle.com/technetwork/database/availability/maa-fmw-soa-racanalysis-427647.pdf (this document also has some interesting comments about pool and cache size). A second recommendation is to use secure files to improve performance, make storage more efficient, and ease manageability. This is generally recommended to improve throughput with a JDBC store when message sizes are large and when network connections to the database are slow. See the details about secure files at http://www.oracle.com/technetwork/database/availability/oraclefmw-soa-11gr1-securefiles-1842740.pdf. Combining these together, the schema for the
JMS backing store would look something like this:
CREATE TABLE JMS1.JMSWLSTORE ( ID INT NOT NULL, TYPE INT NOT NULL, HANDLE INT NOT NULL, RECORD BLOB NOT NULL, PRIMARY KEY (ID) USING INDEX GLOBAL PARTITION BY HASH (ID) PARTITIONS 8TABLESPACE JMSTS ) LOB (RECORD) STORE AS SECUREFILE (TABLESPACE JMSTS ENABLE STORAGE IN ROW);
The number of partitions should be a power of two to get similar sizes in the partitions. The recommended number of partitions will vary depending on the expected table/index growth and should be analyzed over time by a DBA and adjusted accordingly. See the “Oracle Database VLDB and Partitioning Guide” for other relevant parameters. Look at the custom DDL feature to use a custom JMS JDBC store table (see http://docs.oracle.com/middleware/1213/wls/CNFGD/store.htm#i1160628).
Note that LLR tables are indexed on XIDSTR. Looking at some sample data, the keys are not sequential and only differ for a short middle subset so a reversed index (i.e., reversing bytes from the key) would not help in that case. Using global hash partition indexes, as mentioned above, might be a better option for LLR tables.
These improvements work whether you are using Multi Data Source (MDS) or Active GridLink (AGL) running against a RAC Cluster.
A new trick was added to the performance arsenal in WLS JMS 12.1.3. As with any application that uses a database, there’s overhead in all round-trips from the application to the database. The JMS JDBC store code uses batching with databases that support it. Depending on the configured batch sizes (i.e., DeletesPerBatchMaximum and InsertsPerBatchMaximum) and the number of operations in the transaction, the transaction will consist of one or more round-trips for the batch execution(s) and a round-trip for the commit. The new configuration option OraclePiggybackCommitEnabled also piggy backs the commit on the batch operation for Oracle-only Thin driver. For small transactions, a single round-trip executes the batch and does the commit, cutting in half the number of round-trips.
Much work has been done looking at the overall performance for both MDS and AGL. Starting with the connection affinity enhancements in WLS 12.1.2, the performance of MDS configured with failover (as opposed to round-robin) and AGL is roughly the same. The MDS failover
algorithm reserves all connections on one instance until it fails so that there is affinity of connections. Prior to WLS 12.1.2, MDS with failover has better performance than AGL because AGL does not have affinity of connections to a single instance. The use of AGL over MDS is recommended
because it provides superior RAC support in many areas including management and failover (see Using Active GridLink Data Sources
for more details). However, AGL is only licensed for use with WebLogic Suite. Finally, the key to performance is not only the number of work threads (as one might expect) but the number of concurrent producers and consumers. For less than ten producers and consumers, use normal services on multiple RAC instances with the hash partitioned index and secure files for the LOB, as described above. For higher concurrency (over nine producers and
consumers), it is more efficient to use a singleton database service. It should be configured with a preferred instance and failover instance for High Availability.
Optimization of the use of a WLS data source for a JMS JDBC store can significantly improve the performance of JMS throughput and the corresponding application. As with all performance investigations, you will need to test this with your own application and data.