1 2 Previous Next 18 Replies Latest reply on Jul 19, 2012 7:44 PM by 802709

    Implications/side effects of changing cache size of sequence

    orausern
      Hi Experts,

      We are on Oracle 11.2.0.2 on Solaris 10. I have a question about sequences. They are defined as NOCACHE in my database and we are seeing a lot of "row cache lock". So I am thinking of changing the sequences to cache 20 values. Does this have any other implication -except that during db restart the cached values can be lost.

      Thanks,
        • 1. Re: Implications/side effects of changing cache size of sequence
          Osama_Mustafa
          No impact on that even it will avoid the overhead of extra physical I/O and give tremendous performance boost to your system
          • 2. Re: Implications/side effects of changing cache size of sequence
            Osama_Mustafa
            Handle:      orausern
            Status Level:      Newbie (40)
            Registered:      Mar 27, 2006
            Total Posts:      405
            Total Questions:      119 (81 unresolved)
            Name      nirav


            What is this !!!!!!!!!!!!!!!
            try to close your thread at least .
            • 3. Re: Implications/side effects of changing cache size of sequence
              802709
              I dont think that row cache lock event is relevant with sequence cache size.

              sequence cache is useful when you get lots of value from sequence in a plsql code (loop). this is not an exact side effect but just consider: for ex

              when a session get a value from sequence and sequence cache is 20, next 20 value of sequence will be reserve for your session, other words, if you get 450 as value of sequence next 20 value (untill 470) will be yours to, if someone else try to get a value (after you get 450) other session will get 471.
              that is the only side effect if that counts and this is avaliable if you use sequence in a PLSQL code like loop.

              so, if you get lots of value in a loop, you should set cache size because if you dont you will see lots of log file sync event. if your plsql codes doesnt use sequences much, then it is not necessary.

              ps: these info is valid for single instance. on a rac system it will be different.
              • 4. Re: Implications/side effects of changing cache size of sequence
                VenkatB
                so, if you get lots of value in a loop, you should set cache size because if you dont you will see lots of log file sync event. if your plsql codes doesnt use sequences much, then it is not necessary.
                What has sequence or sequence cache got anything to do with log file sync waits? Log file sync is a client event where the user processes wait while LGWR flushes the redo logs. It's caused most probably by frequent commits (especially commits within a loop)

                Sequence caching is always useful in applications when concurrent sessions request sequence value where Oracle has to update SEQ$ data dictionary. This will cause heavy contention in database particularly when there are concurrent users working on it. All sequences have to be cached, unless, the application (poorly written one) cannot afford to have any gaps within the primary key value or any other keys maintained by sequences

                Regards
                Venkat
                • 5. Re: Implications/side effects of changing cache size of sequence
                  802709
                  @VenkatB, when you get a value from sequence, to provide consistency, sequence execute an internal commit, works like kind of autonomous transaction.
                  edit: this is also how, after a crash sequence still get his last value.

                  that is because if you dont set cache and use in a loop the sequence, that cause to log file sync event. (of course you must get many values from sequence)

                  edit:

                  Edited by: Mustafa KALAYCI on 19.Tem.2012 17:17
                  • 6. Re: Implications/side effects of changing cache size of sequence
                    AdamMartin
                    Mustafa KALAYCI wrote:
                    when a session get a value from sequence and sequence cache is 20, next 20 value of sequence will be reserve for your session, other words, if you get 450 as value of sequence next 20 value (untill 470) will be yours to, if someone else try to get a value (after you get 450) other session will get 471.
                    No. Sequences do not work this way.

                    The sequence cache is instance-wide, not session specific.

                    The DB pulls into memory two numbers (regardless of the size of the cache). It pulls in the next value of the sequence and another number that is the next value + cache size (-1) of the sequence. When the numbers match, the DB does an autonomous commit and gets the next range of values. With a cache of 20, if the next value is 451, the DB will have in memory 2 high-low numbers (for a total range of 20 reserved sequence values) of 451 and 470. Then it will have 452 and 470; then 453 and 470; etc... incrementing the low value until it reaches the high value. Any session can grab the next value.
                    • 9. Re: Implications/side effects of changing cache size of sequence
                      VenkatB
                      @VenkatB, when you get a value from sequence, to provide consistency, sequence execute an internal commit, works like kind of autonomous transaction.
                      edit: this is also how, after a crash sequence still get his last value.

                      that is because if you dont set cache and use in a loop the sequence, that cause to log file sync event. (of course you must get many values from sequence)
                      >

                      Prove it.

                      Log file sync may only be incidental but may happen only from the application code when they get sequence and then do a commit within a loop. In fact, log file sync need not happen just because it's committed in loop.

                      When user process gets sequence value, Oracle updates SEQ$ with the value to update the value. In fact, Oracle does it for almost all of the foreground code. When Oracle has to add an extent, when Oracle has to update a new datafile header, when Oracle has to autoextend a file, etc...it always inserts/updates one of the dictionary views all the time. So it's nothing new or just with sequence.

                      And don't confuse yourself with a timed event with a wait.

                      I have tested on a 11g and 10g and I don't see any log file sync waits in tkprof or v$session_event
                      SQL> create sequence seq_test start with 1 nomaxvalue nocache;
                      
                      Sequence created.
                      
                      SQL> Declare
                        2   v_cnt number;
                        3  Begin
                        4   For i in 1..10000
                        5   Loop
                        6     select seq_test.nextval into v_cnt from dual;
                        7   End Loop;
                        8  End;
                        9  /
                      
                      PL/SQL procedure successfully completed.
                      • 10. Re: Implications/side effects of changing cache size of sequence
                        802709
                        @VenkatB, if I am not wrong, what I said is from Tom Kyte's book. when i find it i will write here. also I had tests about seqs too and I was able to see log file sync event in my top events (and some other log events too). also your code is too small, there is almost nothing to create redo log. while running with a highly loaded plsql code (dml operations in the loop) and after using sequences probably will cause to what i said.

                        @Adam Martin, I had some tests about that too maybe I'm wrong but I will re-check my codes and documentation and if wrong, i will make myself correct.
                        • 11. Re: Implications/side effects of changing cache size of sequence
                          VenkatB
                          while running with a highly loaded plsql code (dml operations in the loop) and after using sequences probably will cause to what i said.
                          Yesssss, this is what I said. If you had sequence call and an insert within the loop, it's possible that the insert will cause log file sync waits.

                          See what I had said before

                          Log file sync may only be incidental but may happen only from the application code when they get sequence and then do a commit within a loop. In fact, log file sync need not happen just because it's committed in loop.

                          My argument was that Sequence or the sequence cache had nothing to do with log file sync waits directly. But said it would indeed cause contention when multiple sessions access the same sequence in parallel in a busy environment.

                          Regards
                          Venkat
                          • 13. Re: Implications/side effects of changing cache size of sequence
                            802709
                            @Adam Martin, you are right. this is my mistake. as you said caching is instance wide. probably my test cases were wrong.

                            @VenkatB, I understand what you mean know but I think I'm right too, because just an insert statement within a loop of course cause log file sync but what I mean is sequence will increase that if nocache is specified. as you know every commit will cause to write redo entries to log files. so dml and nocache sequence will increase log events. that's what i mean and (if i dont get wrong you) what you and me saids are not opposite.

                            Edited by: Mustafa KALAYCI on 19.Tem.2012 18:14
                            • 14. Re: Implications/side effects of changing cache size of sequence
                              Jonathan Lewis
                              Mustafa KALAYCI wrote:
                              @VenkatB, if I am not wrong, what I said is from Tom Kyte's book. when i find it i will write here. also I had tests about seqs too and I was able to see log file sync event in my top events (and some other log events too). also your code is too small, there is almost nothing to create redo log. while running with a highly loaded plsql code (dml operations in the loop) and after using sequences probably will cause to what i said.

                              @Adam Martin, I had some tests about that too maybe I'm wrong but I will re-check my codes and documentation and if wrong, i will make myself correct.
                              You are wrong about the caching - it is, as Adam Martin explains, instance wide.

                              You are basically right about the log file sync - if your sequence is defined as NOCACHE a call to nextval updates seq$ and commits. The reason that Venkat doesn't seen any log file sync waits, though, is because of the hidden pl/sql "optimisation" that avoids sending a sync message to lgwr until the entire database call (in this case the pl/sql loop) is complete. If the loop was being operated in some 3 GL code then every nextval would result in a log file sync wait.

                              As far as the OP is concerned - I would have expected the overheads relating to database calls (update seq$) and redo generation effects (with or without log file sync) to be more significant than the row cache locks, although the row cache lock wait are highlly likely when you have concurrent processes updating seq$ on the same row very frequently.

                              Regards
                              Jonathan Lewis
                              1 2 Previous Next