Most companies leverage database sequences as monotonically increasing number generators to populate synthetic primary key and unique indexes. As applications leverage sequentially generated integers and continuously access the right-most leaf block during heavy workloads, performance can degrade significantly on RAC databases as buffer block contention is introduced.
Historically, Viscosity has implemented Reverse Key Indexes or Hash Partitioned Indexes and has even changed the block size of the tablespace/database, but the application/schema had to be refactored to recognize the performance gains. Oracle introduced and documented scalable sequences in Oracle Database 18c to eliminate the hot index contention issue in RAC during large-scale data insertions.
Before we begin our review of scalable sequences, let's leverage the Oracle built-in namespace, sys_context, to retrieve information about our current session, instance, and session id, as they are relevant to our scalable sequence discussions:
Next, let's create a scalable sequence called myseq with the scale keyword.
We can dissect the first 6 digits of the sequence number offset into 2 categories:
The first 3 digits represent the instance offset number: [ (instance id % 100) + 100 ] => 101
The second 3 digits represent the session offset number:
(session id from session information % 1000 ) = 1047/1000 = remainder of 047
The remaining 22 digits have 0's padded, with the last digit of our sequence number being 4.
When we CREATE or ALTER a sequence, we have the option to specify SCALE|NOSCALE. NOSCALE is the default meaning that it will be the normal sequence. Another option is to specify EXTEND | NOEXTEND, where the NOEXTEND option is the default. In the example below, we have created a sequence called hca_seq_scale_noextend_01 with the SCALE option but with the max value of 12 digits:
Remember that NOEXTEND is the default, so we do not need to specify that option. Next, let's select the next value from the sequence:
If you look at this output, the numeric values have a total of 12 digits. Remember, the first 6-digit offset represents the instance and session id information. The remaining 6 digits represent the sequence values.
Let's create another sequence called hca_seq_scale_extend_01 with both SCALE and EXTEND keywords:
Dissecting the output of 101047 000000000001:
We can query the dictionary view dba_sequences to identify scalable sequences:
The two columns of particular interest are the scale_flag and extend_flag columns. As you can see, the default max value is 28 digits. If we create a scalable sequence with the default options, although it does not create with the EXTEND option, the default digit size is 28 for the max value.
We can alter a scalable sequence to have SCALE|NOSCALE and to EXTEND|NOEXTEND. Below is an example of the sequences that we created earlier:
In summary, the high concurrency of inserts with right-hand leaf nodes of the B-tree indexes leads to performance issues. With Oracle RAC, this performance bottleneck is exponentially magnified as hot blocks are transferred across the interconnect. With scalable sequences, because we are generating unordered values for the primary or unique keys, we will notice a significant reduction in index block contention caused by the right-handed indexes. Thus, yielding better throughput, scalability with batch jobs, and data loads resulting in better performance and response times. For all new applications on RAC, consider scalable sequences as your new standard.
One of the sayings we have at Viscosity is our customers “have four aces in their pocket.” Over the next 11 days, the talented staff at Viscosity, along with our Oracle ACEs, will address more Oracle Database 18c and 19c new features. Continue to join us next year as we continue our Oracle Database 19c hands-on-lab workshops.
Happy Holidays!