Re: Sequnce Cache Size in RAC

It is generally a good performance boost to increase the cache size in RAC or Exa, but as Andy mentioned you need to make sure your application is tolerant of skipped values. This can sometimes be a problem if a RAC node crashes and the next value is not propagated. In earlier versions of RAC it was a bit hard to chase down performance issues due to non-cached sequences, but more recent versions make it easier to track down.

On Wed, Feb 16, 2022 at 1:04 AM Andy Sayer <andysayer_at_gmail.com> wrote:

> This is how a (default) no order sequence behaves. Eg if you set cache
> size 5 and had two nodes the following sequence values could be chosen in
> chronological order with uneven distribution of requests:
> Node 1: 1 (gets 1-5)
> Node 2: 6 (gets 6–10)
> Node 1: 2
> Node 1: 3
> Node 1: 4
> Node 1: 5
> Node 1: 11 (gets 11-15)
> Node 2: 7
>
> Is this a problem? Shouldn’t be. But if your application has a baked in
> assumption that sequence value is the same as order of insert then there
> can be logic that no longer works.
>
> Thanks,
> Andy
>
> On Wed, 16 Feb 2022 at 04:55, Krishnaprasad Yadav <chrishna0007_at_gmail.com>
> wrote:
>
>> Hi Experts,
>> Intention to raise this question was , some of my
>> colleagues suggested that increase in cache size from 0 to bigger number
>> can cause difference in cache value from both node .
>>
>> like in my case , cache size is 0 , and this is RAC environment , so in
>> this case , certain numbers are cached in node 1 and a separate set will be
>> cached for node 2 .
>>
>> so any maily insert using this sequence can cause issue of difference in
>> number
>>
>> Please share some light on this .
>>
>> Regards,
>> Krishna
>>
>>
>> On Tue, 15 Feb 2022 at 16:45, Laurentiu Oprea <
>> laurentiu.oprea06_at_gmail.com> wrote:
>>
>>> Hello Krishna,
>>>
>>> Your question is really incomplete, thus the answers you are getting are
>>> in consequence.
>>>
>>> You might need to provide details on:
>>> -> why the cache was initially set to 0
>>> -> what drives the motivation to increase the cache and what kind of
>>> investigations was undertaken to conclude that the issues will be solved by
>>> this
>>> -> what version your database is running
>>>
>>> You might want to read these articles by Jonathan Lewis:
>>> https://jonathanlewis.wordpress.com/2022/02/07/sequence-catalogue/
>>> and also this article maybe:
>>> https://oracle-base.com/articles/18c/scalable-sequences-18c
>>>
>>> Thanks.
>>>
>>> PS: to answer your specific question: increasing cache size for
>>> sequences in your environment may or may not create issues.
>>>
>>> În mar., 15 feb. 2022 la 12:31, Krishnaprasad Yadav <
>>> chrishna0007_at_gmail.com> a scris:
>>>
>>>> Hi Experts ,
>>>>
>>>> We are proactively trying to increase sequence cache size , currently
>>>> the database is in RAC .
>>>> I need to know if there is a RAC environment , increase in CACHE size
>>>> of sequence makes any difference . Currently the value is cache_size is 0
>>>> Does changing value from 0 to 50 of cache_size can cause any issue
>>>> w.r.t to application .
>>>>
>>>>
>>>> Regards,
>>>> Krishna
>>>>
>>>>
>>>>
>>>>
>>>>

--
http://www.freelists.org/webpage/oracle-l