Display Session Sequence Cached Values Remaining

Posted April 17, 2019 by Jim Knicely, Vertica Principal Solution Architect

Helpful Tips message on post-it note
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 sequence, 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/latest/HTML/Content/Authoring/AdministratorsGuide/Sequences/Sequences.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/Sequences/HowVerticaAllotsCacheforSequencing.htm

Have fun!