Production server configuration is 4 node RAC. Version is 18.104.22.168. 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.
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..
> 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.
Suppose I query from all_sequences and i see a difference of 1000 (last number). Cache size for each instance will be 1000/4?