This discussion is archived
10 Replies Latest reply: Jan 20, 2013 8:05 AM by Aman.... RSS

Sequence cache

spur230 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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.... Oracle ACE
    Currently Being Moderated
    Thanks a bunch sir for the explanation and the demo. I think this is happening because of the CACHE clause,right?

    Regards
    Aman....

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points