DB Adapter - Native sequencing, SequencePreallocationSize

July 01, 2015

Reading time ~1 minute

Here are some DB adapter tips regarding use of native sequencing.

In cases where you want to use the database adapter for inserts, you may want to use native sequencing to populate a primary key.

One problem that you can get into is documented in the [orafmw blog] (https://orafmw.wordpress.com/2011/01/28/sequence-increment-and-pre-allocation-size/)

“The sequence named [ORDER_SEQ] is setup incorrectly. Its increment does not match its pre-allocation size.”

This issue arises because DbAdapter by default sets pre-allocation size as 50. This should match with Database Sequence’s increment size. I usually go in and set this one to 1 as shown in the blog.

One reason why should keep a larger number here is for performance. The [Oracle Fusion Middleware Performance and Tuning Guide, Oracle Adapters Performance Tuning] (http://docs.oracle.com/cd/E14571_01/core.1111/e10108/adapters.htm#ASPER327) chapter 15.3.1 suggests

  • Use Native Sequencing

If you are using the XSL functions to assign primary keys to records, consider using the built-in native sequencing support in the adapter. Sequencing support obtains and caches 50 keys at a time by default. Caching improves performance by reducing the number of round trips. The chunk size can be controlled incrementally by modifying the sequencePreallocationSize connector property.

Other interesting performance improvements for DB adapter relevant for Exalogic - I will look more into that later. Here is the reference to doc [Oracle Fusion Middleware User’s Guide for Technology Adapters, Oracle JCA Adapter for Database] (http://docs.oracle.com/cd/E23943_01/integration.1111/e10231/adptr_db.htm#TKADP2434) chapter 9.8.3

Jon Petter is Department Manager for Middleware at SYSCO. Building an organization of skilled Oracle Middleware experts, with a focus on Oracle SOA Suite, Weblogic, Oracle Service Bus, BPM Suite, BAM, Oracle Event Processing. He has worked with Oracle products since 1997, and has worked with some of the larger SOA customers in Norway. Board member of the Oracle User Group Norway (OUGN), and Oracle ACE Associate.

Customizing service calls with multiple levels of security in OSB 12c

This will be a part two of my previous post which you can read [here](http://blog.sysco.no/osb/jdeveloper/Service-call-with-multiple-leve...… Continue reading