If you’ve used a sequence in this way for a table that’s subject to a high volume of activity – and particularly if it’s subject to highly concurrent activity – you want the sequence numbers to be supplied as quickly as possible. Performance issues (Single Instance)Īs I said at the start of this article, the commonest use of sequences is to generate synthetic keys. There is no resource penalty for setting a large cache for a sequence, and the most important fact about sequences is that, in most cases, you should probably be using a large cache size.
The “cache” isn’t a cache in the traditional sense, it’s just a single pair of numbers (no matter how large you set the sequence cache size): the current value and the value at which you bump the highwater value.
#THE SEQUENCE UPDATE#
When a call to s1.nextval returns the value shown in highwater ( highwater and nextvalue are the same), the session making the call will execute and commit a recursive transaction to add the increment value to the current highwater and update the seq$ table. Assuming I’ve just created a sequence called s1, here’s a suitable query followed by the result it gives me immediately after I created the sequence, followed by the result I get after one call to fetch s1.nextval:Īs you can see, the dynamic performance view has a column nextvalue that holds the value which will be supplied the next time a user calls for s1.nextval.Īll three views hold a “highwater” value (though it’s called “ last_number ” in view user_sequences). You can see the database image of a sequence by querying the view user_sequences.
#THE SEQUENCE SOFTWARE#
Behind the scenes, this is exactly what the Oracle software does, though being an internalized mechanism, it can “cheat” in ways that the home-grown code can’t. Unfortunately, Oracle refers to this “in-memory” mechanism as the cache – which is very misleading.Ī common strategy for “home-made” sequences is to have rows in a table with columns (sequence name, most recently used sequence number), then write a function that selects the relevant row from the table for update, increments the number, updates the table, and supplies the number to the end-user. Therefore, Oracle uses recursive, autonomous transactions to handle the table updates, and it avoids updating the table for every single call to nextval by counting in memory and remembering when it will next need to update the table. You ought to use an autonomous transaction so that nobody has to wait for the first session to commit its current transaction before the next number can be generated. However, that makes things very slow if you have to update a table each time someone needs the next number. The easiest way to do this is simply to keep a globally visible counter and increment it every time a session says: “give me a number” (or, more accurately, “tell me the value of sequence_name.nextval”).īut Oracle has to keep track of the last number supplied to avoid the risk of supplying the same number twice – that’s easy enough for a database system: just keep the most recent number in a table. Oracle invented sequences to make it possible to supply on demand a (virtually) limitless sequence of numbers that were guaranteed to be unique.
#THE SEQUENCE FULL#
Until 12c, the full syntax of the create sequence command is as follows: I’ll start by discussing the pre-12c use of sequences for single-instance, Oracle, then I’ll move on to the critical changes that are relevant to multi-instance RAC, and end with some comments on the features that appeared more recently in 12.1, 18c and 19c to deal particularly with “Application Continuity”, RAC and “Sharding”.
#THE SEQUENCE SERIES#
In this short series of articles, I aim to eliminate the most typical misunderstandings, warn you of the threats and provide ideas for workarounds. Despite a history of several decades, sequences are often misunderstood and misused – and there are a few threats involving sequences that Oracle Corp. For a very long time, the Oracle RDBMS has supplied the “sequence” mechanism to generate such values efficiently and with minimum contention between transactions. Many database applications make use of “meaningless ids” or “synthetic keys” rather than using columns of user data to construct the primary key for their tables.
0 kommentar(er)
