Viscosity North America
logo-black
  • Data
    • Performance Assessment
    • Proactive Healthcheck
    • Database Migration & Consolidation
    • Performance Tuning
    • Microsoft Services
      • Microsoft Azure Services
      • Microsoft SQL Server Services
      • Microsoft Gold Azure Partner
    • Oracle Services
      • Oracle Database 19c
      • RAC
      • GoldenGate
      • Data Guard
      • Oracle & SQL Database
  • Apps
    • Oracle APEX
    • Viscosity AMP
  • Cloud
    • Hybrid Cloud
    • Viscosity Edge Cloud
    • Virtualization & Cloud Expertise
    • Microsoft Azure Services
  • Infrastructure
    • Exadata
      • Exadata Resale & Services
      • Engineered Systems Managed Services
    • Oracle Database Appliance
      • Oracle Database Appliance Resale & Services
      • ODA Health Checks
  • Events
    • Upcoming Events
    • 2023 Past Events
    • 2022 Past Events
  • About
    • Viscosity's Blog
    • Press Releases
    • Publications
    • Partnerships
    • Clients
    • Contact
Hit enter to search or ESC to close
ENGAGE WITH US

Oracle 18c , RAC , Oracle 19c , SQL , Scalable Sequences

On the Second Day of 18C/19C, Viscosity Gave to Me…

By Viscosity North America
December 09, 2022

Scalable Sequences

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: 

 

Screenshot 2022-11-17 at 12.34.48

 

Next, let's create a scalable sequence called myseq with the scale keyword. 

 

Screenshot 2022-11-17 at 12.36.19

 

We can dissect the first 6 digits of the sequence number offset into 2 categories: 

  • Instance ID 
  • Session ID  

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: 

 

Screenshot 2022-11-17 at 12.37.16

 

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: 

 

Screenshot 2022-11-17 at 12.38.01

 

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:  

  • 101047 represents the 6-digit offset 
  • 000000000001 represents the 12-digit sequence which represents the 12 x 9's in our example for maximum value.  

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! 

 

DOWNLOAD PDF
All posts
About Author
Viscosity North America

You might also like
On the Twelfth Day of 18c/19c, Viscosity Gave to Me...
On the Twelfth Day of 18c/19c, Viscosity Gave to Me...
December 09, 2022
On the Eleventh Day of 18c/19c, Viscosity Gave to Me...
On the Eleventh Day of 18c/19c, Viscosity Gave to Me...
December 09, 2022
On the Eighth Day of 18c/19c, Viscosity Gave to Me...
On the Eighth Day of 18c/19c, Viscosity Gave to Me...
December 09, 2022
SUBMIT YOUR COMMENT
logo for footer

Viscosity's core expertise includes:

Data Transformation, Emerging Technology, High Availability & Scalability Solutions, Cloud Migrations, Performance Tuning, Data Integrations, Machine Learning, APEX Development, and Custom Application Development.


Solutions

Resources

Events

Blog

About

 

Contact
Email: sales@viscosityna.com

Telephone:
(469) 444-1380

Copyright 2023. All Rights Reserved by Viscosity North America.