Day 2: Scalable Sequences

December 9, 2020

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 have 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:

SQL> col instance for a20

SQL> col sid for a20
  1 select sys_context(‘USERENV’, ‘INSTANCE’)

Instance,
  2 sys_context(‘USERENV’, ‘SID’) SID

  3* from dual

SQL> /

INSTANCE       SID

——————– ——————–

1                       47

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

    To receive the full article, please complete the form provided.  A pdf will be automatically emailed to you, so be sure to check your spam/junk mail if you do not see it.

      Full Name (required)

      Your Email (required)

      Company (required)

      Phone number (required)

      Job Title (required)