jump to navigation

18c Scalable Sequences Part III (Too Much Rope) May 8, 2018

Posted by Richard Foote in 18c, 18c New Features, Oracle Indexes, Scalable Sequences.
add a comment

amused to death

I previously looked in Part I and Part II how Scalable Sequences officially released in 18c can reduce index contention issues, by automatically assigning a 6 digit prefix to the sequence value based on the instance ID and session ID of the session.

We need to be careful and consider this 6 digit prefix if we decide to set a maxvalue, which we might consider doing as the default Scalable Sequence value is quite large as I showed previously:

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

SQL> select ziggy_seq.nextval from dual;

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

Now I might consider a maximum value of say 9999 to be sufficient for my needs:

SQL> create sequence major_tom_seq maxvalue 9999 scale;

Sequence created.

SQL> select sequence_name, scale_flag, extend_flag from user_sequences

where sequence_name='MAJOR_TOM_SEQ';

SEQUENCE_NAME  SCALE_FLAG EXTEND_FLAG
-------------- ---------- -----------
MAJOR_TOM_SEQ  Y          N

However, if I try to generate a value from my Scalable Sequence:

SQL> select major_tom_seq.nextval from dual;
select major_tom_seq.nextval from dual
*
ERROR at line 1:
ORA-64603: NEXTVAL cannot be instantiated for MAJOR_TOM_SEQ. Widen the sequence
by 3 digits or alter sequence with SCALE EXTEND.

It generates a error saying that the next value can’t be instantiated. This is due to the maxvalue of 9999 not being sufficient to store the necessary 6 digit prefix, never mind the actual generated value. Therefore the maximum value of a Scalable Sequence must at least be a 7 digit number.

If I really wanted an effective sequence maxvalue of 9999 with a Scalable Sequence, I need to add the 6 digit prefix to this maximum value:

SQL> alter sequence major_tom_seq maxvalue 9999999999;

Sequence altered.

SQL> select major_tom_seq.nextval from dual;

NEXTVAL
----------
1029730001

Alternatively, I could have created (or altered) the sequence with the EXTEND clause, such that Oracle then automatically adds the necessary 6 digits to the sequence definition:

SQL> create sequence bowie_seq maxvalue 9999 scale extend;

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      Y          Y

SQL> select bowie_seq.nextval from dual;

NEXTVAL
----------
1010160001

 

Just a final note. Scalable Sequences are available in 12.1.0.1, but they were neither documented or officially supported. But you can at least play with them if you don’t have access to an 18c environment. That said, you can play with them for free on the Oracle Live SQL facility.

Advertisements

18c Scalable Sequences Part II (Watch That Man) May 2, 2018

Posted by Richard Foote in 18c, 18c New Features, Oracle Indexes, Scalable Sequences.
1 comment so far

watch that man

In Scalable Sequences Part I, I introduced this new 18c feature (although it was hidden and undocumented in previous releases). By adding a 6 digit prefix value that constitutes the first 3 digits for the Instance Id and the next 3 digits for the Session Id, it results in a sequence value that doesn’t always hit the right hand most leaf block of the associated Primary Key index.

If we look at the prefixes of the ID column after rows were inserted from 3 different sessions:

SQL> select distinct substr(to_char(id),1,6) from ziggy;

SUBSTR(TO_CHAR(ID),1,6)
------------------------
101389
101398
101260

We notice there are indeed 3 prefix values associated with the 3 sessions. Notice also that the first 3 digits (101) are all the same as each session was established from the same instance (this is a non-RAC environment).

If we would ever want to look at a specific range of more current ID values, we would have to play funny games in extracting the non-prefix portion of the sequence:

SQL> select * from ziggy where to_number(substr(to_char(id),7)) between 158280 and 158290;

                           ID NAME
----------------------------- ------------
 1012600000000000000000158280 DAVID BOWIE
 1012600000000000000000158285 DAVID BOWIE
 1012600000000000000000158286 DAVID BOWIE
 1012600000000000000000158287 DAVID BOWIE
 1012600000000000000000158288 DAVID BOWIE
 1012600000000000000000158289 DAVID BOWIE
 1012600000000000000000158290 DAVID BOWIE
 1013980000000000000000158281 DAVID BOWIE
 1013980000000000000000158282 DAVID BOWIE
 1013980000000000000000158283 DAVID BOWIE
 1013890000000000000000158284 DAVID BOWIE

The price we pay for having a prefix value that skews the data in a manner that prevents contention on the associated index.

As discussed in Part I, we also pay a price in that only one of these 3 sessions will actually be the one which does indeed insert the maximum current ID value in the index, thereby hitting the right hand most leaf block and generating 90-10 splits. All other sessions will be inserting elsewhere within the index structure thereby generating 50-50 block splits, resulting in more free space in the associated index.

But at least we do reduce possible index contention related wait events

In Part III, I’ll take a look at the additional EXTEND option with Scalable Sequences.

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

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

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.