RE: Sequnce Cache Size in RAC: SEQUENCE extremely good JL talk reference

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 16 Feb 2022 13:36:08 -0500
Message-ID: <030201d82364$10defaa0$329cefe0$_at_rsiz.com>

I noticed the typo in the subject line, so I augmented the subject line in the hope that folks will be able to find the answers at some future point without a new lengthy thread.  

Good luck, and thanks JL for your excellent treatment of the subject.  

unique_id_generator is what they should have called it (in hindsight) to avoid the recurrent presumption by non-VFMRers that sequences are designed to be gapless or even easily strictly monotonically increasing in RAC.  

Sigh. That was compounded by sequences being chosen to replace unique_identifier values in EBiz in releases after V5 of the RDBMS. When on a version of the RDBMS with table locking granularity, it really didn’t matter that unique_identifier tables were a potential concurrency landmine, but some applications had already been written that presumed ordered, gapless numeric ids that is definitely NOT the designed purpose for sequences. Sigh. Again.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Wednesday, February 16, 2022 4:18 AM To: Krishnaprasad Yadav
Cc: Laurentiu Oprea; Oracle L
Subject: Re: Sequnce Cache Size in RAC    

https://www.red-gate.com/simple-talk/databases/oracle-databases/oracle-sequences-rac/  

Regards

Jonathan Lewis    

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

Received on Wed Feb 16 2022 - 19:36:08 CET