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’)
2 sys_context(‘USERENV’, ‘SID’) SID
3* from dual
Next, let's create a scalable sequence called myseq with the scale keyword…