11 Replies Latest reply: Jul 5, 2006 2:39 PM by DougClarke RSS

    Toplink Sequence - Preallocation in Oracle

    450830
      In my application , we are using Oc4j and Toplink. Some times when we
      restart the app server , breaks in sequences are observer. We are using native
      sequence provided by Oracle. In toplink doc , it is specified that preallocation
      is happening and preallocation size will be equvalent to increment size of Sequence created in Oracle. We haven't specified increment size of the sequence and hence it will take default value .. ie 1.. Apprx there is a 20 count difference. This is not happening always...

      Any idea about this issue ?
        • 1. Re: Toplink Sequence - Preallocation in Oracle
          448817
          If you are using a Session xml are you sure it contains something similar to below?

          <sequencing>
          <default-sequence xsi:type="native-sequence">
          <name>Native</name>
          <preallocation-size>1</preallocation-size>
          </default-sequence>
          </sequencing>
          • 2. Re: Toplink Sequence - Preallocation in Oracle
            450830
            Instead of xml , we are using java to specify those details.
            We are not specifying preallocation any where. We are just
            specifying to use Native. Thats all...

            More over i checked the value of getPreallocation() method
            and I'm getting 1.... I dont know why Toplink is behaving like
            this. More over this is not happening all the times
            • 3. Re: Toplink Sequence - Preallocation in Oracle
              JamesSutherland
              The database also pre-allocates sequence ids, and may not guarantee that there are not skipped sequences. Also when a transaction rolls-back during the commit sequence ids will also be lost.

              I general I would strongly suggest using sequence-preallocation for performance reasons.
              • 4. Re: Toplink Sequence - Preallocation in Oracle
                450830
                By default preallocation size in Oracle is 1 and in toplink it is 50 ( provided if we dont specify the preallocation size in oracle ) . That is contradicting right ? If we dont specify the preallocation size in Oracle then it will be 1 and hence Toplink will take 1 as the preallocation , right ?
                • 5. Re: Toplink Sequence - Preallocation in Oracle
                  364925
                  It is a contradiction and TopLink trying to preallocate 50 sequence values will throw an exception after getting just one. You have to make sure that Oracle sequence and TopLink sequence preallocation sizes are the same.
                  • 6. Re: Toplink Sequence - Preallocation in Oracle
                    450830
                    preallocation of oracle sequence , do you mean the cache_size of sequence in Oracle ?

                    Is there any relation between CACHE_SIZE of sequence in Oracle and Toplink Cache ?
                    • 7. Re: Toplink Sequence - Preallocation in Oracle
                      364925
                      Sorry for not being clear. There is no relation between CACHE_SIZE of sequence in Oracle and Toplink Cache, but rather INCREMENT BY parameter in Oracle sequence and sequencePreallocationSize in TopLink should be assigned the same value.
                      • 8. Re: Toplink Sequence - Preallocation in Oracle
                        275933
                        So is this a terminology mismatch?

                        The Oracle DB's CACHE xxx parameter of CREATE SEQUENCE will "Specify how many values of the sequence the database preallocates and keeps in memory for faster access.".

                        But TopLink's doc implies that it is the INCREMENT BY parameter that is analagous to TopLink's "preallocation size" (i.e. "To avoid sequencing errors in the application, set the TopLink preallocation size and the Oracle SEQUENCE object INCREMENT to the same value. ).

                        If I specify "...CACHE 20 INCREMENT BY 1" in the database, and preallocation size 1 in TopLink (for a brand new sequence starting from 1), this will work. But do I need to synchronize the CACHE 20 behavior between the DB and TopLink?

                        Don't we *want* some sort of SEQUENCE caching in TopLink to forego the DB roundtrip?

                        TIA
                        gary
                        • 9. Re: Toplink Sequence - Preallocation in Oracle
                          DougClarke
                          The INCREMENT size must match TopLink's pre-allocation size. The CACHE setting in the database has no effect on TopLink. TopLink's sequence caching is done using the pre-allocation setting.

                          Doug
                          • 10. Re: Toplink Sequence - Preallocation in Oracle
                            275933
                            Doug,

                            Thanks for the prompt reponse. FYI, my client is looking to standardize on TopLink and I'm helping the developers write up a sort of 'TopLink Best Practices'.

                            Since the database default for 'CREATE SEQUENCE' has INCREMENT BY = 1, and the default for TopLink native sequencing is 50, it's obvious that I have to write up something for the developers and DBA's.

                            Instead of the usual:
                            CREATE SEQUENCE app_seq
                            START WITH 1
                            INCREMENT BY 1
                            CACHE 20
                            NOCYCLE;

                            I should be asking them to do:
                            CREATE SEQUENCE app_seq
                            START WITH 1
                            INCREMENT BY 50
                            NOCACHE
                            NOCYCLE;

                            and leave TopLink's default of 50.

                            TIA
                            gary
                            • 11. Re: Toplink Sequence - Preallocation in Oracle
                              DougClarke
                              Gary,

                              It really depends on the application. Some customers never want gaps in their sequences so they set it to 1. Others adjust the size based on the average quantity needed per transaction. Changing the pre-allocation size is pretty straight forward.

                              If creating the sequence to match TopLink I typically use:
                              CREATE SEQUENCE {NAME}
                              START WITH {PRE_ALLOC}
                              INCREMENT BY {PRE_ALLOC};
                              Doug