Database Software

We are currently in the process of migrating a single database to a RAC database under an aggressive time line. One of the issues we're currently facing is the use of sequences by our application. Various components of our app require ORDERED sequences (...terrible dependency, actually), and since these components can now connect to multiple instances with separate SGA's (with separately cached sequences), they produce bad results... fail!... "break"... Nothing new...

Because of the aggressive time line, modifying each component in the application is not feasible.

We've come up with a "temporary" solution:

*1. Create a service called SEQSRV that is defined as having a preferred instance: INSTA and and available instance: INSTB with a TAF policy of BASIC.
2. Create the necessary TNS entries for the service.
3. Create a database link (SEQ).
4. Create (recreate) the sequence(s) with appropriately tuned CACHE value NOORDERED (because they will be ORDERED by virtue of accessing only one instance).
5. Create a synonym from the sequence(s) incorporating the database link
sql> create synonym <synonym_name> for <sequence>@SEQ

* - in case we have an issue with INSTA, requests will fail over to INSTB.

I've tested this with a few options:
a. CACHE=20, 5000, 1000000
b. ORDERED, NORDERED
c. from multiple concurrent clients.
d. 'select sequence_synonym.nextval from dual' in a loop for iterations: 50, 5000, 2000000

The test results showed solid performance for all runs, and the trace indicated a DSF locks were proportional to (CACHE/number of iterations). On higher iterations, there was a bit of recursive sql along with the usual TNS-related wait information, but nothing indicating poor performance.

Can anyone comment on this? Is our logic flawed? Any suggestions? Anything?

Jeffrey Frey
Sr. Oracle DBA
BondDesk Group Llc

Edited by: jjfrey on Feb 11, 2009 12:17 PM