Imagine you have created a sequence with the basic commandĪs demonstrated in the previous article, Oracle creates a row in the seq$ table with highwater = 1 and cache = 5000. The other is the order option, where the instances continuously negotiate through the use of global enqueues to determine which instance should be responsible for the sequence at any moment. How do the instances co-ordinate their use of sequence values and avoid the risk of two instances using the same value? There are two solutions: the default noorder mechanism where each instance behaves as if it doesn’t know about the other instances. ![]() There is another issue with sequences and RAC, though, that has to be considered before worrying about possible hot spots. Every session that’s inserting consecutive values into an index will be trying to access the same “right-hand / high-value” index leaf block.Refreshing the sequence “cache” updates a specific row in table sys.seq$.This generic issue turns into two specific issues for sequences: Whenever you move from single-instance to multi-instance, the fundamental problem is how to avoid excessive competition between instances for “popular” data blocks. The answer arrived shortly afterwards: this is due to (unpublished) bug 31423645, fixed in Oracle 19.11 with back-ports possible for earlier versions of Oracle.” RAC NOTE: Literally minutes after I had emailed the final draft of this article to the Simple Talk editor, a question came up on the Oracle-L list server asking why duplicate values were appearing from a sequence with scale and extend enabled when the queries accessing it were running parallel. I’ll end the article by mentioning the newer features of sequences that appeared in the upgrades from 12c to 19c, emphasizing one RAC scalability feature. In this article, I’ll spend most of my time explaining how the problems can get worse (and be addressed) as you move from single-instance Oracle to multi-instance Oracle (RAC). ![]() synthetic/surrogate keys) and the mechanical threats (cache size and index contention) that still had to be addressed by the developer/DBA. In my first article on Sequences, I reviewed the fundamental reason why Oracle Corp. Oracle sequences – 12c features including “identity”.
0 Comments
Leave a Reply. |