2 Replies Latest reply: Aug 20, 2014 1:37 AM by Salman Qureshi RSS

    Sequence generation in Oracle RAC env. (Cached, non-ordered)

    dinky

      Env: Oracle Env. 11G, Transaction Java Application

       

      How does CACHED, non-ordered sequence behave (for PK generation), here is the scenario,

      Cache size =10

      Node 1 Active - Records created 1,2,3 (3 records total in table)

      Node Changes - 2 is Active - Records created 11,12,13, 14 (7 records total in table)

      Node Changes - 1 is Active - Records created 21, 22 (or are sequences going to be 4, 5) (9 records total in table)

      Node Changes - 2 is Active - No new record added (9 records total in table)

      Node Changes - 1 is Active - 2 more Records created (9 recor11s total in table) - Is it going to be 23, 24 OR or is it going to be 6,7 OR or is it going to be 31, 32 (9 records total in table)

       

      Please help with accurate generated seq numbers (PKs) for 9 records.

       

      Also, What happens to PK numbers at:

      * shutdown

      * hotbackup

      * mirroring whole schema or database to another separate db using storage mirroring

      * mirroring whole schema or database to another separate db using datapump

       

      How about using cached, ordered sequences to start with and change to no-order if needed for performance reasons

       

      -D

        • 1. Re: Sequence generation in Oracle RAC env. (Cached, non-ordered)
          JohnWatson

          A necessary problem solving skill in the Oracle environment is the ability to experiment. A couple of minutes constructing a simple test is better than hours of speculation:

           

          jw1> create sequence s1;

           

          Sequence created.

           

          jw1> select s1.nextval from dual;

           

            NEXTVAL

          ----------

                  1

           

          jw1> /

           

            NEXTVAL

          ----------

                  2

           

          jw1> conn sys/oracle@jw2 as sysdba

          Connected.

          jw2> select s1.nextval from dual;

           

            NEXTVAL

          ----------

                  21

           

          jw2> select s1.nextval from dual;

           

            NEXTVAL

          ----------

                  22

           

          jw2> conn sys/oracle@jw1 as sysdba

          Connected.

          jw1> select s1.nextval from dual;

           

            NEXTVAL

          ----------

                  3

           

          jw1> select s1.nextval from dual;

           

            NEXTVAL

          ----------

                  4

           

          jw1> exit

          Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

          With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

          Advanced Analytics and Real Application Testing options

          [oracle@berlin1 ~]$ srvctl start instance -db jw -instance jw1 -startoption force

          [oracle@berlin1 ~]$ sqlplus sys/oracle@jw1 as sysdba

           

          SQL*Plus: Release 12.1.0.1.0 Production on Sat Aug 16 10:40:40 2014

           

          Copyright (c) 1982, 2013, Oracle.  All rights reserved.

           

           

          Connected to:

          Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

          With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

          Advanced Analytics and Real Application Testing options

           

          jw1> select s1.nextval from dual;

          • 2. Re: Sequence generation in Oracle RAC env. (Cached, non-ordered)
            Salman Qureshi

            Hi,

            Demonstration from John should have cleared the things. Both RAC instances are having cached 10 valued and hence you see this different range of values from sessions connected to different nodes. If you issue "alter system flush shared_pool', you might even see jump in the values because current cached values would be flushed out.

             

            Salman