jump to navigation

Intro: Initial Thoughts On Oracle Autonomous Database Cloud Services (Automatic For The People) March 22, 2019

Posted by Richard Foote in 18c, 19c, 19c New Features, Autonomous Data Warehouse, Autonomous Database, Autonomous Transaction Processing, Oracle Indexes.

automatic for the people

I’m currently writing up a few blog pieces on indexing in the Oracle Autonomous Database environments, but I thought I’ll begin by introducing what exactly are Oracle Autonomous Database Cloud Services and some of my initial thoughts, as there’s still some confusion on all this.

Introduced by Uncle Larry at Oracle OpenWorld 2017, Oracle Autonomous Databases are positioned as “self-driving” Cloud- Based database environments, that automatically address many of the tasks usually performed by DBAs such as patching, backups, security and what is of most interest to me, database tuning.

The first of these cloud environments, the Oracle Autonomous Data Warehouse Cloud Service  (ADW) was first released in March 2018, with the Oracle Autonomous Transaction Processing Cloud Service (ATP) released in August 2018.

So key point number one. These are all Oracle Autonomous Database Cloud Services, there are no actual Autonomous Databases as such. These environments currently consist of the following key components:

  • Oracle Database 18c (and above)
  • Oracle Cloud Environment
  • Oracle Exadata Infrastructure
  • Oracle Policy-Driven Automation

So the Oracle Database alone is not an autonomous database. An Oracle 18c database running on your On-Premises Exadata is still not enough to be an autonomous database platform. It requires all the above components, which is why this is only available on the Oracle Cloud environment (and will likely be soon available on the Oracle Cloud at Customer environment, where all these components can also be replicated).

Now having a database environment is which many of the mundane DBA tasks (such as database provisioning, patching, taking backups, etc.) can be automated can only be a good thing and the Autonomous Database Cloud services delivers on all these. Creating an Autonomous Database environment truly just takes a few minutes and I could easily connect via SQL*PLUS and SQL Developer on my laptop a few minutes later.

However, my key interest here is in database tuning (hey, I’m a database tuning guy) and the capability of the Autonomous Database Cloud Services in being able to self-tune and self-repair itself when database performance issues and inefficiencies are encountered.

So, are we there yet?

So my second key point is that is many ways, we already have a “self-tuning” database with Oracle and have had so for many many years. I’m old enough to remember the Rule-Based Optimizer, when the structure of the SQL was critical to performance. I remember tuning Rollback Segments and manually setting extent sizes to make sure no segment got too close to 121 extents else it couldn’t grow any further. I remember manually setting Freelists, I remember having to write my own jobs to run maintenance tasks and setting tablespaces to be in backup mode etc. etc. etc.

The Oracle Database has evolved over the years, where Oracle DBAs don’t have to worry about these things. If you wish, you can now configure the Oracle database to also automatically adjust memory components, automatically collect necessary schema statistics, automatically create baselines and to tune SQL queries, etc. etc. etc.

All of these Oracle database capabilities are crucial in the new Oracle autonomous database environments, as are new Oracle 18c features and as will be new Oracle 19c features (especially in relation to self-tuning the Autonomous Transaction Processing Cloud Service). The newer autonomous database capabilities are just part of this Oracle database self-tuning evolution, with in the future some new policy-driven based automation thrown into the mix.

So are we indeed there yet with a completely self-tuning database with these new autonomous database services? In a word, no.

Which brings me to my next key point. This is all very very new. All these autonomous database services are effectively at “Edition One” status. This will all take time to eventually evolve to be truly, fully self-tuning database environments. There’ll be some new cool capabilities and features which will assist in some areas but be initially deficient in other areas. But clearly this is the future and clearly in future releases, more and more self-tuning capabilities will be added that will make things easier to both manage and tune Oracle database environments.

Note Oracle Corporation itself (depending on who you talk to) is quite clear that it isn’t there yet, with the web-page on the Autonomous Transaction Processing Cloud services clearly stating that “Workload Optimization* (coming soon)“, but with lots of clues on what’s to come with features such as “Database tunes itself (indexes, memory, partitions, SQL plans, parallelism, optimizer stats) for the incoming workload as data changes over time“.

Do many of these upcoming features sound familiar? If you’re not sure, check out the Oracle Database 19c New Features manual.

Which brings me to my final key point here. Even if you’re not particularly interested in the Cloud, if you view managing On-Premises environments as being your foreseeable future, some the best things that has happened to you in relation to the Oracle Database comes courtesy to you as a result of Oracle’s strategic direction with the cloud. Many of the best new features introduced in the past few Oracle Database releases, especially in relation to the CBO and much of the online stuff such moving tables and partitions online, moving data files online, converting tables to be partitioned online, converting partitioned tables differently online, merging/splitting partitions online, etc. etc. are clearly going to be critical in a self-managing/tuning database. As a DBA of an On-Premises database environment, you can also take advantage of these new capabilities.

It will enable Oracle in its autonomous environments for example to automatically convert that table to be partitioned in this specific manner to improve overall performance, all behind the scenes, without anyone necessarily knowing it’s done so.

Is it there yet? No. Is it coming? You bet.

That said, some newer Oracle Database 19c features that will clearly be critical to a self-tuning autonomous databases moving forward such as Real-Time Statistics, SQL Quarantine and Automatic Indexing will only be available in the Oracle Cloud and Exadata platforms. So take note…

Which brings me to indexing.

When the first Oracle Autonomous Data Warehouse cloud service was announced in March 2018, one of the key “features” of the new autonomous platform was that indexing was disabled (as were other traditional Data Warehouse database capabilities such as Partitioning, Materialized Views, etc.). But how can you effectively “tune” a database when you take away some of the key tuning capabilities? The short answer is that you can’t, unless the database has somewhat simplistic data workloads and data structures.

Remember, this was “Version One” with the first autonomous database environment and the initial strategy was to make the Oracle database not smarter, but dumber. Simplify it such that DBAs can’t easily “break” things and by simply making the Exadata platform do all the heavy lifting with regards to database tuning. A more simplified environment makes things a little easier to “self-tune” as there are fewer moving parts to worry about.

For more simplistic Data Warehouse environments, this might all be adequate. For those of you who follow my blog and my previous discussions on indexing on Exadata, dropping all indexes on Exadata, even on Data Warehouse environments was generally a bad idea. So providing an Oracle database platform, even an autonomous one running on an Exadata platform, where all indexing was effectively disabled, was always going to have performance shortfalls in some scenarios. Try running ad-hoc queries on the supplied SSB LINEORDER table for example. Smart Scans, Storage Indexes, HCC, Result Caches, etc. will only take you so far.

So as I said, Oracle evolves and improves and now allows database indexes to be created in the Autonomous Data Warehouse cloud service.

Which will be the focus on upcoming blog posts, so stay tuned.

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

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

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';

--------------- ---------- ------------
ZIGGY_SEQ       Y          N

SQL> select ziggy_seq.nextval from dual;


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';

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


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';

-------------- ---------- -----------
BOWIE_SEQ      Y          Y

SQL> select bowie_seq.nextval from dual;



Just a final note. Scalable Sequences are available in, 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.

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

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

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;


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.

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.

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.


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.


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';


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 🙂