LATEST TOPICS

Oracle 12c: A quick look in to the new SESSION sequence

We are aware of the Oracle’s sequences. Sequences help us generate incremented sequence of numbers to be used as key for a record in the table. Until Oracle 11g, we used to create sequences which are persisted in the database, which means the values were retained in the database for each iteration irrespective of the fact whether the session incrementing the sequence is ACTIVE or TERMINATED.

With the release of Oracle database 12c, Oracle has introduced a new type of sequences called SESSION SEQUENCES, which can be used to generate incremented sequence of numbers at a session level. This means, the generated sequences would be visible only to the session, which incremented the sequence and not to other database sessions. This is like a private sequence available for iteration only for a particular session. Once the session is terminated, the sequence is reset to the starting value.

This type of sequences are not meant for generic use and has special use cases like “generating primary keys for a Global Temporary Table” or “loading staging tables”.

To facilitate the creation of a sequence as a SESSION SEQUENCE, Oracle introduced a new clause called SESSION in the CREATE SEQUENCE statement. Following is the syntax for creating a session level sequence.

---//
---// syntax for creating session level sequence //---
---//
CREATE SEQUENCE [ schema. ] sequence
  [ { INCREMENT BY | START WITH } integer
  | { MAXVALUE integer | NOMAXVALUE }
  | { MINVALUE integer | NOMINVALUE }
  | { CYCLE | NOCYCLE }
  | { KEEP | NOKEEP }
  SESSION
  ;

As we can see there are no CACHE/NOCACHE or ORDER/NOORDER clause available with a session level sequence. We can use CACHE/NOCACHE or ORDER/NOORDER flags with session sequences, however any such references would be ignored by Oracle for a session sequence.

Let’s create a session level sequence to understand how it works. In the following example, I am creating a session level sequence MY_SESS_SEQ and evaluating its behaviour.

---//
---// create session sequence MY_SESS_SEQ //---
---//
SQL> show user
USER is "MYAPP"

SQL> CREATE SEQUENCE MY_SESS_SEQ
  2  START WITH 1 INCREMENT BY 1
  3  MINVALUE 1 MAXVALUE 999999999
  4  SESSION;

Sequence created.

We have created a session sequence called MY_SESS_SEQ under the MYAPP schema with a starting value of 1 and the sequence is incremented by 1 in each iteration. Let’s see the sequence in action now. In the following demonstration, I am accessing the sequence from the MYAPP user’s session with session ID 102.

---//
---// accessing session sequence from MYAPP user session //---
---//
SQL> show user
USER is "MYAPP"
SQL> select sys_context('userenv','sid') sid from dual;

SID
---------------
102

SQL> select MYAPP.MY_SESS_SEQ.NEXTVAL from dual;

   NEXTVAL
----------
         1

SQL> /

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

---//
---// sequence last_number is not updated during iteration //---
---//         
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER,SESSION_FLAG
  2  from dba_sequences
  3  where SEQUENCE_NAME='MY_SESS_SEQ';

SEQUENCE_OWNER  SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER S
--------------- --------------- ---------- ---------- ------------ ----------- -
MYAPP           MY_SESS_SEQ              1  999999999            1           1 Y

We have iterated the sequence few times within MYAPP user’s session. However, we could see the LAST_NUMBER of the session is not updated throughout any of these iterations. This is because the session sequence are non persisting in nature. BTW, there is a new column called SESSION_FLAG in the [DBA/ALL/USER]_SEQUENCES view which indicates whether a sequence is a session or global sequence.

Let’s access this same session sequence from a different session. In the following demonstration, I am accessing the session sequence from MYAPP user’s session with session ID 131.

---//
---// accessing session sequence from a new user session //---
---//
SQL> show user
USER is "MYAPP"

SQL> select sys_context('userenv','sid') sid from dual;

SID
---------------
131

SQL> select MYAPP.MY_SESS_SEQ.NEXTVAL from dual;

   NEXTVAL
----------
         1
         
SQL> /

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> /

   NEXTVAL
----------
         4

---//
---// sequence last_number is not updated during iteration //---
---//   
SQL> select SEQUENCE_OWNER,SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMENT_BY,LAST_NUMBER,SESSION_FLAG
  2  from dba_sequences
  3  where SEQUENCE_NAME='MY_SESS_SEQ';

SEQUENCE_OWNER  SEQUENCE_NAME    MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER S
--------------- --------------- ---------- ---------- ------------ ----------- -
MYAPP           MY_SESS_SEQ              1  999999999            1           1 Y         

As we could see, when we accessed the session sequence from a different session, it started the iteration from the initial value (i.e. 1). This implies that the value which were iterated in the previous session were not persisted in the database. We could also see that the LAST_NUMBER of the session sequence never gets updated due to it’s non persistence nature.

There is a option to convert a session sequence to a global sequence and vice versa. However, when we convert a session sequence to global sequence, it doesn’t start iteration from the next sequence value rather it starts the iteration from the LAST_NUMBER (or min value in case the session sequence was not altered in between) of the sequence as shown below.

---//
---// performing few iterations of the session sequence //---
---//
SQL> show user
USER is "MYAPP"

SQL> select sys_context('userenv','sid') sid from dual;

SID
---------------
135

SQL> select MYAPP.MY_SESS_SEQ.NEXTVAL from dual;

   NEXTVAL
----------
         1

SQL> /

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> /

   NEXTVAL
----------
         4

SQL> /

   NEXTVAL
----------
         5

---//
---// converting session sequence to global sequence //---
---//         
SQL> alter sequence MYAPP.MY_SESS_SEQ global;

Sequence altered.

---//
---// validating sequence conversion //---
---//
SQL> select session_flag from dba_sequences
  2  where SEQUENCE_NAME='MY_SESS_SEQ';

S
-
N

---//
---// next iteration started from min value after conversion //---
---//
SQL> select MYAPP.MY_SESS_SEQ.NEXTVAL from dual;

   NEXTVAL
----------
         1

      

As we could see, the moment we converted the session sequence to a global sequence, the iteration started from the initial min value rather than from the current value of the sequence. Conversely, when we convert a global sequence to a session sequence, the LAST_NUMBER of the session sequence would be set its recent NEXTVAL value and the next iteration of session sequence would always start from that LAST_NUMBER as shown below.

---//
---// performing few iterations of global sequence //---
---//
SQL> select sys_context('userenv','sid') sid from dual;

SID
---------------
135

SQL> select session_flag from dba_sequences
  2  where SEQUENCE_NAME='MY_SESS_SEQ';

S
-
N

SQL> select MYAPP.MY_SESS_SEQ.NEXTVAL from dual;

   NEXTVAL
----------
         1

SQL> /

   NEXTVAL
----------
         2

SQL> /

   NEXTVAL
----------
         3

SQL> /

   NEXTVAL
----------
         4

SQL> /

   NEXTVAL
----------
         5


---//
---// converting global sequence to session sequence //---
---//
SQL>  alter sequence MYAPP.MY_SESS_SEQ session;

Sequence altered.

---//
---// session sequence last_number is set to its recent NEXTVAL //--
---//
SQL> select LAST_NUMBER from dba_sequences where sequence_name='MY_SESS_SEQ';

LAST_NUMBER
-----------
          6

---//
---// any new iteration of session sequence will start from the last_number //---
---//          
SQL> select MYAPP.MY_SESS_SEQ.NEXTVAL from dual;

   NEXTVAL
----------
         6

---//
---// iterating the session sequence from a new session //---
---//         
SQL> conn myapp/myapp@cdb1_pdb_1
Connected.
SQL> select sys_context('userenv','sid') sid from dual;

SID
---------------
116


SQL> select MYAPP.MY_SESS_SEQ.NEXTVAL from dual;

   NEXTVAL
----------
         6

SQL> /

   NEXTVAL
----------
         7

This is an interesting point regarding the sequence conversion. With this demonstrations, we are now aware how the sequence value behaves when converting from session to global and vice versa. Finally session sequences seem to have better performance when compared with the global sequences as they don’t have to persist the sequence values into the dictionary and therefore no disk I/O required.

To demonstrate this behavior, in the following example I am comparing the execution statistics of accessing a session sequence (MY_SESS_SEQ) against a global sequence (MYAPP_SEQ).

---//
---// execution statistics of session sequence //---
---//
SQL> set autotrace traceonly stat
SQL> select MYAPP.MYAPP_SEQ.NEXTVAL from dual;


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          0  consistent gets
          0  physical reads
          0  redo size
        541  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


---//
---// execution statistics of global sequence //---
---//           
SQL> set autotrace traceonly stat
SQL> select MYAPP.MYAPP_SEQ.NEXTVAL from dual;


Statistics
----------------------------------------------------------
          5  recursive calls
          3  db block gets
          1  consistent gets
          0  physical reads
       1028  redo size
        541  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

As we could see, while accessing the session sequence we didn’t have to perform any kind of I/O (0 redo size, 0 block gets, 0 consistent gets), where as while accessing the global sequence we had to perform I/O (3 block gets, 1028 redo size, 1 consistent gets) as well as few recursive calls (5 recursive calls). This implies that there is much less work involved in accessing a session sequence and that helps in speeding up the performance. This performance improvement in session sequence would be very beneficial while we are generating huge number of sequences for session specific operations. However, we must remember that session sequences are not a replacement of the global sequences (courtesy to it’s non persistence nature) and they only have specific use cases.

%d bloggers like this:
Visit Us On LinkedinVisit Us On TwitterVisit Us On Google PlusCheck Our Feed