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.

“Let’s Talk Database” is Back !! Canberra/Sydney/Melbourne May 1, 2018

Posted by Richard Foote in 18c New Features, Data Clustering, Let's Talk Database, Oracle Indexes.
add a comment

seminar photo

Due to popular demand, I’ve been asked by Oracle to again run some “Let’s Talk Database” events this month. Dates and venues are as follows:

Wednesday, 23 May – Canberra (Cliftons Canberra, 10 Moore St): Registration Link.

Tuesday, 29 MayMelbourne (Oracle Melbourne Office, 417 St Kilda Road): Registration Link.

Wednesday, 30 MaySydney (Oracle Sydney Office, North Ryde): Registration Link.

 

Agenda:

8:30 – 9:00am – Registration and coffee

9:00 – 10:30am – Data Clustering

10:30 – 11:00am – Break

11:00 – 12:30pm – Oracle Database 18c – New Features

12:30 – 1:30pm – Lunch, Networking and Informal Q&A

 

Data Clustering: A Key To Developing High Performance & Scalable Apps”

Today’s agile applications have to deal with ever increasing data volumes; rich varieties of data types with their associated intricate/flexibility requirements; and complex hybrid cloud-based environments, where critical high volume transactional-based applications have to function in combination with equally important real-time advanced data analytics reporting solutions. As such, having an innovative data clustering strategy in combination with appropriate data-aware deployments is vital to ensure today’s complex applications are high-performing, scalable, and robust. Many of today’s applications struggle to perform or scale because they lack the necessary flexible indexing and data management strategies at the database layer. This session will demonstrate various innovative data clustering and indexing-based tricks and tactics that will ensure applications run as efficiently as possible, regardless of the size or complexity of the underlying data management layer.

“Oracle Database 18c New Features”

This session will look at some of the key new features and capabilities introduced in Oracle Database 18c. New features discussed include Memory Optimized Row Store for OLTP workloads, Database In-Memory for External Tables, Inline External Tables, In-Memory Database improvements, Zero Impact Grid Infrastructure Patching, Alter Partitioned Table Merge Online, Alter Table Modify Partitioned Table to Partitioned Table, Approximate Query improvements, Private Temporary Tables and Polymorphic Table Functions. The session will also discuss how to play with some of these new features now without the need for an Oracle Cloud account.”

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

Posted by Richard Foote in 18c New Features, Oracle Indexes, Scalable Sequences.
6 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.

Oracle Database 18c: Introduction to Memoptimized RowStore (Memory of a Free Festival) March 5, 2018

Posted by Richard Foote in 18c, 18c New Features, Memoptimized RowStore, Oracle Indexes.
2 comments

memory of a free festival

One of the potentially cool new features introduced in Oracle Database 18c is the Memoptimized RowStore, designed to improve the performance and scalability of key-value based queries.

A new pool in the SGA called the Memoptimize Pool can be configured to store specific heap tables that you may wish to optimize, in a manner not dissimilar to how you may wish to store specific tables in the Keep Pool. The difference with this new pool however is that not only are the table blocks store in the memory pool, but 25% of the pool is configured to automatically create and store a hash index based on the Primary Key (PK) of the table.

Accesses to the table via an equality predicate on the PK can then use the in-memory hash index to very efficiently access the required row, eliminating any physical IOs. In addition, Exadata Exadirect Oracle Net Protocol can leverage Remote Direct Memory Access (RDMA) to allow clients to directly access the required memory regions over the network, avoiding both CPU and OS overheads. The result is potentially very fast and very scalable key-value based queries.

A new parameter MEMOPTIMIZE_POOL_SIZE is used to size the pool as required. Note this pool is part of the SGA and so needs to be carefully considered when sizing the overall SGA. Currently with the first release of 18c, the MEMOPTIMIZE_POOL_SIZE can not be dynamically changed and is not automatically sized and tuned.

Once you’ve set the MEMOPTIMIZE_POOL_SIZE accordingly, you can then create or alter tables to take advantage of the new pool.

The following demo was run on Oracle Live SQL:

I first attempt to create a new table using the new MEMOPTIMIZE FOR READ clause to allocate the table to this new pool:

create table bowie (id number, code number, name varchar2(42))
memoptimize for read;

ORA-62156: MEMOPTIMIZE FOR READ feature not allowed on segment with deferred storage

So a new table can not be created with the default setting of segment with deferred storage. Let’s try again:

create table bowie (id number, code number, name varchar2(42))
segment creation immediate memoptimize for read;

ORA-62142: MEMOPTIMIZE FOR READ feature requires NOT DEFERRABLE PRIMARY KEY constraint on the table

So to use the Memoptimized KeyStore, a table must have a PK. These are the columns by which the automatic in-memory hash index is based.

create table bowie (id number constraint bowie_pk primary key, code number, name varchar2(42))
segment creation immediate memoptimize for read;

Table created.

Success. So this table has been created to use the Memoptimized RowStore.

You can also alter existing tables to make use of the Memoptimized RowStore:

create table bowie2 (id number constraint bowie2_pk primary key, code number, name varchar2(42));

Table created.
insert into bowie2 select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <=100000;

100000 row(s) inserted.

commit;

Statement processed.

alter table bowie2 memoptimize for read;

Table altered.

For tables that have just been altered to use the Memoptimized RowStore, you can load the table data into the pool via the new DBMS_MEMOPTIMIZE package:

exec dbms_memoptimize.populate(schema_name=>'SQL_GLGTFSMUBLHEUOFHPRXBRHITF', table_name=>'BOWIE');

ORA-62138: MEMOPTIMIZE memory area does not exist. ORA-06512: at "SYS.DBMS_MEMOPTIMIZE", line 10
ORA-06512: at "SYS.DBMS_MEMOPTIMIZE", line 113
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_SQL", line 1721

So it appears from the error message that the Memoptimized RowStore is not currently enabled on Oracle Live SQL.

Which makes some sense as it would be very difficult  to size the MEMOPTIMIZE_POOL_SIZE parameter appropriately if any old bugger can create objects in the pool.

So it means we can’t use the Oracle Live SQL environment to test and play around with this new feature, but it doesn’t stop us from exploring a little in how this feature would work.

Let’s now populate the original table:

insert into bowie select rownum, mod(rownum,1000), 'DAVID BOWIE' from dual connect by level <=100000;

100000 row(s) inserted.

commit;

Statement processed.

 

And collects statistics:

exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE');

Statement processed.

In much as the same way as the Oracle Database In-Memory option can’t guarantee all objects assigned to the InMemory pool can fit in the allocated pool set by  INMEMORY_SIZE, the same goes for the Memoptimized RowStore. Therefore the CBO can only but assume the tables reside in the memory structures in which they’re allocated and only at run time decide if it might need to access the required data elsewhere (e.g. via the Buffer Cache, via a database index rather than the in-memory hash index, etc.).

Note both the commands to create a table and to alter a table to use the Memoptimised RowStore worked even though the necessary pool doesn’t appear to have been allocated in the database.

So lets investigate what the CBO does with a table assigned to the Memoptimized RowStore.

As the PK will automatically have an inmemory hash index created within the Memoptimized RowStore, does Oracle still automatically create a database index as well:

select index_name, blevel, leaf_blocks from user_indexes where table_name='BOWIE';

INDEX_NAME BLEVEL LEAF_BLOCKS

BOWIE_PK        1         187

So yes, an index is still automatically created to police the PK constraint if a table is assigned to the Memoptimized RowStore.

So let’s have a look at the execution plan for a query based on a PK equality predicate:

select * from bowie where id=42;

---------------------------------------------------------------------------------------------------
| Id | Operation                              | Name     | Rows | Bytes | Cost (%CPU) | Time      |
---------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                       |          |    1 |    20 |       2 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID READ OPTIM | BOWIE    |    1 |    20 |       2 (0) |  00:00:01 |
|* 2 | INDEX UNIQUE SCAN READ OPTIM           | BOWIE_PK |    1 |       |       1 (0) |  00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=42)

So we have a couple of new execution plan steps here. INDEX UNIQUE SCAN READ OPTIM which states the in-memory Hash Index will be accessed to find the location of the required row and TABLE ACCESS BY INDEX ROWID READ OPTIM which states the table will then accessed via the rowid obtained from the in-memory Hash Index.

If we run a query that uses a BETWEEN predicate on the PK column:

select * from bowie where id between 42 and 50;

------------------------------------------------------------------------------------------------
| Id | Operation                           | Name     | Rows | Bytes | Cost (%CPU) | Time      |
------------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                    |          |   10 |   200 |       3 (0) |  00:00:01 |
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED | BOWIE    |   10 |   200 |       3 (0) |  00:00:01 |
|* 2 | INDEX RANGE SCAN                    | BOWIE_PK |   10 |       |       2 (0) |  00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID">=42 AND "ID"<=50)

We notice the Memoptimized RowStore is not used, just the database PK index via a standard Index Range Scan.

If we run a query that the CBO knows will only retrieve 2 rows via an IN clause:

select * from bowie where id in (42, 442);

-----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU) | Time      |
-----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |    2 |    40 |       3 (0) |  00:00:01 |
|  1 | INLIST ITERATOR             |          |      |       |             |           |
|  2 | TABLE ACCESS BY INDEX ROWID | BOWIE    |    2 |    40 |       3 (0) |  00:00:01 |
|* 3 | INDEX UNIQUE SCAN           | BOWIE_PK |    2 |       |       2 (0) |  00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ID"=42 OR "ID"=442)

Again, the Memoptimized RowStore is not used, just the database PK index.

The Memoptimized RowStore is only used on queries with a single equality predicate on the table. Even a query with an equality predicate in combination with another predicate on the same table (odd as that may be), will result in the Memopimized RowStore not being used:

select * from bowie where id=42 and name='DAVID BOWIE';

----------------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU) | Time     |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT            |          |    1 |    21 |       2 (0) | 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID | BOWIE    |    1 |    21 |       2 (0) | 00:00:01 |
|* 2 | INDEX UNIQUE SCAN           | BOWIE_PK |    1 |       |       1 (0) | 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("NAME"='DAVID BOWIE')
2 - access("ID"=42)

 

So the Memoptimized RowStore is only considered by the CBO, for queries based on a singular PK=value predicate on a table.

More to come once I have my own database to play in 🙂