3 Replies Latest reply on Aug 8, 2011 3:38 PM by user154136

    Ordered Sequences

      We have a 3rd partly legacy app that uses a sequence in a trigger to maintain transactional order. We are migrating to an Exadata RAC environment so we were trying to evaluate the best way to migrate this app. We know we can use an ordered sequence but were concerned about possible contention issues. Our other solution was to create a service and pin it to one node and have all of the sequence calls use this service through a dblink. Issue is that we have been unable to get the second solution to work effectively. It seems the sequence is going to all nodes even though the dblink is pointed to a service on one node. Anyone have any prior experience with ordered sequences and perfromance impact or any other ordered sequence workarounds.
        • 1. Re: Ordered Sequences
          Marc Fielding
          I'd expect the performance hit from querying over a dblink would be far more than from an uncached ordered sequence. I'd suggest benchmarking with an ordered, uncached sequence and seeing if your predicted workload level sees contention at all. My colleague Christo Kutrovsky did some testing on this a few years ago, and blogged about it: http://www.pythian.com/news/383/sequences-in-oracle-10g-rac/

          • 2. Re: Ordered Sequences
            Using an ordered nocache sequence with RAC will likely be extremely painful - it will prevent scalability because it forces a serialization point. You'd be much better just limiting this application to a single node if a single node can handle the workload.
            BTW, even using ordered nocache sequence w/o RAC is a scalability hurdle, just gets exacerbated with RAC.

            Greg Rahn
            • 3. Re: Ordered Sequences
              Thanks for the replies. We are going to try to use an ordered sequence with a high cache and see how that impacts performance.