This discussion is archived
4 Replies Latest reply: Sep 10, 2013 5:55 AM by PVM RSS

Sequence caching in RAC

PVM Newbie
Currently Being Moderated

Hi All,

 

Production server configuration is 4 node RAC. Version is 11.2.0.3. Linux Platform. We are using sequences in DB.

 

I was reading some blogs on sequence working on RAC env. I came to know a sequence with cache noorder will improve the performance.But i have not seen how can we cache a sequence properly in RAC.Can you please let me know your suggestions for the below questions?

 

When we create any sequence, how can we cache a sequence properly on 4 instances?

How can we calculate the cache for each instance?

How shall we know whether sequence is using or not?

 

Thanks,

Mani

  • 1. Re: Sequence caching in RAC
    rp0428 Guru
    Currently Being Moderated
    Production server configuration is 4 node RAC. Version is 11.2.0.3. Linux Platform. We are using sequences in DB.

     

    I was reading some blogs on sequence working on RAC env. I came to know a sequence with cache noorder will improve the performance.But i have not seen how can we cache a sequence properly in RAC.Can you please let me know your suggestions for the below questions?

     

    When we create any sequence, how can we cache a sequence properly on 4 instances?

    How can we calculate the cache for each instance?

    How shall we know whether sequence is using or not?

    The 'user' doesn't cache the sequence, Oracle does. Just specify the CACHE value when you create the sequence.

     

    Oracle will use the SAME cache value for each instance. So if the cache value is 20 one instance might cache 1-20, the next instance cache 21-40 and so on. When an instance needs more values it will get the next set of 20 values that are available for that sequence.

     

    Which 'cache' gets used depends on which instance a query runs on. A query that executes on instance #2 will use the cache on instance #2.

  • 2. Re: Sequence caching in RAC
    PVM Newbie
    Currently Being Moderated

    Thanks for the reply...

     

    On what basis can we calculate the sequence cache?..Like when we create a sequence how can we specify the cache value..

  • 3. Re: Sequence caching in RAC
    Dom Brooks Guru
    Currently Being Moderated

    > On what basis can we calculate the sequence cache?

    On the basis of how many sequence numbers you expect to consume in a given period and how significant is the internal DML doing updates to seq$ table.

    Benchmark for your circumstances.

    If the workload at your given rate and cache size is significant then increase the cache size until you're happy.

  • 4. Re: Sequence caching in RAC
    PVM Newbie
    Currently Being Moderated

    Suppose I query from all_sequences and i see a difference of 1000 (last number). Cache size for each instance will be 1000/4?

     

    Thanks

Legend

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