Some quick facts about sequence caches and gaps in IDs

  1. Oracle sequences generate key values without gaps (minor exception from the rule: scalable sequences).
  2. Surrogate keys can and will have gaps.
  3. Gaps exist because it is not worth the huge effort to avoid them.

The biggest misunderstanding is probably the difference between the value coming from a sequence and the primary key value inside a database column (ID column). We can use a sequence to set a surrogate key. Typically we do this by a database trigger or an identity column or even as a value directly during the insert.

“The history of sequence caches is a history of misunderstandings.”

somebody on the internet, 1963

Essentially it is a two step process. The new ID value is created by sequence.nextval and then used during the insert. Oracle provided the logic to get a new sequence value in a fast, performant and multi-session friendly way. The developer is responsible to make sure this value is not wasted.

The sequence cache is a performance instrument. A lost cache value means we will see gaps in future ID values. There is only a very limited number of activities that makes cached sequence values “disappear”. The majority of gaps originates from other activities, like failed inserts or deletes. Many developers seem to think, that such a gap is caused by the sequence cache. I find it important to separate what reason ultimately lead to a gap in an ID value.

A gap analysis can be the first step. The following select finds gaps and orders them by size. Put your own table and PK column into the “ids” subquery and find out how the gaps are distributed in that table.

-- gap analysis
with ids as (select region_id as id, lead(region_id) over (order by region_id) as nextid from regions)
   , gaps as (select id, nextid - id as gap_size from ids where nextid > id + 1)
select gap_size , count(*) gaps_found  
from gaps
group by gap_size
order by count(*) desc;

Example result

gap_sizegaps_found
205
32
22
191

Here the data shows a few small gaps. Either some insert didn’t make it or rows had been deleted. But there are also several gaps of size 19 and 20, which means the sequence cache was probably lost at least 6 times.

To make it clear, IDs are allowed to have gaps. However when an end user complains why he constantly sees gaps in some ID, then we should investigate what is going on. Frequent gaps should not be the expected normal behaviour – it is worth finding the root cause for such an issue.

So here is a (non complete) list of activities that do or do not influence the caching of sequences and the existence of gaps in an ID column.

action result
multiple sessionscache used
multiple sessions with one session slow to commitIDs not in order of commit => not a gap eventually, but looks like a gap until final commit
alter system flush shared_pool ;cache values lost => gap in ID
shutdown immediate;cache values not lost
shutdown abort;cache values lost => gap in ID
insert; commit;no gap
insert; rollback;gap in ID
failed insertgap in ID
delete; commit;gap in ID
switch sequence from cache to nocachecache values not lost
kill sessionpossible gap in ID
(values not used|commited are lost)
different RAC sessionscache values not lost
(but separate caches on each RAC node)
SGA size too smallcache values lost => gap in ID
(sequence cache ages out of shared pool per LRU list)
session sequencesnot useful for unique ID values => duplicate ID
scalable sequencescache values not lost => planned large gap in ID
(because of prefix)
alter system cancel SQL ‘SID, serial’;
(insert killed)
gap in ID
alter system disconnect session post_transaction;no gap
(unless sequence was fetched before the transaction started)
alter system disconnect session immediate; gap in ID
sequence restartpossible duplicate IDs
sequence interval > 1planned gap in ID

That a sequence ages out of the shared pool is in my experience the most common cause of lost sequence values that are noticed. A strong indication is when frequent gaps of 20 values exist.

Possible workarounds are

a) set the sequence to nocache if the sequence is used rarely
alter myseq nocache;
This might cost performance.

b) pin the sequence in the shared pool
dbms_shared_pool.keep('MYSCHEMA.MYSEQ','Q')
Pinning the sequence is useful when you have times with lots of concurrent activities followed by larger idle periods.
This costs a little memory.

c) increase SGA size – sometimes it helps to increase the restricted shared pool area.
This costs memory (and money) – not available for other non-database processes.

Conclusions

  • Small gaps in surrogate keys (ID columns) are natural, mostly because of delete statements or failed inserts.
  • The sequence object typically does not produce gaps.
  • Very frequent gaps of size 20 (default cache size) are not natural and might need investigation.

Advertisements

EBR – how to run a background job in the correct edition

Introduction

Edition based redefinition is a no extra cost feature of the oracle database. It allows to have multiple versions of the same plsql based code (packages, views, triggers, object types, synonyms, …) in the database at the same time.

Code, that starts a scheduled background job, should execute this background job in the same current edition.

Here is how do it using job classes.

TL;DR.

First create a job class that connects to a specific edition via a service name

begin
  dbms_scheduler.create_job_class (
    job_class_name => 'JCED_DEV$ALPHA',
    service => 'DEV_ALPHA'
  );
end;
/

Then start the background job using that job class.

v_jobclass_prefix := 'JCED_';
v_jobname := 'myJob';
dbms_scheduler.create_job(job_name            => v_jobname,
                          job_type            => 'STORED_PROCEDURE',
                          job_action          => 'myPkg.doSomething',
                          job_class           => v_jobclass_prefix ||sys_context('userenv','current_edition_name'),
                          number_of_arguments => 0,
                          start_date          => sysdate,
                          enabled             => true,
                          auto_drop           => true);

Problem description

This article assumes some basic understanding of the edition based redefinition (EBR) feature of the oracle database.

Scheduled jobs do run in the background in a separate new session. Any new session runs in the default edition of the database, unless it specifies the edition at the time the session is created.

It is possible to switch the edition on demand during a session, but this is not recommended. Chris Saxon misused this possibility for one of his SQL Magic tricks. Not everything that is possible should be used.

Chris Saxon – SQL Magic

Solution

A scheduled job can use a job class. A job class can be set to use a database service. A database service can be set to connect to a specific edition.

When using EBR it is a recommended practice to create a service for each edition and possibly another one for the default edition. Information how to create a service for an edition can by found on one of my older blog entries: working with editions – part 2. Also Oren Nakdimon recently publiced a very nice article about it: using services for exposing new editions .

The following assumes that we have three editions ORA$BASE, TST$BETA and DEV$ALPHA. It also assumes that we have created matching database services ORA_BASE, TST_BETA and DEV_ALPHA and that those services are running.

create the job classes

For each edition we create a job class JCED_<edition_name> (JCED_ORA$BASE, JCED_TST$BETA and JCED_DEV$ALPHA). This can be done with DBA or with the MANAGE SCHEDULER privilege.

Job classes also allow to prioritize between jobs and to connect them to a ressource group.

I’m using a prefix JCED (Job Class EDitioned) to tag the classes. You can use any name you want, but it is advisable to have some common identification for the classes. The name also allows me later to find the correct job class, depending on the current edition of the session.

-- Run as DBA
begin
  dbms_scheduler.create_job_class (
    job_class_name => 'JCED_ORA$BASE',
    service => 'ORA_BASE'
  );
end;
/

begin
  dbms_scheduler.create_job_class (
    job_class_name => 'JCED_TST$BETA',
    service => 'TST_BETA'
  );
end;
/

begin
  dbms_scheduler.create_job_class (
    job_class_name => 'JCED_DEV$ALPHA',
    service => 'DEV_ALPHA'
  );
end;
/

Then we need to make those classes available to the user that will later create the scheduled jobs.

grant execute on sys.JCED_ORA$BASE to mySchema;
grant execute on sys.JCED_TST$BETA to mySchema;
grant execute on sys.JCED_DEV$ALPHA to mySchema;

It is also possible to allow a schema to use any class.

grant execute any class to mySchema;

Job classes do not belong to any schema. They are always created in SYS. Something to keep in mind, for example when exporting/importing schemas to another database.

programatically create a job

The following function creates a background job using the current edition. The job calls a procedure with 3 arguments.

The function is part of some larger package myPackage. It is assumed that the doSomething procedure is also part of the same package.

/* constant declaration section in package body */
  g_jobclass_prefix     constant varchar2(10) := 'JCED_';


/* modules */

function createBackgroundJob(p_id in number, p_starttime in timestamp) return varchar2
is
  co_modul_name CONSTANT VARCHAR2(96) := $$PLSQL_UNIT || '.createBackgroundJob';
  v_job_nr binary_Integer;
  v_jobname varchar2(100);
begin

  v_jobname := 'myJob_'||to_char(p_id);
  dbms_scheduler.create_job(job_name            => v_jobname,
                            job_type            => 'STORED_PROCEDURE',
                            job_action          => $$plsql_unit || '.doSomething',
                            job_class           => g_jobclass_Prefix||sys_context('userenv','current_edition_name'),
                            number_of_arguments => 3,
                            start_date          => p_starttime,
                            enabled             => FALSE,
                            auto_drop           => true);
  dbms_scheduler.set_job_argument_value(job_name=>v_jobname, argument_position=>1, argument_value=>p_id);
  dbms_scheduler.set_job_argument_value(job_name=>v_jobname, argument_position=>2, argument_value=>'RUN QUICK');
  dbms_scheduler.set_job_argument_value(job_name=>v_jobname, argument_position=>3, argument_value=>myPackage.g_trace_level);

  return v_jobname;
end createBackgroundJob;

The name of the job class is calculated using the name of the current edition by sys_context('userenv','current_edition_name').

start the background job

The createBackgroundJob function only prepares the background job. To run it, we need to call the create function and enable the job afterwards.

declare
  job varchar2(128);
begin  
  job := myPackage.createBackgroundJob(1, localtimestamp);
  dbms_scheduler.enable(job);
end;
/

Conclusion

Using job classes is a easy way to start a scheduled job in the same edition that the currently running session is in

Further remarks

Justification

I used this logic to split a plsql heavy task into several worker tasks that could run in parallel. I wanted to make sure that the parallel execution was done using the same edition as the thread that started it.

Restrictions

I did not test if it is possible to run lightweight jobs in a specific edition. There seems to be no obvious restriction that prevents using a job class also for lightweight jobs.

Since job classes belong to sys sometimes they need to be with the schemaname in front :sys.JCED_DEV$ALPHA.

It is not recommened to switch an edition from inside some code, because that code itself is running in a specific edition (the default one). This is very hard to control and to do properly.

Jobs are created with job names in upper case. In some cases we need to make sure to use the upper cased job name, to find/handle the correct job.

Error handling

Error handling needs special care when combining scheduled jobs and editions. For example I make sure that proper instrumentation is in place and that the log entry also includes the edition in which the error happend. In some situations, like if the service is dropped, then the session is still created, but falls back to the default edition. We want to be sure, that we notice any issues arising from such a situation.