jump to navigation

18c Scalable Sequences Part I (Saviour Machine) April 30, 2018

Posted by Richard Foote in 18c New Features, Oracle Indexes, Scalable Sequences.
trackback

man who sold the world

One of the problems with populating the value of a Primary Key (or some such), is that they can cause contention, especially in relation to indexes. By having an indexed value that monotonically increases, the right-hand most index leaf block is continually being accessed, which results in a “hot block”, which in turn results in buffer block contention. This can be extremely problematic in RAC environments, with this leaf block continually bouncing between RAC instances resulting in excessive cluster wait events.

There are a number of possible methods to address this contention, including the use of Reverse Key Indexes, Hash Partitioned Indexes, the caching of Sequence values through to RAC aware Sequence implementations.

Oracle Database 18c has introduced a new option to resolve this issue, Scalable Sequences. These are sequences that have an automatically applied 6 digit prefix value that consists of 3 digits for the Instance offset and 3 digits for a Session offset value. This ensures that different Instances no longer contend for the same index leaf block and that sessions within a particular instance also no longer contend for the same index leaf block.

Reduced contention therefore means a far more scalable environment enabling faster inserts of data, although it can come with some disadvantages worth noting.

If we first look at just a standard Sequence:

SQL> create sequence bowie_seq;

Sequence created.

SQL> select sequence_name, scale_flag, extend_flag from user_sequences

where sequence_name='BOWIE_SEQ';

SEQUENCE_NAME   SCALE_FLAG EXTEND_FLAG
--------------- ---------- ------------
BOWIE_SEQ       N          N

SQL> select bowie_seq.nextval from dual;

NEXTVAL
----------
         1

We notice that just a standard number is generated from the sequence. This number can be controlled somewhat by whether we want to guarantee order or not, if we want to cache a series of sequence values, how they get incremented, etc.

If we create a table with a Primary Key ID column:

SQL> create table bowie (id number constraint bowie_id_i primary key, name varchar2(42));

Table created.

Then next a procedure to populate the table with a bunch of rows, using the normal sequence for the ID column:

SQL> create or replace procedure pop_bowie as
begin
for i in 1..100000 loop
insert into bowie values (bowie_seq.nextval, 'DAVID BOWIE');
commit;
end loop;
end;
/

Procedure created.

We then run this procedure from 3 different sessions:

SQL> exec pop_bowie --- 3 sessions

PL/SQL procedure successfully completed.

If we look at the data from INDEX_STATS:

SQL> analyze index bowie_id_i validate structure;

Index analyzed.

SQL> select name, lf_blks, pct_used from index_stats;

NAME          LF_BLKS   PCT_USED
---------- ---------- ----------
BOWIE_ID_I        672         93

We notice the ID index has 672 leaf blocks and has a relatively high 93% PCT_USED, primarily because the majority of the index block splits have been 90-10 due to the largest indexed value more often than not causing the right most index leaf block to split.

Let’s now perform the same demo, but this time with an 18c Scalable Sequence. A Scalable Sequence is simply defined with the new SCALE clause:

SQL> create sequence ziggy_seq scale;

Sequence created.

SQL> select sequence_name, scale_flag, extend_flag from user_sequences

where sequence_name='ZIGGY_SEQ';

SEQUENCE_NAME   SCALE_FLAG EXTEND_FLAG
--------------- ---------- ------------
ZIGGY_SEQ       Y          N

If we look at a default sequence value now:

SQL> select ziggy_seq.nextval from dual;

NEXTVAL
----------------------------------
      1013890000000000000000000001

We notice the value is now much larger by default (28 digits) with the first 6 digits assigned (as previously mentioned, the first 3 digits is an Instance offset value and the next 3 digits a Session offset value). The last digit represents the incremental sequence value.

If we create a similar table and procedure as previously:

SQL> create table ziggy (id number constraint ziggy_id_i primary key, name varchar2(42));

Table created.

SQL> create or replace procedure pop_ziggy as
begin
for i in 1..100000 loop
insert into ziggy values (ziggy_seq.nextval, 'DAVID BOWIE');
commit;
end loop;
end;
/

Procedure created.

And again run the procedure in 3 different sessions, this time using the Scalable Sequence:

SQL> exec pop_ziggy --- 3 sessions

PL/SQL procedure successfully completed.

SQL> analyze index ziggy_id_i validate structure;

Index analyzed.

SQL> select name, lf_blks, pct_used from index_stats;

NAME          LF_BLKS   PCT_USED
---------- ---------- ----------
ZIGGY_ID_I       1858         71

We notice a couple of key differences. Firstly, the number of leaf blocks is much greater at 1858 from the previous 672 leaf blocks. This is due in large part to the larger ID values being generated via the Scalable Sequence,  with fewer ID values now fitting in each leaf block. The second difference is the somewhat worse PCT_USED of 71% (previously 93%) due to the fewer 90-10 index block splits with the index entries now less likely to be the greatest value currently within the index. Only one of the 3 sessions is likely to now be inserting into the right hand most leaf block on the index. This also contributes to the increased number of leaf blocks as each leaf block has more free space on average than previously.

More on Scalable Sequences in Part II.

Advertisements

Comments»

1. Connor McDonald - April 30, 2018

“scaleable sequences” …. worst name ever 🙂 Because it creates the impression that normal sequences are not.

Liked by 1 person

Richard Foote - April 30, 2018

But normal sequences don’t have the SCALE clause, so clearly they therefore can’t scale 🙂 Or Extend :). I know, I know…

Like

2. Ric - May 1, 2018

The best solution (also impractical) is a random UNIQUE number generator. Which would be really cool, but incredibly difficult to do.

Like

3. 18c Scalable Sequences Part II (Watch That Man) | Richard Foote's Oracle Blog - May 2, 2018

[…] Scalable Sequences Part I, I introduced this new 18c feature (although it was hidden and undocumented in previous releases). […]

Like

4. 18c Scalable Sequences Part III (Too Much Rope) | Richard Foote's Oracle Blog - May 8, 2018

[…] previously looked in Part I and Part II how Scalable Sequences officially released in 18c can reduce index contention issues, […]

Like

5. Prem - August 14, 2018

So, what is the use of having scalable sequence and what are the advantages of this over normal sequence?

Like

Richard Foote - October 5, 2018

Hi Prem

As I discussed above, by introducing a random (enough) prefix to a sequence value, numbers no longer monotonically increase and so no longer all hit the right hand most side of the index and so can reduce contention issues in relation to hot blocks and RAC cache fusion overheads.

Cheers

Richard

Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: