8i | 9i | 10g | 11g | 12c | 13c | 18c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Session Sequences in Oracle Database 12c Release 1 (12.1)

Related articles.

Basic Usage

Adding the SESSION keyword during sequence creation causes it to be created as a session sequence. Using the GLOBAL keyword, or omitting the additional clause entirely will create the sequence as a global sequence.

DROP SEQUENCE my_global_sequence;
DROP SEQUENCE my_global_sequence_2;
DROP SEQUENCE my_session_sequence;

-- Global Sequence
CREATE SEQUENCE my_global_sequence;
CREATE SEQUENCE my_global_sequence_2 GLOBAL;

-- Session Sequence
CREATE SEQUENCE my_session_sequence SESSION;

Being the default, global sequences have their current value persisted in the database, so they provide a consistent result between sessions in the database, as shown below.

SQL> CONN test/test@pdb1
Connected.
SQL> SELECT my_global_sequence.NEXTVAL FROM dual;

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

1 row selected.

SQL> CONN test/test@pdb1
Connected.
SQL> SELECT my_global_sequence.NEXTVAL FROM dual;

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

1 row selected.

SQL>

In contrast, session sequences do not persist their current value, so the current value of the sequence is not retained between sessions.

SQL> CONN test/test@pdb1
Connected.
SQL> SELECT my_session_sequence.NEXTVAL FROM dual;

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

1 row selected.

SQL> CONN test/test@pdb1
Connected.
SQL> SELECT my_session_sequence.NEXTVAL FROM dual;

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

1 row selected.

SQL>

For session sequences, any references to the CACHE, NOCACHE, ORDER and NOORDER clauses are ignored.

Performance

The following code tests the performance of the sequences created in the previous section by accessing them in the loop and timing the tests.

SET SERVEROUTPUT ON
DECLARE
  l_start  NUMBER;
  l_loops  NUMBER := 10000;
  l_number NUMBER;
BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := my_global_sequence.NEXTVAL;
  END LOOP;

  DBMS_OUTPUT.put_line('Global Sequence : ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    l_number := my_session_sequence.NEXTVAL;
  END LOOP;

  DBMS_OUTPUT.put_line('Session Sequence: ' || (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Global Sequence : 161 hsecs
Session Sequence: 51 hsecs

PL/SQL procedure successfully completed.

SQL>

So we can see the session sequences perform better than global sequences, which is not surprising since they do not need to persist anything to the data dictionary.

Why Use Session Sequences?

Although we have seen they perform better, session sequences are only sensible for specific circumstances. They should not be used as a replacement for the vast majority of sequences. Two specific scenarios where they may prove useful are listed below.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.