Display Session Sequence Cached Values Remaining
Jim_Knicely
- Select Field - Administrator
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.
- By default, when a session begins, the cluster initiator node requests cache for itself and other nodes in the cluster.
- The initiator node distributes cache to other nodes when it distributes the execution plan.
- 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!
0