10 Replies Latest reply: Jan 20, 2013 10:05 AM by Aman.... RSS

    Sequence cache

    spur230
      I am using Oracle 11.2.0.4. I need to new primary key for a table which already had 10 million rows. I will be creating a new table with CTAS and a sequence.

      During my testing I found, increasing sequence cache size from 20 to 200 helped me decrease elapsed time of CTAS statement from 2 minutes to 17 seconds. There was no difference in further increasing the cache size.

      Is there any caveat in increasing the sequence cache size? Should I reduce the sequence cache size back to default 20 after the initial load of the table?

      Thanks for your time
        • 1. Re: Sequence cache
          Srini Chavali-Oracle
          AFAIK, 11.2.0.4 has not yet been released. Do you mean 10.2.0.4 ?

          Pl read here about sequence caches - http://docs.oracle.com/cd/E11882_01/server.112/e25494/views002.htm#sthref2635 - since sequence numbers are accessed from cache rather than disk, it explains your performance improvement.

          You can reduce the cache back to its original value. Cached values are lost when the database is bounced. (Edit - this is incorrect - pl see below !)

          HTH
          Srini

          Edited by: Srini Chavali on Jan 20, 2013 8:21 AM
          • 2. Re: Sequence cache
            rp0428
            >
            Should I reduce the sequence cache size back to default 20 after the initial load of the table?
            >
            Yes - a common practice, even for existing sequences, is to ALTER the CACHE setting prior to large loads and then to ALTER it back to the previous value after the load is done.

            There aren't really any caveats for that type of use and manipulation.
            • 3. Re: Sequence cache
              Jonathan Lewis
              Srini Chavali wrote:

              Pl read here about sequence caches - http://docs.oracle.com/cd/E11882_01/server.112/e25494/views002.htm#sthref2635 - since sequence numbers are accessed from cache rather than disk, it explains your performance improvement.
              That page (up to the bit about dropping sequences, which is where I stopped reading) is appalling. Whoever wrote it seems to have no idea about how Oracle handles sequences. The only bit that's right is the "big picture" that says that if you increase the CACHE parameter for a sequence it causes less I/O (and even then the reduction is in updates and redo, not in reads).

              It doesn't help, of course, that there are two different uses of the word "cache" with two very different meanings on the page (and a third, which could be mentioned but isn't).

              Cached values are lost when the database is bounced.
              Only if the shutdown is a crash or an abort. Oracle changed the code a long time ago so that when sequences are ejected from the library cache their entry in the seq$ table is updated to protect the gap.


              Regards
              Jonathan Lewis
              • 4. Re: Sequence cache
                Jonathan Lewis
                spur230 wrote:

                Is there any caveat in increasing the sequence cache size?
                Only that someone might complain about the size of the gaps that could appear.
                Should I reduce the sequence cache size back to default 20 after the initial load of the table?
                There's no technical reason why you need bother.

                Regards
                Jonathan Lewis
                • 5. Re: Sequence cache
                  Aman....
                  Only if the shutdown is a crash or an abort. Oracle changed the code a long time ago so that when sequences are ejected from the library cache their entry in the seq$ table is updated to protect the gap.
                  Sir,

                  Does that mean that if I ,

                  1) Create a sequence with some Cache.
                  2) Use a loop to insert some values within the cache in the table
                  3) Do the flush of shared pool
                  4) Shutdown the database

                  From next time, I shall be able to get the sequence started from the same values from where I started without the gap?

                  Regards
                  Aman....

                  PS: Wish your back pain becomes better ASAP.
                  • 6. Re: Sequence cache
                    Srini Chavali-Oracle
                    Appreciate your comments Jonathan :-) May I suggest that you give feedback directly in the comments section at the bottom of that doc link (if you have not already) ? If the documentation is incorrect/incomplete/unclear, I think it is incumbent upon us to point it out and make it better - and Oracle is more likely than not to pay attention :-)

                    As far as sequence cached values not being lost upon an expected and orderly shutdown, do you know when that "feature" was introduced ? I will perform a simple test on 11.2.0.3 and post the results here.

                    Thanks
                    Srini
                    • 7. Re: Sequence cache
                      Jonathan Lewis
                      Srini Chavali wrote:
                      Appreciate your comments Jonathan :-) May I suggest that you give feedback directly in the comments section at the bottom of that doc link (if you have not already) ? If the documentation is incorrect/incomplete/unclear, I think it is incumbent upon us to point it out and make it better - and Oracle is more likely than not to pay attention :-)
                      Sometimes I do leave corrections - but sometimes (as in this case) the work required to point out and correct the errors is too great, and there's no point in simply writing in a note that it's bad.
                      As far as sequence cached values not being lost upon an expected and orderly shutdown, do you know when that "feature" was introduced ? I will perform a simple test on 11.2.0.3 and post the results here.
                      Here's a continuous cut-n-paste from 8.1.7.4 - the change happened a LONG time ago.
                      SQL> select s1.nextval from dual;
                      
                         NEXTVAL
                      ----------
                              22
                      
                      1 row selected.
                      
                      SQL> /
                      
                         NEXTVAL
                      ----------
                              23
                      
                      1 row selected.
                      
                      SQL> /
                      
                         NEXTVAL
                      ----------
                              24
                      
                      1 row selected.
                      
                      SQL> connect / as sysdba
                      Connected.
                      SQL> shutdown
                      Database closed.
                      Database dismounted.
                      ORACLE instance shut down.
                      SQL> startup
                      ORACLE instance started.
                      
                      Total System Global Area  290846748 bytes
                      Fixed Size                    75804 bytes
                      Variable Size             156020736 bytes
                      Database Buffers          134217728 bytes
                      Redo Buffers                 532480 bytes
                      Database mounted.
                      Database opened.
                      SQL> connect test_user/test
                      Connected.
                      SQL> select s1.nextval from dual;
                      
                         NEXTVAL
                      ----------
                              25
                      
                      1 row selected.
                      
                      SQL> select sequence_name, cache_size from user_sequences;
                      
                      SEQUENCE_NAME                  CACHE_SIZE
                      ------------------------------ ----------
                      S1                                     20
                      
                      1 row selected.
                      
                      SQL>
                      (You'll have to trust me that I did the first few selects just after startup.)

                      Regards
                      Jonathan Lewis
                      • 8. Re: Sequence cache
                        Jonathan Lewis
                        Aman.... wrote:

                        3) Do the flush of shared pool

                        From next time, I shall be able to get the sequence started from the same values from where I started without the gap?
                        Aman,

                        Sorry, my wording was bad. I was thinking only of shutting down.

                        I don't think that the sequence high value is preserved if the sequence is kicked out of the library cache under memory pressure, and I'm fairly certain that values will be lost if you do a "flush shared_pool". (Both actions indicate a need for urgency, and writing the high value back takes time.)

                        Regards
                        Jonathan Lewis
                        • 9. Re: Sequence cache
                          Srini Chavali-Oracle
                          Thanks Jonathan - I can indeed verify your test case (not that I doubted it !)
                          $ sqlplus / as sysdba
                          
                          SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 20 08:02:01 2013
                          
                          Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                          
                          Connected to an idle instance.
                          
                          SQL> startup;
                          ORACLE instance started.
                          
                          Total System Global Area  835104768 bytes
                          Fixed Size                  2232960 bytes
                          Variable Size             473959808 bytes
                          Database Buffers          356515840 bytes
                          Redo Buffers                2396160 bytes
                          Database mounted.
                          Database opened.
                          
                          SQL> select * from v$version;                                                                 
                          
                          BANNER
                          --------------------------------------------------------------------------------
                          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                          PL/SQL Release 11.2.0.3.0 - Production
                          CORE    11.2.0.3.0      Production
                          TNS for Linux: Version 11.2.0.3.0 - Production
                          NLSRTL Version 11.2.0.3.0 - Production
                          
                          SQL> create user foobar identified by foobar default tablespace users temporary tablespace temp;
                          
                          User created.
                          
                          SQL> grant dba to foobar;
                          
                          Grant succeeded.
                          
                          SQL> connect foobar/foobar;
                          Connected.
                          
                          SQL> create sequence test_seq start with 1 cache 100;
                          
                          Sequence created.
                          
                          SQL> select test_seq.nextval from dual;
                          
                             NEXTVAL
                          ----------
                                   1
                          
                          SQL> /
                          
                             NEXTVAL
                          ----------
                                   2
                          
                          SQL> /
                          
                             NEXTVAL
                          ----------
                                   3
                          
                          SQL> connect / as sysdba;
                          Connected.
                          
                          SQL> shutdown;
                          Database closed.
                          Database dismounted.
                          ORACLE instance shut down.
                          
                          SQL> startup;
                          ORACLE instance started.
                          
                          Total System Global Area  835104768 bytes
                          Fixed Size                  2232960 bytes
                          Variable Size             473959808 bytes
                          Database Buffers          356515840 bytes
                          Redo Buffers                2396160 bytes
                          Database mounted.
                          Database opened.
                          
                          SQL> connect foobar/foobar
                          Connected.
                          
                          SQL> select test_seq.nextval from dual;
                          
                             NEXTVAL
                          ----------
                                   4
                          
                          SQL> select sequence_name, cache_size from user_sequences;
                          
                          SEQUENCE_NAME                  CACHE_SIZE
                          ------------------------------ ----------
                          TEST_SEQ                              100
                          
                          SQL> 
                          SQL> exit
                          Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          At this point, I killed the smon process at the OS level (simulating an instance crash).
                          $ sqlplus / as sysdba
                          
                          SQL*Plus: Release 11.2.0.3.0 Production on Sun Jan 20 08:08:26 2013
                          
                          Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                          
                          Connected to an idle instance.
                          
                          SQL> startup;
                          ORACLE instance started.
                          
                          Total System Global Area  835104768 bytes
                          Fixed Size                  2232960 bytes
                          Variable Size             473959808 bytes
                          Database Buffers          356515840 bytes
                          Redo Buffers                2396160 bytes
                          Database mounted.
                          Database opened.
                          
                          SQL> connect foobar/foobar
                          Connected.
                          
                          SQL> select test_seq.nextval from dual;
                          
                             NEXTVAL
                          ----------
                                 104
                          
                          SQL> select sequence_name, cache_size from user_sequences;
                          
                          SEQUENCE_NAME                  CACHE_SIZE
                          ------------------------------ ----------
                          TEST_SEQ                              100
                          
                          SQL> select test_seq.nextval from dual;
                          
                             NEXTVAL
                          ----------
                                 105
                          
                          SQL> exit
                          Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                          With the Partitioning, OLAP, Data Mining and Real Application Testing options
                          $ 
                          I was not aware that this was introduced as far back as 8i. Thanks :-)

                          Srini
                          • 10. Re: Sequence cache
                            Aman....
                            Thanks a bunch sir for the explanation and the demo. I think this is happening because of the CACHE clause,right?

                            Regards
                            Aman....