{"id":90455,"date":"2021-03-26T18:06:05","date_gmt":"2021-03-26T18:06:05","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=90455"},"modified":"2021-07-14T13:06:50","modified_gmt":"2021-07-14T13:06:50","slug":"oracle-sequences-the-basics","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-sequences-the-basics\/","title":{"rendered":"Oracle sequences: The basics"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Oracle sequences: The basics<\/a><\/li>\n
  2. Oracle sequences: RAC<\/a><\/li>\n
  3. Typical uses of Oracle sequences<\/a><\/li>\n
  4. Oracle sequences \u2013 12c features including \u201cidentity\u201d<\/a>\u00a0<\/li>\n<\/ol>\n\n

    Many database applications make use of \u201cmeaningless ids\u201d or \u201csynthetic keys\u201d rather than using columns of user data to construct the primary key for their tables. For a very long time, the Oracle RDBMS has supplied the \u201csequence\u201d<\/em> mechanism to generate such values efficiently and with minimum contention between transactions. Despite a history of several decades, sequences are often misunderstood and misused \u2013 and there are a few threats involving sequences that Oracle Corp. is still working to minimize.<\/p>\n

    In this short series of articles, I aim to eliminate the most typical misunderstandings, warn you of the threats and provide ideas for workarounds. I\u2019ll start by discussing the pre-12c use of sequences for single-instance, Oracle, then I\u2019ll 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 \u201cApplication Continuity\u201d, RAC and \u201cSharding\u201d.<\/p>\n

    Defining a sequence<\/h2>\n

    Until 12c, the full syntax of the create sequence<\/em><\/strong> command is as follows:<\/p>\n

    CREATE SEQUENCE [schema_name.]{sequence_name}\r\n\tSTART WITH      n\r\n\tINCREMENT BY    n\r\n\t[MINVALUE n | NOMINVALUE ]\r\n\t[MAXVALUE n | NOMAXVALUE ]\r\n\t[CACHE n | NOCACHE]\r\n\t[ORDER | NOORDER]\r\n\t[CYCLE | NOCYCLE]<\/pre>\n
    The shortest statement you could use to create a sequence, however, would be simply:<\/p>\n
    CREATE SEQUENCE sequence_name;<\/pre>\n
    This command would be equivalent to:<\/p>\n
    CREATE SEQUENCE sequence_name\r\n\tSTART WITH      1\r\n\tINCREMENT BY    1\r\n\tMINVALUE        1\r\n\tNOMAXVALUE -- which is effectively 1e28 - 1\r\n\tCACHE           20    -- which is usually a bad idea, as we shall see later\r\n\tNOORDER\r\n\tNOCYCLE<\/pre>\n
    Oracle invented sequences to make it possible to supply on demand a (virtually) limitless sequence of numbers that were guaranteed to be unique. The easiest way to do this is simply to keep a globally visible counter and increment it every time a session says: \u201cgive me a number\u201d (or, more accurately, \u201ctell me the value of sequence_name.nextval<\/code>\u201d).<\/p>\n
    But Oracle has to keep track of the last number supplied to avoid the risk of supplying the same number twice \u2013 that\u2019s easy enough for a database system: just keep the most recent number in a table. However, that makes things very slow if you have to update a table each time someone needs the next number. 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. Therefore, Oracle uses recursive, autonomous transactions to handle the table updates, and it avoids updating the table for every single call to nextval<\/code> by counting in memory<\/strong> and remembering when it will next need to update the table. Unfortunately, Oracle refers to this \u201cin-memory\u201d mechanism as the cache \u2013 which is very misleading.<\/p>\n
    A common strategy for \u201chome-made\u201d 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. Behind the scenes, this is exactly what the Oracle software does, though being an internalized mechanism, it can \u201ccheat\u201d in ways that the home-grown code can\u2019t.<\/p>\n
    You can see the database image of a sequence by querying the view user_sequences<\/code>. Assuming I\u2019ve just created a sequence called s1,<\/em><\/strong> here\u2019s 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<\/code>:<\/p>\n
    select\r\n        min_value, max_value, \r\n        increment_by, cache_size, last_number, \r\n        cycle_flag, order_flag\r\nfrom \r\n        user_sequences\r\nwhere \r\n        sequence_name = 'S1'\r\n\/<\/pre>\n
    <\/p>\n
    If you have DBA privileges, you could go one step further by finding the object_id<\/code> of the sequence and then querying the dictionary table (seq$<\/code>) that the view is based on. Again, I\u2019ve run the query twice, once before and once just after the first call to s1.nextval):<\/code><\/p>\n
    select \r\n        minvalue, maxvalue, increment$, cache, highwater, cycle#, order$ \r\nfrom \r\n        seq$ \r\nwhere\r\n        obj# = 124874\r\n;<\/pre>\n
    <\/p>\n
    There\u2019s one final option for checking the state of a sequence \u2013 the in-memory information that is visible to privileged accounts through the dynamic performance view v$_sequences<\/code> (the unexpected underscore isn\u2019t a typo). In this case, I\u2019ve queried the view just once, after the first call to s1.nextval<\/code> (the sequence won\u2019t even be in the view until the first call to nextval),<\/code> and I\u2019ve included a column that isn\u2019t available in the dictionary table:<\/p>\n
    select\r\n        min_value, max_value,\r\n        increment_by, cache_size, highwater,\r\n        cycle_flag, order_flag, nextvalue\r\nfrom\r\n        v$_sequences\r\nwhere\r\n        object# = 124874\r\n\/<\/pre>\n
     <\/p>\n
    <\/p>\n
    As you can see, the dynamic performance view has a column nextvalue<\/code> that holds the value which will be supplied the next time a user calls for s1.nextval<\/code>.<\/p>\n
    All three views hold a \u201chighwater\u201d<\/em> value (though it\u2019s called \u201c<\/em>last_number<\/code>\u201d<\/em> in view user_sequences<\/code>). When a call to s1.nextval<\/em><\/strong> returns the value shown in highwater<\/code> (highwater<\/code> and nextvalue<\/code> are the same), the session making the call will execute and commit a recursive transaction to add the increment<\/code> value to the current highwater<\/code> and update the seq$<\/code> table.<\/p>\n
    The \u201ccache\u201d isn\u2019t a cache in the traditional sense, it\u2019s 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<\/code> value. 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.<\/p>\n
    Performance issues (Single Instance)<\/h2>\n
    As I said at the start of this article, the commonest use of sequences is to generate synthetic keys. If you\u2019ve used a sequence in this way for a table that\u2019s subject to a high volume of activity \u2013 and particularly if it\u2019s subject to highly concurrent activity \u2013 you want the sequence numbers to be supplied as quickly as possible. You don\u2019t want to have to pause on every 20th<\/sup> call for a data dictionary table to be updated, and you don\u2019t want to wait because 50 other sessions are all colliding on the same sequence maintenance.<\/p>\n
    Cache size<\/h3>\n
    It\u2019s quite easy to recognize this issue in single-instance Oracle as you\u2019ll see sessions in v$lock<\/code> waiting on the \u201c<\/em><\/strong>SQ<\/code>\u201d<\/em><\/strong> enqueue (lock) in mode 4, and the reported wait event will be \u201c<\/em>enq: SQ - contention<\/code>\u201d<\/em>, with p1 <\/code>set to the object_id<\/code> of the sequence they\u2019re waiting for. The solution to this problem is simply to increase the cache size, and it\u2019s not uncommon on very busy systems to see sequences with caches in the thousands:<\/p>\n
    alter sequence s1 cache 10000;<\/pre>\n
    Given that it\u2019s often important for performance and concurrency to increase the cache size of a sequence from the default 20, you should appreciate that it\u2019s a bad idea to create a single sequence to supply values for every single column that needs unique meaningless values. Play safe \u2013 give each synthetic key its own sequence. (Some other RDBMS do this automatically through an \u201cidentity\u201d data type, but that feature has been introduced only in recent versions of Oracle.)<\/p>\n
    This default performance problem is sometimes made worse by a demand from the end-users that \u201cthere should be no missing numbers\u201d<\/em> which leads to DBAs setting the sequence to nocache<\/code>. In other words, every call to nextval<\/code> results in the highwater<\/code> being incremented and the seq$<\/code> table being updated. This can be a massive source of contention and could easily end up as the key bottleneck in the system \u2013 and to make things worse, it doesn\u2019t stop numbers from going missing (e.g. a session might have to rollback or might crash after its call to nextval<\/code>, and the value would then be lost).<\/p>\n
    Oracle sequences are not intended to provide an unbroken sequence of values; they are only intended to supply a unique set of numbers. If you need some form of sequential audit trail (and you want it to be scalable) then you will have to do some fairly careful coding to create your own \u2013 it\u2019s not a trivial exercise.<\/p>\n
    Index Contention<\/h3>\n
    There is another common performance issue that is a byproduct of using sequences to generate unique ids: if uniqueness is important, you ought to declare a constraint on the relevant column, and a unique (or primary key) constraint will automatically result in Oracle creating a corresponding index. The index will become a point of contention because all the sessions that are trying to insert a new row will have generated sequence numbers which are (a) very close to each other and (b) the highest values generated so far; this means you will have multiple sessions trying to insert new values into the same (so-called \u201cright-hand\u201d) leaf block of the index.<\/p>\n
    In these circumstances, you\u2019re likely to see a lot of waits for \u201cbuffer busy wait\u201d<\/em> on the index; you may also see waits for \u201cenq: TX – index contention\u201d<\/em>, and even \u201cenq: TX – allocate ITL entry\u201d <\/em>as sessions queue up waiting for an index leaf block split to complete. Unfortunately, there\u2019s no good solution to this problem, though there are damage limitation mechanisms you could implement, and one of the enhancements Oracle 18c supplies is a declarative version of one of the commoner mechanisms.<\/p>\n
    Once upon a time, a common suggestion to avoid the index hot-spot was to recreate the index as a \u201creverse-key\u201d index but, for continuously growing tables, that\u2019s a bad idea as it typically replaces a few seconds of \u201cbuffer busy wait\u201d<\/em> with minutes (or more) of extra \u201cdb file sequential read\u201d<\/em> waits. (For an explanation of this comment, see: https:\/\/jonathanlewis.wordpress.com\/2015\/06\/17\/reverse-key-2\/<\/a> )<\/p>\n
    If you\u2019re licensed for the partitioning option, a better way to remove the hot spot is to recreate the index as a globally hash partitioned index \u2013 with the usual proviso that the number of partitions should be a power of 2 to ensure equal sized partitions. There are some optimizer-related drawbacks to partitioning an index in this way, but since we\u2019re talking about the use of sequences for synthetic (meaningless) keys, the only queries you expect to see targeting the index are likely to be of the form: unique_key = {constant},<\/code> and the optimizer is unlikely to be misled by a query with a predicate of that type.<\/p>\n
    The partitioning option is expensive, of course, and isn\u2019t available on Standard Edition, so the manually coded fall back is spread the hot spot by manipulating the sequence number after you\u2019ve fetched it. First, you have to pick a number that is so large that you think the sequence will never need to reach it (say 1e10). Then you add a session-dependent value to the sequence number that makes sequence values that are close to each other end up widely separated in the index because they were inserted by different sessions. For example, you might add (1 + mod(sid, 16)) * 1e10<\/code> to the sequence value; this will give you most of the performance benefit of hash partitioning your index into 16 partitions \u2013 splitting your one hot-spot into 16 \u201cslightly-warm\u201d spots.<\/p>\n
    Inevitably there\u2019s a price to pay for this scaling mechanism. In an ideal world, an index on data generated by a sequence would use close to 100% of the available space in each index leaf block because Oracle would be doing what it calls \u201cleaf node 90-10 splits\u201d<\/em> each time the right-hand (high value) leaf block split. If you spread the inserts across N different insertion points in the index (using 1+mod(sid,N)<\/code> as above), then all but the highest insertion point would be doing \u201c50-50\u201d<\/em> leaf node splits.<\/p>\n
    Although Oracle has a statistic in the instance and session activity statistics (v$sysstat \/ v$sesstat)<\/code> for \u201cleaf node 90-10 splits\u201d, a better name would be \u201cleaf node 100-0 splits\u201d. The \u201csplit\u201d simply adds a leaf block at the right-hand (high-value) end of the index and carries on into it; it doesn\u2019t copy any data from the preceding leaf block. By comparison, the \u201cnormal\u201d mid-index leaf node split links a new leaf block into the right place then moves roughly half the data from the existing leaf block into the new leaf block.<\/p>\n
    The side effect of manually \u201cpseudo-partitioning\u201d an index in this way is that it might operate at roughly twice its previous size. (And that\u2019s not allowing for the fact that you now have an index where the smallest entry is a few bytes longer than the largest entry used to be.) In real-life, there are a couple of odd concurrency effects that can make any index with hot spots waste a significant amount of space, so the change may not be quite as dramatic as the theory predicts.<\/p>\n
    Miscellaneous<\/h2>\n
    I\u2019ll finish this article with a brief round-up of the remaining, less commonly used features of the pre-12c sequence.<\/p>\n
    Start with<\/strong>: you can specify any legal integer value as the first value to use, positive or negative; the default is 1.<\/p>\n
    Increment by<\/strong>: although I\u2019ve been discussing sequences that increase by 1, you can set the increment to any legal integer value (positive or negative). So if you wanted a sequence of the positive even numbers you could specify \u201cstart with 2 increment by 2\u201d. The default increment is 1.<\/p>\n
    Minvalue<\/strong>: if your increment is negative, you can set a value below<\/strong> which Oracle raises an error (ORA-08004: sequence goes below<\/strong> MINVALUE<\/code>).<\/p>\n
    Maxvalue:<\/em><\/strong> the mirror to minvalue<\/code> \u2013 with a positive increment in place you can set a value above<\/strong> which Oracle will raise the error (ORA-08004: sequence goes above<\/strong> MAXVALUE<\/code>).<\/p>\n
    Cycle:<\/em><\/strong> I don\u2019t think I\u2019ve ever seen this used in the wild, but you can set up a sequence to repeat if it has a maxvalue<\/code> with positive increment or a minvalue<\/code> with negative increment. If the call to nextval<\/code> would push the sequence past its min\/max value then it goes back to its start value and continues from there.<\/p>\n
    Order<\/strong>: This is an option that applies very specifically to how RAC handles sequences, and I\u2019ll leave that to the second article in the series.<\/p>\n
    If you start playing around with sequences, you\u2019ll discover that there are a number of ways of combining the options to produce fairly self-explanatory Oracle errors. For example:<\/p>\n
    SQL> create sequence s4 start with 10 increment by 15 nomaxvalue cycle;\r\ncreate sequence s4 start with 10 increment by 15 nomaxvalue cycle\r\n*<\/pre>\n
    <\/p>\n
    There are so many possibilities that there\u2019s no point in trying to list all of them. If you want to read the Oracle error messages file ($ORACLE_HOME\/rdbms\/mesg\/oraus.msg) then the error numbers relating to creating sequences go from ORA-04001 to ORA-04016.<\/p>\n
    Summary<\/h2>\n
    In this first article, I\u2019ve described the basic and most common use of sequences and highlighted the need to choose a sensible cache size to avoid concurrency problems with the sequence itself. In particular, I\u2019ve emphasized the threat of defining a sequence as nocache<\/em><\/strong> while pointing out that, contrary to popular assumption, this option doesn\u2019t stop you \u201closing\u201d sequence numbers.<\/p>\n
    I\u2019ve pointed out the concurrency issues that arise when you create an index to enforce the uniqueness of sequence values, and discussed strategies for minimizing the threat, at the same time pointing out that whatever you do there are side effects. In passing, I\u2019ve mentioned that time-based indexes will exhibit similar behaviour to sequence-based indexes.<\/p>\n
    Finally, I\u2019ve given you a brief run-down on the less frequently used options for defining a sequence, leaving any comments about the order<\/em><\/strong> option to the next article, which will start talking about RAC and the extra care needed when handling sequences in a multi-instance system.<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    Oracle sequences can be used to create artificial IDs for a table. In this article, Jonathan Lewis explains how they work under-the-hood.…<\/p>\n","protected":false},"author":101205,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143533],"tags":[95506],"coauthors":[39048],"class_list":["post-90455","post","type-post","status-publish","format-standard","hentry","category-featured","category-oracle-databases","tag-automate"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90455","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/101205"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=90455"}],"version-history":[{"count":4,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90455\/revisions"}],"predecessor-version":[{"id":90990,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/90455\/revisions\/90990"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=90455"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=90455"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=90455"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=90455"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}