Re: Sequnce Cache Size in RAC

The only thing a sequence guarantees is a unique value. It never guarantees (transaction) order or “no gaps”, no matter what the settings.

Using ORDER or No CACHE is a scalability issue and has been said, RAC will perform worse and scale less well than a sequence in a single instance.

You do need to talk to the apps team to get a definitive requirement of what they are trying to achieve with this sequence. There are no solutions which guarantee transaction order and no-gaps that are also easily scalable. This is doubly true for RAC.

Neil.
sent from my phone

On 16 Feb 2022, at 07:39, Andy Sayer <andysayer_at_gmail.com> wrote:


The problem would be with the application, not the sequence, and this is only if it has this unscalable logic built in.

Yes, ordered sequence will behave more like a 0 cache sequence. An ordered sequence can only be provided if the nodes agree on the current value of the sequence. I’m order to do this, you’re going to be either hitting the network to talk to the nodes each time or you’ll be going to disk and using that as the source of truth. I’m not sure how Oracle has implemented it but it’s not going to be fantastic performance.

If you are working with RAC then the applications you are using will need to be able to accommodate this or you will be worse off (performance wise) than a single instance. You need to check with your application whether it can handle data that was inserted in not-necessarily sequence order. It really shouldn’t matter, but we’ve all seen bad assumptions in code before.

It’s the same with the gaps “problem”. You’re going to get gaps if you’re using a sequence (unless your code never fails, rows can’t be deleted, transactions can’t be rolled back), your application will need to be able to handle that. It handles that by not having assumptions about gapless sequences. This is easily said, but we’ve all seen the assumption made in code.

Thanks,
Andy

On Wed, 16 Feb 2022 at 07:04, Krishnaprasad Yadav <chrishna0007_at_gmail.com<mailto:chrishna0007_at_gmail.com>> wrote: Dear Experts ,

Thanks for reverting back and clearing the doubts . As per Andy such issue are mostly in No order sequence , so if sequence are ordered so this problem wont be their ,Is my understanding is correct?

Regards,
Krishna

On Wed, 16 Feb 2022 at 12:08, Jon Crisler <joncrisler_at_gmail.com<mailto:joncrisler_at_gmail.com>> wrote: 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<mailto: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<mailto: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<mailto: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/<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fjonathanlewis.wordpress.com%2F2022%2F02%2F07%2Fsequence-catalogue%2F&data=04%7C01%7C%7C63b683050e6f487aeac308d9f11f66ba%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637805939518688413%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=EsoOrnjjx9hYbFxil3%2FbvrlIRkMEwsKnX1qCrErdyt8%3D&reserved=0> and also this article maybe:
https://oracle-base.com/articles/18c/scalable-sequences-18c<https://emea01.safelinks.protection.outlook.com/?url=https%3A%2F%2Foracle-base.com%2Farticles%2F18c%2Fscalable-sequences-18c&data=04%7C01%7C%7C63b683050e6f487aeac308d9f11f66ba%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637805939518688413%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=dGmMv1dBQaUXs%2B0ORTlEA7HfMOHnAwAMVdXGO4ofl%2Fk%3D&reserved=0>

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<mailto: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