8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Session Sequences in Oracle Database 12c Release 1 (12.1)
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
NOORDER clauses are ignored.
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.
- During load operations to staging tables, where the sequence is used by a single session and the contents of the table will be truncated between uses. As a result, maintaining the sequence value is not necessary.
- For surrogate keys in global temporary tables. These are session specific anyway, so there is no danger of problems with the session sequences. This is especially useful in 12c as the combination of temporary undo and session sequences means global temporary tables can be used in a read-only database, like active data guard.
For more information see:
Hope this helps. Regards Tim...