Display Session Sequence Cached Values Remaining

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

To optimize the performance of large INSERT-SELECT and COPY operations, Vertica uses Sequence caching.

To allocate cache among the nodes in a cluster for a given sequences, Vertica uses the following process.

  1. By default, when a session begins, the cluster initiator node requests cache for itself and other nodes in the cluster.
  2. The initiator node distributes cache to other nodes when it distributes the execution plan.
  3. Because the initiator node requests caching for all nodes, only the initiator locks the global catalog for the cache request.

You can display the number of remaining values in a session’s sequence cache via the SESSION_SEQUENCES system table.

Example:

dbadmin=> CREATE SEQUENCE jim_seq CACHE 5;
CREATE SEQUENCE

dbadmin=> SELECT sequence_name, left FROM session_sequences;
sequence_name | left
---------------+------
(0 rows)

dbadmin=> SELECT jim_seq.NEXTVAL;
NEXTVAL
---------
       1
(1 row)

dbadmin=> SELECT sequence_name, left FROM session_sequences;
sequence_name | left
---------------+------
jim_seq       |    4
(1 row)

dbadmin=> SELECT jim_seq.NEXTVAL;
NEXTVAL
---------
       2
(1 row)

dbadmin=> SELECT jim_seq.NEXTVAL;
NEXTVAL
---------
       3
(1 row)

dbadmin=> SELECT sequence_name, left FROM session_sequences;
sequence_name | left
---------------+------
jim_seq       |    2
(1 row)

dbadmin=> SELECT jim_seq.NEXTVAL;
NEXTVAL
---------
       4
(1 row)

dbadmin=> SELECT jim_seq.NEXTVAL;
NEXTVAL
---------
       5
(1 row)

dbadmin=> SELECT sequence_name, left FROM session_sequences;
sequence_name | left
---------------+------
jim_seq       |    0
(1 row)

dbadmin=> SELECT jim_seq.NEXTVAL;
NEXTVAL
---------
       6
(1 row)

dbadmin=> SELECT sequence_name, left FROM session_sequences;
sequence_name | left
---------------+------
jim_seq       |    4
(1 row)

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Sequences/Sequences.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Sequences/HowVerticaAllotsCacheforSequencing.htm

Have fun!

Sign In or Register to comment.