DBA Hub

📋Steps in this guide1/3

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

Use sequences whose current value is not persisted between sessions in Oracle database 12c Release 1 (12.1).

oracle 12cconfigurationintermediate
by OracleDba
13 views
1

Basic Usage

Adding the keyword during sequence creation causes it to be created as a session sequence. Using the keyword, or omitting the additional clause entirely will create the sequence as a global sequence. 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. In contrast, session sequences do not persist their current value, so the current value of the sequence is not retained between sessions. For session sequences, any references to the , , and clauses are ignored.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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;

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>

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>
2

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. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
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>
3

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...

Comments (0)

Please to add comments

No comments yet. Be the first to comment!