This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

V_MONITOR schema

The system tables in this section reside in the v_monitor schema.

The system tables in this section reside in the v_monitor schema. These tables provide information about the health of the Vertica database.

1 - ACTIVE_EVENTS

Returns all active events in the cluster.

Returns all active events in the cluster. See Monitoring events.

Column Name Data Type Description
NODE_NAME VARCHAR The node name where the event occurred.
EVENT_CODE INTEGER A numeric ID that indicates the type of event. See Event Types for a list of event type codes.
EVENT_ID INTEGER A unique numeric ID assigned by the Vertica catalog, which identifies the specific event.
EVENT_SEVERITY VARCHAR

The severity of the event from highest to lowest. These events are based on standard syslog severity types.

  • 0—Emergency

  • 1—Alert

  • 2—Critical

  • 3—Error

  • 4—Warning

  • 5—Notice

  • 6—Informational

  • 7—Debug

EVENT_POSTED_TIMESTAMP TIMESTAMP The year, month, day, and time the event was reported. The time is posted in military time.
EVENT_EXPIRATION VARCHAR The year, month, day, and time the event expire. The time is posted in military time. If the cause of the event is still active, the event is posted again.
EVENT_CODE_DESCRIPTION VARCHAR A brief description of the event and details pertinent to the specific situation.
EVENT_PROBLEM_DESCRIPTION VARCHAR A generic description of the event.
REPORTING_NODE VARCHAR The name of the node within the cluster that reported the event.
EVENT_SENT_TO_CHANNELS VARCHAR The event logging mechanisms that are configured for Vertica. These can include vertica.log, (configured by default) syslog, and SNMP.
EVENT_POSTED_COUNT INTEGER Tracks the number of times an event occurs. Rather than posting the same event multiple times, Vertica posts the event once and then counts the number of additional instances in which the event occurs.

2 - ALLOCATOR_USAGE

Provides real-time information on the allocation and reuse of memory pools for a Vertica node.

Provides real-time information on the allocation and reuse of memory pools for a Vertica node.

There are two memory pools in Vertica, global and SAL. The global memory pool is related to Vertica catalog objects. The SAL memory pool is related to the system storage layer. These memory pools are physical structures from which Vertica allocates and reuses portions of memory.

Within the memory pools, there are two allocation types. Both global and SAL memory pools include chunk and object memory allocation types.

  • Chunk allocations are from tiered storage, and are grouped into sizes, in bytes, that are powers of 2.

  • Object allocations are object types, for example, a table or projection. Each object assumes a set size.

The table provides detailed information on these memory pool allocations.

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node from which Vertica has collected this allocator information.
POOL_NAME VARCHAR

One of two memory pools:

  • global: Memory pool is related to Vertica catalog objects.

  • SAL: Memory pool is related to the system storage layer.

ALLOCATION_TYPE VARCHAR

One of two memory allocation types:

  • chunk: Chunk allocations are grouped into sizes that are powers of 2.

  • object: Object allocations assume a set amount of memory based upon the specific object.

UNIT_SIZE INTEGER

The size, in bytes, of the memory allocation.

For example, if the allocation type is a table (an object type), then Vertica allots 8 bytes.

FREE_COUNT INTEGER

Indicates the count of blocks of freed memory that Vertica has reserved for future memory needs.

For example, if you delete a table, Vertica reserves the 8 bytes originally allotted for the table. The 8 bytes freed become 1 unit of memory that Vertica adds to this column.

FREE_BYTES INTEGER

Indicates the number of freed memory bytes.

For example, with a table deletion, Vertica adds 8 bytes to this column.

USED_COUNT INTEGER

Indicates the count of in-use blocks for this allocation.

For example, if your database includes two table objects, Vertica adds 2 to this column.

USED_BYTES INTEGER

The number of bytes of in-use blocks of memory.

For example, if your database includes two table objects, each of which assume 8 bytes, Vertica adds 16 to this column.

TOTAL_SIZE INTEGER Indicates the number of bytes that is the sum of all free and used memory.
CAPTURE_TIME TIMESTAMPTZ Indicates the current timestamp for when Vertica collected the for this table.
ALLOCATION_NAME VARCHAR

Provides the name of the allocation type.

  • If the allocation is an object type, provides the name of the object. For example, CAT::Schema. Object types can also have the name internal, meaning that the object is an internal data structure.

    Those object types that are not internal are prefaced with either CAT or SAL. Those prefaced with CAT indicate memory from the global memory pool. SAL indicates memory from the system storage memory pool.

  • If the allocation type is chunk, indicates a power of 2 in this field to represent the number of bytes assumed by the chunk. For example, 2^5.

Sample: how memory pool memory is allotted, retained, and freed

The following table shows sample column values based upon a hypothetical example. The sample illustrates how column values change based upon addition or deletion of a table object.

  • When you add a table object (t1), Vertica assumes a UNIT_SIZE of 8 bytes, with a USED_COUNT of 1.

  • When you add a second table object (t2), the USED_COUNT increases to 2. Since each object assumes 8 bytes, USED_BYTES increases to 16.

  • When you delete one of the two table objects, Vertica USED_COUNT decreases to 1, and USED_BYTES decreases to 8. Since Vertica retains the memory for future use, FREE_BYTES increases to 8, and FREE_COUNT increases to 1.

  • Finally, when you create a new table object (t3), Vertica frees the memory for reuse. FREE_COUNT and FREE_BYTES return to 0.

Column Names Add One Table Object (t1) Add a Second Table Object (t2) Delete a Table Object (t2) Create a New Table Object (t3)
NODE_NAME v_vmart_node0001 v_vmart_node0001 v_vmart_node0001 v_vmart_node0001
POOL_NAME global global global global
ALLOCATION_TYPE object object object object
UNIT_SIZE 8 8 8 8
FREE_COUNT 0 0 1 0
FREE_BYTES 0 0 8 0
USED_COUNT 1 2 1 2
USED_BYTES 8 16 8 16
TOTAL_SIZE 8 16 16 16
CAPTURE_TIME 2017-05-24 13:28:07.83855-04 2017-05-24 14:16:04.480953-04 2017-05-24 14:16:32.077322-04 2017-05-24 14:17:07.320745-04
ALLOCATION_NAME CAT::Table CAT::Table CAT::Table CAT::Table

Examples

The following example shows one sample record for a chunk allocation type, and one for an object type.

=> \x
Expanded display is on.


=> select * from allocator_usage;
-[ RECORD 1 ]---+-----------------------------
node_name       | v_vmart_node0004
pool_name       | global
allocation_type | chunk
unit_size       | 8
free_count      | 1069
free_bytes      | 8552
used_count      | 7327
used_bytes      | 58616
total_size      | 67168
capture_time    | 2017-05-24 13:28:07.83855-04
allocation_name | 2^3
.
.
.
-[ RECORD 105 ]-+------------------------------
node_name       | v_vmart_node0004
pool_name       | SAL
allocation_type | object
unit_size       | 128
free_count      | 0
free_bytes      | 0
used_count      | 2
used_bytes      | 256
total_size      | 256
capture_time    | 2017-05-24 14:44:30.153892-04
allocation_name | SAL::WOSAlloc
.
.
.

3 - COLUMN_STORAGE

Returns the amount of disk storage used by each column of each projection on each node.

Returns the amount of disk storage used by each column of each projection on each node.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
COLUMN_ID INTEGER Catalog-assigned integer value that uniquely identifies thecolumn.
COLUMN_NAME VARCHAR The column name for which information is listed.
ROW_COUNT INTEGER The number of rows in the column.
USED_BYTES INTEGER The disk storage allocation of the column in bytes.
ENCODINGS VARCHAR The encoding type for the column.
COMPRESSION VARCHAR The compression type for the column. You can compare ENCODINGS and COMPRESSION columns to see how different encoding types affect column storage when optimizing for compression.
ROS_COUNT INTEGER The number of ROS containers.
PROJECTION_ID INTEGER Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME VARCHAR The associated projection name for the column.
PROJECTION_SCHEMA VARCHAR The name of the schema associated with the projection.
ANCHOR_TABLE_ID INTEGER Catalog-assigned integer value that uniquely identifies theanchor table.
ANCHOR_TABLE_NAME VARCHAR The associated table name.
ANCHOR_TABLE_SCHEMA VARCHAR The associated table's schema name.
ANCHOR_TABLE_COLUMN_ID VARCHAR Catalog-assigned VARCHAR value that uniquely identifies a table column.
ANCHOR_TABLE_COLUMN_NAME VARCHAR The name of the anchor table.

4 - COMMUNAL_CLEANUP_RECORDS

This system table lists files that Vertica considers leaked on an Eon Mode communal storage.

Eon Mode only

This system table lists files that Vertica considers leaked on an Eon Mode communal storage. Leaked files are files that are detected as needing deletion but were missed by the normal cleanup mechanisms. This information helps you determine how much space on the communal storage you can reclaim or have reclaimed by cleaning up the leaked files.

Column Name Data Type Description
detection_timestamp TIMESTAMPTZ Timestamp at which the file was detected as leaked.
location_path VARCHAR The path of communal storage location.
file_name VARCHAR The name of the leaked file.
size_in_bytes INTEGER The size of the leaked file in bytes.
queued_for_delete BOOLEAN Specifies whether the file was queued for deletion. Files queued for deletion might not be deleted right away. Also, a subsequent call to clean_communal_storage reports these files as leaked if the files hadn't already been deleted.

Examples

=> SELECT clean_communal_storage('true');
                                                                     clean_communal_storage
----------------------------------------------------------------------------------------------------------------------------------------------
 CLEAN COMMUNAL STORAGE
Total leaked files: 10
Total size: 217088
Files have been queued for deletion.
Check communal_cleanup_records for more information.
(1 row)

=> SELECT * FROM communal_cleanup_records;
      detection_timestamp      | location_path     |                       file_name                       | size_in_bytes | queued_for_delete
-------------------------------+-------------------+-------------------------------------------------------+---------------+-------------------
 2018-05-01 17:01:34.045955-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000003_0.gt |         28672 | t
 2018-05-01 17:01:34.045966-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000006_0.gt |         28672 | t
 2018-05-01 17:01:34.045952-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000001_0.gt |         36864 | t
 2018-05-01 17:01:34.045974-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000008_0.gt |         36864 | t
 2018-05-01 17:01:34.045981-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000002_0.gt |         12288 | t
 2018-05-01 17:01:34.045986-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000007_0.gt |          8192 | t
 2018-05-01 17:01:34.045991-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000010_0.gt |         16384 | t
 2018-05-01 17:01:34.046001-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000005_0.gt |         24576 | t
 2018-05-01 17:01:34.046026-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000009_0.gt |          4096 | t
 2018-05-01 17:01:34.046033-04 | s3://chumbucket/  | 020000000000000000000000000000000000000000000004_0.gt |         20480 | t
(10 rows)

See also

CLEAN_COMMUNAL_STORAGE

5 - COMMUNAL_TRUNCATION_STATUS

Stores information on the state of the cluster in the case of a catalog truncation event.

Eon Mode only

Stores information on the state of the cluster in the case of a catalog truncation event.

Column Name Data Type Description
CURRENT_CATALOG_VERSION VARCHAR Current value of the catalog truncation version (CTV).
CLUSTER_TRUNCATION_VERSION VARCHAR The value of the CTV from the cluster_config.json file.

Examples

=> SELECT * FROM COMMUNAL_TRUNCATION_STATUS;
current_catalog_version  | cluster_truncation_version
-------------------------+----------------------------
                    35   |                        35

6 - CONFIGURATION_CHANGES

Records the change history of system configuration parameters.

Records the change history of system configuration parameters. This information is useful for identifying:

  • Who changed the configuration parameter value

  • When the configuration parameter was changed

  • Whether nonstandard settings were in effect in the past

Column Name Data Type Description
EVENT_TIMESTAMP TIMESTAMPTZ Time when the row was recorded.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_ID INTEGER Identifier of the user who changed configuration parameters.
USER_NAME VARCHAR Name of the user who changed configuration parameters at the time Vertica recorded the session.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
PARAMETER VARCHAR Name of the changed parameter. See Configuration parameter management for a detailed list of supported parameters.
VALUE VARCHAR New value of the configuration parameter.

Privileges

Superuser

7 - CONFIGURATION_PARAMETERS

Provides information about all configuration parameters that are currently in use by the system.

Provides information about all configuration parameters that are currently in use by the system.

Column Name Data Type Description
NODE_NAME VARCHAR

Node names of the database cluster.

ALL indicates that all nodes have the same value.

PARAMETER_NAME VARCHAR The parameter name.
CURRENT_VALUE VARCHAR The parameter's current setting.
RESTART_VALUE VARCHAR The parameter's value after the next restart.
DATABASE_VALUE VARCHAR The value that is set at the database level. If no database-level value is set, the value reflects the default value.
DEFAULT_VALUE VARCHAR The parameter's default value.
CURRENT_LEVEL VARCHAR

Level at which CURRENT_VALUE is set, one of the following:

  • NODE

  • DATABASE

  • SESSION

  • DEFAULT

RESTART_LEVEL VARCHAR

Level at which the parameter will be set after the next restart, one of the following:

  • NODE

  • DATABASE

  • DEFAULT

IS_MISMATCH BOOLEAN Whether CURRENT_VALUE and RESTART_VALUE match.
GROUPS VARCHAR

A group to which the parameter belongs—for example, OptVOptions.

ALLOWED_LEVELS VARCHAR

Levels at which the specified parameter can be set, a comma-delimited list of any of the following values:

  • DATABASE

  • NODE

  • SESSION

  • USER

SUPERUSER_VISIBLE_ONLY BOOLEAN

Whether non-superusers can view all parameter settings. If true, the following columns are masked to non-superusers:

  • CURRENT_VALUE

  • RESTART_VALUE

  • DATABASE_VALUE

  • DEFAULT_VALUE

CHANGE_UNDER_SUPPORT_GUIDANCE BOOLEAN Whether the parameter is intended for use only under guidance from Vertica technical support.
CHANGE_REQUIRES_RESTART BOOLEAN Whether the configuration change requires a restart.
DESCRIPTION VARCHAR Describes the parameter's usage.

Examples

The following example shows a case where the parameter requires a restart for the new setting to take effect:

=> SELECT * FROM CONFIGURATION_PARAMETERS WHERE parameter_name = 'RequireFIPS';
-[ RECORD 1 ]-----------------+----------------------
node_name                     | ALL
parameter_name                | RequireFIPS
current_value                 | 0
restart_value                 | 0
database_value                | 0
default_value                 | 0
current_level                 | DEFAULT
restart_level                 | DEFAULT
is_mismatch                   | f
groups                        |
allowed_levels                | DATABASE
superuser_visible_only        | f
change_under_support_guidance | f
change_requires_restart       | t
description                   | Execute in FIPS mode

The following example shows a case where a non-superuser is viewing a parameter with restricted visibility:

=> \c VMart nonSuperuser
You are now connected to database "VMart" as user "nonSuperuser".
=> SELECT * FROM CONFIGURATION_PARAMETERS WHERE superuser_visible_only = 't';
-[ RECORD 1 ]-----------------+-------------------------------------------------------
node_name                     | ALL
parameter_name                | S3BucketCredentials
current_value                 | ********
restart_value                 | ********
database_value                | ********
default_value                 | ********
current_level                 | DEFAULT
restart_level                 | DEFAULT
is_mismatch                   | f
groups                        |
allowed_levels                | SESSION, DATABASE
superuser_visible_only        | t
change_under_support_guidance | f
change_requires_restart       | f
description                   | JSON list mapping S3 buckets to specific credentials.

See also

Configuration parameter management

8 - CPU_USAGE

Records CPU usage history on the system.

Records CPU usage history on the system.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
START_TIME TIMESTAMP Beginning of history interval.
END_TIME TIMESTAMP End of history interval.
AVERAGE_CPU_USAGE_PERCENT FLOAT Average CPU usage in percent of total CPU time (0-100) during history interval.

Privileges

Superuser

9 - CRITICAL_HOSTS

Lists the critical hosts whose failure would cause the database to become unsafe and force a shutdown.

Lists the critical hosts whose failure would cause the database to become unsafe and force a shutdown.

Column Name Data Type Description
HOST_NAME VARCHAR Name of a critical host

Privileges

None

10 - CRITICAL_NODES

Lists the whose failure would cause the database to become unsafe and force a shutdown.

Lists the critical nodes whose failure would cause the database to become unsafe and force a shutdown.

Column Name Data Type Description
NODE_ID INTEGER Catalog-assigned integer value that uniquely identifies thenode.
NODE_NAME VARCHAR Name of a critical node.

11 - CRITICAL_SUBCLUSTERS

Lists the primary subclusters whose loss would cause the database to become unsafe and force it to shutdown.

Lists the primary subclusters whose loss would cause the database to become unsafe and force it to shutdown. Vertica checks this table before stopping a subcluster to ensure it will not trigger a database shutdown. If you attempt to stop or remove a subcluster in this table, Vertica returns an error message. See Starting and stopping subclusters for more information.

This table only has contents when the database is in Eon Mode and when one or more subclusters are critical.

Column Name Data Type Description
SUBCLUSTER_OID INTEGER Unique identifier for the subcluster.
SUBCLUSTER_NAME VARCHAR The name of the subcluster in a critical state.

Examples

=> SELECT * FROM critical_subclusters;
  subcluster_oid   |  subcluster_name
-------------------+--------------------
 45035996273704996 | default_subcluster
(1 row)

See also

12 - CURRENT_SESSION

Returns information about the current active session.

Returns information about the current active session. Use this table to find out the current session's sessionID and get the duration of the previously-run query.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node for which information is listed
USER_NAME VARCHAR Name used to log into the database, NULL if the session is internal
CLIENT_OS_HOSTNAME VARCHAR The hostname of the client as reported by their operating system.
CLIENT_HOSTNAME VARCHAR

The IP address and port of the TCP socket from which the client connection was made; NULL if the session is internal.

Vertica accepts either IPv4 or IPv6 connections from a client machine. If the client machine contains mappings for both IPv4 and IPv6, the server randomly chooses one IP address family to make a connection. This can cause the CLIENT_HOSTNAME column to display either IPv4 or IPv6 values, based on which address family the server chooses.

TYPE INTEGER

Identifies the session type, one of the following integer values:

  • 1: Client

  • 2: DBD

  • 3: Merge out

  • 4: Move out

  • 5: Rebalance cluster

  • 6: Recovery

  • 7: Refresh

  • 8: Shutdown

  • 9: License audit

  • 10: Timer service

  • 11: Connection

  • 12: VSpread

  • 13: Sub-session

  • 14: Repartition table

CLIENT_PID INTEGER Process identifier of the client process that issued this connection. This process might be on a different machine than the server.
LOGIN_TIMESTAMP TIMESTAMP When the user logged into the database or the internal session was created. This column can help identify open sessions that are idle.
SESSION_ID VARCHAR Identifier required to close or interrupt a session. This identifier is unique within the cluster at any point in time, but can be reused when the session closes.
CLIENT_LABEL VARCHAR User-specified label for the client connection that can be set when using ODBC. See Label in ODBC DSN connection properties.
TRANSACTION_START TIMESTAMP When the current transaction started, NULL if no transaction is running
TRANSACTION_ID VARCHAR Hexadecimal identifier of the current transaction, NULL if no transaction is in progress
TRANSACTION_DESCRIPTION VARCHAR Description of the current transaction
STATEMENT_START TIMESTAMP When the current statement started execution, NULL if no statement is running
STATEMENT_ID VARCHAR Unique numeric ID for the currently-running statement, NULL if no statement is being processed. Combined, TRANSACTION_ID and STATEMENT_ID uniquely identify a statement within a session.
LAST_STATEMENT_DURATION_US INTEGER Duration in microseconds of the last completed statement
CURRENT_STATEMENT VARCHAR The currently-running statement, if any. NULL indicates that no statement is currently being processed.
LAST_STATEMENT VARCHAR NULL if the user has just logged in, otherwise the currently running statement or most recently completed statement.
EXECUTION_ENGINE_PROFILING_CONFIGURATION VARCHAR See Profiling Settings below.
QUERY_PROFILING_CONFIGURATION VARCHAR See Profiling Settings below.
SESSION_PROFILING_CONFIGURATION VARCHAR See Profiling Settings below.
CLIENT_TYPE VARCHAR

Type of client from which the connection was made, one of the following:

  • ADO.NET Driver

  • ODBC Driver

  • JDBC Driver

  • vsql

CLIENT_VERSION VARCHAR Client version
CLIENT_OS VARCHAR Client operating system
CLIENT_OS_USER_NAME VARCHAR Identifies the user that logged into the database, also set for unsuccessful login attempts.
REQUESTED_PROTOCOL VARCHAR Communication protocol version that the ODBC client driver sends to Vertica server, used to support backward compatibility with earlier server versions.
EFFECTIVE_PROTOCOL VARCHAR Minimum protocol version supported by client and driver.

Profiling settings

The following columns show settings for different profiling categories:

  • EXECUTION_ENGINE_PROFILING_CONFIGURATION

  • QUERY_PROFILING_CONFIGURATION

  • SESSION_PROFILING_CONFIGURATION

These can have the following values:

  • Empty: No profiling is set

  • Session: On for current session.

  • Global: On by default for all sessions.

  • >Session, Global: On by default for all sessions, including current session.

For information about controlling profiling settings, see Enabling profiling.

13 - DATA_COLLECTOR

Shows settings for all components: their current retention policy properties and other data collection statistics.

Shows settings for all Data collector components: their current retention policy properties and other data collection statistics.

Data Collector is on by default. To turn it off, set configuration parameter EnableDataCollector to 0.

Column Name Data Type Description
NODE_NAME VARCHAR Node name on which data is stored.
COMPONENT VARCHAR Name of the component.
TABLE_NAME VARCHAR The data collector table name for which information is listed.
DESCRIPTION VARCHAR Short description about the component.
ACCESS_RESTRICTED BOOLEAN Indicates whether access to the table is restricted to the DBADMIN, PSEUDOSUPERUSER, or SYSMONITOR roles.
MEMORY_BUFFER_SIZE_KB INTEGER Specifies in kilobytes the maximum amount of data that is buffered in memory before moving it to disk. You can modify this value with SET_DATA_COLLECTOR_POLICY.
DISK_SIZE_KB INTEGER Specifies in kilobytes the maximum disk space allocated for this component's Data Collector table. If set to 0, the Data Collector retains only as much component data as it can buffer in memory, as specified by MEMORY_BUFFER_SIZE_KB. You can modify this value with SET_DATA_COLLECTOR_POLICY.
INTERVAL_SET BOOLEAN Boolean, specifies whether time-based retention is enabled (INTERVAL_TIME is ≥ 0).
INTERVAL_TIME INTERVAL

INTERVAL data type that specifies how long data of a given component is retained in that component's Data Collector table. You can modify this value with SET_DATA_COLLECTOR_POLICY or SET_DATA_COLLECTOR_TIME_POLICY.

For example, if you specify component TupleMoverEvents and set interval-time to an interval of two days ('2 days'::interval), the Data Collector table dc_tuple_mover_events retains records of Tuple Mover activity over the last 48 hours. Older Tuple Mover data are automatically dropped from this table.

RECORD_TOO_BIG_ERRORS INTEGER Integer that increments by one each time an error is thrown because data did not fit in memory (based on the data collector retention policy).
LOST_BUFFERS INTEGER Number of buffers lost.
LOST_RECORDS INTEGER Number of records lost.
RETIRED_FILES INTEGER Number of retired files.
RETIRED_RECORDS INTEGER Number of retired records.
CURRENT_MEMORY_RECORDS INTEGER The current number of rows in memory.
CURRENT_DISK_RECORDS INTEGER The current number of rows stored on disk.
CURRENT_MEMORY_BYTES INTEGER Total current memory used in kilobytes.
CURRENT_DISK_BYTES INTEGER Total current disk space used in kilobytes.
FIRST_TIME TIMESTAMP Timestamp of the first record.
LAST_TIME TIMESTAMP Timestamp of the last record
KB_PER_DAY FLOAT Total kilobytes used per day.

Examples

Get the current status of resource pools:

=> SELECT * FROM data_collector WHERE component = 'ResourcePoolStatus' ORDER BY node_name;
-[ RECORD 1 ]----------+---------------------------------
node_name              | v_vmart_node0001
component              | ResourcePoolStatus
table_name             | dc_resource_pool_status
description            | Resource Pool status information
access_restricted      | t
memory_buffer_size_kb  | 64
disk_size_kb           | 25600
interval_set           | f
interval_time          | 0
record_too_big_errors  | 0
lost_buffers           | 0
lost_records           | 0
retired_files          | 385
retired_records        | 3492335
current_memory_records | 0
current_disk_records   | 30365
current_memory_bytes   | 0
current_disk_bytes     | 21936993
first_time             | 2020-08-14 11:03:28.007894-04
last_time              | 2020-08-14 11:59:41.005675-04
kb_per_day             | 548726.098227313
-[ RECORD 2 ]----------+---------------------------------
node_name              | v_vmart_node0002
component              | ResourcePoolStatus
table_name             | dc_resource_pool_status
description            | Resource Pool status information
access_restricted      | t
memory_buffer_size_kb  | 64
disk_size_kb           | 25600
interval_set           | f
interval_time          | 0
record_too_big_errors  | 0
lost_buffers           | 0
lost_records           | 0
retired_files          | 385
retired_records        | 3492335
current_memory_records | 0
current_disk_records   | 28346
current_memory_bytes   | 0
current_disk_bytes     | 20478345
first_time             | 2020-08-14 11:07:12.006484-04
last_time              | 2020-08-14 11:59:41.004825-04
kb_per_day             | 548675.811828872
-[ RECORD 3 ]----------+---------------------------------
node_name              | v_vmart_node0003
component              | ResourcePoolStatus
table_name             | dc_resource_pool_status
description            | Resource Pool status information
access_restricted      | t
memory_buffer_size_kb  | 64
disk_size_kb           | 25600
interval_set           | f
interval_time          | 0
record_too_big_errors  | 0
lost_buffers           | 0
lost_records           | 0
retired_files          | 385
retired_records        | 3492335
current_memory_records | 0
current_disk_records   | 28337
current_memory_bytes   | 0
current_disk_bytes     | 20471843
first_time             | 2020-08-14 11:07:13.008246-04
last_time              | 2020-08-14 11:59:41.006729-04
kb_per_day             | 548675.63541403

See also

14 - DATA_READS

Lists each storage location that a query reads in Eon Mode.

Eon Mode only

Lists each storage location that a query reads in Eon Mode. If the query fetches data from multiple locations, this table provides a row for each location per node that read data. For example, a query might run on three nodes and fetch data from the depot and communal storage. In this case, the table displays six rows for the query: three rows for each node's depot read, and three for each node's communal storage read.

Column Name Column Type Description
START_TIME TIMESTAMP When Vertica started reading data from the location.
NODE_NAME VARCHAR Name of the node that fetched the data
SESSION_ID VARCHAR Unique numeric ID assigned by the Vertica catalog, which identifies the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INT Unique numeric ID assigned by the Vertica catalog, which identifies the user.
USER_NAME VARCHAR Name of the user running the query.
TRANSACTION_ID INT Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
STATEMENT_ID INT Unique numeric ID for the statement that read the data. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session; these columns are useful for creating joins with other system tables.
REQUEST_ID INT ID of the data request.
LOCATION_ID INT ID of the storage location read.
LOCATION_PATH VARCHAR Path of the storage container read by the query.
BYTES_READ INT Number of bytes read by the query from this location.

Examples

=> SELECT * FROM V_MONITOR.DATA_READS WHERE TRANSACTION_ID = 45035996273707457;
-[ RECORD 1 ]--+---------------------------------------------------
start_time     | 2019-02-13 19:43:43.840836+00
node_name      | v_verticadb_node0001
session_id     | v_verticadb_node0001-11828:0x6f3
user_id        | 45035996273704962
user_name      | dbadmin
transaction_id | 45035996273707457
statement_id   | 1
request_id     | 230
location_id    | 45035996273835000
location_path  | /vertica/data/verticadb/v_verticadb_node0001_depot
bytes_read     | 329460142
-[ RECORD 2 ]--+---------------------------------------------------
start_time     | 2019-02-13 19:43:43.8421+00
node_name      | v_verticadb_node0002
session_id     | v_verticadb_node0001-11828:0x6f3
user_id        | 45035996273704962
user_name      | dbadmin
transaction_id | 45035996273707457
statement_id   | 1
request_id     | 0
location_id    | 45035996273835002
location_path  | /vertica/data/verticadb/v_verticadb_node0002_depot
bytes_read     | 329473033
-[ RECORD 3 ]--+---------------------------------------------------
start_time     | 2019-02-13 19:43:43.841845+00
node_name      | v_verticadb_node0003
session_id     | v_verticadb_node0001-11828:0x6f3
user_id        | 45035996273704962
user_name      | dbadmin
transaction_id | 45035996273707457
statement_id   | 1
request_id     | 0
location_id    | 45035996273835004
location_path  | /vertica/data/verticadb/v_verticadb_node0003_depot
bytes_read     | 329677294

15 - DATABASE_BACKUPS

Lists historical information for each backup that successfully completed after running the vbr utility.

Lists historical information for each backup that successfully completed after running the vbr utility. This information is useful for determining whether to create a new backup before you advance the AHM. Because this system table displays historical information, its contents do not always reflect the current state of a backup repository. For example, if you delete a backup from a repository, the DATABASE_BACKUPS system table continues to display information about it.

To list existing backups, run vbr as described in Viewing backups.

Column Name Data Type Description
BACKUP_TIMESTAMP TIMESTAMP The timestamp of the backup.
NODE_NAME VARCHAR The name of the initiator node that performed the backup.
SNAPSHOT_NAME VARCHAR The name of the backup, as specified in the snapshotName parameter of the vbr configuration file.
BACKUP_EPOCH INTEGER The database epoch at which the backup was saved.
NODE_COUNT INTEGER The number of nodes backed up in the completed backup, and as listed in the [Mappingn] sections of the configuration file.
OBJECTS VARCHAR The name of the object(s) contained in an object-level backup. This column is empty if the record is for a full cluster backup.
FILE_SYSTEM_TYPE VARCHAR The type of file system, such as Linux.

Privileges

Superuser

16 - DATABASE_CONNECTIONS

Lists the connections to other databases for importing and exporting data.

Lists the connections to other databases for importing and exporting data. See Moving Data Between Vertica Databases.

Column Name Data Type Description
DATABASE VARCHAR The name of the connected database
USERNAME VARCHAR The username used to create the connection
HOST VARCHAR The host name used to create the connection
PORT VARCHAR The port number used to create the connection
ISVALID BOOLEAN Whether the connection is still open and usable or not

Examples

=> CONNECT TO VERTICA vmart USER dbadmin PASSWORD '' ON '10.10.20.150',5433;
CONNECT
=> SELECT * FROM DATABASE_CONNECTIONS;
 database | username |     host     | port | isvalid
----------+----------+--------------+------+---------
 vmart    | dbadmin  | 10.10.20.150 | 5433 | t
(1 row)

17 - DATABASE_MIGRATION_STATUS

Provides real-time and historical data on Enterprise-to- database migration attempts.

Provides real-time and historical data on Enterprise-to-Eon database migration attempts.

Column Name Data Type Description
NODE_NAME VARCHAR Name of a node in the source Enterprise database.
TRANSACTION_ID VARCHAR Hexadecimal identifier of the migration process transaction.
PHASE VARCHAR

A stage of database migration on a given node, one of the following, listed in order of execution:

  • Catalog Conversion: Conversion of enterprise-mode catalog to Eon-compatible catalog.

  • Data Transfer: Transfer of data files and library files to communal storage

  • Catalog Transfer: Includes transfer of checkpoint and transaction log files.

STATUS VARCHAR

Specifies status of a given phase, one of the following:

  • RUNNING

  • COMPLETED

  • ABORT

ABORT indicates a given migration phase was unable to complete—for example, the client disconnected, or a network outage occurred—and the migration returned with an error. In this case, call MIGRATE_ENTERPRISE_TO_EON again to restart migration. For details, see Handling Interrupted Migration.

BYTES_TO_TRANSFER INTEGER

For each migration phase, the size of data to transfer to communal storage, set when phase status is RUNNING:

  • Catalog Conversion: 0

  • Data Transfer: Size of data files and library files

  • Catalog Transfer: Size of transaction logs

BYTES_TRANSFERRED INTEGER

For each migration phase, the size of data transfered to communal storage. This value is updated while phase status is RUNNING, and set to the total number of bytes transferred when status is COMPLETED:

  • Catalog Conversion: 0

  • Data Transfer: Size of data files and library files

  • Catalog Transfer: Size of transaction logs

COMMUNAL_STORAGE_LOCATION VARCHAR URL of targeted communal storage location
START_TIME
 TIMESTAMP Demarcate the start and end of each PHASE-specified migration operation.
END_TIME

Privileges

Superuser

Examples

The following example shows data of a migration that is in progress:

=> SELECT node_name, phase, status, bytes_to_transfer, bytes_transferred, communal_storage_location FROM database_migration_status ORDER BY node_name, start_time;
     node_name    |       phase        |  status   | bytes_to_transfer | bytes_transferred | communal_storage_location
------------------+--------------------+-----------+-------------------+------------------+---------------------------
 v_vmart_node0001 | Catalog Conversion | COMPLETED |                 0 |                0 | s3://verticadbbucket/
 v_vmart_node0001 | Data Transfer      | COMPLETED |              1134 |             1134 | s3://verticadbbucket/
 v_vmart_node0001 | Catalog Transfer   | COMPLETED |              3765 |             3765 | s3://verticadbbucket/
 v_vmart_node0002 | Catalog Conversion | COMPLETED |                 0 |                0 | s3://verticadbbucket/
 v_vmart_node0002 | Data Transfer      | COMPLETED |              1140 |             1140 | s3://verticadbbucket/
 v_vmart_node0002 | Catalog Transfer   | COMPLETED |              3766 |             3766 | s3://verticadbbucket/
 v_vmart_node0003 | Catalog Conversion | COMPLETED |                 0 |                0 | s3://verticadbbucket/
 v_vmart_node0003 | Data Transfer      | RUNNING   |           5272616 |           183955 | s3://verticadbbucket/

18 - DELETE_VECTORS

Holds information on deleted rows to speed up the delete process.

Holds information on deleted rows to speed up the delete process.

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node storing the deleted rows.
SCHEMA_NAME VARCHAR The name of the schema where the deleted rows are located.
PROJECTION_NAME VARCHAR The name of the projection where the deleted rows are located.
DV_OID INTEGER The unique numeric ID (OID) that identifies this delete vector.
STORAGE_OID INTEGER The unique numeric ID (OID) that identifies the storage container that holds the delete vector.
SAL_STORAGE_ID VARCHAR Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
DELETED_ROW_COUNT INTEGER The number of rows deleted.
USED_BYTES INTEGER The number of bytes used to store the deletion.
START_EPOCH INTEGER The start epoch of the data in the delete vector.
END_EPOCH INTEGER The end epoch of the data in the delete vector.

Examples

After you delete data from a Vertica table, that data is marked for deletion. To see the data that is marked for deletion, query the DELETE_VECTORS system table.

Run PURGE to remove the delete vectors from ROS containers.

=> SELECT * FROM test1;
 number
--------
      3
     12
     33
     87
     43
     99
(6 rows)
=> DELETE FROM test1 WHERE number > 50;
 OUTPUT
--------
      2
(1 row)
=> SELECT * FROM test1;
 number
--------
     43
      3
     12
     33
(4 rows)
=> SELECT node_name, projection_name, deleted_row_count FROM DELETE_VECTORS;
    node_name     | projection_name | deleted_row_count
------------------+-----------------+-------------------
 v_vmart_node0002 | test1_b1        |                 1
 v_vmart_node0001 | test1_b1        |                 1
 v_vmart_node0001 | test1_b0        |                 1
 v_vmart_node0003 | test1_b0        |                 1
(4 rows)
=> SELECT PURGE();
...
(Table: public.test1) (Projection: public.test1_b0)
(Table: public.test1) (Projection: public.test1_b1)
...
(4 rows)

After the ancient history mark (AHM) advances:

=> SELECT * FROM DELETE_VECTORS;
 (No rows)

See also

19 - DEPLOY_STATUS

Records the history of deployed Database Designer designs and their deployment steps.

Records the history of deployed Database Designer designs and their deployment steps.

Column Name Data Type Description
EVENT_TIME TIMESTAMP Time when the row recorded the event.
USER_NAME VARCHAR Name of the user who deployed a design at the time Vertica recorded the session.
DEPLOY_NAME VARCHAR Name the deployment, same as the user-specified design name.
DEPLOY_STEP VARCHAR Steps in the design deployment.
DEPLOY_STEP_STATUS VARCHAR Textual status description of the current step in the deploy process.
DEPLOY_STEP_COMPLETE_PERCENT FLOAT Progress of current step in percentage (0–100).
DEPLOY_COMPLETE_PERCENT FLOAT Progress of overall deployment in percentage (0–100).
ERROR_MESSAGE VARCHAR Error or warning message during deployment.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

20 - DEPLOYMENT_PROJECTION_STATEMENTS

Contains information about CREATE PROJECTION statements used to deploy a database design.

Contains information about CREATE PROJECTION statements used to deploy a database design. Each row contains information about a different CREATE PROJECTION statement. The function DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY populates this table.

Column Name Column Type Description
DEPLOYMENT_ID INTEGER Unique ID that Database Designer assigned to the deployment.
DESIGN_NAME VARCHAR Unique name that the user assigned to the design.
DEPLOYMENT_PROJECTION_ID INTEGER Unique ID assigned to the output projection by Database Designer.
STATEMENT_ID INTEGER Unique ID assigned to the statement type that creates the projection.
STATEMENT VARCHAR Text for the statement that creates the projection.

21 - DEPLOYMENT_PROJECTIONS

Contains information about projections created and dropped during the design.

Contains information about projections created and dropped during the design. Each row contains information about a different projection. Database Designer populates this table after the design is deployed.

Column Name Column Type Description
deployment_id INTEGER Unique ID that Database Designer assigned to the deployment.
deployment_projection_id INTEGER Unique ID that Database Designer assigned to the output projection.
design_name VARCHAR Name of the design being deployed.
deployment_projection_name VARCHAR Name that Database Designer assigned to the projection.
anchor_table_schema VARCHAR Name of the schema that contains the table the projection is based on.
anchor_table_name VARCHAR Name of the table the projection is based on.
deployment_operation VARCHAR Action being taken on the projection, for example, add or drop.
deployment_projection_type VARCHAR

Indicates whether Database Designer has proposed new projections for this design (DBD) or is using the existing catalog design (CATALOG). The REENCODED suffix indicates that the projection sort order and segmentation are the same, but the projection columns have new encodings:

  • DBD

  • CATALOG

  • DBD_REENCODED

  • CATALOG_REENCODED

deploy_weight INTEGER Weight of this projection in creating the design. This field is always 0 for projections that have been dropped.
estimated_size_on_disk INTEGER Approximate size of the projection on disk, in MB.

22 - DEPOT_EVICTIONS

Records data on eviction of objects from the depot.

Eon Mode only

Records data on eviction of objects from the depot.

Column Name Data Type Description
START_TIME TIMESTAMP Demarcate the start and end of each depot eviction operation.
END_TIME
NODE_NAME VARCHAR Name of a node where the eviction occurred.
SESSION_ID VARCHAR Unique numeric ID assigned by the Vertica catalog, which identifies the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the user.
USER_NAME VARCHAR The user who made changes to the depot.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
STATEMENT_ID INTEGER Unique numeric ID for the statement that caused the eviction. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session; these columns are useful for creating joins with other system tables.
REQUEST_ID INTEGER Unique identifier of the query request in the user session.
STORAGE_ID VARCHAR Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
STORAGE_OID INTEGER Numeric ID assigned by the Vertica catalog, which identifies the storage.
FILE_SIZE_BYTES INTEGER The size of the file in bytes that was evicted.
NUMBER_HITS INTEGER The number of times the file was accessed.
LAST_ACCESS_TIME TIMESTAMP The last time the file was read.
REASON VARCHAR

The reason the file was evicted, one of the following:

  • DROP SUBSCRIPTION

  • CLEAR DEPOT

  • EVICTION DUE TO NEW

  • DROP OBJECT

  • LOAD

  • QUERY

  • PEER TO PEER FILL

  • DEPOT FILL AT STARTUP

  • DEPOT SIZE CHANGE

IS_PINNED BOOLEAN Specifies whether the file is pinned to this depot.

Examples

=> SELECT * FROM V_MONITOR.DEPOT_EVICTIONS LIMIT 2;
-[ RECORD 1 ]----+-------------------------------------------------
start_time       | 2019-02-20 15:32:26.765937+00
end_time         | 2019-02-20 15:32:26.766019+00
node_name        | v_verticadb_node0001
session_id       | v_verticadb_node0001-8997:0x3e
user_id          | 45035996273704962
user_name        | dbadmin
transaction_id   | 45035996273705450
statement_id     | 1
request_id       | 406
storage_id       | 0000000000000000000000000000000000a000000001fbf6
storage_oid      | 45035996273842065
file_size_bytes  | 61
number_hits      | 1
last_access_time | 2019-02-20 15:32:26.668094+00
reason           | DROP OBJECT
is_pinned        | f
-[ RECORD 2 ]----+-------------------------------------------------
start_time       | 2019-02-20 15:32:26.812803+00
end_time         | 2019-02-20 15:32:26.812866+00
node_name        | v_verticadb_node0001
session_id       | v_verticadb_node0001-8997:0x3e
user_id          | 45035996273704962
user_name        | dbadmin
transaction_id   | 45035996273705453
statement_id     | 1
request_id       | 409
storage_id       | 0000000000000000000000000000000000a000000001fbf6
storage_oid      | 45035996273842079
file_size_bytes  | 91
number_hits      | 1
last_access_time | 2019-02-20 15:32:26.770807+00
reason           | DROP OBJECT
is_pinned        | f

23 - DEPOT_FETCH_QUEUE

Lists all pending depot requests for queried file data to fetch from communal storage.

Eon Mode only

Lists all pending depot requests for queried file data to fetch from communal storage.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that requested the fetch.
SAL_STORAGE_ID VARCHAR Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
TRANSACTION_ID INTEGER Identifies the transaction that contains the fetch-triggering query.
SOURCE_FILE_NAME VARCHAR Full path in communal storage of the file to fetch.
DESTINATION_FILE_NAME VARCHAR Destination path of the file to fetch.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM depot_fetch_queue;
-[ RECORD 1 ]----------+-------------------------------------------------------------
node_name              | v_example_db_node0002
sal_storage_id         | 029b6fac864e1982531dcde47d00edc500d000000001d5e7
transaction_id         | 45035996273705983
source_file_name       | s3://mydata/mydb/14a/029b6fac864e1982531dcde47d00edc500d000
                              000001d5e7_0.gt
destination_file_name  | /vertica/example_db/v_example_db_node0002_depot/747/029b6fac
                              864e1982531dcde47d00edc500d000000001d5eb_0.gt
-[ RECORD 2 ]----------+-------------------------------------------------------------
node_name              | v_example_db_node0003
sal_storage_id         | 026635d69719c45e8d2d86f5a4d62c7b00b000000001d5e7
transaction_id         | 45035996273705983
source_file_name       | s3://mydata/mydb/4a5/029b6fac864e1982531dcde47d00edc500d0000
                              00001d5eb_0.gt
destination_file_name  | /vertica/example_db/v_example_db_node0002_depot/751/026635d6
                              9719c45e8d2d86f5a4d62c7b00b000000001d5e7_0.gt

24 - DEPOT_FETCHES

Records data of depot fetch requests.

Eon Mode only

Records data of depot fetch requests.

Column Name Data Type Description
START_TIME TIMESTAMP Demarcate the start and end of each depot fetch operation.
END_TIME
NODE_NAME VARCHAR Identifies the node that initiated fetch request.
TRANSACTION_ID INTEGER Uniquely identifies the transaction of the query that required the fetched file.
STORAGE_ID VARCHAR Unique hexadecimal numeric ID assigned by the Vertica catalog, to identify the storage.
STORAGE_OID INTEGER Numeric ID assigned by the Vertica catalog, which identifies the storage.
FILE_SIZE_BYTES INTEGER Fetch size in bytes.
SOURCE_FILE VARCHAR Source file path used, set to null if the file was fetched from a peer.
DESTINATION_FILE VARCHAR Destination file path
SOURCE_NODE VARCHAR

Source node from which the file was fetched, set to one of the following:

  • Name of the source node if file was fetched from a peer

  • Null if file was fetched from communal storage

IS_SUCCESSFUL BOOLEAN Boolean, specifies whether this fetch succeeded.
REASON VARCHAR Reason why the fetch failed, null if IS_SUCCESSFUL is true.

Examples

=> \x
Expanded display is on.

=> SELECT * FROM DEPOT_FETCHES LIMIT 2;
-[ RECORD 1 ]----+-------------------------------------------------------------
start_time       | 2019-08-30 15:16:15.125962+00
end_time         | 2019-08-30 15:16:15.126791+00
node_name        | v_verticadb_node0001
transaction_id   | 45035996273706225
storage_id       | 0239ef74126e70db410b301610f1e5b500b0000000020d65
storage_oid      | 45035996273842065
file_size_bytes  | 53033
source_file      |
destination_file | /vertica/data/verticadb/v_verticadb_node0001_depot/957/0239e
                     f74126e70db410b301610f1e5b500b0000000020d65_0.gt
source_node      | v_verticadb_node0002
is_successful    | t
reason           |
-[ RECORD 2 ]----+-------------------------------------------------------------
start_time       | 2019-08-30 15:16:15.285208+00
end_time         | 2019-08-30 15:16:15.285949+00
node_name        | v_verticadb_node0001
transaction_id   | 45035996273706234
storage_id       | 0239ef74126e70db410b301610f1e5b500b0000000020dc7
storage_oid      | 45035996273842075
file_size_bytes  | 69640
source_file      |
destination_file | /vertica/data/verticadb/v_verticadb_node0001_depot/055/0239e
                     f74126e70db410b301610f1e5b500b0000000020dc7_0.gt
source_node      | v_verticadb_node0002
is_successful    | t
reason           |

=> select node_name,transaction_id,storage_id,is_successful,reason FROM
   depot_fetches WHERE is_successful = 'f' LIMIT 3;
-[ RECORD 1 ]--+-------------------------------------------------
node_name      | v_verticadb_node0001
transaction_id | 45035996273721070
storage_id     | 0289281ac4c1f6580b95096fab25290800b0000000027d09
is_successful  | f
reason         | Could not create space in the depot
-[ RECORD 2 ]--+-------------------------------------------------
node_name      | v_verticadb_node0001
transaction_id | 45035996273721070
storage_id     | 0289281ac4c1f6580b95096fab25290800b0000000027d15
is_successful  | f
reason         | Could not create space in the depot
-[ RECORD 3 ]--+-------------------------------------------------
node_name      | v_verticadb_node0002
transaction_id | 45035996273721070
storage_id     | 02693f1c68266e38461084a840ee42aa00c0000000027d09
is_successful  | f
reason         | Could not create space in the depot

25 - DEPOT_FILES

Lists all objects contained in all database depots.

Eon Mode only

Lists all objects contained in all database depots.

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node this file is on.
SAL_STORAGE_ID VARCHAR Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
STORAGE_OID INTEGER Numeric ID assigned by the Vertica catalog, which identifies the storage.
COMMUNAL_FILE_PATH VARCHAR The path to the original file in communal storage. On AWS, this is an S3 URI.
DEPOT_FILE_PATH VARCHAR The path to the file in the depot.
SHARD_NAME VARCHAR The name of the shard this file is a part of.
STORAGE_TYPE VARCHAR The type of system storing this file.
NUMBER_OF_ACCESSES INTEGER The number of times this file has been accessed.
FILE_SIZE_BYTES INTEGER How large the file is in bytes.
LAST_ACCESS_TIME TIMESTAMPTZ A timestamp of when the file was last accessed.
ARRIVAL_TIME TIMESTAMPTZ When Vertica loaded the file into the depot.
SOURCE VARCHAR

Where the file came from. One of the following:

  • LOAD

  • QUERY

  • PEER TO PEER FILL

  • DEPOT FILL AT STARTUP

IS_PINNED BOOLEAN Specifies whether the file is pinned to this depot.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM depot_files LIMIT 2;

-[ RECORD 1 ]------+---------------------------------------------------------------
node_name          | v_verticadb_node0001
sal_storage_id     | 0275d4a7c99795d22948605e5940758900a000000001d1b1
storage_oid        | 45035996273842075
communal_file_path | s3://mybucket/myfolder/mydb/475/0275d4a7c99795d22948605e5940758900a000000001d1
b1/0275d4a7c99795d22948605e5940758900a000000001d1b1_
depot_file_path    | /vertica/data/verticadb/v_verticadb_node0001_depot/177/0275d4a7c99795d229486
05e5940758900a000000001d1b1/0275d4a7c99795d22948605e
shard_name         | replica
storage_type       | DFS
number_of_accesses | 0
file_size_bytes    | 456465645
last_access_time   | 2018-09-05 17:34:30.417274+00
arrival_time       | 2018-09-05 17:34:30.417274+00
source             | DEPOT FILL AT STARTUP
is_pinned          | f
-[ RECORD 2 ]------+---------------------------------------------------------------
node_name          | v_verticadb_node0001
sal_storage_id     | 0275d4a7c99795d22948605e5940758900a000000001d187
storage_oid        | 45035996273842079
communal_file_path | s3://mybucket/myfolder/mydb/664/0275d4a7c99795d22948605e5940758900a000000001d1
87/0275d4a7c99795d22948605e5940758900a000000001d187_
depot_file_path    | /vertica/data/verticadb/v_verticadb_node0001_depot/135/0275d4a7c99795d229486
05e5940758900a000000001d187/0275d4a7c99795d22948605e
shard_name         | replica
storage_type       | DFS
number_of_accesses | 0
file_size_bytes    | 40
last_access_time   | 2018-09-05 17:34:30.417244+00
arrival_time       | 2018-09-05 17:34:30.417244+00
source             | DEPOT FILL AT STARTUP
is_pinned          | f

26 - DEPOT_PIN_POLICIES

Lists all objects —tables, projections, and table partitions—that are pinned to database depots.

Eon Mode only

Lists all objects —tables, projections, and table partitions—that are pinned to database depots.

Column Name Data Type Description
SCHEMA_NAME VARCHAR Schema of the pinned object.
OBJECT_NAME VARCHAR Name of the pinned object.
POLICY_DETAILS VARCHAR

Specifies the object type, one of the following:

  • Table

  • Projection

  • Partition

MIN_VAL VARCHAR If the pinned object is one or more contiguous table partitions, specifies the range of partition keys.
MAX_VAL
LOCATION_LABEL The depot's storage location label.

See also

27 - DEPOT_SIZES

Reports depot caching capacity on Vertica nodes.

Eon Mode only

Reports depot caching capacity on Vertica nodes.

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node containing the depot.
LOCATION_OID INTEGER Catalog-assigned integer value that uniquely identifies the storage location storing the depot.
LOCATION_PATH VARCHAR The path where the depot is stored.
LOCATION_LABEL VARCHAR The label associated with the depot's storage location.
MAX_SIZE_BYTES INTEGER The maximum size the depot can contain, in bytes.
CURRENT_USAGE_BYTES INTEGER The current size of the depot, in bytes.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM Depot_Sizes;

-[ RECORD 1 ]-------+---------------------------------------------------

node_name           | v_verticadb_node0003
location_oid        | 45035996273823200
location_path       | /vertica/data/verticadb/v_verticadb_node0003_depot
location_label      | auto-data-depot
max_size_bytes      | 0
current_usage_bytes | 0

-[ RECORD 2 ]-------+---------------------------------------------------
node_name           | v_verticadb_node0001
location_oid        | 45035996273823196
location_path       | /vertica/data/verticadb/v_verticadb_node0001_depot
location_label      | auto-data-depot
max_size_bytes      | 33686316032
current_usage_bytes | 206801871

-[ RECORD 3 ]-------+---------------------------------------------------
node_name           | v_verticadb_node0002
location_oid        | 45035996273823198
location_path       | /vertica/data/verticadb/v_verticadb_node0002_depot
location_label      | auto-data-depot
max_size_bytes      | 0
current_usage_bytes | 0

28 - DEPOT_UPLOADS

Lists details about depot uploads to communal storage.

Eon Mode only

Lists details about depot uploads to communal storage.

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node on which the depot resides.
PLAN_ID VARCHAR A unique node-specific numeric ID for each plan run by the Optimizer.
SUBMIT_TIME TIMESTAMP The time the task was submitted to the uploader.
START_TIME TIMESTAMP The time the upload started.
END_TIME TIMESTAMP The time the upload ended.
SOURCE_FILE VARCHAR The source file path used.
DESTINATION_FILE VARCHAR The destination file path.
FILE_SIZE_BYTES INTEGER The size of the uploaded file, in bytes.
MEMORY_USED_KB INTEGER

The size of the uploader file buffer for the task.

Valid for a task with a RUNNING or COMPLETED status. For a RUNNING status, this shows the current file buffer size, (whatever the uploader is using, which may grow over time for large uploads).

For a COMPLETED status, this shows the largest size used in case the buffer grew during the upload.

STATUS VARCHAR

The status of the task, valid values are:

COMPLETED - the task has completed

QUEUED - the task is still in the queue, but haven't been picked up by the uploader.

RUNNING - the task is currently running and the corresponding file is uploading.

29 - DESIGN_QUERIES

Contains info about design queries for a given design.

Contains info about design queries for a given design. The following functions populate this table:

Column Name Column Type Description
DESIGN_ID INTEGER Unique id that Database Designer assigned to the design.
DESIGN_NAME VARCHAR Name that you specified for the design.
DESIGN_QUERY_ID INTEGER Unique id that Database Designer assigned to the design query.
DESIGN_QUERY_ID_INDEX INTEGER Database Designer chunks the query text if it exceeds the maximum attribute size before storing it in this table. Database Designer stored all chunks stored under the same value of DESIGN_QUERY_ID. DESIGN_QUERY_ID_INDEX keeps track of the order of the chunks, starting with 0 and ending in n, the index of the final chunk.
QUERY_TEXT VARCHAR Text of the query chunk, or the entire query text if it does not exceed the maximum attribute size.
WEIGHT FLOAT A value from 0 to 1 that indicates the importance of that query in creating the design. Assign a higher weight to queries that you run frequently so that Database Designer prioritizes those queries in creating the design. Default: 1.
DESIGN_QUERY_SEARCH_PATH VARCHAR The search path with which the query is to be parsed.
DESIGN_QUERY_SIGNATURE INTEGER Categorizes queries that affect the design that Database Designer creates in the same way. Database Designer assigns a signature to each query, weights one query for each signature group, depending on how many queries there are with that signature, and Database Designer considers that query when creating the design.

Example

Add queries to VMART_DESIGN and query the DESIGN_QUERIES table:


=> SELECT DESIGNER_ADD_DESIGN_QUERIES('VMART_DESIGN', '/tmp/examples/vmart_queries.sql','true');
 DESIGNER_ADD_DESIGN_QUERIES
-----------------------------
Number of accepted queries                      =9
Number of queries referencing non-design tables =0
Number of unsupported queries                   =0
Number of illegal queries                       =0
=> \x
Expanded display is on.
=> SELECT * FROM V_MONITOR.DESIGN.QUERIES
-[ RECORD 1 ]------------+-------------------
design_id                | 45035996273705090
design_name              | vmart_design
design_query_id          | 1
design_query_id_index    | 0
query_text               | SELECT fat_content
FROM (
SELECT DISTINCT fat_content
  FROM product_dimension
  WHERE department_description
  IN ('Dairy') ) AS food
  ORDER BY fat_content
  LIMIT 5;
weight                   | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature   | 45035996273724651

-[ RECORD 2]-------------+-------------------
design_query_id          | 2
design_query_id_index    | 0
query_text               | SELECT order_number, date_ordered
        FROM store.store_orders_fact orders
        WHERE orders.store_key IN (
        SELECT store_key
        FROM store.store_dimension
        WHERE store_state = 'MA')
        AND orders.vendor_key NOT IN (
        SELECT vendor_key
        FROM public.vendor_dimension
        WHERE vendor_state = 'MA')
        AND date_ordered < '2012-03-01';

weight                   | 1
design_query_search_path | v_dbd_vmart_design_vmart_design_ltt, "$user", public, v_catalog, v_monitor, v_internal
design_query_signature   | 45035996273724508
-[ RECORD 3]-------------+-------------------
...

30 - DESIGN_STATUS

Records the progress of a running Database Designer design or history of the last Database Designer design executed by the current user.

Records the progress of a running Database Designer design or history of the last Database Designer design executed by the current user.

Column Name Data Type Description
EVENT_TIME TIMESTAMP Time when the row recorded the event.
USER_NAME VARCHAR Name of the user who ran a design at the time Vertica recorded the session.
DESIGN_NAME VARCHAR Name of the user-specified design.
DESIGN_PHASE VARCHAR Phase of the design.
PHASE_STEP VARCHAR Substep in each design phase
PHASE_STEP_COMPLETE_PERCENT FLOAT Progress of current substep in percentage (0–100).
PHASE_COMPLETE_PERCENT FLOAT Progress of current design phase in percentage (0–100).

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

The following example shows the content of the DESIGN_STATUS table of a complete Database Designer run:

=> SELECT event_time, design_name, design_phase, phase_complete_percent
   FROM v_monitor.design_status;
event_time          | design_name | design_phase                                   | phase_complete_percent
--------------------+-------------+------------------------------------------------+------------------------
2012-02-14 10:31:20 | design1     | Design started                                 |
2012-02-14 10:31:21 | design1     | Design in progress: Analyze statistics phase   |
2012-02-14 10:31:21 | design1     | Analyzing data statistics                      | 33.33
2012-02-14 10:31:22 | design1     | Analyzing data statistics                      | 66.67
2012-02-14 10:31:24 | design1     | Analyzing data statistics                      | 100
2012-02-14 10:31:25 | design1     | Design in progress: Query optimization phase   |
2012-02-14 10:31:25 | design1     | Optimizing query performance                   | 37.5
2012-02-14 10:31:31 | design1     | Optimizing query performance                   | 62.5
2012-02-14 10:31:36 | design1     | Optimizing query performance                   | 75
2012-02-14 10:31:39 | design1     | Optimizing query performance                   | 87.5
2012-02-14 10:31:41 | design1     | Optimizing query performance                   | 87.5
2012-02-14 10:31:42 | design1     | Design in progress: Storage optimization phase |
2012-02-14 10:31:44 | design1     | Optimizing storage footprint                   | 4.17
2012-02-14 10:31:44 | design1     | Optimizing storage footprint                   | 16.67
2012-02-14 10:32:04 | design1     | Optimizing storage footprint                   | 29.17
2012-02-14 10:32:04 | design1     | Optimizing storage footprint                   | 31.25
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 33.33
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 35.42
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 37.5
2012-02-14 10:32:05 | design1     | Optimizing storage footprint                   | 62.5
2012-02-14 10:32:39 | design1     | Optimizing storage footprint                   | 87.5
2012-02-14 10:32:39 | design1     | Optimizing storage footprint                   | 87.5
2012-02-14 10:32:41 | design1     | Optimizing storage footprint                   | 100
2012-02-14 10:33:12 | design1     | Design completed successfully                  |
(24 rows)

31 - DESIGN_TABLES

Contains information about all the design tables for all the designs for which you are the owner.

Contains information about all the design tables for all the designs for which you are the owner. Each row contains information about a different design table. Vertica creates this table when you run DESIGNER_CREATE_DESIGN.

Column Name Column Type Description
DESIGN_NAME VARCHAR Unique name that the user specified for the design.
DESIGN_TABLE_ID INTEGER Unique ID that Database Designer assigned to the design table.
TABLE_SCHEMA VARCHAR Name of the schema that contains the design table.
TABLE_ID INTEGER System object identifier (OID) assigned to the design table.
TABLE_NAME VARCHAR Name of the design table.

Example

Add all the tables from the VMart database to the design VMART_DESIGN. This operation populates the DESIGN_TABLES table:


=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','online_sales.*');
DESIGNER_ADD_DESIGN_TABLES
----------------------------
 3
(1 row)
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','public.*');
DESIGNER_ADD_DESIGN_TABLES
----------------------------
 9
(1 row)
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','store.*');
DESIGNER_ADD_DESIGN_TABLES
----------------------------
 3
(1 row)
=> SELECT * FROM DESIGN_TABLES;
design_name  | design_table_id | table_schema |     table_id      |      table_name
-------------+-----------------+--------------+-------------------+-----------------------
VMART_DESIGN |               1 | online_sales | 45035996373718754 | online_page_dimension
VMART_DESIGN |               2 | online_sales | 45035996373718758 | call_center_dimension
VMART_DESIGN |               3 | online_sales | 45035996373718762 | online_sales_fact
VMART_DESIGN |               4 | public       | 45035996373718766 | customer_dimension
VMART_DESIGN |               5 | public       | 45035996373718770 | product_dimension
VMART_DESIGN |               6 | public       | 45035996373718774 | promotion_dimension
VMART_DESIGN |               7 | public       | 45035996373718778 | date_dimension
VMART_DESIGN |               8 | public       | 45035996373718782 | vendor_dimension
VMART_DESIGN |               9 | public       | 45035996373718786 | employee_dimension
VMART_DESIGN |              10 | public       | 45035996373718822 | shipping_dimension
VMART_DESIGN |              11 | public       | 45035996373718826 | warehouse_dimension
VMART_DESIGN |              12 | public       | 45035996373718830 | inventory_face
VMART_DESIGN |              13 | store        | 45035996373718794 | store_dimension
VMART_DESIGN |              14 | store        | 45035996373718798 | store_sales_fact
VMART_DESIGN |              15 | store        | 45035996373718812 | store_orders_fact
        (15 rows)

32 - DESIGNS

Contains information about a Database Designer design.

Contains information about a Database Designer design. After you create a design and specify certain parameters for Database Designer, DESIGNER_CREATE_DESIGN creates this table in the V_MONITOR schema.

Column Name Column Type Description
DESIGN_ID INTEGER Unique ID that Database Designer assigns to this design.
DESIGN_NAME VARCHAR Name that the user specifies for the design.
KSAFETY_LEVEL INTEGER K-safety level for the design. Database Designer assigns a K-safety value of 0 for clusters with 1 or 2 nodes, and assigns a value of 1 for clusters with 3 or more nodes.
OPTIMIZATION_OBJECTIVE VARCHAR

Name of the optimization objective for the design. Valid values are:

  • QUERY

  • LOAD

  • BALANCED (default)

DESIGN_TYPE VARCHAR

Name of the design type. Valid values are:

  • COMPREHENSIVE (default)

  • INCREMENTAL

PROPOSE_SUPER_FIRST BOOLEAN Specifies to propose superprojections before projections, by default f. If DESIGN_MODE is COMPREHENSIVE, this field has no impact.
DESIGN_AVAILABLE BOOLEAN t if the design is currently available, otherwise, f (default).
COLLECTED_STATISTICS BOOLEAN t if statistics are to be collected when creating the design, otherwise, f (default).
POPULATE_DESIGN_TABLES_FROM_QUERIES BOOLEAN t if you want to populate the design tables from the design queries, otherwise, f (default).
ENCODING_DESIGN BOOLEAN t if the design is an encoding optimization design on pre-existing projections, otherwise, f (default).
DEPLOYMENT_PARALLELISM INTEGER Number of tables to be deployed in parallel when the design is complete. Default: 0
UNSEGMENTED_PROJECTIONS BOOLEAN t if you specify unsegmented projections, otherwise, f (default).
ANALYZE_CORRELATIONS_MODE INTEGER

Specifies how Database Designer should handle existing column correlations in a design and whether or not Database Designer should reanalyze existing column correlations.

  • 0: (default) Ignore column correlations when creating the design.

  • 1: Consider the existing correlations in the tables when creating the design.

  • 2: Analyze column correlations if not previously performed, and consider the column correlations when creating the design.

  • 3: Analyze all column correlations in the tables and consider them when creating the design, even if they have been analyzed previously.

33 - DISK_QUOTA_USAGES

Provides information about schemas and tables that have disk quotas.

Provides information about schemas and tables that have disk quotas. Schemas and tables without quotas are not included.

Column Name Data Type Description
OBJECT_OID INTEGER Unique identifier for a schema or table.
OBJECT_NAME VARCHAR Name of the schema or table. Table names include the schema prefix.
IS_SCHEMA BOOLEAN Whether the object is a schema. If false, the object is a table.
TOTAL_DISK_USAGE_IN_BYTES
 INTEGER Current usage of the object. For information about what is counted, see Disk quotas.
DISK_QUOTA_IN_BYTES INTEGER Current quota for the object.

Examples

=> SELECT * FROM DISK_QUOTA_USAGES;
    object_oid     | object_name | is_schema | total_disk_usage_in_bytes | disk_quota_in_bytes
-------------------+-------------+-----------+---------------------+---------------------
 45035996273705100 | s           | t         |                 307 |               10240
 45035996273705104 | public.t    | f         |                 614 |                1024
 45035996273705108 | s.t         | f         |                 307 |                2048
(3 rows)

34 - DISK_RESOURCE_REJECTIONS

Returns requests for resources that are rejected due to disk space shortages.

Returns requests for resources that are rejected due to disk space shortages. Output is aggregated by both RESOURCE_TYPE and REJECTED_REASON to provide more comprehensive information.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
RESOURCE_TYPE VARCHAR The resource request requester (example: Temp files).
REJECTED_REASON VARCHAR

One of the following:

  • Insufficient disk space

  • Failed volume

REJECTED_COUNT INTEGER Number of times this REJECTED_REASON has been given for this RESOURCE_TYPE.
FIRST_REJECTED_TIMESTAMP TIMESTAMP The time of the first rejection for this REJECTED_REASON and RESOURCE_TYPE.
LAST_REJECTED_TIMESTAMP TIMESTAMP The time of the most recent rejection for this REJECTED_REASON and RESOURCE_TYPE.
LAST_REJECTED_VALUE INTEGER The value of the most recent rejection for this REJECTED_REASON and RESOURCE_TYPE.

See also

35 - DISK_STORAGE

Returns the amount of disk storage used by the database on each node.

Returns the amount of disk storage used by the database on each node. Each node can have one or more storage locations, and the locations can be on different disks with separate properties, such as free space, used space, and block size. The information in this system table is useful in determining where data files reside.

All returned values for this system table are in the context of the file system of the host operating system, and are not specific to Vertica-specific space.

The storage usage annotation called CATALOG indicates that the location is used to store the catalog. Each CATALOG location is specified only when creating a new database. You cannot add a CATALOG location annotation using CREATE LOCATION, nor remove an existing CATALOG annotation.

Storage location performance

The performance of a storage location is measured with two values:

  • Throughput in MB/sec

  • Latency in seeks/sec

These two values are converted to a single number (Speed) with the following formula:

read-time = (1/throughput) + (1/latency)

  • read-time: Time to read 1MB of data

  • 1/throughput: Time to read 1MB of data

  • 1/latency: Time to seek to the data.

A disk is faster than another disk if its read-time is less.

Column Name Data Type Description
NODE_NAME VARCHAR Node name for which information is listed.
STORAGE_PATH VARCHAR Path where the storage location is mounted.
STORAGE_USAGE VARCHAR

Type of information stored in the location, one of the following:

  • DATA: Only data is stored in the location.

  • TEMP: Only temporary files that are created during loads or queries are stored in the location.

  • DATA,TEMP: Both types of files are stored in the location.

  • USER: The storage location can be used by non-dbadmin users, who are granted access to the storage location

  • CATALOG: The area is used for the Vertica catalog. This usage is set internally and cannot be removed or changed.

RANK INTEGER Integer rank assigned to the storage location based on its performance. Ranks are used to create a storage locations on which projections, columns, and partitions are stored on different disks based on predicted or measured access patterns. See Managing storage locations.
THROUGHPUT INTEGER Integer that measures a storage location's performance in MB/sec. 1/throughput is the time taken to read 1MB of data.
LATENCY INTEGER Integer that measures a storage location's performance in seeks/sec. 1/latency is the time taken to seek to the data.
STORAGE_STATUS VARCHAR

Status of the storage location, one of the following:

  • active

  • retired

DISK_BLOCK_SIZE_BYTES INTEGER Block size of the disk in bytes
DISK_SPACE_USED_BLOCKS INTEGER Number of disk blocks in use
DISK_SPACE_USED_MB INTEGER Number of megabytes of disk storage in use
DISK_SPACE_FREE_BLOCKS INTEGER Number of free disk blocks available
DISK_SPACE_FREE_MB INTEGER Number of megabytes of free storage available
DISK_SPACE_FREE_PERCENT VARCHAR Percentage of free disk space remaining

36 - DRAINING_STATUS

Returns the draining status of each node in a database.

Returns the draining status of each node in a database. The table also provides aggregate user session counts and information about the oldest user session connected to each node. For more information about the user sessions connected to a database, see SESSIONS.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node for which information is listed.
SUBCLUSTER_NAME VARCHAR Name of the subcluster that contains the node.
IS_DRAINING BOOLEAN True if the node is draining; otherwise, false.
COUNT_CLIENT_USER_SESSIONS INTEGER Number of user client sessions connected to the node.
OLDEST_SESSION_USER VARCHAR Name of the user with the oldest live session connected to the node. NULL if no users are connected.
OLDEST_SESSION_ID VARCHAR Identifier associated with OLDEST_SESSION_USER. This is required to close or interrupt a session. NULL if no users are connected.
OLDEST_SESSION_LOGIN_TIMESTAMP TIMESTAMP Date and time the OLDEST_SESSION_USER logged into the database. NULL if no users are connected.

37 - ERROR_MESSAGES

Lists system error messages and warnings Vertica encounters while processing queries.

Lists system error messages and warnings Vertica encounters while processing queries. Some errors occur when no transaction is in progress, so the transaction identifier or statement identifier columns might return NULL.

Column Name Data Type Description
EVENT_TIMESTAMP TIMESTAMPTZ Time when the row recorded the event
NODE_NAME VARCHAR Name of the node that is reporting the requested information
USER_ID INTEGER Identifier of the user who received the error message
USER_NAME VARCHAR Name of the user who received the error message when Vertica recorded the session
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
REQUEST_ID INTEGER Unique identifier of the query request in the user session
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any; otherwise NULL
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
ERROR_LEVEL VARCHAR

Severity of the error, one of the following:

  • LOG

  • INFO

  • NOTICE

  • WARNING

  • ERROR

  • ROLLBACK

  • INTERNAL

  • FATAL

  • PANIC

ERROR_CODE INTEGER Error code that Vertica reports
MESSAGE VARCHAR Textual output of the error message
DETAIL VARCHAR Additional information about the error message, in greater detail
HINT VARCHAR

Actionable hint about the error. For example:

HINT: Set the locale in this session to en_US@collation=binary using the command "\locale en_US@collation=binary"

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

38 - EVENT_CONFIGURATIONS

Monitors the configuration of events.

Monitors the configuration of events.

Column Name Data Type Description
EVENT_ID VARCHAR The name of the event.
EVENT_DELIVERY_CHANNELS VARCHAR The delivery channel on which the event occurred.

39 - EXECUTION_ENGINE_PROFILES

Provides profiling information about runtime query execution.

Provides profiling information about runtime query execution. The hierarchy of IDs, from highest level to actual execution, is:

  • PATH_ID

  • BASEPLAN_ID

  • LOCALPLAN_ID

  • OPERATOR_ID

Counters (output from the COUNTER_NAME column) are collected for each actual Execution Engine (EE) operator instance.

The following columns combine to form a unique key:

  • TRANSACTION_ID

  • STATEMENT_ID

  • NODE_NAME

  • OPERATOR_ID

  • COUNTER_NAME

  • COUNTER_TAG

Column Name Data Type Description
NODE_NAME VARCHAR Node name for which information is listed.
USER_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the user.
USER_NAME VARCHAR User name for which query profile information is listed.
SESSION_ID VARCHAR Identifier of the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
TRANSACTION_ID INTEGER Identifier for the transaction within the session if any; otherwise NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed.
OPERATOR_NAME VARCHAR Name of the Execution Engine (EE) component; for example, NetworkSend.
OPERATOR_ID INTEGER Identifier assigned by the EE operator instance that performs the work. OPERATOR_ID is different from LOCALPLAN_ID because each logical operator, such as Scan, may be executed by multiple threads concurrently. Each thread operates on a different operator instance, which has its own ID.
BASEPLAN_ID INTEGER Assigned by the optimizer on the initiator to EE operators in the original base (EXPLAIN) plan. Each EE operator in the base plan gets a unique ID.
PATH_ID INTEGER

Identifier that Vertica assigns to a query operation or path; for example to a logical grouping operation that might be performed by multiple execution engine operators.

For each path, the same PATH ID is shared between the query plan (using EXPLAIN output) and in error messages that refer to joins.

LOCALPLAN_ID INTEGER Identifier assigned by each local executor while preparing for plan execution (local planning). Some operators in the base plan, such as the Root operator, which is connected to the client, do not run on all nodes. Similarly, certain operators, such as ExprEval, are added and removed during local planning due to implementation details.
ACTIVITY_ID INTEGER Identifier of the plan activity.
RESOURCE_ID INTEGER Identifier of the plan resource.
COUNTER_NAME VARCHAR Name of the counter (see Counter Names below). The counter counts events for one statement.
COUNTER_TAG VARCHAR String that uniquely identifies the counter for operators that might need to distinguish between different instances. For example, COUNTER_TAG is used to identify to which of the node bytes are being sent to or received from the NetworkSend operator.
COUNTER_VALUE INTEGER Value of the counter.
IS_EXECUTING BOOLEAN Indicates whether the profile is active or completed.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Counter names

The value of COUNTER_NAME can be any of the following:

COUNTER_NAME Description
active threads A counter of the LoadUnion operator, indicates the number of input threads (Load operators) that are currently processing input.
blocks analyzed by SIPs expression Number of data blocks analyzed by SIPS expression from the Scan operator.
blocks filtered by SIPs expression Number of data blocks filtered by SIPS expression from the Scan operator.
blocks filtered by SIPs value lists Number of data blocks filtered by SIPS sorted value lists from the Scan operator.
buffers spilled [NetworkSend] Buffers spilled to disk by NetworkSend.
bytes read from disk [Scan] Amount of data read (locally or remotely) from ROS containers on disk.
bytes read from disk cache [Scan] Amount of data read from cache.
bytes received Number of bytes received over the network for query execution.
bytes sent [NetworkSend Size of data after encoding and compression sent over the network (actual network bytes).
bytes spilled [NetworkSend] Bytes spilled to disk by NetworkSend.
bytes total [SendFiles] (recover-by-container plan): Total number of bytes to send/receive.
cached storages cumulative size (bytes) [StorageMerge] Total amount of temp space used by operator for caching.
cached storages current size (bytes) [StorageMerge] Current amount of temp space used for caching.
cached storages peak size (bytes) [StorageMerge] Peak amount of temp space an operator used for caching.
clock time (µs) Real-time clock time spent processing the query, in microseconds.
clock time (µs) of UDChunker Real-time clock time spent in the UDChunker phase of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.
clock time (µs) of UDFilter(s) Real-time clock time spent in all UDFilter phases of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.
clock time (µs) of UDParser Real-time clock time spent in the UDParser phase of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.
clock time (µs) of UDSource Real-time clock time spent in the UDSource phase of a load operation, in microseconds. Use the COUNTER_TAG column to distinguish among load sources.
completed merge phases Number of merge phases already completed by an LSort or DataTarget operator. Compare to the total merge phases. Variants on this value include join inner completed merge phases.
cumulative size of raw temp data (bytes) Total amount of temporary data the operator has written to files. Compare to cumulative size of temp files (bytes) to understand impact of encoding and compression in an externalizing operator. Variants on this value include join inner cumulative size of raw temp files (bytes).
cumulative size of temp files (bytes) For externalizing operators only, the total number of encoded and compressed temp data the operator has written to files. A sort operator might go through multiple merge phases, where at each pass sorted chunks of data are merged into fewer chunks. This counter remembers the cumulative size of all temp files past and present. Variants on this value include join inner cumulative size of temp files (bytes).
current allocated rid memory (bytes) Per-rid memory tracking: current allocation amount under this rid.
current file handles Number of files open.
current memory allocations (count) Number of actual allocator calls made.
current memory capacity (bytes) Amount of system memory held, which includes chunks that are only partially consumed.
current memory overhead (bytes) Memory consumed, for example, by debug headers. (Normally no overhead.)
current memory padding (bytes) Memory padding for free list tiers (2^n bytes).
current memory requested (bytes) Memory actually requested by the caller.
current size of temp files (bytes) For externalizing operators only, the current size of the encoded and compressed temp data that the operator has written to files. Variants on this value include join inner current size of temp files (bytes).
current threads Unused.
current unbalanced memory allocations (count) Pooled version of "current memory XXX" counters.
current unbalanced memory capacity (bytes)
current unbalanced memory overhead (bytes)
current unbalanced memory requested (bytes)
distinct value estimation time (µs) [Analyze Statistics] Time (in microcseconds) spent to estimate number of distinct values from the sample after data is read off disk and into the statistical sample.
encoded bytes received [NetworkRecv] Size of received data after decompressed (but still encoded) received over the network.
encoded bytes sent [NetworkSend] Size of data sent over the network after encoding.
end time Time (timestamp) when Vertica stopped processing the operation
estimated rows produced Number of rows that the optimizer estimated would be produced. See rows produced for the actual number of rows that are produced.
exceptions cumulative size of raw temp data (bytes) Counters that store the total or current size of exception data.
exceptions rows cumulative size of temp files (bytes)
exceptions rows current size of temp files (bytes)
execution time (µs) CPU clock time spent processing the query, in microseconds.
fast aggregated rows Number of rows being processed by fast aggregations in the hash groupby operator (no group/aggregation).
files completed Relevant only to SendFiles/RecvFiles operators (that is, recover-by-container plan) number of files sent/received.
files total Relevant only to SendFiles/RecvFiles operators (that is, recover-by-container plan) total number of files to send/receive.
Hadoop FS bytes read through native libhdfs++ client [Scan, Load] Number of bytes read from an hdfs source (using libhdfs++).
Hadoop FS bytes read through webhdfs [Scan, Load] Number of bytes read from a webhdfs source.
Hadoop FS bytes written through webhdfs [DataTarget] Number of bytes written to webhdfs storage.
Hadoop FS hdfs:// operations that used native libhdfs++ calls [Scan, Load, DataTarget] Number of times Vertica opened a file with an hdfs:// URL and used the native hdfs protocol
Hadoop FS hdfs:// operations that used webhdfs calls [Scan, Load, DataTarget] Number of times Vertica opened a file with an hdfs:// URL and used the webhdfs protocol
Hadoop FS read operations through native libhdfs++ client failure count [Scan, Load] Number of times a native libhdfs++ source encountered an error and gave up
Hadoop FS read operations through native libhdfs++ client retry count [Scan, Load] Number of times a native libhdfs++ source encountered an error and retried
Hadoop FS read operations through webhdfs failure count [Scan, Load] Number of times a webhdfs source encountered an error and gave up
Hadoop FS read operations through webhdfs retry count [Scan, Load] Number of times a webhdfs source encountered an error and retried
Hadoop FS write operations through webhdfs failure count [DataTarget] Number of times a webhdfs write encountered an error and gave up
Hadoop FS write operations through webhdfs retry count [DataTarget] Number of times a webhdfs write encountered an error and retried
histogram creation time(µs) [Analyze Statistics] Time spent estimating the number of distinct values from the sample after data is read off disk and into the statistical sample.
initialization time (µs) Time in microseconds spent initializing an operator during the CompilePlan step of query processing. For example, initialization time could include the time spent compiling expressions and gathering resources.
input queue wait (µs) Time in microseconds that an operator spends waiting for upstream operators.
input rows Actual number of rows that were read into the operator.
input size (bytes) Total number of bytes of the Load operator's input source, where NULL is unknown (read from FIFO).
inputs processed Number of sources processed by a Load operator.
intermediate rows to process Number of rows to process in a phase as determined by a sort or GROUP BY (HASH).
join inner clock time (µs) Real clock time spent on processing the inner input of the join operator.
join inner completed mergephases See the completed merge phases counter.
join inner cumulative size of raw temp data (bytes)
join inner cumulative size of temp files (bytes)
join inner current size of temp files (bytes)
join inner execution time (µs) The CPU clock time spent on processing the inner input of the join operator.
join inner hash table building time (µs) Time spent for building the hash table for the inner input of the join operator.
join inner hash table collisions Number of hash table collisions that occurred when building the hash table for the inner input of the join operator.
join inner hash table entries Number of hash table entries for the inner input of the join operator.
join inner total merge phases See the completed merge phases counter.
join outer clock time (µs) Real clock time spent on processing the outer input of the join operator (including doing the join).
join outer execution time (µs) CPU clock time spent on processing the outer input of the join operator (including doing the join).
max sample size (rows) [Analyze Statistics] Maximum number of rows that will be stored in the statistical sample.
memory reserved (bytes) Memory reserved by this operator. Deprecated.
network wait (µs) [NetworkSend, NetworkRecv] Time in microseconds spent waiting on the network.
number of bytes read from persistent storage Estimated number of bytes read from persistent storage to process this query.
number of bytes read from depot storage Estimated number of bytes read from the depot to process this query.
number of cancel requests received Number of cancel requests received (per operator) when cancelling a call to the execution engine.
number of invocations Number of times a UDSF function was invoked.
number of storage containers opened [Scan] Number of containers opened by the operator, at least 1. If the scan operator switches containers, this counter increases accordingly. See Local caching of storage containers for details.
output queue wait (µs) Time in microseconds that an operator spends waiting for the output buffer to be consumed by a downstream operator.
peak allocated rid memory (bytes) Per-rid memory tracking: peak allocation amount under this rid.
peak cooperating threads Peak number of threads which parsed (in parallel) a single load source, using "cooperative parse." counter_tag indicates the source when joining with dc_load_events.
peak file handles Peak value of the corresponding "current XXX" counters.
peak memory allocations (count)
peak memory capacity (bytes)
peak memory overhead (bytes)
peak memory padding (bytes)
peak memory requested (bytes)
peak temp space
peak threads
peak unbalanced memory allocations (count)
peak unbalanced memory capacity (bytes)
peak unbalanced memory overhead (bytes)
peak unbalanced memory padding (bytes)
peak unbalanced memory requested (bytes)
portion offset Offset value of a portion descriptor in an apportioned load. counter_tag indicates the source when joining with dc_load_events.
portion size Size value of a portion descriptor in an apportioned load. counter_tag indicates the source when joining with dc_load_events.
producer stall (µs) [NetworkSend] Time in microseconds spent by NetworkSend when stalled waiting for network buffers to clear.
producer wait (µs) [NetworkSend] Time in microseconds spent by the input operator making rows to send.
read (bytes) Number of bytes read from the input source by the Load operator.
receive time (µs) Time in microseconds that a Recv operator spends reading data from its socket.
rejected data cumulative size of raw temp data (bytes)

Counters that store total or current size of rejected row numbers. Are variants of:

  • cumulative size of raw temp data (bytes)

  • cumulative size of temp files (bytes)

  • current size of temp files (bytes)

rejected data cumulative size of temp files (bytes)
rejected data current sizeof temp files (bytes)
rejected rows cumulative size of raw temp data (bytes)
rejected rows cumulative size of temp files (bytes)
rejected rows current size of temp files (bytes)
reserved rid memory (bytes) Per-rid memory tracking: total memory reservation under this rid.
rle rows produced Number of physical tuples produced by an operator. Complements the rows produced counter, which shows the number of logical rows produced by an operator. For example, if a value occurs 1000 rows consecutively and is RLE encoded, it counts as 1000 rows produced not only 1 rle rows produced.
ROS blocks bounded [DataTarget] Number of ROS blocks created, due to boundary alignment with RLE prefix columns, when an EE DataTarget operator is writing to ROS containers.
ROS blocks encoded [DataTarget] Number of ROS blocks created when an EE DataTarget operator is writing to ROS containers.
ROS bytes written [DataTarget] Number of bytes written to disk when an EE DataTarget operator is writing to ROS containers.
rows added by predicate analysis Number of rows in the query results that were added without individual evaluation, based on the predicate and range of possible results in a block.
rows filtered by SIPs expression Number of rows filtered by the SIPS expression from the Scan operator.
rows filtered by query predicate Number of rows excluded from query results because they failed a condition (predicate), for example in a WHERE clause.
rows in sample [Analyze Statistics] Actual number of rows that will be stored in the statistical sample.
rows output by sort [DataTarget] Number of rows sorted when an EE DataTarget operator is writing to ROS containers.
rows processed [DataSource] Number of rows processed when an EE DataSource operator is reading from ROS containers.
rows processed by SIPs expression Number of rows processed by the SIPS expression in the Scan operator.
rows produced Number of logical rows produced by an operator. See also the rle rows produced counter.
rows pruned by query predicates Number of rows discarded from query results because, based on predicates and value ranges, no row in the block could satisfy the predicate.
rows pruned by valindex [DataSource] Number of rows it skips direct scanning with help of valindex when an EE DataSource operator is writing to ROS containers. This counter's value is not greater than "rows processed" counter.
rows read in sort See the counter total rows read in sort.
rows received [NetworkRecv] Number of received sent over the network.
rows rejected Number of rows rejected by the Load operator.
rows sent [NetworkSend] Number of rows sent over the network.
rows to process Total number of rows to be processed in a phase, based upon the number of table accesses. Compare to the counter, rows processed. Divide the rows processed value by the rows to process value for percent completion.
rows written in join sort Total number of rows being read out of the sort facility in Join.
rows written in sort Number of rows read out of the sort by the SortManager. This counter and the counter total rows read from sort are typically equal.
send time (µs) Time in microseconds that a Send operator spends writing data to its socket.
start time Time (timestamp) when Vertica started to process the operation.
total merge phases Number of merge phases an LSort or DataTarget operator must complete to finish sorting its data. NULL until the operator can compute this value (all data must first be ingested by the operator). Variants on this value include join inner total merge phases.
total rows read in join sort Total number of rows being put into the sort facility in Join.
total rows read in sort total Total number of rows ingested into the sort by the SortManager. This counter and the counter rows written in sort are typically equal.
total rows written in sort See the counter, rows written in sort.
total sources Total number of distinct input sources processed in a load.
unpacked (bytes) Number of bytes produced by a compressed source in a load (for example, for a gzip file, the size of the file when decompressed).
wait clock time (µs) StorageUnion wait time in microseconds.
written rows [DataTarget] Number of rows written when an EE DataTarget operator writes to ROS containers

Examples

The two queries below show the contents of the EXECUTION_ENGINE_PROFILES table:

=> SELECT operator_name, operator_id, counter_name, counter_value
    FROM EXECUTION_ENGINE_PROFILES WHERE operator_name = 'Scan'
    ORDER BY counter_value DESC;
 operator_name | operator_id | counter_name |  counter_value
---------------+-------------+--------------+------------------
 Scan          |          20 | end time     | 1559929719983785
 Scan          |          20 | start time   | 1559929719983737
 Scan          |          18 | end time     | 1559929719983358
 Scan          |          18 | start time   | 1559929718069860
 Scan          |          16 | end time     | 1559929718069319
 Scan          |          16 | start time   | 1559929718069188
 Scan          |          14 | end time     | 1559929718068611
 Scan          |          18 | end time     | 1559929717579145
 Scan          |          18 | start time   | 1559929717579083
 Scan          |          16 | end time     | 1559929717578509
 Scan          |          18 | end time     | 1559929717379346
 Scan          |          18 | start time   | 1559929717379307
 Scan          |          16 | end time     | 1559929717378879
 Scan          |          16 | start time   | 1559929716894312
 Scan          |          14 | end time     | 1559929716893599
 Scan          |          14 | start time   | 1559929716893501
 Scan          |          12 | end time     | 1559929716892721
 Scan          |          16 | start time   | 1559929716666110
...

=> SELECT DISTINCT counter_name FROM execution_engine_profiles;
                    counter_name
-----------------------------------------------------
 reserved rid memory (bytes)
 rows filtered by SIPs expression
 rows output by sort
 chunk rows scanned squared
 join inner execution time (us)
 current unbalanced memory requested (bytes)
 clock time (us)
 join outer clock time (us)
 exception handling execution time (us)
 peak memory capacity (bytes)
 bytes received
 peak memory requested (bytes)
 send time (us)
 ROS blocks encoded
 current size of temp files (bytes)
 peak memory allocations (count)
 current unbalanced memory overhead (bytes)
 rows segmented
...

The following query includes the path_id column, which links the path that the query optimizer takes (via the EXPLAIN command's textual output) with join error messages. 

=> SELECT operator_name, path_id, counter_name, counter_value FROM execution_engine_profiles where operator_name = 'Join';
 operator_name | path_id |                    counter_name                     |  counter_value
---------------+---------+-----------------------------------------------------+------------------
 Join          |      64 | current memory allocations (count)                  |                0
 Join          |      64 | peak memory allocations (count)                     |               57
 Join          |      64 | current memory requested (bytes)                    |                0
 Join          |      64 | peak memory requested (bytes)                       |          1698240
 Join          |      64 | current memory overhead (bytes)                     |                0
 Join          |      64 | peak memory overhead (bytes)                        |                0
 Join          |      64 | current memory padding (bytes)                      |                0
 Join          |      64 | peak memory padding (bytes)                         |           249840
 Join          |      64 | current memory capacity (bytes)                     |                0
 Join          |      64 | peak memory capacity (bytes)                        |           294912
 Join          |      64 | current unbalanced memory allocations (count)       |              145
 Join          |      64 | peak unbalanced memory allocations (count)          |              146
 Join          |      64 | current unbalanced memory requested (bytes)         |           116506
 Join          |      64 | peak unbalanced memory requested (bytes)            |          1059111
 Join          |      64 | current unbalanced memory overhead (bytes)          |             3120
 Join          |      64 | peak unbalanced memory overhead (bytes)             |             3120
...

See also

40 - EXTERNAL_TABLE_DETAILS

Returns the amount of disk storage used by the source files backing external tables in the database.

Returns the amount of disk storage used by the source files backing external tables in the database. The information in this system table is useful in determining Hadoop license compliance.

When computing the size of an external table, Vertica counts all data found in the location specified by the COPY FROM clause. If you have a directory that contains ORC and delimited files, for example, and you define your external table with "COPY FROM *" instead of "COPY FROM *.orc", this table includes the size of the delimited files. (You would probably also encounter errors when querying that external table.) When you query this system table Vertica does not validate your table definition; it just uses the path to find files to report.

Restrict your queries to filter by schema, table, or format to avoid expensive queries. Vertica calculates the values in this table at query time, so "SELECT *" accesses every input file contributing to every external table.

Predicates in queries may use only the TABLE_SCHEMA, TABLE_NAME, and SOURCE_FORMAT columns. Values are case-sensitive.

This table includes TEMP external tables.

This table reports only data that the current user can read. To include all the data backing external tables, either query this table as a user that has access to all HDFS data or use a session delegation token that grants this access. For more information about using delegation tokens, see Accessing kerberized HDFS data.

Column Name Data Type Description
SCHEMA_OID INTEGER The unique identification number of the schema in which the external table resides.
TABLE_SCHEMA VARCHAR The name of the schema in which the external table resides.
TABLE_OID INTEGER A unique numeric ID assigned by the Vertica catalog that identifies the table.
TABLE_NAME VARCHAR The table name.
SOURCE_FORMAT VARCHAR The data format the source file used, one of ORC, PARQUET, DELIMITED, USER DEFINED, or NULL if another format.
TOTAL_FILE_COUNT INTEGER The number of files used to store this table's data, expanding globs and partitions.
TOTAL_FILE_SIZE_BYTES INTEGER Total number of bytes used by all of this table's data files.
SOURCE_STATEMENT VARCHAR The load statement used to copy data from the source files.
FILE_ACCESS_ERROR VARCHAR The access error returned during the source statement. NULL, if there was no access error during the source statement.

41 - HIVE_CUSTOM_PARTITIONS_ACCESSED

This table provides information about all custom locations for Hive partition data that Vertica has accessed.

This table provides information about all custom locations for Hive partition data that Vertica has accessed. It applies when Hive uses a non-default location for partition data, the HCatalog Connector is used to access that data, and the CREATE HCATALOG SCHEMA statement for the schema sets the CUSTOM_PARTITIONS parameter.

Column Name Data Type Description
ACCESS_TIME TIMESTAMPTZ Time when Vertica accessed the partition data.
ACCESS_NODE VARCHAR(128) Name of the node that performed the access.
TRANSACTION_ID INTEGER Identifier for the query that produced the access.
FILESYSTEM VARCHAR(128) File system of the partition data. This value is the scheme portion of the URL.
AUTHORITY VARCHAR(128) If the file system is HDFS, this value is the nameservice. If the file system is S3, it is the name of the bucket.
URL VARCHAR(6400) Full path to the partition.

Privileges

No explicit permissions are required; however, users see only the records that correspond to schemas they have permissions to access.

42 - HOST_RESOURCES

Provides a snapshot of the node.

Provides a snapshot of the node. This is useful for regularly polling the node with automated tools or scripts.

Column Name Data Type Description
HOST_NAME VARCHAR The host name for which information is listed.
OPEN_FILES_LIMIT INTEGER The maximum number of files that can be open at one time on the node.
THREADS_LIMIT INTEGER The maximum number of threads that can coexist on the node.
CORE_FILE_LIMIT_MAX_SIZE_BYTES INTEGER The maximum core file size allowed on the node.
PROCESSOR_COUNT INTEGER The number of system processors.
PROCESSOR_CORE_COUNT INTEGER The number of processor cores in the system.
PROCESSOR_DESCRIPTION VARCHAR A description of the processor. For example: Inter(R) Core(TM)2 Duo CPU T8100 @2.10GHz (1 row)
OPENED_FILE_COUNT INTEGER The total number of open files on the node.
OPENED_SOCKET_COUNT INTEGER The total number of open sockets on the node.
OPENED_NONFILE_NONSOCKET_COUNT INTEGER The total number of other file descriptions open in which 'other' could be a directory or FIFO. It is not an open file or socket.
TOTAL_MEMORY_BYTES INTEGER The total amount of physical RAM, in bytes, available on the system.
TOTAL_MEMORY_FREE_BYTES INTEGER The amount of physical RAM, in bytes, left unused by the system.
TOTAL_BUFFER_MEMORY_BYTES INTEGER The amount of physical RAM, in bytes, used for file buffers on the system
TOTAL_MEMORY_CACHE_BYTES INTEGER The amount of physical RAM, in bytes, used as cache memory on the system.
TOTAL_SWAP_MEMORY_BYTES INTEGER The total amount of swap memory available, in bytes, on the system.
TOTAL_SWAP_MEMORY_FREE_BYTES INTEGER The total amount of swap memory free, in bytes, on the system.
DISK_SPACE_FREE_MB INTEGER The free disk space available, in megabytes, for all storage location file systems (data directories).
DISK_SPACE_USED_MB INTEGER The disk space used, in megabytes, for all storage location file systems.
DISK_SPACE_TOTAL_MB INTEGER The total free disk space available, in megabytes, for all storage location file systems.

Examples

=> SELECT * FROM HOST_RESOURCES;
-[ RECORD 1 ]------------------+------------------------------------------
host_name                      | 10.20.100.247
open_files_limit               | 65536
threads_limit                  | 3833
core_file_limit_max_size_bytes | 0
processor_count                | 2
processor_core_count           | 2
processor_description          | Intel(R) Xeon(R) CPU E5-2695 v2 @ 2.40GHz
opened_file_count              | 8
opened_socket_count            | 15
opened_nonfile_nonsocket_count | 7
total_memory_bytes             | 4018823168
total_memory_free_bytes        | 126550016
total_buffer_memory_bytes      | 191803392
total_memory_cache_bytes       | 2418753536
total_swap_memory_bytes        | 2147479552
total_swap_memory_free_bytes   | 2145771520
disk_space_free_mb             | 18238
disk_space_used_mb             | 30013
disk_space_total_mb            | 48251
-[ RECORD 2 ]------------------+------------------------------------------
host_name                      | 10.20.100.248
open_files_limit               | 65536
threads_limit                  | 3833
core_file_limit_max_size_bytes | 0
processor_count                | 2
processor_core_count           | 2
processor_description          | Intel(R) Xeon(R) CPU E5-2695 v2 @ 2.40GHz
opened_file_count              | 8
opened_socket_count            | 9
opened_nonfile_nonsocket_count | 7
total_memory_bytes             | 4018823168
total_memory_free_bytes        | 356466688
total_buffer_memory_bytes      | 327278592
total_memory_cache_bytes       | 2744279040
total_swap_memory_bytes        | 2147479552
total_swap_memory_free_bytes   | 2147479552
disk_space_free_mb             | 37102
disk_space_used_mb             | 11149
disk_space_total_mb            | 48251
-[ RECORD 3 ]------------------+------------------------------------------
host_name                      | 10.20.100.249
open_files_limit               | 65536
threads_limit                  | 3833
core_file_limit_max_size_bytes | 0
processor_count                | 2
processor_core_count           | 2
processor_description          | Intel(R) Xeon(R) CPU E5-2695 v2 @ 2.40GHz
opened_file_count              | 8
opened_socket_count            | 9
opened_nonfile_nonsocket_count | 7
total_memory_bytes             | 4018823168
total_memory_free_bytes        | 241610752
total_buffer_memory_bytes      | 309395456
total_memory_cache_bytes       | 2881675264
total_swap_memory_bytes        | 2147479552
total_swap_memory_free_bytes   | 2147479552
disk_space_free_mb             | 37430
disk_space_used_mb             | 10821
disk_space_total_mb            | 48251

43 - IO_USAGE

Provides disk I/O bandwidth usage history for the system.

Provides disk I/O bandwidth usage history for the system.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
START_TIME TIMESTAMP Beginning of history interval.
END_TIME TIMESTAMP End of history interval.
READ_KBYTES_PER_SEC FLOAT Counter history of the number of bytes read measured in kilobytes per second.
WRITTEN_KBYTES_PER_SEC FLOAT Counter history of the number of bytes written measured in kilobytes per second.

Privileges

Superuser

44 - LDAP_LINK_DRYRUN_EVENTS

Collects the results from LDAP dry run meta-functions:.

Collects the results from LDAP dry run meta-functions:

For detailed instructions on using these meta-functions, see Configuring LDAP link with dry runs.

Column Name Data Type Description
EVENT_TIMESTAMP TIMESTAMP The date and time of an LDAP server and Vertica LDAP Link interaction.
NODE_NAME VARCHAR The clerk node.
SESSION_ID VARCHAR The identification number of the LDAP Link session.
USER_ID INTEGER The unique, system-generated user identification number.
USER_NAME VARCHAR The name of the user for which the information is listed.
TRANSACTION_ID INTEGER The system-generated transaction identification number. Is NULL if a transaction id does not exist.
EVENT_TYPE VARCHAR The result of a dry run.
ENTRY_NAME VARCHAR The name of the object on which the event occurred, if applicable. For example, the event SYNC-STARTED does not use an object.
ROLE_NAME VARCHAR The name of a role.
LDAPURIHASH INTEGER The URI hash number for the LDAP user.
LDAP_URI VARCHAR The URI for the LDAP server.
BIND_DN VARCHAR The Distinguished Name used for the dry run bind.
FILTER_GROUP VARCHAR The group attribute passed to the dry run meta-functions as LDAPLinkFilterGroup.
FILTER_USER VARCHAR The user attribute passed to the dry run meta-functions as LDAPLinkFilterUser.
LINK_SCOPE VARCHAR The DN level to replicate, passed to the dry run meta-functions as LDAPLinkScope.
SEARCH_BASE VARCHAR The DN level from which LDAP Link begins the search, passed to the dry run meta-functions as LDAPLinkSearchBase.
GROUP_MEMBER VARCHAR Identifies the members of an LDAP group, passed to the dry run meta-functions as LDAPLinkGroupMembers.
GROUP_NAME VARCHAR The LDAP field to use when creating a role name in Vertica, passed to the dry run meta-functions as LDAPLinkGroupName.
LDAP_USER_NAME VARCHAR The attribute that identifies individual users, passed to the dry run meta-functions as LDAPLinkUserName.
TLS_REC_CERT VARCHAR The connection policy used for the dry run connection for certificate management. This connection policy is set through the LDAPLink TLS Configuration.
TLS_CA_CERT VARCHAR The CA certificate used for the dry run connection specified by the LDAPLink TLS Configuration.

45 - LDAP_LINK_EVENTS

Monitors events that occurred during an LDAP Link synchronization.

Monitors events that occurred during an LDAP Link synchronization.

Column Name Data Type Description
EVENT_TIMESTAMP TIMESTAMP The time the event occurred.
NODE_NAME VARCHAR The name of the node or nodes for which the information is listed.
SESSION_ID VARCHAR The identification number of the LDAP Link session.
USER_ID INTEGER The unique, system-generated user identification number.
USER_NAME VARCHAR The name of the user for which the information is listed.
TRANSACTION_ID INTEGER The system-generated transaction identification number. Is NULL if a transaction id does not exist.
EVENT_TYPE VARCHAR The type of event being logged, for example USER_CREATED and PROCESSING_STARTED.
ENTRY_NAME VARCHAR The name of the object on which the event occurred, if applicable. For example, the event SYNC-STARTED does not use an object.
ENTRY_OID INTEGER The unique identification number for the object on which the event occurred, if applicable.
LDAPURIHASH INTEGER The URI hash number for the LDAP user.

46 - LOAD_SOURCES

Like LOAD_STREAMS, monitors active and historical load metrics on each node.

Like LOAD_STREAMS, monitors active and historical load metrics on each node. The LOAD_SOURCES table breaks information down by source and portion. Rows appear in this table only for COPY operations that are profiled or run for more than one second. LOAD_SOURCES does not record information about loads from ORC or Parquet files or COPY LOCAL.

A row is added to this table when the loading of a source or portion begins. Column values related to the progress of the load are updated during the load operation.

Columns that uniquely identify the load source (the various ID and name columns) and column IS_EXECUTING always have non-NULL values.

Column Name Data Type Description
SESSION_ID VARCHAR Identifier of the session for which Vertica captures load stream information. This identifier is unique within the cluster for the current session but can be reused in a subsequent session.
TRANSACTION_ID INTEGER Identifier for the transaction within a session. If a session is active, but no transaction has begun, this value is NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
STREAM_NAME VARCHAR

Load stream identifier. If the user does not supply a specific name, the STREAM_NAME default value is tablename-ID, where:

  • tablename is the table into which data is being loaded.

  • ID is an integer value. ID is guaranteed to be unique within the current session on a node.

This system table includes stream names for every COPY statement that takes more than 1 second to run. The 1-second duration includes the time to plan and execute the statement.

SCHEMA_NAME VARCHAR Schema name for which load information is listed. Lets you identify two streams that are targeted at tables with the same name in different schemas. NULL, if selecting from an external table.
TABLE_OID INTEGER A unique numeric ID assigned by the Vertica catalog that identifies the table. NULL, if selecting from an external table.
TABLE_NAME VARCHAR Name of the table being loaded. NULL, if selecting from an external table.
NODE_NAME VARCHAR Name of the node loading the source.
SOURCE_NAME VARCHAR

  • Full file path if copying from a file.

  • Value returned by getUri() if the source is a user-defined source.

  • STDIN if loading from standard input.

PORTION_OFFSET INTEGER Offset of the source portion, or NULL if not apportioned.
PORTION_SIZE INTEGER Size of the source portion, or NULL if not apportioned.
IS_EXECUTING BOOLEAN Whether this source is currently being parsed, where t is true and f is false.
READ_BYTES INTEGER Number of bytes read from the input file.
ROWS_PRODUCED INTEGER Number of rows produced from parsing the source.
ROWS_REJECTED INTEGER Number of rows rejected from parsing the source. If CopyFaultTolerantExpressions is true, also includes rows rejected during expression evaluation.
INPUT_SIZE INTEGER Size of the input source in bytes, or NULL for unsized sources. For UDSources, this value is the value returned by getSize().
PARSE_COMPLETE_PERCENT INTEGER Percent of rows from the input file that have been parsed.
FAILURE_REASON VARCHAR

Indicates cause for failure, one of the following:

  • Load source aborted, error message indicates cause. For example: COPY: Could not open file [filename] for reading; Permission denied
  • Load canceled, displays error message: Statement interrupted

In all other cases, set to NULL.

PEAK_COOPERATING_THREADS INTEGER The peak number of threads parsing this source in parallel.
CLOCK_TIME_SOURCE INTEGER Displays in real-time how many microseconds (µs) have been consumed by the UDSource phase of a load operation.
CLOCK_TIME_FILTERS INTEGER Displays in real-time how many microseconds (µs) have been consumed by all UDFilter phases of a load operation.
CLOCK_TIME_CHUNKER INTEGER Displays in real-time how many microseconds (µs) have been consumed by the UDChunker phase of a load operation.
CLOCK_TIME_PARSER INTEGER Displays in real-time how many microseconds (µs) have been consumed by the UDParser phase of a load operation.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

47 - LOAD_STREAMS

Monitors active and historical load metrics for load streams.

Monitors active and historical load metrics for load streams. This is useful for obtaining statistics about how many records got loaded and rejected from the previous load. Vertica maintains system table metrics until they reach a designated size quota (in kilobytes). This quota is set through internal processes, which you cannot set or view directly.

Column Name Data Type Description
SESSION_ID VARCHAR Identifier of the session for which Vertica captures load stream information. This identifier is unique within the cluster for the current session, but can be reused in a subsequent session.
TRANSACTION_ID INTEGER Identifier for the transaction within a session. If a session is active but no transaction has begun, this is NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
STREAM_NAME VARCHAR

Load stream identifier. If the user does not supply a specific name, the STREAM_NAME default value is:
tablename-ID

where tablename is the table into which data is being loaded, and ID is an integer value, guaranteed to be unique with the current session on a node.

This system table includes stream names for every COPY statement that takes more than 1-second to run. The 1-second duration includes the time to plan and execute the statement.

SCHEMA_NAME VARCHAR Schema name for which load stream information is listed. Lets you identify two streams that are targeted at tables with the same name in different schemas
TABLE_ID INTEGER Catalog-assigned numeric value that uniquely identifies the table.
TABLE_NAME VARCHAR Name of the table being loaded.
LOAD_START VARCHAR Linux system time when the load started.
LOAD_DURATION_MS NUMERIC(54,0) Duration of the load stream in milliseconds.
IS_EXECUTING BOOLEAN Indicates whether the load is executing, where t is true and f is false.
ACCEPTED_ROW_COUNT INTEGER Number of rows loaded.
REJECTED_ROW_COUNT INTEGER Number of rows rejected.
READ_BYTES INTEGER Number of bytes read from the input file.
INPUT_FILE_SIZE_BYTES INTEGER

Size of the input file in bytes.

Note: When using STDIN as input, the input file size is zero (0).

PARSE_COMPLETE_PERCENT INTEGER Percent of rows from the input file that have been parsed.
UNSORTED_ROW_COUNT INTEGER

Cumulative number rows not sorted across all projections.

Note: UNSORTED_ROW_COUNT could be greater than ACCEPTED_ROW_COUNT because data is copied and sorted for every projection in the target table.

SORTED_ROW_COUNT INTEGER Cumulative number of rows sorted across all projections.
SORT_COMPLETE_PERCENT INTEGER Percent of rows from the input file that have been sorted.

Privileges

If you have the SYSMONITOR role or are the dbadmin user, this table shows all loads. Otherwise it shows only your loads.

48 - LOCK_USAGE

Provides aggregate information about lock requests, releases, and attempts, such as wait time/count and hold time/count.

Provides aggregate information about lock requests, releases, and attempts, such as wait time/count and hold time/count. Vertica records:

  • Lock attempts at the end of the locking process

  • Lock releases after lock attempts are released

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information on which lock interaction occurs.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
OBJECT_NAME VARCHAR Name of object being locked; can be a table or an internal structure (projection, global catalog, or local catalog).
MODE VARCHAR Intended operations of the transaction. Otherwise, this value is NONE. For a list of lock modes and compatibility, see Lock modes.
AVG_HOLD_TIME INTERVAL Average time (measured in intervals) that Vertica holds a lock.
MAX_HOLD_TIME INTERVAL Maximum time (measured in intervals) that Vertica holds a lock.
HOLD_COUNT INTEGER Total number of times the lock was granted in the given mode.
AVG_WAIT_TIME INTERVAL Average time (measured in intervals) that Vertica waits on the lock.
MAX_WAIT_TIME INTERVAL Maximum time (measured in intervals) that Vertica waits on a lock.
WAIT_COUNT INTEGER Total number of times lock was unavailable at the time it was first requested.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

49 - LOCKS

Monitors lock grants and requests for all nodes.

Monitors lock grants and requests for all nodes. If no locks are active, a query on LOCKS returns no rows.

Column Name Data Type Description
NODE_NAMES VARCHAR

Comma-separated list of nodes where lock interaction occurs.

A transaction can have the same lock in the same mode in the same scope on multiple nodes. However, the transaction gets only one line in the table.

OBJECT_NAME VARCHAR Name of object to lock, either a table or an internal structure: projection, global catalog, or local catalog.
OBJECT_ID INTEGER Unique numeric ID assigned by the Vertica catalog that identifies the object to lock.
TRANSACTION_ID VARCHAR Identifier of transaction within the session, if any; otherwise NULL. Transaction IDs can be used to join other system tables.
TRANSACTION_DESCRIPTION VARCHAR Identifier of transaction and associated description. Typically, this query caused the transaction's creation.
LOCK_MODE VARCHAR Transaction's lock type.
LOCK_SCOPE VARCHAR

Expected duration of the lock after it is granted. Before the lock is granted, Vertica lists the scope as REQUESTED.

After a lock is granted, its scope is set to one of the following:

  • STATEMENT_LOCALPLAN

  • STATEMENT_COMPILE

  • STATEMENT_EXECUTE

  • TRANSACTION_POSTCOMMIT

  • TRANSACTION

All scopes other than TRANSACTION are transient and used only as part of normal query processing.

REQUEST_TIMESTAMP TIMESTAMP Time when the transaction began waiting on the lock.
GRANT_TIMESTAMP TIMESTAMP

Time the transaction acquired or upgraded the lock:

  • Return values are NULL until the grant occurs.

  • If the grant occurs immediately, values might be the same as REQUEST_TIMESTAMP.

See also

50 - LOGIN_FAILURES

This system table lists failures for each failed login attempt.

This system table lists failures for each failed login attempt. This information helps you determine if a user is having difficulty getting into the database or identify a possible intrusion attempt.

Column Name Data Type Description
LOGIN_TIMESTAMP TIMESTAMPTZ Time when Vertica recorded the login.
DATABASE_NAME VARCHAR The name of the database for the login attempt.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_NAME VARCHAR Name of the user whose login failed at the time Vertica recorded the session.
CLIENT_HOSTNAME VARCHAR Host name and port of the TCP socket from which the client connection was made. NULL if the session is internal.
CLIENT_PID INTEGER

Identifier of the client process that issued this connection.

In some cases, the client process is on a different machine from the server.

CLIENT_VERSION VARCHAR Unused.
CLIENT_OS_USER_NAME VARCHAR The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.
AUTHENTICATION_METHOD VARCHAR

Name of the authentication method used to validate the client application or user who is trying to connect to the server using the database user name provided

Valid values:

  • Trust

  • Reject

  • GSS

  • LDAP

  • Ident

  • Hash

  • TLS

See Configuring client authentication for further information.

CLIENT_AUTHENTICATION_NAME VARCHAR Locally created name of the client authentication method.
REASON VARCHAR

Description of login failure reason.

Valid values:

  • INVALID USER

  • ACCOUNT LOCKED

  • REJECT

  • FAILED

  • INVALID AUTH METHOD

  • INVALID DATABASE

Privileges

Superuser

51 - MEMORY_EVENTS

Records events related to Vertica memory usage.

Records events related to Vertica memory usage.

Column Name Data Type Description
NODE_NAME VARCHAR Node where the event occurred
EVENT_TIME TIMESTAMPTZ Event start time
EVENT_TYPE VARCHAR

Type of event, one of the following:

  • MEMORY_REPORT: The Vertica memory poller created a report on memory usage, for the reason specified in EVENT_REASON. For details, see Memory usage reporting.

  • MALLOC_TRIM: Vertica ran the glibc function malloc_trim() to reclaim glibc-allocated memory. For details, see Memory trimming.

EVENT_REASON VARCHAR Reason for the event—for example, trim threshold was greater than RSS / available-memory.
EVENT_DETAILS VARCHAR Additional information about the event—for example, how much memory malloc_trim() reclaimed.
DURATION_US INTEGER Duration of the event in microseconds (µs).

Privileges

None

Examples

=> SELECT * FROM MEMORY_EVENTS;
-[ RECORD 1 ]-+-----------------------------------------------------------------
event_time    | 2019-05-02 13:17:20.700892-04
node_name     | v_vmart_node0001
event_type    | MALLOC_TRIM
event_reason  | memory_trim()
event_details | pre-trim RSS 378822656 post-trim RSS 372129792 benefit 0.0176675
duration_us   | 7724

52 - MEMORY_USAGE

Records system resource history for memory usage.

Records system resource history for memory usage. This is useful for comparing memory that Vertica uses versus memory in use by the entire system.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
START_TIME TIMESTAMP Beginning of history interval.
END_TIME TIMESTAMP End of history interval.
AVERAGE_MEMORY_USAGE_PERCENT FLOAT Records the average memory usage in percent of total memory (0-100) during the history interval.

Privileges

Superuser

53 - MERGEOUT_PROFILES

Returns information about and status of automatic mergeout operations.

Returns information about and status of automatic mergeout operations.

This table excludes operations with a REQUEST_TYPE of NO_WORK. It also excludes the operations of user-invoked mergeout functions, such as DO_TM_TASK.

Column Name Data Type Description
START_TIME TIMESTAMP When the Tuple Mover began processing storage location mergeout requests.
END_TIME TIMESTAMP When the mergeout finished.
TRANSACTION_ID INTEGER Identifier for the transaction within the session.
NODE_NAME VARCHAR Node name for which information is listed.
SCHEMA_NAME VARCHAR The schema for which information is listed.
TABLE_NAME VARCHAR The table for which information is listed.
PROJECTION_NAME VARCHAR The projection for which information is listed.
PROJECTION_OID INTEGER Projection's unique catalog identifier.
REQUEST_TYPE VARCHAR

Identifies the type of operation performed by the tuple mover. Possible values:

  • PURGE

  • MERGEOUT

  • DVMERGEOUT

EVENT_TYPE VARCHAR

Displays the status of the mergeout operation. Possible values:

  • ERROR

  • RETRY

  • REQUEST_QUEUED

  • REQUEST_COMPLETED

THREAD_ID INTEGER The ID of the thread that performed the mergeout.
STRATA_NO INTEGER The ID of the strata the ROS container belongs to.
PARTITION_KEY INTEGER The key of the partition.
CONTAINER_COUNT INTEGER The number of ROS containers in the mergeout operation.
TOTAL_SIZE_IN_BYTES INTEGER Size in bytes of all ROS containers in the mergeout operation.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

To following statement returns failed mergeout operations for table public.store_orders.

=> SELECT node_name, schema_name, table_name, request_type, event_type FROM mergeout_profiles WHERE event_type='ERROR';
    node_name     | schema_name |  table_name  | request_type | event_type
------------------+-------------+--------------+--------------+------------
 v_vmart_node0002 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0002 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0001 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0001 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0003 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0003 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0003 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0002 | public      | store_orders | MERGEOUT     | ERROR
 v_vmart_node0001 | public      | store_orders | MERGEOUT     | ERROR
 (9 rows)

See also

54 - MODEL_STATUS_HISTORY

Lists the status history of registered machine learning models in the database, including models that have been unregistered or dropped.

Lists the status history of registered machine learning models in the database, including models that have been unregistered or dropped. Only superusers or users to whom superusers have granted sufficient privileges can query the table. Vertica recommends granting access to the MLSUPERVISOR role.

Column Name Data Type Description
REGISTERED_NAME VARCHAR Abstract name to which the model, identified by MODEL_ID, was registered at the time of the status change. This REGISTERED_NAME can represent a group of models for a higher-level application, where each model in the group has a unique version number.
REGISTERED_VERSION INTEGER Unique version number of the registered model under its specified REGISTERED_NAME.
NEW_STATUS VARCHAR New status of the registered model.
OLD_STATUS VARCHAR Old status of the registered model.
STATUS_CHANGE_TIME TIMESTAMPTZ Time at which the model status was changed.
OPERATOR_ID INTEGER Internal ID of the user who performed the status change.
OPERATOR_NAME VARCHAR Name of the user who performed the status change.
MODEL_ID INTEGER Internal ID of the model for which information is listed.
SCHEMA_NAME VARCHAR Name of the schema that contains the model. This value is NULL if the model has been dropped.
MODEL_NAME VARCHAR

Name of the model. This value is NULL if the model has been dropped.

Each existing model can be uniquely identified by either its [schema_name.]model_name or the combination of its REGISTERED_NAME and REGISTERED_VERSION.

Example

If a superuser grants SELECT access of the table to the MLSUPERVISOR role, users with that role can then query the MODEL_STATUS_HISTORY table:

-- as superuser
=> GRANT SELECT ON TABLE v_monitor.model_status_history TO MLSUPERVISOR;
WARNING 8555:  You are granting privilege on a system table used by superuser only. Revoke the grant if you are unsure
GRANT PRIVILEGE

-- as user with MLSUPERVISOR role
=> SELECT * FROM MODEL_STATUS_HISTORY;
 registered_name | registered_version |  new_status  |  old_status  |       status_change_time      | operator_id | operator_name |  model_id  | schema_name |    model_name
-----------------+--------------------+--------------+------------- +-------------------------------+-------------+---------------+------------+-------------+-------------------
 app1            |                  1 | UNDER_REVIEW | UNREGISTERED | 2023-01-29 09:09:00.082166-05 | 1224567790  | u1            | 0113756739 | public      | native_linear_reg
 app1            |                  1 | STAGING      | UNDER_REVIEW | 2023-01-29 11:33:02.052464-05 | 2341679901  | supervisor1   | 0113756739 | public      | native_linear_reg
 app1            |                  1 | PRODUCTION   | STAGING      | 2023-01-30 04:12:30.481136-05 | 2341679901  | supervisor1   | 0113756739 | public      | native_linear_reg
(3 rows)

See also

55 - MONITORING_EVENTS

Reports significant events that can affect database performance and functionality if you do not address their root causes.

Reports significant events that can affect database performance and functionality if you do not address their root causes.

See Monitoring events for details.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
EVENT_CODE INTEGER Numeric identifier that indicates the type of event. See Event Types in Monitoring events for a list of event type codes.
EVENT_ID INTEGER Unique numeric ID that identifies the specific event.
EVENT_SEVERITY VARCHAR

Severity of the event from highest to lowest. These events are based on standard syslog severity types:

0 – Emergency

1 – Alert

2 – Critical

3 – Error

4 – Warning

5 – Notice

6 – Info

7 – Debug

EVENT_POSTED_TIMESTAMP TIMESTAMPTZ When this event was posted.
EVENT_CLEARED_TIMESTAMP TIMESTAMPTZ

When this event was cleared.

Note: You can also query the ACTIVE_EVENTS system table to see events that have not been cleared.

EVENT_EXPIRATION TIMESTAMPTZ Time at which this event expires. If the same event is posted again prior to its expiration time, this field gets updated to a new expiration time.
EVENT_CODE_DESCRIPTION VARCHAR Brief description of the event and details pertinent to the specific situation.
EVENT_PROBLEM_DESCRIPTION VARCHAR Generic description of the event.

Privileges

Superuser

See also

ACTIVE_EVENTS

56 - NETWORK_INTERFACES

Provides information about network interfaces on all Vertica nodes.

Provides information about network interfaces on all Vertica nodes.

Column Name Data Type Description
NODE_ID INTEGER Unique identifier for the node that recorded the row.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
INTERFACE VARCHAR Network interface name.
IP_ADDRESS_FAMILY VARCHAR Network address family (either 'ipv4' or 'ipv6').
IP_ADDRESS VARCHAR IP address for this interface.
SUBNET VARCHAR IP subnet for this interface.
MASK VARCHAR IP network mask for this interface.
BROADCAST_ADDRESS VARCHAR IP broadcast address for this interface.

Privileges

None

Examples

=> \x
Expanded display is on.
=> SELECT * FROM network_interfaces ORDER BY node_name ASC LIMIT 14;
-[ RECORD 1 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | lo
ip_address_family | ipv6
ip_address        | ::1
subnet            | ::1
mask              | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
broadcast_address |
-[ RECORD 2 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens192
ip_address_family | ipv6
ip_address        | fd9b:1fcc:1dc4:78d3::31
subnet            | fd9b:1fcc:1dc4:78d3::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 3 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | lo
ip_address_family | ipv4
ip_address        | 127.0.0.1
subnet            | 127.0.0.0
mask              | 255.0.0.0
broadcast_address | 127.0.0.1
-[ RECORD 4 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens192
ip_address_family | ipv4
ip_address        | 192.168.111.31
subnet            | 192.168.111.0
mask              | 255.255.255.0
broadcast_address | 192.168.111.255
-[ RECORD 5 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens32
ip_address_family | ipv4
ip_address        | 10.20.110.21
subnet            | 10.20.110.0
mask              | 255.255.255.0
broadcast_address | 10.20.110.255
-[ RECORD 6 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens32
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:61d3
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 7 ]-----+----------------------------------------
node_id           | 45035996273704982
node_name         | v_verticadb_node0001
interface         | ens192
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:2721
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 8 ]-----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | lo
ip_address_family | ipv6
ip_address        | ::1
subnet            | ::1
mask              | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff
broadcast_address |
-[ RECORD 9 ]-----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens192
ip_address_family | ipv6
ip_address        | fd9b:1fcc:1dc4:78d3::32
subnet            | fd9b:1fcc:1dc4:78d3::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 10 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | lo
ip_address_family | ipv4
ip_address        | 127.0.0.1
subnet            | 127.0.0.0
mask              | 255.0.0.0
broadcast_address | 127.0.0.1
-[ RECORD 11 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens192
ip_address_family | ipv4
ip_address        | 192.168.111.32
subnet            | 192.168.111.0
mask              | 255.255.255.0
broadcast_address | 192.168.111.255
-[ RECORD 12 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens32
ip_address_family | ipv4
ip_address        | 10.20.110.22
subnet            | 10.20.110.0
mask              | 255.255.255.0
broadcast_address | 10.20.110.255
-[ RECORD 13 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens32
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:1787
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |
-[ RECORD 14 ]----+----------------------------------------
node_id           | 45035996273841968
node_name         | v_verticadb_node0002
interface         | ens192
ip_address_family | ipv6
ip_address        | fe80::250:56ff:fe8e:2c9c
subnet            | fe80::
mask              | ffff:ffff:ffff:ffff::
broadcast_address |

57 - NETWORK_USAGE

Provides network bandwidth usage history on the system.

Provides network bandwidth usage history on the system. This is useful for determining if Vertica is using a large percentage of its available network bandwidth.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
START_TIME TIMESTAMP Beginning of history interval.
END_TIME TIMESTAMP End of history interval.
TX_KBYTES_PER_SEC FLOAT Counter history of outgoing (transmitting) usage in kilobytes per second.
RX_KBYTES_PER_SEC FLOAT Counter history of incoming (receiving) usage in kilobytes per second.

Privileges

Superuser

58 - NODE_EVICTIONS

Monitors node evictions on the system.

Monitors node evictions on the system.

Column Name Data Type Description
EVICTION_TIMESTAMP TIMESTAMPTZ Timestamp when the eviction request was made.
NODE_NAME VARCHAR The node name logging the information.
EVICTED_NODE_NAME VARCHAR The node name of the evicted node.
EVICTED_NODE_ID INTEGER The evicted node ID.
NODE_STATE_BEFORE_EVICTION VARCHAR The previous node state at the time of eviction.

59 - NODE_RESOURCES

Provides a snapshot of the node.

Provides a snapshot of the node. This is useful for regularly polling the node with automated tools or scripts.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
HOST_NAME VARCHAR The hostname associated with a particular node.
NODE_IDENTIFIER VARCHAR A unique identifier for the node.
PROCESS_SIZE_BYTES INTEGER The total size of the program.
PROCESS_RESIDENT_SET_SIZE_BYTES INTEGER The total number of bytes that the process has in memory.
PROCESS_SHARED_MEMORY_SIZE_BYTES INTEGER The amount of shared memory used.
PROCESS_TEXT_MEMORY_SIZE_BYTES INTEGER The total number of text bytes that the process has in physical memory. This does not include any shared libraries.
PROCESS_DATA_MEMORY_SIZE_BYTES INTEGER The amount of physical memory, in bytes, used for performing processes. This does not include the executable code.
PROCESS_LIBRARY_MEMORY_SIZE_BYTES INTEGER The total number of library bytes that the process has in physical memory.
PROCESS_DIRTY_MEMORY_SIZE_BYTES INTEGER The number of bytes that have been modified since they were last written to disk.
SPREAD_HOST VARCHAR The node name of the spread host.
NODE_PORT VARCHAR The port used for intra-cluster communication.
DATA_PORT VARCHAR The port used by the Vertica client.
DBCLERK BOOLEAN Whether this node is the DB clerk. The DB clerk is responsible for coordinating some administrative tasks in the database.

60 - NODE_STATES

Monitors node recovery state-change history on the system.

Monitors node recovery state-change history on the system. Vertica returns information only on nodes whose state is currently UP. To determine which nodes are not up, query the NODES table.

Column Name Data Type Description
EVENT_TIMESTAMP TIMESTAMPTZ Time when Vertica recorded the event.
NODE_ID INTEGER Catalog-assigned integer value that uniquely identifies thenode.
NODE_NAME VARCHAR Name of the node.
NODE_STATE VARCHAR

The node's state, one of the following:

  • UP

  • DOWN

  • READY

  • UNSAFE

  • SHUTDOWN

  • SHUTDOWN_ERROR

  • RECOVERING

  • RECOVER_ERROR

  • RECOVERED

  • INITIALIZING

  • STANDBY

  • NEEDS_CATCHUP

The following flow chart details different node states:

Privileges

None

61 - NOTIFIER_ERRORS

Reports errors encountered by notifiers.

Reports errors encountered by notifiers.

Column Name Data Type Description
ERROR_TIME TIMESTAMPTZ The time that the error occurred.
NODE_NAME VARCHAR Name of the node that encountered the error.
NOTIFIER_NAME VARCHAR Name of the notifier that triggered the error.
DESCRIPTION VARCHAR A description of the error.

Privileges

Superuser

62 - OUTPUT_DEPLOYMENT_STATUS

Contains information about the deployment status of all the projections in your design.

Contains information about the deployment status of all the projections in your design. Each row contains information about a different projection. Vertica populates this table when you deploy the database design by running the function DESIGNER_RUN_POPULATE_DESIGN_AND_DEPLOY.

Column Name Column Type Description
deployment_id INTEGER Unique ID that Database Designer assigned to the deployment.
design_name VARCHAR Unique name that the user assigned to the design.
deployment_projection_id INTEGER Unique ID that Database Designer assigned to the output projection.
deployment_projection_name VARCHAR Name that Database Designer assigned to the output projection or the name of the projection to be dropped.
deployment_status VARCHAR

Status of the deployment:

  • pending

  • complete

  • needs_refresh

  • in_progress

  • error

error_message VARCHAR Text of any error that occurred when creating or refreshing the specified projection.

63 - OUTPUT_EVENT_HISTORY

Contains information about each stage that Database Designer performs to design and optimize your database design.

Contains information about each stage that Database Designer performs to design and optimize your database design.

Column Name Data Type Description
TIME_STAMP TIMESTAMP Date and time of the specified stage.
DESIGN_ID INTEGER Unique id that Database Designer assigned to the design.
DESIGN_NAME VARCHAR Unique name that the user assigned to the design.
STAGE_TYPE VARCHAR

Design stage that Database Designer was working on at the time indicated by the TIME_STAMP field. Possible values include:

  • Design in progress

  • Analyzing data statistics

  • Optimizing query performance

  • Optimizing storage footprint

  • All done

  • Deployment in progress

ITERATION_NUMBER INTEGER Iteration number for the Optimizing query performance stage.
TOTAL_QUERY_COUNT INTEGER Total number of design queries in the design.
REMAINING_QUERY_COUNT INTEGER Number of design queries remaining for Database Designer to process.
MAX_STEP_NUMBER INTEGER Number of steps in the current stage.
CURRENT_STEP_NUMBER INTEGER Step in the current stage being processed at the time indicated by the TIME_STAMP field.
CURRENT_STEP_DESCRIPTION VARCHAR

Name of the step that Database Designer is performing at that time indicated in the TIME_STAMP field. Possible values include:

  • Design with deployment started

  • Design in progress: Analyze statistics phase

  • design_table_name

  • projection_name

  • Design in progress: Query optimization phase

  • Extracting interesting columns

  • Enumerating sort orders

  • Setting up projection candidates

  • Assessing projection candidates

  • Choosing best projections

  • Calculating estimated benefit of best projections

  • Complete

  • Design in progress: Storage optimization phase

  • Design completed successfully

  • Setting up deployment metadata

  • Identifying projections to be dropped

  • Running deployment

  • Deployment completed successfully

TABLE_ID INTEGER Unique id that Database Designer assigned to the design table.

Examples

The following example shows the steps that Database Designer performs while optimizing the VMart example database:

=> SELECT DESIGNER_CREATE_DESIGN('VMART_DESIGN');
=> SELECT DESIGNER_ADD_DESIGN_TABLES('VMART_DESIGN','public.*');
=> SELECT DESIGNER_ADD_DESIGN_QUERIES('VMART_DESIGN','/tmp/examples/vmart_queries.sql',);
...
=> \x
Expanded display is on.
=> SELECT * FROM OUTPUT_EVENT_HISTORY;
-[ RECORD 1 ] -----------+----------------------------
time_stamp               | 2013-06-05 11:44:41.588
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Design in progress
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          |
current_step_number      |
current_step_description | Design with deployment started
table id                 |
-[ RECORD 2 ] -----------+----------------------------
time_stamp               | 2013-06-05 11:44:41.611
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Design in progress
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          |
current_step_number      |
current_step_description | Design in progress: Analyze statistics phase
table id                 |
-[ RECORD 3 ] -----------+----------------------------
time_stamp               | 2013-06-05 11:44:42.011
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Analyzing statistics
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          | 15
current_step_number      | 1
current_step_description | public.customer_dimension
table id                 |
...
-[ RECORD 20 ] ----------+----------------------------
time_stamp               | 2013-06-05 11:44:49.324
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Optimizing query performance
iteration_number         | 1
total_query_count        | 9
remaining_query_count    | 9
max_step_number          | 7
current_step_number      | 1
current_step_description | Extracting interesting columns
table id                 |
...
-[ RECORD 62 ] ----------+----------------------------
time_stamp               | 2013-06-05 11:51:23.790
design_id                | 45035996273705090
design_name              | VMART_DESIGN
stage_type               | Deployment in progress
iteration_number         |
total_query_count        |
remaining_query_count    |
max_step_number          |
current_step_number      |
current_step_description | Deployment completed successfully
table id                 |

64 - PARTITION_COLUMNS

For each projection of a partitioned table, shows the following information:.

For each projection of a partitioned table, shows the following information:

  • Disk space used by each column per node.

  • Statistics that were collected on partition columns

Disk usage

The column DISK_SPACE_BYTES shows how much disk space the partitioned data uses, including deleted data. So, if you delete rows but do not purge them, the DELETED_ROW_COUNT column changes to show the number of deleted rows in each column; however, DISK_SPACE_BYTES remains unchanged. After deleted rows are purged, Vertica,reclaims the disk space: DISK_SPACE_BYTES changes accordingly, and DELETED_ROW_COUNT is reset to 0.

For grouped partitions, PARTITION_COLUMNS shows the cumulative disk space used for each column per grouped partition. The column GROUPED_PARTITION_KEY, if not null, identifies the partition in which a given column is grouped.

Statistics

STATISTICS_TYPE always shows the most complete type of statistics that are available on a given column, irrespective of timestamp. For example, if you collect statistics for a table on all levels—table, partition, and row, STATISTICS_TYPE is set to FULL (table-level), even if partition- and row-level statistics were collected more recently.

Column Name Data Type Description
COLUMN_NAME VARCHAR Identifies a named column within the partitioned table.
COLUMN_ID INTEGER Unique numeric ID assigned by the Vertica, which identifies the column.
TABLE_NAME VARCHAR Name of the partitioned table.
PROJECTION_NAME VARCHAR Projection name for which information is listed.
PROJECTION_ID INTEGER Unique numeric ID assigned by Vertica, which identifies the projection.
NODE_NAME VARCHAR Node that hosts partitioned data.
PARTITION_KEY VARCHAR Identifies the table partition.
GROUPED_PARTITION_KEY VARCHAR Identifies the grouped partition to which a given column belongs.
ROW_COUNT INTEGER The total number of partitioned data rows for each column, including deleted rows.
DELETED_ROW_COUNT INTEGER Number of deleted partitioned data rows in each column.
DISK_SPACE_BYTES INTEGER Amount of space used by partitioned data.
STATISTICS_TYPE VARCHAR

Specifies what sort of statistics are used for this column, one of the following listed in order of precedence:

  1. FULL: Table-level statistics

  2. PARTITION: Partition-level statistics

  3. ROWCOUNT: Minimal set of statistics and aggregate row counts

STATISTICS_UPDATED_TIMESTAMP TIMESTAMPTZ Specifies when statistics of the type specified in STATISTICS_TYPE were collected for this column.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

Given the following table definition:

=> CREATE TABLE messages
(
    time_interval timestamp NOT NULL,
    thread_id varchar(32) NOT NULL,
    unique_id varchar(53) NOT NULL,
    msg_id varchar(65),
    ...
    )
PARTITION BY ((messages.time_interval)::date);

a query on partition_columns might return the following (truncated) results:

=> SELECT * FROM partition_columns order by table_name, column_name;
column_name | column_id | table_name | projection_name | projection_id     | node_name        | partition_key | grouped_partition_key  | row_count | deleted_row_count | disk_space_bytes
---------------------------+----------+----------------+-------------------+------------------+---------------+------------------------+-----------+-------------------+------------------
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03    |                        | 6147      | 0                 | 41145
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15    |                        | 178       | 0                 | 65
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03    |                        | 6782      | 0                 | 45107
msg_id | 45035996273743190 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04    |                        | 866       | 0                 | 5883
...

thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                        | 6147      | 0                 | 70565
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                        | 178       | 0                 | 2429
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                        | 6782      | 0                 | 77730
thread_id | 45035996273743186 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                        | 866       | 0                 | 10317
...

time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                    | 6147      | 0                 | 6320
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                    | 178       | 0                 | 265
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                    | 6782      | 0                 | 6967
time_interval | 45035996273743184 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                    | 866       | 0                 | 892
...

unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-03 |                        | 6147      | 0                 | 70747
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0002 | 2010-07-15 |                        | 178       | 0                 | 2460
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-03 |                        | 6782      | 0                 | 77959
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-04 |                        | 866       | 0                 | 10332
unique_id | 45035996273743188 | messages | messages_super | 45035996273743182 | v_vmart_node0003 | 2010-07-15 |                        | 184       | 0                 | 2549
...

(11747 rows)

65 - PARTITION_REORGANIZE_ERRORS

new column projection_id.

Monitors all background partitioning tasks, and if Vertica encounters an error, creates an entry in this table with the appropriate information. Does not log repartitioning tasks that complete successfully.

Column Name Data Type Description
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_NAME VARCHAR Name of the user who received the error at the time Vertica recorded the session.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
TABLE_NAME VARCHAR Name of the partitioned table.
PROJECTION_ID INTEGER Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME VARCHAR Projection name for which information is listed.
MESSAGE VARCHAR Textual output of the error message.
HINT VARCHAR Actionable hint about the error.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

66 - PARTITION_STATUS

For each projection of each partitioned table, shows the fraction of its data that is actually partitioned according to the current partition expression.

For each projection of each partitioned table, shows the fraction of its data that is actually partitioned according to the current partition expression. When the partitioning of a table is altered, the value in PARTITION_REORGANIZE_PERCENT for each of its projections drops to zero and goes back up to 100 when all the data is repartitioned.

Column Name Data Type Description
PROJECTION_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
TABLE_SCHEMA VARCHAR Name of the schema that contains the partitioned table.
TABLE_NAME VARCHAR Table name that is partitioned.
TABLE_ID INTEGER Unique numeric ID assigned by the Vertica, which identifies the table.
PROJECTION_SCHEMA VARCHAR Schema containing the projection.
PROJECTION_NAME VARCHAR Projection name for which information is listed.
PARTITION_REORGANIZE_PERCENT INTEGER For each projection, drops to zero and goes back up to 100 when all the data is repartitioned after the partitioning of a table has been altered. Ideally all rows will show 100 (%).

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

67 - PARTITIONS

Displays partition metadata, one row per partition key, per ROS container.

Displays partition metadata, one row per partition key, per ROS container.

Column Name Data Type Description
PARTITION_KEY VARCHAR The partition value(s).
PROJECTION_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
TABLE_SCHEMA VARCHAR The schema name for which information is listed.
PROJECTION_NAME VARCHAR The projection name for which information is listed.
ROS_ID VARCHAR A unique numeric ID assigned by the Vertica catalog, which identifies the ROS container.
ROS_SIZE_BYTES INTEGER The ROS container size in bytes.
ROS_ROW_COUNT INTEGER Number of rows in the ROS container.
NODE_NAME VARCHAR Node where the ROS container resides.
DELETED_ROW_COUNT INTEGER The number of deleted rows in the partition.
LOCATION_LABEL VARCHAR The location label of the default storage location.

Notes

  • A many-to-many relationship exists between partitions and ROS containers. PARTITIONS displays information in a denormalized fashion.

  • To find the number of ROS containers having data of a specific partition, aggregate PARTITIONS over the partition_key column.

  • To find the number of partitions stored in a ROS container, aggregate PARTITIONS over the ros_id column.

Examples

See Viewing partition storage data.

68 - PROCESS_SIGNALS

Returns a history of signals that were received and handled by the Vertica process.

Returns a history of signals that were received and handled by the Vertica process. For details about signals, see the Linux documentation.

Column Name Data Type Description
SIGNAL_TIMESTAMP TIMESTAMPTZ Time when Vertica recorded the signal.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
SIGNAL_NUMBER INTEGER Signal number, refers to POSIX SIGNAL_NUMBER
SIGNAL_CODE INTEGER Signal code.
SIGNAL_PID INTEGER Linux process identifier of the signal.
SIGNAL_UID INTEGER Process ID of sending process.
SIGNAL_ADDRESS INTEGER Address at which fault occurred.

Privileges

Superuser

69 - PROJECTION_RECOVERIES

Retains history about projection recoveries.

Retains history about projection recoveries. Because Vertica adds an entry per recovery plan, a projection/node pair might appear multiple times in the output.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is recovering or has recovered the corresponding projection.
PROJECTION_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
PROJECTION_NAME VARCHAR Name of the projection that is being or has been recovered on the corresponding node.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any. TRANSACTION_ID initializes as NO_TRANSACTION with a value of 0. Vertica will ignore the recovery query and keep (0) if there's no action to take (no data in the table, etc). When no recovery transaction starts, ignored value appears in this table's STATUS column.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
METHOD VARCHAR

Recovery method that Vertica chooses. Possible values are:

  • incremental

  • incremental-replay-delete

  • split

  • recovery-by-container

STATUS VARCHAR

Current projection-recovery status on the corresponding node. STATUS can be "queued," which indicates a brief period between the time the query is prepared and when it runs. Possible values are:

  • queued

  • running

  • finished

  • ignored

  • error-retry

  • error-fatal

PROGRESS INTEGER

An estimate (value in the range [0,100]) of percent complete for the recovery task described by this information.

Note: The actual amount of time it takes to complete a recovery task depends on a number of factors, including concurrent workloads and characteristics of the data; therefore, accuracy of this estimate can vary.

The PROGRESS column value is NULL after the task completes.

DETAIL VARCHAR

More detailed information about PROGRESS. The values returned for this column depend on the type of recovery plan:

  • General recovery plans – value displays the estimated progress, as a percent, of the three primary parts of the plan: Scan, Sort, and Write.

  • Recovery-by-container plans – value begins with CopyStorage: and is followed by the number of bytes copied over the total number of bytes to copy.

  • Replay delete plans – value begins with Delete: and is followed by the number of deletes replayed over an estimate of the total number of deletes to replay.

The DETAIL column value becomes NULL after the recovery plan completes.

START_TIME TIMESTAMPTZ Time the recovery task described by this information started.
END_TIME TIMESTAMPTZ Time the recovery task described by this information ended.
RUNTIME_PRIORITY VARCHAR

Determines the amount of runtime resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool. Valid values are:

  • HIGH

  • MEDIUM

  • LOW

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

RECOVERY_STATUS

70 - PROJECTION_REFRESHES

System table PROJECTION_REFRESHES records information about refresh operations, successful and unsuccessful. PROJECTION_REFRESHES retains projection refresh data until one of the following events occurs:

  • Another refresh operation starts on a given projection.

  • CLEAR_PROJECTION_REFRESHES is called and clears data on all projections.

  • The table's storage quota is exceeded.

Column Name Data Type Description
NODE_NAME VARCHAR Node where the refresh was initiated.
PROJECTION_SCHEMA VARCHAR Name of the projection schema.
PROJECTION_ID INTEGER Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME VARCHAR Name of the refreshed projection.
ANCHOR_TABLE_NAME VARCHAR Name of the projection's anchor table.
REFRESH_STATUS VARCHAR

Status of refresh operations for this projection, one of the following:

  • Queued : Projection is queued for refresh.

  • Refreshing: Projection refresh is in progress.

  • Refreshed: Projection refresh is complete.

  • Failed: Projection refresh failed.

PERCENT_COMPLETE VARCHAR Shows the current percentage of completion for the refresh operation. When the refresh is complete, the column is set to NULL.
REFRESH_PHASE VARCHAR

Indicates how far the refresh has progressed:

  • Historical: Refresh reached the first phase and is refreshing data from historical data. This refresh phase requires the most amount of time.

  • Current: Refresh reached the final phase and is attempting to refresh data from the current epoch. To complete this phase, refresh must obtain a lock on the table. If the table is locked by another transaction, refresh is blocked until that transaction completes.

The LOCKS system table is useful for determining if a refresh is blocked on a table lock. To determine if a refresh has been blocked, locate the term "refresh" in the transaction description. A refresh has been blocked when the scope for the refresh is REQUESTED and other transactions acquired a lock on the table.

This field is NULL until the projection starts to refresh and is NULL after the refresh completes.

REFRESH_METHOD VARCHAR

Method used to refresh the projection:

  • Buddy: Projection refreshed from the contents of a buddy projection. This method maintains historical data, so the projection can used for historical queries.

  • Scratch: Projection refreshed without using a buddy projection. This method does not generate historical data, so the projection cannot participate in historical queries on data that precedes the refresh.

  • Rebalance: If the projection is segmented, it is refreshed from scratch; if unsegmented, it is refreshed from a buddy projection.

REFRESH_FAILURE_COUNT INTEGER Number of times a refresh failed for the projection. REFRESH_FAILURE_COUNT does not indicate whether the projection was eventually refreshed. See REFRESH_STATUS to determine whether the refresh operation is progressing.
SESSION_ID VARCHAR Unique numeric ID assigned by the Vertica catalog, which identifies the refresh session.
REFRESH_START TIMESTAMPTZ Time the projection refresh started.
REFRESH_DURATION_SEC INTERVAL SECOND (0) How many seconds the projection refresh ran.
IS_EXECUTING BOOLEAN Differentiates active and completed refresh operations.
RUNTIME_PRIORITY VARCHAR

Determines how many run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool, one of the following:

  • HIGH

  • MEDIUM

  • LOW

TRANSACTION_ID INTEGER

Identifier for the transaction within the session, if any; otherwise NULL.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

71 - PROJECTION_STORAGE

Monitors the amount of disk storage used by each projection on each node.

Monitors the amount of disk storage used by each projection on each node.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
PROJECTION_ID VARCHAR Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME VARCHAR The projection name for which information is listed.
PROJECTION_SCHEMA VARCHAR The name of the schema associated with the projection.
PROJECTION_COLUMN_COUNT INTEGER The number of columns in the projection.
ROW_COUNT INTEGER The number of rows in the table's projections, including any rows marked for deletion.
USED_BYTES INTEGER Number of bytes in disk storage used to store the compressed projection data. This value should not be compared to the output of the AUDIT function, which returns the raw data size of database objects.
ROS_COUNT INTEGER The number of ROS containers in the projection.
ANCHOR_TABLE_NAME VARCHAR The associated table name for which information is listed.
ANCHOR_TABLE_SCHEMA VARCHAR The associated table schema for which information is listed.
ANCHOR_TABLE_ID INTEGER A unique numeric ID, assigned by the Vertica catalog, which identifies the anchor table.

See also

72 - PROJECTION_USAGE

Records information about projections Vertica used in each processed query.

Records information about projections Vertica used in each processed query.

Column Name Data Type Description
QUERY_START_TIMESTAMP TIMESTAMPTZ Value of query at beginning of history interval.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_NAME VARCHAR Name of the user at the time Vertica recorded the session.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
REQUEST_ID INTEGER Unique identifier of the query request in the user session.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any; otherwise NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
IO_TYPE VARCHAR Input/output.
PROJECTION_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
PROJECTION_NAME VARCHAR Projection name for which information is listed.
ANCHOR_TABLE_ID INTEGER Unique numeric ID assigned by the Vertica, which identifies the anchor table.
ANCHOR_TABLE_SCHEMA VARCHAR Name of the schema that contains the anchor table.
ANCHOR_TABLE_NAME VARCHAR Name of the projection's associated anchor table.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

73 - QUERY_CONSUMPTION

Summarizes execution of individual queries.

Summarizes execution of individual queries. Columns STATEMENT_ID and TRANSACTION_ID combine as unique keys to these queries. One exception applies: a query with multiple plans has as many records.

Column Name Data Type Description
START_TIME END_TIME TIMESTAMP Demarcate the start and end of query execution, whether successful or not.
SESSION_ID VARCHAR Identifies the session where profiling information was captured. This identifier is unique within the cluster at any point in time, but can be reused when the session closes.
USER_ID INTEGER Unique numeric user identifier assigned by the Vertica catalog.
USER_NAME VARCHAR User name specified by this query profile.
TRANSACTION_ID INTEGER Identifies the transaction in which the query ran.
STATEMENT_ID INTEGER Numeric identifier of this query, unique within the query transaction.
CPU_CYCLES_US INTEGER Sum, in microseconds, of CPU cycles spent by all threads to process this query.
NETWORK_BYTES_SENT NETWORK_BYTES_RECEIVED INTEGER Total amount of data sent/received over the network by execution engine operators.
DATA_BYTES_READ DATA_BYTES_WRITTEN INTEGER Total amount of data read/written by storage operators from and to disk, includes all locations: local, HDFS, S3.
DATA_BYTES_LOADED INTEGER Total amount of data loaded from external sources: COPY, external tables, and data load.
BYTES_SPILLED INTEGER Total amount of data spilled to disk—for example, by SortManager, Join, and NetworkSend operators.
INPUT_ROWS INTEGER Number of unfiltered input rows from DataSource and Load operators. INPUT_ROWS shows the number of input rows that the query plan worked with, but excludes intermediate processing. For example, INPUT_ROWS excludes how many times SortManager spilled and read the same row.
INPUT_ROWS_PROCESSED INTEGER Value of INPUT_ROWS minus what was filtered by applying query predicates (valindex) and SIPs, and rows rejected by COPY.
PEAK_MEMORY_KB INTEGER Peak memory reserved by the resource manager for this query.
THREAD_COUNT INTEGER Maximum number of threads opened to process this query.
DURATION_MS INTEGER Total wall clock time, in milliseconds, spent to process this query.
RESOURCE_POOL VARCHAR Name of the resource pool where the query was executed.
OUTPUT_ROWS INTEGER Number of rows output to the client.
REQUEST_TYPE VARCHAR Type of query—for example, QUERY or DDL.
LABEL VARCHAR Label included as a LABEL hint in this query.
IS_RETRY BOOLEAN This query was tried earlier.
SUCCESS BOOLEAN This query executed successfully.

74 - QUERY_EVENTS

Returns information about query planning, optimization, and execution events.

Returns information about query planning, optimization, and execution events.

Column Name Data Type Description
EVENT_TIMESTAMP TIMESTAMPTZ Time when Vertica recorded the event.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_ID INTEGER Identifier of the user for the query event.
USER_NAME VARCHAR Name of the user for which Vertica lists query information at the time it recorded the session.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
REQUEST_ID* INTEGER Unique identifier of the query request in the user session.
TRANSACTION_ID* INTEGER Identifier for the transaction within the session, if any; otherwise NULL.
STATEMENT_ID* INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed.
EVENT_CATEGORY VARCHAR Category of event: OPTIMIZATION or EXECUTION.
EVENT_TYPE VARCHAR

Type of event. For details on each type, see the following sections:

EVENT_DESCRIPTION VARCHAR Generic description of the event.
OPERATOR_NAME VARCHAR Name of the Execution Engine component that generated the event, if applicable; for example, NetworkSend. Values from the OPERATOR_NAME and PATH_ID columns let you tie a query event back to a particular operator in the query plan. If the event did not come from a specific operator, the OPERATOR_NAME column is NULL.
PATH_ID INTEGER

Unique identifier that Vertica assigns to a query operation or path in a query plan, NULL if the event did not come from a specific operaton.

For more information, see EXECUTION_ENGINE_PROFILES.

OBJECT_ID INTEGER Object identifier such as projection or table to which the event refers.
EVENT_DETAILS VARCHAR Free-form text describing the specific event.
EVENT_SEVERITY VARCHAR

Indicates severity of the event with one of the following values:

  • Informational: No action required

  • Warning: Remedial action recommended as specified in SUGGESTED_ACTION

  • Critical: Remedial action required, as specified by SUGGESTED_ACTION

SUGGESTED_ACTION VARCHAR Specifies remedial action, recommended or required as indicated by EVENT_SEVERITY.

Informational event types

Event Type Description
CSE ANALYSIS The optimizer performed Common subexpressions analysis
CSE ANALYSIS STATS Time spent on Common subexpressions analysis (msec)
EXPRESSION_EVAL_ERROR An exception occurred during evaluation of an expression
EXTERNAL_PREDICATE_PUSHDOWN_NOT_SUPPORTED Predicate pushdown for older Hive versions may not be supported. For more information, see Querying external tables.
FLATTENED SUBQUERIES Subqueries flattened in FROM clause
GROUP_BY_PREPASS_FALLBACK Vertica could not run an optimization. In-memory prepass is disabled. The projection may not be optimal.
GROUPBY PUSHDOWN Internal to Vertica
LibHDFS++ FAILOVER RETRY Vertica attempted to contact a NameNode on an HDFS cluster that uses High Availability NameNode and did not receive a response. Vertica retried with a different NameNode.
LibHDFS++ MANUAL FALLBACK Vertica accessed HDFS using the hdfs URL scheme but HDFSUseWebHDFS is set. Vertica fell back to WebHDFS.
LibHDFS++ UNSUPPORTED OPERATION Vertica accessed HDFS using the hdfs URL scheme, but the HDFS cluster uses an unsupported feature such as wire encryption or HTTPS_ONLY or the Vertica session uses delegation tokens. Vertica fell back to WebHDFS.
MERGE_CONVERTED_TO_UNION Vertica has converted a merge operator to a union operator due to the sort order of the multi-threaded storage access stream.
NO GROUPBY PUSHDOWN Internal to Vertica
NODE PRUNING Vertica performed node pruning, which is similar to partition pruning, but at the node level.
ORC_FILE_INFO A query of ORC files encountered missing information (such as time zone) or an unrecognized ORC version. For missing information, Vertica uses a default value (such as the local time zone).
ORC_SOURCE_PRUNED An entire ORC file was pruned during predicate pushdown.
ORC_STRIPES_PRUNED The identified stripes were pruned during predicate pushdown. If an entire ORC file was pruned, it is instead recorded with an ORC_SOURCE_PRUNED event.
OUTER OVERRIDE NOT USED Vertica found swapping inner/outer tables in a join unnecessary because the inner/outer tables were in good order. (For example, a smaller table was used in an inner join.)
OUTER OVERRIDE USED For efficiency and optimization, Vertica has swapped the inner/outer tables in a join. Vertica used the smaller table as the inner table.
PARQUET_ROWGROUPS_PRUNED The identified row groups were pruned during predicate pushdown.
PARTITION_PATH_PRUNED A path (reported in event details) was pruned.
PARTITION_PATH_REJECTED Could not evaluate partition column predicate on a path from source list. Path will be rejected.
PARTITION_PRUNING COPY pruned partitions. The event reports how many paths were pruned, and PARTITION_PATH_PRUNED events record more details.
PREDICATES_DISCARDED_FROM_SCAN Some predicates have been discarded from this scan because expression analysis shows they are not needed.
REJECT_ROWNUMS_HIT_BUFFER_LIMIT Buffering row numbers during rejection hit buffer limit
SEQUENCE CACHE REFILLED Vertica has refilled sequence cache.
SIP_FALLBACK This optimization did not apply to this query type.
SMALL_MERGE_REPLACED Vertica has chosen a more efficient way to access the data by replacing a merge.
STORAGE_CONTAINERS_ELIMINATED Vertica has performed partition pruning for the purpose of optimization.
TRANSITIVE PREDICATE

Vertica has optimized by adding predicates to joins where it makes logical sense to do so.

For example, for the statement, SELECT * FROM A, B WHERE A.a = B.a AND A.a = 1;Vertica may add a predicate B a = 1 as a filter for better storage access of table B.

TYPE_MISMATCH_COLUMNS_PARQUETPARSER The Parquet parser used loose schema matching to load data, and could not coerce values in the Parquet data to the types defined for the table. By default the parser rejects the row. For more information, see PARQUET.
UNMATCHED_TABLE_COLUMNS_PARQUETPARSER The Parquet parser used loose schema matching to load data, and columns in the table had no corresponding columns in the data. The columns were given values of NULL.
VALUE_TRUNCATED A character value is too long.
WEBHDFS FAILOVER RETRY Vertica attempted to contact a NameNode on an HDFS cluster that uses High Availability NameNode and did not receive a response. Vertica retried with a different NameNode.

Warning event types

Review the following event types and recommended actions:

Event Type Description Recommended Action
AUTO_PROJECTION_USED The optimizer used an auto-projection to process this query. Create a projection that is appropriate for this query and others like it; consider using Database Designer to generate query-specific projections.
GROUP_BY_SPILLED This event type is typically related to a specific type of query, which you might need to adjust. Identify the type of query and make adjustments accordingly. You might need to adjust resource pools, projections, or the amount of RAM available. Try running the query on a cluster with no additional workload.
INVALID COST When creating a query plan, the optimizer calculated an invalid cost for a path: not-a-number (NaN) value, infinity value, or negative value. The path cost was set to its default value. No action available to users.
PATTERN_MATCH_NMEE More than one pattern event is true for a single row Modify event expressions to ensure that only one event can be true for any row. Alternatively, modify the query using a MATCH clause with ROWS MATCH FIRST EVENT.
PREDICATE OUTSIDE HISTOGRAM

A predicate value you are trying to match does not exist in a set of possible values for a specific column.

For example, you try to match a VARCHAR value WHERE mystring = "ABC<newline>". In this case, the newline character throws off the predicate matching optimizations.

 Run ANALYZE_STATISTICS on the column.
RESEGMENTED_MANY_ROWS This event type is typically related to a specific type of query, which you might need to adjust. Do projections need to be segmented in a different way to allow for join locality? Can you rewrite the query to filter out more rows at storage access time? (Typically, Vertica does so automatically through predicate pushdown.) Review your explain plan.
RLE_OVERRIDDEN The average run counts are not large enough for Run Length Encoding (RLE). This event occurs with queries where the filtered results for certain columns do not work with RLE because cardinality is less than 10. Review and rewrite your query, if necessary.

Critical event types

Review the following event types, and resolve issues as recommended:

Event Type Description Required Action
DELETE WITH NON OPTIMIZED PROJECTION One or more projections do not have your delete filter column in their sort order, causing Vertica difficulty identifying ros to mark as deleted. Add the delete filter column to the end of every projection sort order for your target delete table.
JOIN_SPILLED Vertica has spilled a join to disk. A join spill event slows down the subject query and all other queries as it consumes resources while using disk as virtual memory.

Try the following:

  1. Review the explain plan. The query might be too ambitious, for example, cross joining two large tables.

  2. Consider adding the query to a lower priority pool to reduce impact on other queries.

  3. Create projections that allow for a merge join instead of a hash join.

  4. Adjust the PLANNEDCONCURRENCYresource pool so that queries have more memory to execute.

MEMORY LIMIT HIT Indicates query complexity or, possibly, lack of available system memory. Consider adjusting the MAXMEMORYSIZE and PLANNEDCONCURRENCY resource pools so that the optimizer has sufficient memory. On a heavily used system, this event may occur more frequently.
NO HISTOGRAM Indicates a table does not have an updated column histogram. Running the function ANALYZE_STATISTICS most often corrects this issue.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

75 - QUERY_METRICS

Monitors the sessions and queries running on each node.

Monitors the sessions and queries running on each node.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
ACTIVE_USER_SESSION_COUNT INTEGER The number of active user sessions (connections).
ACTIVE_SYSTEM_SESSION_COUNT INTEGER The number of active system sessions.
TOTAL_USER_SESSION_COUNT INTEGER The total number of user sessions.
TOTAL_SYSTEM_SESSION_COUNT INTEGER The total number of system sessions.
TOTAL_ACTIVE_SESSION_COUNT INTEGER The total number of active user and system sessions.
TOTAL_SESSION_COUNT INTEGER The total number of user and system sessions.
RUNNING_QUERY_COUNT INTEGER The number of queries currently running.
EXECUTED_QUERY_COUNT INTEGER The total number of queries that ran.

76 - QUERY_PLAN_PROFILES

Provides detailed execution status for queries that are currently running in the system.

Provides detailed execution status for queries that are currently running in the system. Output from the table shows the real-time flow of data and the time and resources consumed for each path in each query plan.

Column Name Data Type Description
TRANSACTION_ID INTEGER An identifier for the transaction within the session if any; otherwise NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session; these columns are useful for creating joins with other system tables.
PATH_ID INTEGER Unique identifier that Vertica assigns to a query operation or path in a query plan. Textual representation for this path is output in the PATH_LINE column.
PATH_LINE_INDEX INTEGER Each plan path in QUERY_PLAN_PROFILES could be represented with multiple rows. PATH_LINE_INDEX returns the relative line order. You should include the PATH_LINE_INDEX column in the QUERY_PLAN_PROFILES ... ORDER BY clause so rows in the result set appear as they do in EXPLAIN-generated query plans.
PATH_IS_EXECUTING BOOLEAN Status of a path in the query plan. True (t) if the path has started running, otherwise false.
PATH_IS_COMPLETE BOOLEAN Status of a path in the query plan. True (t) if the path has finished running, otherwise false.
IS_EXECUTING BOOLEAN Status of a running query. True if the query is currently active (t), otherwise false (f).
RUNNING_TIME INTERVAL The amount of elapsed time the query path took to execute.
MEMORY_ALLOCATED_BYTES INTEGER The amount of memory the path used, in bytes.
READ_FROM_DISK_BYTES INTEGER The number of bytes the path read from disk (or the disk cache).
RECEIVED_BYTES INTEGER The number of bytes received over the network.
SENT_BYTES INTEGER Size of data sent over the network by the path.
PATH_LINE VARCHAR The query plan text string for the path, associated with the PATH ID and PATH_LINE_INDEX columns.

Privileges

Non-superusers see only the records of tables they have permissions to view.

Best practices

Table results can be very wide. For best results when you query QUERY_PLAN_PROFILES, sort on these columns:

  • TRANSACTION_ID

  • STATEMENT_ID

  • PATH_ID

  • PATH_LINE_INDEX

For example:

=> SELECT ... FROM query_plan_profiles
     WHERE ...
    ORDER BY transaction_id, statement_id, path_id, path_line_index;

Examples

See Profiling query plans

See also

77 - QUERY_PROFILES

Provides information about executed queries.

Provides information about executed queries.

Column Name Data Type Description
SESSION_ID VARCHAR The identification of the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
TRANSACTION_ID INTEGER An identifier for the transaction within the session if any; otherwise NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
IDENTIFIER VARCHAR

A string to identify the query in system tables.

Note: You can query the IDENTIFIER column to quickly identify queries you have labeled for profiling and debugging. See Labeling statements for details.

NODE_NAME VARCHAR The node name for which information is listed.
QUERY VARCHAR The query string used for the query.
QUERY_SEARCH_PATH VARCHAR A list of schemas in which to look for tables.
SCHEMA_NAME VARCHAR The schema name in which the query is being profiled, set only for load operations.
TABLE_NAME VARCHAR The table name in the query being profiled, set only for load operations.
QUERY_DURATION_US NUMERIC(18,0) The duration of the query in microseconds.
QUERY_START_EPOCH INTEGER The epoch number at the start of the given query.
QUERY_START VARCHAR The Linux system time of query execution in a format that can be used as a DATE/TIME expression.
QUERY_TYPE VARCHAR Is one of INSERT, SELECT, UPDATE, DELETE, UTILITY, or UNKNOWN.
ERROR_CODE INTEGER The return error code for the query.
USER_NAME VARCHAR The name of the user who ran the query.
PROCESSED_ROW_COUNT INTEGER The number of rows returned by the query.
RESERVED_EXTRA_MEMORY_B INTEGER

Shows how much unused memory (in bytes) remains that is reserved for a given query but is unassigned to a specific operator. This is the memory from which unbounded operators pull first.

The MEMORY_INUSE_KB column in system table RESOURCE_ACQUISITIONS shows how much total memory was acquired for each query.

If operators acquire all memory acquired for the query, the plan must request more memory from the Vertica resource manager.

IS_EXECUTING BOOLEAN Displays information about actively running queries, regardless of whether profiling is enabled.

78 - QUERY_REQUESTS

Returns information about user-issued query requests.

Returns information about user-issued query requests.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_NAME VARCHAR Name of the user who issued the query at the time Vertica recorded the session.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
REQUEST_ID INTEGER Unique identifier of the query request in the user session.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any; otherwise NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
REQUEST_TYPE VARCHAR

Type of the query request. Examples include, but are not limited to:

  • QUERY

  • DDL

  • LOAD

  • UTILITY

  • TRANSACTION

  • PREPARE

  • EXECUTE

  • SET

  • SHOW

REQUEST VARCHAR Query statement.
REQUEST_LABEL VARCHAR Label of the query, if available.
SEARCH_PATH VARCHAR Contents of the search path.
MEMORY_ACQUIRED_MB FLOAT Memory acquired by this query request in megabytes.
SUCCESS BOOLEAN Value returned if the query successfully executed.
ERROR_COUNT INTEGER Number of errors encountered in this query request (logged in ERROR_MESSAGES table).
START_TIMESTAMP TIMESTAMPTZ Beginning of history interval.
END_TIMESTAMP TIMESTAMPTZ End of history interval.
REQUEST_DURATION TIMESTAMPTZ Length of time in days, hours, minutes, seconds, and milliseconds.
REQUEST_DURATION_MS INTEGER Length of time the query ran in milliseconds.
IS_EXECUTING BOOLEAN Distinguishes between actively-running (t) and completed (f) queries.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

QUERY_PROFILES

79 - REBALANCE_OPERATIONS

Contains information on historic and ongoing rebalance operations.

Contains information on historic and ongoing rebalance operations.

Column Name Data Type Description
OBJECT_TYPE VARCHAR

The type of the rebalanced object:

  • Projection

  • DFSfile

OBJECT_ID INTEGER The ID of the rebalanced object.
OBJECT_NAME VARCHAR The name of the rebalanced object. Objects can be tables, projections, or other Vertica objects.
PATH_NAME VARCHAR The DFS path for unstructured data being rebalanced.
TABLE_NAME VARCHAR The name of the rebalanced table. This value is NULL for DFS files.
TABLE_SCHEMA VARCHAR The schema of the rebalanced table. This value is NULL for DFS files.
TRANSACTION_ID INTEGER The identifier for the transaction within the session.
STATEMENT_ID INTEGER The unique numeric ID for the currently-running statement.
NODE_NAME VARCHAR Name of the rebalancing node.
OPERATION_NAME VARCHAR

Identifies the specific rebalance operation being performed, one of:

  • Refresh projection, update temporary projection name and ID to master projection name

  • Drop unsegmented replicas

  • Replicate DFS File

  • Refresh projection

  • Drop replaced or replacement projection, rename temporary projection name to original projection name

  • Update temp table segments

  • Prepare : separate

  • Move storage containers

OPERATION_STATUS VARCHAR

Specifies status of the rebalance operation, one of the followin:

  • START

  • COMPLETE

  • ABORT

IS_EXECUTING BOOLEAN TRUE: the operation is currently running.
REBALANCE_METHOD VARCHAR

The method that Vertica is using to perform the rebalance, one of the following:

  • REFRESH: New projections are created according to the new segmentation definition. Data is copied via a refresh plan from projections with the previous segmentation to the new segments. This method is used only if START_REFRESH is called, a configuration parameter is set, or K-safety changes.

  • REPLICATE: Unsegmented projection data is copied to new nodes and removed from ephemeral nodes.

  • ELASTIC_CLUSTER: The segmentation of existing segmented projections is altered to adjust to a new cluster topology and data is redistributed accordingly.

SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
OPERATION_START_TIMESTAMP TIMESTAMPTZ The time that the rebalance began.
OPERATION_END_TIMESTAMP TIMESTAMPTZ The time that the rebalance ended. If the rebalance is ongoing, this value is NULL.
ELASTIC_CLUSTER_VERSION INTEGER The Elastic Cluster has a version. Each time the cluster topology changes, this version increments.
IS_LATEST BOOLEAN True if this row pertains to the most recent rebalance activity.

Privileges

Superuser

80 - REBALANCE_PROJECTION_STATUS

Maintain history on rebalance progress for relevant projections.

Maintain history on rebalance progress for relevant projections.

Column Name Data Type Description
PROJECTION_ID INTEGER Identifier of the projection to rebalance.
PROJECTION_SCHEMA VARCHAR Schema of the projection to rebalance.
PROJECTION_NAME VARCHAR Name of the projection to rebalance.
ANCHOR_TABLE_ID INTEGER Anchor table identifier of the projection to rebalance.
ANCHOR_TABLE_NAME VARCHAR Anchor table name of the projection to rebalance.
REBALANCE_METHOD VARCHAR

Method used to rebalance the projection, one of the following:

  • REFRESH: New projections are created according to the new segmentation definition. Data is copied via a refresh plan from projections with the previous segmentation to the new segments. This method is used only if START_REFRESH is called, a configuration parameter is set, or K-safety changes.

  • REPLICATE: Unsegmented projection data is copied to new nodes and removed from ephemeral nodes.

  • ELASTIC_CLUSTER: The segmentation of existing segmented projections is altered to adjust to a new cluster topology and data is redistributed accordingly.

DURATION_SEC INTERVAL SEC Deprecated, set to NULL.
SEPARATED_PERCENT NUMERIC(5,2) Percent of storage that has been separated for this projection.
TRANSFERRED_PERCENT NUMERIC(5,2) Percent of storage that has been transferred, for this projection.
SEPARATED_BYTES INTEGER Number of bytes, separated by the corresponding rebalance operation, for this projection.
TO_SEPARATE_BYTES INTEGER Number of bytes that remain to be separated by the corresponding rebalance operation for this projection.
TRANSFERRED_BYTES INTEGER Number of bytes transferred by the corresponding rebalance operation for this projection.
TO_TRANSFER_BYTES INTEGER Number of bytes that remain to be transferred by the corresponding rebalance operation for this projection.
IS_LATEST BOOLEAN True if this row pertains to the most recent rebalance activity, where elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster);
ELASTIC_CLUSTER_VERSION INTEGER

The elastic cluster has a version, and each time the cluster topology changes, this version is incremented. This column reflects the version to which this row of information pertains. The TO_* fields (TO_SEPARATE_* and TO_TRANSFER_*) are only valid for the current version.

To view only rows from the current, latest or upcoming rebalance operation, use:

WHERE elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster);

Privileges

Superuser

See also

81 - REBALANCE_TABLE_STATUS

Maintain history on rebalance progress for relevant tables.

Maintain history on rebalance progress for relevant tables.

Column Name Data Type Description
TABLE_ID INTEGER Identifier of the table that will be, was, or is being rebalanced.
TABLE_SCHEMA VARCHAR Schema of the table that will be, was, or is being rebalanced.
TABLE_NAME VARCHAR Name of the table that will be, was, or is being rebalanced.
REBALANCE_METHOD VARCHAR

Method that will be, is, or was used to rebalance the projections of this table. Possible values are:

  • REFRESH

  • REPLICATE

  • ELASTIC_CLUSTER

DURATION_SEC INTERVAL SEC

Deprecated - populated by NULL.

Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.

SEPARATED_PERCENT NUMERIC(5,2) Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
TRANSFERRED_PERCENT NUMERIC(5,2) Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
SEPARATED_BYTES INTEGER Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
TO_SEPARATE_BYTES INTEGER Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
TRANSFERRED_BYTES INTEGER Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
TO_TRANSFER_BYTES INTEGER Aggregate, by table_id, rebalance_method, and elastic_cluster_version, of the same in REBALANCE_PROJECTION_STATUS.
IS_LATEST BOOLEAN True if this row pertains to the most recent rebalance activity, where elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster;)
ELASTIC_CLUSTER_VERSION INTEGER

The Elastic Cluster has a version, and each time the cluster topology changes, this version is incremented. This column reflects the version to which this row of information pertains. The TO_* fields (TO_SEPARATE_* and TO_TRANSFER_*) are only valid for the current version.

To view only rows from the current, latest or upcoming rebalance operation, use:

WHERE elastic_cluster_version = (SELECT version FROM v_catalog.elastic_cluster;)

start_timestamp TIMESTAMPTZ The time that the rebalance began.
end_timestamp TIMESTAMPTZ The time that the rebalance ended.

Privileges

Superuser

See also

82 - RECOVERY_STATUS

Provides the status of recovery operations, returning one row for each node.

Provides the status of recovery operations, returning one row for each node.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
RECOVER_EPOCH INTEGER Epoch the recovery operation is trying to catch up to.
RECOVERY_PHASE VARCHAR

Current stage in the recovery process. Can be one of the following:

  • NULL

  • current

  • historical pass X, where X is the iteration count

SPLITS_COMPLETED INTEGER Number of independent recovery SPLITS queries that have run and need to run.
SPLITS_TOTAL INTEGER Total number of SPLITS queries that ran. Each query corresponds to one row in the PROJECTION_RECOVERIES table. If SPLITS_TOTAL = 2, then there should be 2 rows added to PROJECTION_RECOVERIES, showing query details.
HISTORICAL_COMPLETED INTEGER Number of independent recovery HISTORICAL queries that have run and need to run.
HISTORICAL_TOTAL INTEGER Total number of HISTORICAL queries that ran. Each query corresponds to one row in the PROJECTION_RECOVERIES table. If HISTORICAL_TOTAL = 2, then there should be 2 rows added to PROJECTION_RECOVERIES, showing query details.
CURRENT_COMPLETED INTEGER Number of independent recovery CURRENT queries that have run and need to run.
CURRENT_TOTAL INTEGER Total number of CURRENT queries that ran. Each query corresponds to one row in the PROJECTION_RECOVERIES table. If CURRENT_TOTAL = 2, then there should be 2 rows added to PROJECTION_RECOVERIES, showing query details.
IS_RUNNING BOOLEAN True (t) if the node is still running recovery; otherwise false (f).

Privileges

None

See also

PROJECTION_RECOVERIES

83 - REMOTE_REPLICATION_STATUS

Provides the status of replication tasks to alternate clusters.

Provides the status of replication tasks to alternate clusters.

Column Name Data Type Description
CURRENT_EPOCH INTEGER
EPOCH INTEGER
LAST_REPLICATED_TIME TIMESTAMPTZ
OBJECTS VARCHAR
REPLICATED_EPOCH INTEGER
REPLICATION_POINT VARCHAR
SNAPSHOT_NAME VARCHAR

Privileges

None

84 - REPARENTED_ON_DROP

Lists re-parenting events of objects that were dropped from their original owner but still remain in Vertica.

Lists re-parenting events of objects that were dropped from their original owner but still remain in Vertica. For example, a user may leave the organization and need to be removed from the database. When the database administrator drops the user from the database, that user's objects are re-parented to another user.

In some cases, a Vertica user's objects are reassigned based on the GlobalHeirUsername parameter. In this case, a user's objects are re-parented to the user indicated by this parameter.

Column Name Data Type Description
REPARENT_TIMESTAMP TIMESTAMP The time the re-parenting event occurred.
NODE_NAME VARCHAR The name of the node or nodes on which the re-parenting occurred.
SESSION_ID VARCHAR The identification number of the re-parenting event.
USER_ID INTEGER The unique, system-generated user identification number.
USER_NAME VARCHAR The name of the user that caused the re-parenting event. For example, a dbadmin user may have dropped a user thus re-parenting that user's objects.
TRANSACTION_ID INTEGER The system-generated transaction identification number. Is NULL if a transaction id does not exist.
OLD_OWNER_NAME VARCHAR The the name of the dropped user who used to own the re-parented object.
OLD_OWNER_OID INTEGER The unique identification number of the user who used to own the re-parented object.
NEW_OWNER_NAME VARCHAR The name of the user who now owns the re-parented objects.
NEW_OWNER_OID INTEGER The unique identification number of the user who now owns the re-parented objects.
OBJ_NAME VARCHAR The name of the object being re-parented.
OBJ_OID INTEGER The unique identification number of the object being re-parented.
SCHEMA_NAME VARCHAR The name of the schema in which the object resides.
SCHEMA_OID INTEGER The unique identification number of the schema in which the re-parented object resides.

85 - RESHARDING_EVENTS

Monitors historic and ongoing resharding operations.

Monitors historic and ongoing resharding operations.

Column Name Data Type Description
EVENT_TIME_STAMP TIMESTAMP Date and time of the resharding event.
NODE_NAME VARCHAR Node name for which resharding information is listed.
SESSION_ID VARCHAR Unique numeric ID assigned by the Vertica catalog that identifies the session for which resharding information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INTEGER Numeric ID of the user who ran the resharding operation.
USER_NAME VARCHAR Name of the user who ran the resharding operation.
TRANSACTION_ID INTEGER Numeric ID of the specified rehsarding transaction within the session.
RUNNING_STATUS VARCHAR

Current status of the resharding operation, one of the following strings:

  • START: The resharding operation has begun on all nodes.

  • RUNNING: The shard named OLD_SHARD_NAME is currently being resharded on the node.

  • RESHARDED: The resharding operation on the node is complete for the shard named OLD_SHARD_NAME.

  • ABORT: The resharding operation was aborted on all nodes.

  • COMPLETE: The resharding operation has completed for all nodes in the database.

OLD_SHARD_NAME VARCHAR Name of the shard to which the node was subscribed previous to the resharding operation. You can query the SHARDS system table for information about the new shard configuration.
OLD_SHARD_OID INTEGER Numeric ID of the shard to which the node was subscribed previous to the resharding operation.
OLD_SHARD_LOWER_BOUND INTEGER Lower bound of the shard to which the node was subscribed prior to the resharding operation. This value is set only if the resharding operation is complete for the shard specified by OLD_SHARD_OID.
OLD_SHARD_UPPER_BOUND INTEGER Upper bound of the shard to which the node was subscribed prior to the resharding operation. This value is set only if the resharding operation is complete for the shard specified by OLD_SHARD_OID.
CATALOG_SIZE INTEGER Catalog size (in bytes) on the node for the shard specified by OLD_SHARD_NAME. This value is provided only when the RUNNING_STATUS of the node is RUNNING or RESHARDED.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

86 - RESOURCE_ACQUISITIONS

Retains information about resources (memory, open file handles, threads) acquired by each running request.

Retains information about resources (memory, open file handles, threads) acquired by each running request. Each request is uniquely identified by its transaction and statement IDs within a given session.

Column Name Data Type Description
NODE_NAME VARCHAR Node name for which information is listed.
TRANSACTION_ID INTEGER Transaction identifier for this request.
STATEMENT_ID INTEGER Unique numeric ID for each statement within a transaction. NULL indicates that no statement is currently being processed.
REQUEST_TYPE VARCHAR Type of request issued to a resource pool. End users always see this column set to Reserve, to indicate that the request is query-specific.
POOL_ID /
POOL_NAME		 INTEGER /
VARCHAR

Each resource pool that participated in handling this request:

  • POOL_ID: A unique numeric ID assigned by the Vertica catalog that uniquely identifies the resource pool.

  • POOL_NAME: Name of the resource pool.

THREAD_COUNT INTEGER Number of threads in use by this request.
OPEN_FILE_HANDLE_COUNT INTEGER Number of open file handles in use by this request.
MEMORY_INUSE_KB INTEGER

Total amount of memory in kilobytes acquired by this query.

Column RESERVED_EXTRA_MEMORY_B in system table QUERY_PROFILES shows how much unused memory (in bytes) remains that is reserved for a given query but is unassigned to a specific operator.

If operators for a query acquire all memory specified by MEMORY_INUSE_KB, the plan must request more memory from the Vertica Resource Manager.

QUEUE_ENTRY_TIMESTAMP TIMESTAMPTZ Timestamp when the request was queued in this resource pool.
ACQUISITION_TIMESTAMP TIMESTAMPTZ Timestamp when the request was admitted to run.
RELEASE_TIMESTAMP TIMESTAMPTZ Time when Vertica released this resource acquisition.
DURATION_MS INTEGER Duration in milliseconds of request execution. If the request cascaded across multiple resource pools, DURATION_MS applies only to this resource pool.
IS_EXECUTING BOOLEAN

Set to true if the resource pool is still executing this request. A value of false can indicate one of the following:

  • The request was completed or denied.

  • The request cascaded to another resource pool.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Queue wait time

You can calculate how long a resource pool queues a given request before it begins execution by subtracting QUEUE_ENTRY_TIMESTAMP from ACQUISITION_TIMESTAMP. For example:

 => SELECT pool_name, queue_entry_timestamp, acquisition_timestamp,
    (acquisition_timestamp-queue_entry_timestamp) AS 'queue wait'
    FROM V_MONITOR.RESOURCE_ACQUISITIONS WHERE node_name ILIKE '%node0001';

See also

87 - RESOURCE_POOL_MOVE

Displays the cascade event information on each node.

Displays the cascade event information on each node.

Column Name Data Type Description
NODE_NAME VARCHAR Node name for which information is listed.
MOVE_TIMESTAMP TIMESTAMPTZ Time when the query attempted to move to the target pool.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INTEGER Identifies the query event user.
USER_NAME VARCHAR Name of the user for which Vertica lists query information at the time it records the session.
TRANSACTION_ID INTEGER Transaction identifier for the request.
STATEMENT_ID INTEGER Unique numeric ID for the statement.
SOURCE_POOL_NAME VARCHAR Name of the resource pool where the query was executing when Vertica attempted the move.
TARGET_POOL_NAME VARCHAR Name of resource pool where the query attempted to move.
MOVE_CAUSE VARCHAR

Denotes why the query attempted to move.

Valid values:

  • MOVE RESOURCE POOL COMMAND

  • RUNTIMECAP EXCEEDED

SOURCE_CAP INTEGER

Effective RUNTIMECAP value for the source pool. The value represents the lowest of these three values:

  • session RUNTIMECAP

  • user RUNTIMECAP

  • source pool RUNTIMECAP

TARGET_CAP INTEGER

Effective RUNTIMECAP value for the target pool. The value represents the lowest of these three values:

  • session RUNTIMECAP

  • user RUNTIMECAP

  • target pool RUNTIMECAP

SUCCESS BOOLEAN True, if the query successfully moved to the target pool.
RESULT_REASON VARCHAR States reason for success or failure of the move.

See also

88 - RESOURCE_POOL_STATUS

Provides current state of built-in and user-defined resource pools on each node.

Provides current state of built-in and user-defined resource pools on each node. Information includes:

  • Current memory usage

  • Resources requested and acquired by various requests

  • Number of queries executing

For general information about resource pools, see Resource pool architecture.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node for which information is provided.
POOL_OID INTEGER Unique numeric ID that identifies the pool and is assigned by the Vertica catalog.
POOL_NAME VARCHAR Name of the resource pool.
IS_INTERNAL BOOLEAN Denotes whether a pool is built-in.
MEMORY_SIZE_KB INTEGER Value of MEMORYSIZE setting of the pool in kilobytes.
MEMORY_SIZE_ACTUAL_KB INTEGER

Current amount of memory, in kilobytes, allocated to the pool by the resource manager. The actual size can be less than specified in the DDL, if both the following conditions exist:

  • The pool has been recently altered in a running system.

  • The request to shuffle memory is pending.

MEMORY_INUSE_KB INTEGER Amount of memory, in kilobytes, acquired by requests running against this pool.
GENERAL_MEMORY_BORROWED_KB INTEGER Amount of memory, in kilobytes, borrowed from the GENERAL pool by requests running against this pool. The sum of MEMORY_INUSE_KB and GENERAL_MEMORY_BORROWED_KB should be less than MAX_MEMORY_SIZE_KB.
QUEUEING_THRESHOLD_KB INTEGER Calculated as MAX_MEMORY_SIZE_KB * 0.95. When the amount of memory used by all requests against this resource pool exceeds the QUEUEING_THRESHOLD_KB, new requests against the pool are queued until memory becomes available.
MAX_MEMORY_SIZE_KB INTEGER

Value, in kilobytes, of the MAXMEMORYSIZE parameter as defined for the pool. After this threshold is reached, new requests against this pool are rejected or queued until memory becomes available.

MAX_QUERY_MEMORY_SIZE_KB INTEGER Value, in kilobytes, of the MAXQUERYMEMORYSIZE parameter as defined for the pool. The resource pool limits this amount of memory to all queries that execute in it.
RUNNING_QUERY_COUNT INTEGER Number of queries currently executing in this pool.
PLANNED_CONCURRENCY INTEGER Value of PLANNEDCONCURRENCY parameter as defined for the pool.
MAX_CONCURRENCY INTEGER Value of MAXCONCURRENCY parameter as defined for the pool.
IS_STANDALONE BOOLEAN If the pool is configured to have MEMORYSIZE equal to MAXMEMORYSIZE, the pool is considered standalone because it does not borrow any memory from the General pool.
QUEUE_TIMEOUT INTERVAL The interval that the request waits for resources to become available before being rejected. If you set this value to NONE, Vertica displays it as NULL.
QUEUE_TIMEOUT_IN_SECONDS INTEGER Value of QUEUETIMEOUT parameter as defined for the pool. If QUEUETIMEOUT is set to NONE, Vertica displays this value as NULL.
EXECUTION_PARALLELISM INTEGER Limits the number of threads used to process any single query issued in this resource pool.
PRIORITY INTEGER

Value of PRIORITY parameter as defined for the pool.

When set to HOLD, Vertica sets a pool's priority to -999 so the query remains queued until QUEUETIMEOUT is reached.

RUNTIMECAP_IN_SECONDS INTEGER Defined for this pool by parameter RUNTIMECAP, specifies in seconds the maximum time a query in the pool can execute. If a query exceeds this setting, it tries to cascade to a secondary pool.
RUNTIME_PRIORITY VARCHAR Defined for this pool by parameter RUNTIMEPRIORITY, determines how the resource manager should prioritize dedication of run-time resources (CPU, I/O bandwidth) to queries already running in this resource pool.
RUNTIME_PRIORITY_THRESHOLD INTEGER Defined for this pool by parameter RUNTIMEPRIORITYTHRESHOLD, specifies in seconds a time limit in which a query must finish before the resource manager assigns to it the resource pool's RUNTIME_PRIORITY setting.
SINGLE_INITIATOR BOOLEAN Set for backward compatibility.
QUERY_BUDGET_KB INTEGER

The current amount of memory that queries are tuned to use. The calculation that Vertica uses to determine this value is described inQuery budgeting.

For a detailed example of query budget calculations, see Do You Need to Put Your Query on a Budget? in the Vertica User Community.

CPU_AFFINITY_SET VARCHAR

The set of CPUs on which queries associated with this pool are executed. Can be:

  • A percentage of CPUs on the system

  • A zero-based list of CPUs (a four-CPU system c of CPUs 0, 1, 2, and 3).

CPU_AFFINITY_MASK VARCHAR The bit mask of CPUs available for use in this pool, read from right to left. See Examples below.
CPU_AFFINITY_MODE VARCHAR

The mode for the CPU affinity, one of the following:

  • ANY

  • EXCLUSIVE

  • SHARED

Examples

The following query returns bit masks that show CPU assignments for three user-defined resource pools. Resource pool bigqueries runs queries on CPU 0, ceo_pool on CPU 1, and testrp on CPUs 0 and 1:

 => SELECT pool_name, node_name, cpu_affinity_set, cpu_affinity_mode,
      TO_BITSTRING(CPU_AFFINITY_MASK::VARBINARY) "CPU Affinity Mask"
      FROM resource_pool_status WHERE IS_INTERNAL = 'false' order by pool_name, node_name;
 pool_name  |    node_name     | cpu_affinity_set | cpu_affinity_mode | CPU Affinity Mask
------------+------------------+------------------+-------------------+-------------------
 bigqueries | v_vmart_node0001 | 0                | SHARED            | 00110001
 bigqueries | v_vmart_node0002 | 0                | SHARED            | 00110001
 bigqueries | v_vmart_node0003 | 0                | SHARED            | 00110001
 ceo_pool   | v_vmart_node0001 | 1                | SHARED            | 00110010
 ceo_pool   | v_vmart_node0002 | 1                | SHARED            | 00110010
 ceo_pool   | v_vmart_node0003 | 1                | SHARED            | 00110010
 testrp     | v_vmart_node0001 | 0-1              | SHARED            | 00110011
 testrp     | v_vmart_node0002 | 0-1              | SHARED            | 00110011
 testrp     | v_vmart_node0003 | 0-1              | SHARED            | 00110011
(9 rows)

See also

89 - RESOURCE_QUEUES

Provides information about requests pending for various resource pools.

Provides information about requests pending for various resource pools.

Column Name Data Type Description
NODE_NAME VARCHAR The name of the node for which information is listed.
TRANSACTION_ID INTEGER Transaction identifier for this request
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID uniquely identifies a statement within a session.
POOL_NAME VARCHAR The name of the resource pool
MEMORY_REQUESTED_KB INTEGER Amount of memory in kilobytes requested by this request
PRIORITY INTEGER Value of PRIORITY parameter specified when defining the pool.
POSITION_IN_QUEUE INTEGER Position of this request within the pool’s queue
QUEUE_ENTRY_TIMESTAMP TIMESTAMP Timestamp when the request was queued

See also

90 - RESOURCE_REJECTION_DETAILS

Records an entry for each resource request that Vertica denies.

Records an entry for each resource request that Vertica denies. This is useful for determining if there are resource space issues, as well as which users/pools encounter problems.

Column Name Data Type Description
REJECTED_TIMESTAMP TIMESTAMPTZ Time when Vertica rejected the resource.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_NAME VARCHAR Name of the user at the time Vertica recorded the session.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
REQUEST_ID INTEGER Unique identifier of the query request in the user session.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any; otherwise NULL.
STATEMENT_ID INTEGER Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identifies a statement within a session.
POOL_ID INTEGER Catalog-assigned integer value that uniquely identifies theresource pool.
POOL_NAME VARCHAR Name of the resource pool
REASON VARCHAR

Reason for rejecting this request; for example:

  • Usage of single request exceeds high limit

  • Timed out waiting for resource reservation

  • Canceled waiting for resource reservation

RESOURCE_TYPE VARCHAR

Memory, threads, file handles or execution slots.

The following list shows the resources that are limited by the resource manager. A query might need some amount of each resource, and if the amount needed is not available, the query is queued and could eventually time out of the queue and be rejected.

  • Number of running plans

  • Number of running plans on initiator node (local)

  • Number of requested threads

  • Number of requested file handles

  • Number of requested KB of memory

  • Number of requested KB of address space

Note: Execution slots are determined by MAXCONCURRENCY parameter.

REJECTED_VALUE INTEGER Amount of the specific resource requested by the last rejection

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

RESOURCE_REJECTIONS

91 - RESOURCE_REJECTIONS

Monitors requests for resources that are rejected by the.

Monitors requests for resources that are rejected by the Resource manager. Information is valid only as long as the node is up and the counters reset to 0 upon node restart.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
POOL_ID INTEGER Catalog-assigned integer value that uniquely identifies theresource pool.
POOL_NAME VARCHAR Name of the resource pool.
REASON VARCHAR

Reason for rejecting this request, for example:

  • Usage of single request exceeds high limit

  • Timed out waiting for resource reservation

  • Canceled waiting for resource reservation

RESOURCE_TYPE VARCHAR

Memory, threads, file handles or execution slots.

The following list shows the resources that are limited by the resource manager. A query might need some amount of each resource, and if the amount needed is not available, the query is queued and could eventually time out of the queue and be rejected.

  • Number of running plans

  • Number of running plans on initiator node (local)

  • Number of requested threads

  • Number of requested file handles

  • Number of requested KB of memory

  • Number of requested KB of address space

REJECTION_COUNT INTEGER Number of requests rejected due to specified reason and RESOURCE_TYPE.
FIRST_REJECTED_TIMESTAMP TIMESTAMPTZ Time of the first rejection for this pool.
LAST_REJECTED_TIMESTAMP TIMESTAMPTZ Time of the last rejection for this pool.
LAST_REJECTED_VALUE INTEGER Amount of the specific resource requested by the last rejection.

Examples

=> SELECT node_name, pool_name, reason, resource_type, rejection_count AS count, last_rejected_value AS value FROM resource_rejections;
    node_name     | pool_name |           reason            | resource_type | count |  value
------------------+-----------+-----------------------------+---------------+-------+---------
 v_vmart_node0001 | sysquery  | Request exceeded high limit | Memory(KB)    |     1 | 8248449
(1 row)

See also

92 - RESOURCE_USAGE

Monitors system resource management on each node.

Monitors system resource management on each node.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
REQUEST_COUNT INTEGER The cumulative number of requests for threads, file handles, and memory (in kilobytes).
LOCAL_REQUEST_COUNT INTEGER The cumulative number of local requests.
REQUEST_QUEUE_DEPTH INTEGER The current request queue depth.
ACTIVE_THREAD_COUNT INTEGER The current number of active threads.
OPEN_FILE_HANDLE_COUNT INTEGER The current number of open file handles.
MEMORY_REQUESTED_KB INTEGER The memory requested in kilobytes.
ADDRESS_SPACE_REQUESTED_KB INTEGER The address space requested in kilobytes.
ROS_USED_BYTES INTEGER The size of the ROS in bytes.
ROS_ROW_COUNT INTEGER The number of rows in the ROS.
RESOURCE_REQUEST_REJECT_COUNT INTEGER The number of rejected plan requests.
RESOURCE_REQUEST_TIMEOUT_COUNT INTEGER The number of resource request timeouts.
RESOURCE_REQUEST_CANCEL_COUNT INTEGER The number of resource request cancelations.
DISK_SPACE_REQUEST_REJECT_COUNT INTEGER The number of rejected disk write requests.
FAILED_VOLUME_REJECT_COUNT INTEGER The number of rejections due to a failed volume.
TOKENS_USED INTEGER For internal use only.
TOKENS_AVAILABLE INTEGER For internal use only.

93 - SESSION_MARS_STORE

Shows Multiple Active Result Sets (MARS) storage information.

Shows Multiple Active Result Sets (MARS) storage information.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
SESSION_ID VARCHAR Identifier of the Vertica session. This identifier is unique within the cluster for the current session but can be reused in a subsequent session.
USER_NAME VARCHAR The username used to create the connection.
RESULTSET_ID INTEGER Identifier assigned to the result set.
ROW_COUNT INTEGER Number of rows requested by the query.
REMAINING_ROW_COUNT INTEGER Number of rows that still need to be returned.
BYTES_USED INTEGER The number of bytes requested.

94 - SESSION_PARAMETERS

Provides information about user-defined parameters (UDPARAMETERS) set for the current session.

Provides information about user-defined parameters (UDPARAMETERS) set for the current session.

Column Name Data Type Description
SESSION_ID VARCHAR The unique identifier for the session.
SCHEMA_NAME VARCHAR The name of the schema on which the session is running.
LIB_NAME VARCHAR The name of the user library running the UDx, if necessary.
LIB_OID VARCHAR The object ID of the library containing the function, if one is running.
PARAMETER_NAME VARCHAR The name of the session parameter.
CURRENT_VALUE VARCHAR The value of the session parameter.

See also

95 - SESSION_PROFILES

Provides basic session parameters and lock time out data.

Provides basic session parameters and lock time out data. To obtain information about sessions, see Profiling database performance.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
USER_NAME VARCHAR The name used to log in to the database or NULL if the session is internal.
CLIENT_HOSTNAME VARCHAR The host name and port of the TCP socket from which the client connection was made; NULL if the session is internal.
LOGIN_TIMESTAMP TIMESTAMP The date and time the user logged into the database or when the internal session was created. This field is useful for identifying sessions that have been left open for a period of time and could be idle.
LOGOUT_TIMESTAMP TIMESTAMP The date and time the user logged out of the database or when the internal session was closed.
SESSION_ID VARCHAR A unique numeric ID assigned by the Vertica catalog, which identifies the session for which profiling information is captured. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
EXECUTED_STATEMENT_SUCCESS_COUNT INTEGER The number of successfully run statements.
EXECUTED_STATEMENT_FAILURE_COUNT INTEGER The number of unsuccessfully run statements.
LOCK_GRANT_COUNT INTEGER The number of locks granted during the session.
DEADLOCK_COUNT INTEGER The number of deadlocks encountered during the session.
LOCK_TIMEOUT_COUNT INTEGER The number of times a lock timed out during the session.
LOCK_CANCELLATION_COUNT INTEGER The number of times a lock was canceled during the session.
LOCK_REJECTION_COUNT INTEGER The number of times a lock was rejected during a session.
LOCK_ERROR_COUNT INTEGER The number of lock errors encountered during the session.
CLIENT_TYPE VARCHAR

The type of client from which the connection was made. Possible client type values:

  • ADO.NET Driver

  • ODBC Driver

  • JDBC Driver

  • vsql

CLIENT_VERSION VARCHAR Returns the client version.
CLIENT_OS VARCHAR Returns the client operating system.
CLIENT_OS_USER_NAME VARCHAR The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.

See also

LOCKS

96 - SESSIONS

Monitors external sessions.

Monitors external sessions. Use this table to perform the following tasks:

  • Identify users who are running lengthy queries.

  • Identify users who hold locks because of an idle but uncommitted transaction.

  • Determine the details of the database security used for a particular session, either Secure Socket Layer (SSL) or client authentication.

  • Identify client-specific information, such as client version.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
USER_NAME VARCHAR The name used to log in to the database or NULL if the session is internal.
CLIENT_OS_HOSTNAME VARCHAR The hostname of the client as reported by their operating system.
CLIENT_HOSTNAME VARCHAR

The IP address and port of the TCP socket from which the client connection was made; NULL if the session is internal.

Vertica accepts either IPv4 or IPv6 connections from a client machine. If the client machine contains mappings for both IPv4 and IPv6, the server randomly chooses one IP address family to make a connection. This can cause the CLIENT_HOSTNAME column to display either IPv4 or IPv6 values, based on which address family the server chooses.

CLIENT_PID INTEGER The process identifier of the client process that issued this connection. Remember that the client process could be on a different machine than the server.
LOGIN_TIMESTAMP TIMESTAMP The date and time the user logged into the database or when the internal session was created. This field can help you identify sessions that have been left open for a period of time and could be idle.
SESSION_ID VARCHAR The identifier required to close or interrupt a session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
IDLE_SESSION_TIMEOUT VARCHAR Specifies how long this session can remain idle before timing out, set by SET SESSION IDLESESSIONTIMEOUT.
GRACE_PERIOD VARCHAR Specifies how long a session socket remains blocked while awaiting client input or output for a given query, set by SET SESSION GRACEPERIOD. If the socket is blocked for a continuous period that exceeds the grace period setting, the server shuts down the socket and throws a fatal error. The session is then terminated.
CLIENT_LABEL VARCHAR A user-specified label for the client connection that can be set when using ODBC. See Label in ODBC DSN connection properties. An MC output value means there are is a client connection to an MC-managed database for that USER_NAME
TRANSACTION_START DATE The date/time the current transaction started or NULL if no transaction is running.
TRANSACTION_ID INTEGER A string containing the hexadecimal representation of the transaction ID, if any; otherwise, NULL.
TRANSACTION _DESCRIPTION VARCHAR Description of the current transaction.
STATEMENT_START TIMESTAMP The timestamp the current statement started execution, or NULL if no statement is running.
STATEMENT_ID INTEGER

A unique numeric ID assigned by the Vertica catalog, which identifies the currently-executing statement.

A value of NULL indicates that no statement is currently being processed.

LAST_STATEMENT_DURATION_US INTEGER The duration of the last completed statement in microseconds.
RUNTIME_PRIORITY VARCHAR Specifies how many run-time resources (CPU, I/O bandwidth) are allocated to queries that are running in the resource pool.
CURRENT_STATEMENT VARCHAR The currently executing statement, if any. NULL indicates that no statement is currently being processed.
LAST_STATEMENT VARCHAR NULL if the user has just logged in; otherwise the currently running statement or the most recently completed statement.
SSL_STATE VARCHAR

Indicates if Vertica used Secure Socket Layer (SSL) for a particular session. Possible values are:

  • None—Vertica did not use SSL.

  • Server—Server authentication was used, so the client could authenticate the server.

  • Mutual—Both the server and the client authenticated one another through mutual authentication.

See Security and authentication and TLS protocol.

AUTHENTICATION_ METHOD VARCHAR

The type of client authentication used for a particular session, if known. Possible values are:

  • Unknown

  • Trust

  • Reject

  • Hash

  • Ident

  • LDAP

  • GSS

  • TLS

See Security and authentication and Configuring client authentication.

CLIENT_TYPE VARCHAR

The type of client from which the connection was made. Possible client type values:

  • ADO.NET Driver

  • ODBC Driver

  • JDBC Driver

  • vsql

CLIENT_VERSION VARCHAR Client version.
CLIENT_OS VARCHAR Client operating system.
CLIENT_OS_USER _NAME VARCHAR The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.
CLIENT_AUTHENTICATION_NAME VARCHAR User-assigned name of the authentication method.
CLIENT_ AUTHENTICATION INTEGER Object identifier of the client authentication method.
REQUESTED_PROTOCOL INTEGER The requested Vertica client server protocol to be used when connecting.
EFFECTIVE_PROTOCOL INTEGER The requested Vertica client server protocol used when connecting.
EXTERNAL_MEMORY_KB INTEGER Amount of memory consumed by the Java Virtual Machines associated with the session.

Privileges

A superuser has unrestricted access to all session information. Users can view information only about their own, current sessions.

See also

97 - SPREAD_STATE

Lists daemon settings for all nodes in the cluster.

Lists Spread daemon settings for all nodes in the cluster.

Column Name Data Type Description
NODE_NAME VARCHAR Which node the settings are for.
TOKEN_TIMEOUT INTEGER The timeout period in milliseconds before spread considers a node to be down due to lack of response to a message.

Examples

=> SELECT * FROM V_MONITOR.SPREAD_STATE;
    node_name     | token_timeout
------------------+---------------
 v_vmart_node0003 |          8000
 v_vmart_node0001 |          8000
 v_vmart_node0002 |          8000
(3 rows)

See also

98 - STORAGE_BUNDLE_INFO_STATISTICS

Indicates which projections have storage containers with invalid bundle metadata in the database catalog.

Indicates which projections have storage containers with invalid bundle metadata in the database catalog. If any ROS or DV container has invalid bundle metadata fields, Vertica increments the corresponding column (ros_without_bundle_info_count or dv_ros_without_bundle_info_count) by one.

To update the catalog with valid bundle metadata, call UPDATE_STORAGE_CATALOG, as an argument to Vertica meta-function DO_TM_TASK. For details, see Writing bundle metadata to the catalog.

Column Name Data Type Description
node_name VARCHAR Name of this projection's node
projection_oid INTEGER Projection's unique catalog identifier
projection_name VARCHAR Projection name
projection_schema VARCHAR Projection schema name
total_ros_count INTEGER Total number of ROS containers for this projection
ros_without_bundle_info_count INTEGER Number of ROS containers for this projection with invalid bundle metadata
total_dv_ros_count INTEGER Total number of DV (delete vector) containers for this projection
dv_ros_without_bundle_info_count INTEGER Number of DV containers for this projection with invalid bundle metadata

99 - STORAGE_CONTAINERS

Monitors information about Vertica storage containers.

Monitors information about Vertica storage containers.

Column Name Data Type Description
NODE_NAME* VARCHAR Node name for which information is listed.
SCHEMA_NAME* VARCHAR Schema name for which information is listed.
PROJECTION_ID* INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
PROJECTION_NAME* VARCHAR Projection name for which information is listed on that node.
STORAGE_OID* INTEGER Numeric ID assigned by the Vertica catalog, which identifies the storage. The same OID can appear on more than one node.
SAL_STORAGE_ID VARCHAR Unique hexadecimal numeric ID assigned by the Vertica catalog, which identifies the storage.
TOTAL_ROW_COUNT* VARCHAR

Total rows in the storage container listed for that projection.

If the database has been re-sharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.

DELETED_ROW_COUNT* INTEGER

Total rows in the storage container deleted for that projection.

If the database has been re-sharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.

USED_BYTES* INTEGER

Number of bytes in the storage container used to store the compressed projection data. This value should not be compared to the output of the AUDIT function, which returns the raw data size of database objects.

If the database has been re-sharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.

START_EPOCH* INTEGER Number of the start epoch in the storage container for which information is listed.
END_EPOCH* INTEGER Number of the end epoch in the storage container for which information is listed.
GROUPING VARCHAR

The group by which columns are stored:

  • ALL: All columns are grouped

  • PROJECTION: Columns grouped according to projection definition

  • NONE: No columns grouped, despite grouping in the projection definition

  • OTHER: Some grouping but neither all nor according to projection (e.g., results from add column)

SEGMENT_LOWER_BOUND INTEGER Lower bound of the segment range spanned by the storage container or NULL if the corresponding projection is not elastic.
SEGMENT_UPPER_BOUND INTEGER Upper bound of the segment range spanned by the storage container or NULL if the corresponding projection is not elastic.
LOCATION_LABEL VARCHAR (128) The location label (if any) for the storage container is stored.
DELETE_VECTOR_COUNT INTEGER

The number of delete vectors in the storage container.

If the database has been re-sharded, this value will be inaccurate until the Tuple mover realigns the storage containers to the new shard layout.

SHARD_ID INTEGER Set only for an Eon Mode database, ID of the shard that this container belongs to.
SHARD_NAME VARCHAR(128) Set only for an Eon Mode database, name of the shard that this container belongs to.
ORIGINAL_SEGMENT_LOWER_BOUND INTEGER The lower bound of a storage container before database re-sharding. This value is set only if the database has been re-sharded and the storage containers have not been realigned with current shard definitions. For details, see RESHARD_DATABASE.
ORIGINAL_SEGMENT_UPPER_BOUND INTEGER The upper bound of a storage container before database re-sharding. This value is set only if the database has been re-sharded and the storage container has not been realigned with current shard definitions. For details, see RESHARD_DATABASE.

* Column values cached for faster query performance

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

The following query identifies any storage containers that have not yet been realigned to the new shard segmentation bounds after running RESHARD_DATABASE:

=> SELECT COUNT(*) FROM storage_containers WHERE original_segment_lower_bound IS NOT NULL AND original_segment_upper_bound IS NOT NULL;

100 - STORAGE_POLICIES

Monitors the current storage policies in effect for one or more database objects.

Monitors the current storage policies in effect for one or more database objects.

Column Name Data Type Description
SCHEMA_NAME VARCHAR Schema name for which information is listed.
OBJECT_NAME VARCHAR The name of the database object associated through the storage policy.
POLICY_DETAILS VARCHAR The object type of the storage policy.
LOCATION_LABEL VARCHAR (128) The label for this storage location.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

101 - STORAGE_TIERS

Provides information about all storage locations with the same label across all cluster nodes.

Provides information about all storage locations with the same label across all cluster nodes. This table lists data totals for all same-name labeled locations.

The system table shows what labeled locations exist on the cluster, as well as other cluster-wide data about the locations.

Column Name Data Type Description
LOCATION_LABEL VARCHAR The label associated with a specific storage location. The storage_tiers system table includes data totals for unlabeled locations, which are considered labeled with empty strings ('').
NODE_COUNT INTEGER The total number of nodes that include a storage location named location_label.
LOCATION_COUNT INTEGER

The total number of storage locations named location_label.

This value can differ from node_count if you create labeled locations with the same name at different paths on different nodes. For example:

v_vmart_node0001: Create one labeled location, FAST

V_vmart_node0002: Create two labeled locations, FAST, at different directory paths

In this case, node_count value = 2, while location_count value = 3.

ROS_CONTAINER_COUNT INTEGER The total number of ROS containers stored across all cluster nodes for location_label.
TOTAL_OCCUPIED_SIZE INTEGER The total number of bytes that all ROS containers for location_label occupy across all cluster nodes.

Privileges

None

See also

102 - STORAGE_USAGE

Provides information about file system storage usage.

Provides information about file system storage usage. This is useful for determining disk space usage trends.

Column Name Data Type Description
POLL_TIMESTAMP TIMESTAMPTZ Time when Vertica recorded the row.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
PATH VARCHAR Path where the storage location is mounted.
DEVICE VARCHAR Device on which the storage location is mounted.
FILESYSTEM VARCHAR File system on which the storage location is mounted.
USED_BYTES INTEGER Counter history of number of used bytes.
FREE_BYTES INTEGER Counter history of number of free bytes.
USAGE_PERCENT FLOAT Percent of storage in use.

Privileges

Superuser

See also

103 - STRATA

Contains internal details of how the combines ROS containers in each projection, broken down by stratum and classifies the ROS containers by size and partition.

Contains internal details of how the Tuple Mover combines ROS containers in each projection, broken down by stratum and classifies the ROS containers by size and partition. The related STRATA_STRUCTURES table provides a summary of the strata values.

Mergeout describes how the Tuple Mover combines ROS containers.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
SCHEMA_NAME VARCHAR The schema name for which information is listed.
PROJECTION_ID INTEGER Catalog-assigned numeric value that uniquely identifies the projection.
PROJECTION_NAME VARCHAR The projection name for which information is listed on that node.
STRATUM_KEY VARCHAR References the partition or partition group for which information is listed.
STRATA_COUNT INTEGER The total number of strata for this projection partition.
MERGING_STRATA_COUNT INTEGER The number of strata the Tuple Mover can merge out.
STRATUM_CAPACITY INTEGER The maximum number of ROS containers for the stratum before they must be merged.
STRATUM_HEIGHT FLOAT The size ratio between the smallest and largest ROS container in this stratum.
STRATUM_NO INTEGER The stratum number. Strata are numbered starting at 0, for the stratum containing the smallest ROS containers.
STRATUM_LOWER_SIZE VARCHAR The smallest ROS container size allowed in this stratum.
STRATUM_UPPER_SIZE VARCHAR The largest ROS container size allowed in this stratum.
ROS_CONTAINER_COUNT INTEGER The current number of ROS containers in the projection partition.

104 - STRATA_STRUCTURES

This table provides an overview of internal details.

This table provides an overview of Tuple Mover internal details. It summarizes how the ROS containers are classified by size. A more detailed view can be found in the STRATA virtual table.

Column Name Data Type Description
NODE_NAME VARCHAR The node name for which information is listed.
SCHEMA_NAME VARCHAR The schema name for which information is listed.
PROJECTION_NAME VARCHAR The projection name for which information is listed on that node.
PROJECTION_ID INTEGER Catalog-assigned numeric value that uniquely identifies the projection.
STRATUM_KEY VARCHAR References the partition or partition group for which information is listed.
STRATA_COUNT INTEGER The total number of strata for this projection partition.
MERGING_STRATA_COUNT INTEGER In certain hardware configurations, a high strata could contain more ROS containers than the Tuple Mover can merge out; output from this column denotes the number of strata the Tuple Mover can merge out.
STRATUM_CAPACITY INTEGER The maximum number of ROS containers that the strata can contained before it must merge them.
STRATUM_HEIGHT FLOAT The size ratio between the smallest and largest ROS container in a stratum.
ACTIVE_STRATA_COUNT INTEGER The total number of strata that have ROS containers in them.

Examples


=> \pset expanded
Expanded display is on.
=> SELECT node_name, schema_name, projection_name, strata_count,
            stratum_capacity, stratum_height, active_strata_count
            FROM strata_structures WHERE stratum_capacity > 60;
-[ RECORD 1 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0001
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 2 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0001
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 3 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0002
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 4 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0002
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 5 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0003
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 6 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0003
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 7 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0004
schema_name         | public
projection_name     | shipping_dimension_DBD_22_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1
-[ RECORD 8 ]-------+--------------------------------------------------------
node_name           | v_vmartdb_node0004
schema_name         | public
projection_name     | shipping_dimension_DBD_23_seg_vmart_design_vmart_design
strata_count        | 4
stratum_capacity    | 62
stratum_height      | 25.6511590887058
active_strata_count | 1

105 - SYSTEM

Monitors the overall state of the database.

Monitors the overall state of the database.

Column Name Data Type Description
CURRENT_EPOCH INTEGER The current epoch number.
AHM_EPOCH INTEGER The AHM epoch number.
LAST_GOOD_EPOCH INTEGER The smallest (min) of all the checkpoint epochs on the cluster.
REFRESH_EPOCH INTEGER Deprecated, always set to -1.
DESIGNED_FAULT_TOLERANCE INTEGER The designed or intended K-safety level.
NODE_COUNT INTEGER The number of nodes in the cluster.
NODE_DOWN_COUNT INTEGER The number of nodes in the cluster that are currently down.
CURRENT_FAULT_TOLERANCE INTEGER

The number of node failures the cluster can tolerate before it shuts down automatically.

This is the current K-safety level.

CATALOG_REVISION_NUMBER INTEGER The catalog version number.
ROS_USED_BYTES INTEGER The ROS size in bytes (cluster-wide).
ROS_ROW_COUNT INTEGER The number of rows in ROS (cluster-wide).
TOTAL_USED_BYTES INTEGER The total storage in bytes across the database cluster.
TOTAL_ROW_COUNT INTEGER The total number of rows across the database cluster.

106 - SYSTEM_RESOURCE_USAGE

Provides history about system resources, such as memory, CPU, network, disk, I/O.

Provides history about system resources, such as memory, CPU, network, disk, I/O.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
END_TIME TIMESTAMP End time of the history interval.
AVERAGE_MEMORY_USAGE_PERCENT FLOAT Average memory usage in percent of total memory (0-100) during the history interval.
AVERAGE_CPU_USAGE_PERCENT FLOAT Average CPU usage in percent of total CPU time (0-100) during the history interval.
NET_RX_KBYTES_PER_SECOND FLOAT Average number of kilobytes received from network (incoming) per second during the history interval.
NET_TX_KBYTES_PER_SECOND FLOAT Average number of kilobytes transmitting to network (outgoing) per second during the history interval.
IO_READ_KBYTES_PER_SECOND FLOAT Disk I/O average number of kilobytes read from disk per second during the history interval.
IO_WRITTEN_KBYTES_PER_SECOND FLOAT Average number of kilobytes written to disk per second during the history interval.

Privileges

Superuser

107 - SYSTEM_SERVICES

Provides information about background system services that Workload Analyzer monitors.

Provides information about background system services that Workload Analyzer monitors.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
SERVICE_TYPE VARCHAR

Type of service; can be one of:

  • SYSTEM

  • TUPLE MOVER

SERVICE_GROUP VARCHAR Group name, if there are multiple services of the same type.
SERVICE_NAME VARCHAR Name of the service.
SERVICE_INTERVAL_SEC INTEGER How often the service is executed (in seconds) during the history interval.
IS_ENABLED BOOLEAN Denotes if the service is enabled.
LAST_RUN_START TIMESTAMPTZ Denotes when the service was started last time.
LAST_RUN_END TIMESTAMPTZ Denotes when the service was completed last time.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

108 - SYSTEM_SESSIONS

Provides information about system internal session history by system task.

Provides information about system internal session history by system task.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_NAME VARCHAR Name of the user at the time Vertica recorded the session.
SESSION_ID INTEGER Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
STATEMENT_ID VARCHAR Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session.
SESSION_TYPE VARCHAR

Session type, one of:

  • CLIENT

  • DBD

  • MERGEOUT

  • REBALANCE_CLUSTER

  • RECOVERY

  • REFRESH

  • TIMER_SERVICE

  • CONNECTION

  • SUBSESSION

  • REPARTITION_TABLE

  • LICENSE_AUDIT

  • STARTUP

  • SHUTDOWN

  • VSPREAD

RUNTIME_PRIORITY VARCHAR Specifies how many run-time resources (CPU, I/O bandwidth) are allocated to queries that are running in the resource pool.
DESCRIPTION VARCHAR Transaction description in this session.
SESSION_START_TIMESTAMP TIMESTAMPTZ Value of session at beginning of history interval.
SESSION_END_TIMESTAMP TIMESTAMPTZ Value of session at end of history interval.
IS_ACTIVE BOOLEAN Denotes if the session is still running.
SESSION_DURATION_MS INTEGER Duration of the session in milliseconds.
CLIENT_TYPE VARCHAR Columns not used in SYSTEM_SESSIONS system table. To view values for these columns, see the V_MONITOR schema system tables SESSIONS, USER_SESSIONS, CURRENT_SESSION, and SESSION_PROFILES.
CLIENT_VERSION VARCHAR
CLIENT_OS VARCHAR
CLIENT_OS_USER_NAME VARCHAR The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.

Privileges

Superuser

109 - TABLE_RECOVERIES

Provides detailed information about recovered and recovering tables during a recovery by table.

Provides detailed information about recovered and recovering tables during a recovery by table.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is performing the recovery.
TABLE_NAME VARCHAR The name of the table being recovered.
TABLE_OID INTEGER The object ID of the table being recovered.
STATUS VARCHAR

The status of the table. Tables can have the following status:

  • recovered: The table is fully recovered

  • recovering: The table is in the process of recovery

  • error_retry: Vertica has attempted to recover the table, but the recovery failed.

Tables that have not yet begun the recovery process do not have a status.

PHASE VARCHAR The phase of the recovery.
THREAD_ID VARCHAR The ID of the thread that performed the recovery.
START_TIME TIMESTAMPTZ The date and time that the table began recovery.
END_TIME TIMESTAMPTZ The date and time that the table completed recovery.
RECOVER_PRIORITY INTEGER The recovery priority of the table being recovered.
RECOVER_ERROR VARCHAR Error that caused the recovery to fail.
IS_HISTORICAL BOOLEAN If f, the record contains recovery information for the current process.

Privileges

None

Examples

=> SELECT * FROM TABLE_RECOVERIES;
-[RECORD 1]----------------------------------
node_name        | node04
table_oid        | 45035996273708000
table_name       | public.t
status           | recovered
phase            | current replay delete
thread_id        | 7f7a817fd700
start_time       | 2017-12-13 08:47:28.825085-05
end_time         | 2017-12-13 08:47:29.216571-05
recover_priority | -9223372036854775807
recover_error    | Event apply failed
is_historical    | t
-[RECORD 2]--------------------------------------
node_name        | v_test_parquet_ha_node0011
table_oid        | 45035996273937680
table_name       | public.t2_impala230_uncompre_multi_file_libhdfs_1
status           | error-retry
phase            | historical
thread_id        | 7f89a574f700
start_time       | 2018-02-24 11:30:59.008831-05
end_time         | 2018-02-24 11:33:09.780798-05
recover_priority | -9223372036854775807
recover_error    | Could not stop all dirty transactions[txnId = 45035996273718426; ]
is_historical    | t

110 - TABLE_RECOVERY_STATUS

Provides node recovery information during a Recovery By Table.

Provides node recovery information during a Recovery By Table.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is performing the recovery.
NODE_RECOVERY_START_TIME TIMESTAMPTZ The timestamp for when the node began recovering.
RECOVER_EPOCH INTEGER The epoch that the recovery operation is trying to recover to.
RECOVERING_TABLE_NAME VARCHAR The name of the table currently recovering.
TABLES_REMAIN INTEGER The total number of tables on the node.
IS_RUNNING BOOLEAN Indicates if the recovery process is still running.

Privileges

None

Examples

=> SELECT * FROM TABLE_RECOVERY_STATUS;
-[ RECORD 1 ]------------+-----------------
node_name                | v_vmart_node0001
node_recovery_start_time |
recover_epoch            |
recovering_table_name    |
tables_remain            | 0
is_running               | f
-[ RECORD 2 ]------------+-----------------
node_name                | v_vmart_node0002
node_recovery_start_time |
recover_epoch            |
recovering_table_name    |
tables_remain            | 0
is_running               | f
-[ RECORD 3 ]------------+-----------------
node_name                | v_vmart_node0003
node_recovery_start_time | 2017-12-13 08:47:28.282377-05
recover_epoch            | 23
recovering_table_name    | user_table
tables_remain            | 5
is_running               | y

111 - TABLE_STATISTICS

Displays statistics that have been collected for tables and their respective partitions.

Displays statistics that have been collected for tables and their respective partitions.

Column Name Data Type Description
LOGICAL_STATS_OID INTEGER Uniquely identifies a collection of statistics for a given table.
TABLE_NAME VARCHAR Name of an existing database table.
MIN_PARTITION_KEY,
MAX_PARTITION_KEY
 VARCHAR Statistics for a range of partition keys collected by ANALYZE_STATISTICS_PARTITION, empty if statistics were collected by ANALYZE_STATISTICS.
ROW_COUNT INTEGER The number of rows analyzed for each statistics collection.
STAT_COLLECTION_TIME TIMESTAMPTZ The timestamp of each statistics collection.

112 - TLS_CONFIGURATIONS

Lists settings for TLS Configuration objects for the server, LDAP, etc.

Lists settings for TLS Configuration objects for the server, LDAP, etc.

Column Name Data Type Description
NAME VARCHAR

Name of the TLS Configuration. Vertica includes the following TLS Configurations by default:

  • server

  • LDAPLink

  • LDAPAuth

  • data_channel

OWNER VARCHAR Owner of the TLS Configuration object.
CERTIFICATE VARCHAR The certificate associated with the TLS Configuration object.
CA_CERTIFICATES VARCHAR

The CA certificate(s) used to verify client certificates.

In cases where a TLS Configuration uses more than one CA, each CA will have its own row in the table.

CIPHER_SUITES VARCHAR The cipher suites to used to secure the connection.
MODE VARCHAR

How Vertica establishes TLS connections with another host, one of the following, in order of ascending security:

  • DISABLE: Disables TLS. All other options for this parameter enable TLS.

  • ENABLE: Enables TLS. Vertica does not check client certificates.

  • TRY_VERIFY: Establishes a TLS connection if one of the following is true:

    • the other host presents a valid certificate

    • the other host doesn't present a certificate

    If the other host presents an invalid certificate, the connection will use plaintext.

  • VERIFY_CA: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA. If the other host does not present a certificate, the connection uses plaintext.

  • VERIFY_FULL: Connection succeeds if Vertica verifies that the other host's certificate is from a trusted CA and the certificate's cn (Common Name) or subjectAltName attribute matches the hostname or IP address of the other host.

    Note that for client certificates, cn is used for the username, so subjectAltName must match the hostname or IP address of the other host.

VERIFY_FULL is unsupported for client-server TLS (the connection type handled by ServerTLSConfig) and behaves as VERIFY_CA.

Examples

In this example, the LDAPAuth TLS Configuration uses two CA certificates:

=> SELECT * FROM tls_configurations WHERE name='LDAPAuth';
    name   |  owner  | certificate | ca_certificate | cipher_suites |  mode
----------+---------+-------------+----------------+---------------+---------
 LDAPAuth | dbadmin | server_cert | ca             |               | DISABLE
 LDAPAuth | dbadmin | server_cert | ica            |               | DISABLE
(2 rows)

To make more clear the relationship between a TLS Configuration and its CA certificates, you can format the query with LISTAGG:

=> SELECT name, owner, certificate, LISTAGG(ca_certificate) AS ca_certificates, cipher_suites, mode
FROM tls_configurations
WHERE name='LDAPAuth'
GROUP BY name, owner, certificate, cipher_suites, mode
ORDER BY 1;
   name   |  owner  | certificate | ca_certificates | cipher_suites |  mode
----------+---------+-------------+-----------------+---------------+---------
 LDAPAuth | dbadmin | server_cert | ca,ica          |               | DISABLE
(1 row)

113 - TRANSACTIONS

Records the details of each transaction.

Records the details of each transaction.

Column Name Data Type Description
START_TIMESTAMP TIMESTAMPTZ Beginning of history interval.
END_TIMESTAMP TIMESTAMPTZ End of history interval.
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the user.
USER_NAME VARCHAR Name of the user for which transaction information is listed.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any; otherwise NULL.
DESCRIPTION VARCHAR Textual description of the transaction.
START_EPOCH INTEGER Number of the start epoch for the transaction.
END_EPOCH INTEGER Number of the end epoch for the transaction
NUMBER_OF_STATEMENTS INTEGER Number of query statements executed in this transaction.
ISOLATION VARCHAR Denotes the transaction mode as "READ COMMITTED" or "SERIALIZABLE".
IS_READ_ONLY BOOLEAN Denotes "READ ONLY" transaction mode.
IS_COMMITTED BOOLEAN Determines if the transaction was committed. False means ROLLBACK.
IS_LOCAL BOOLEAN Denotes transaction is local (non-distributed).
IS_INITIATOR BOOLEAN Denotes if the transaction occurred on this node (t).
IS_DDL BOOLEAN Distinguishes between a DDL transaction (t) and non-DDL transaction (f).

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also

Transactions

114 - TRUNCATED_SCHEMATA

Lists the original names of restored schemas that were truncated due to name lengths exceeding 128 characters.

Lists the original names of restored schemas that were truncated due to name lengths exceeding 128 characters.

Column Name Data Type Description
RESTORE_TIME TIMESTAMPTZ The time that the table was restored.
SESSION_ID VARCHAR Identifier for the restoring session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INTEGER Identifier of the user for the restore event.
USER_NAME VARCHAR Name of the user for which Vertica lists restore information at the time it recorded the session.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any; otherwise NULL.
ORIGINAL_SCHEMA_NAME VARCHAR The original name of the schema prior to the restore.
NEW_SCHEMA_NAME VARCHAR The name of the schema after it was truncated.

Privileges

None

115 - TUNING_RECOMMENDATIONS

Returns tuning recommendation results from the last call to ANALYZE_WORKLOAD.

Returns tuning recommendation results from the last call to ANALYZE_WORKLOAD. This information is useful for building filters on the Workload Analyzer result set.

Column Name Data Type Description
OBSERVATION_COUNT INTEGER Integer for the total number of events observed for this tuning recommendation. For example, if you see a return value of 1, Workload Analyzer is making its first tuning recommendation for the event in 'scope'.
FIRST_OBSERVATION_TIME TIMESTAMPTZ Timestamp when the event first occurred. If this column returns a null value, the tuning recommendation is from the current status of the system instead of from any prior event.
LAST_OBSERVATION_TIME TIMESTAMPTZ Timestamp when the event last occurred. If this column returns a null value, the tuning recommendation is from the current status of the system instead of from any prior event.
TUNING_PARAMETER VARCHAR

Objects on which to perform a tuning action. For example, a return value of:

  • public.t informs the DBA to run Database Designer on table t in the public schema

  • bsmith notifies a DBA to set a password for user bsmith

TUNING_DESCRIPTION VARCHAR

Textual description of the tuning recommendation to perform on the tuning_parameter object. For example:

  • Run database designer on table schema.table

  • Create replicated projection for table schema.table

  • Consider incremental design on query

  • Re-segment projection projection-name on high-cardinality column(s)

  • Drop the projection projection-name

  • Alter a table's partition expression

  • Reorganize data in partitioned table

  • Decrease the MoveOutInterval configuration parameter setting

TUNING_COMMAND VARCHAR

Command string if tuning action is a SQL command. For example:

Update statistics on a particular schema's table.column:

SELECT ANALYZE_STATISTICS('public.table.column');

Resolve mismatched configuration parameter LockTimeout:

SELECT * FROM CONFIGURATION_PARAMETERS WHERE parameter_name = 'LockTimeout';

Set the password for user bsmith:

ALTER USER (bsmith) IDENTIFIED BY ('new_password');

TUNING_COST VARCHAR

Cost is based on the type of tuning recommendation and is one of:

  • LOW: minimal impact on resources from running the tuning command

  • MEDIUM: moderate impact on resources from running the tuning command

  • HIGH: maximum impact on resources from running the tuning command

Depending on the size of your database or table, consider running high-cost operations after hours instead of during peak load times.

Privileges

Superuser

Examples

See ANALYZE_WORKLOAD.

See also

116 - TUPLE_MOVER_OPERATIONS

Monitors the status of operations on each node.

Monitors the status of Tuple Mover operations on each node.

Column Name Data Type Description
OPERATION_START_TIMESTAMP TIMESTAMP Start time of a Tuple Mover operation.
NODE_NAME VARCHAR Node name for which information is listed.
OPERATION_NAME VARCHAR

One of the following:

  • Analyze Statistics

  • DVMergeout

  • Mergeout

  • Partitioning

  • Rebalance

  • Recovery Replay Delete

OPERATION_STATUS VARCHAR

Returns the status of each operation, one of the following:

  • Empty string: not running

  • Start

  • Running

  • Complete

  • Update

  • Abort

  • Change plan type to Replay Delete

TABLE_SCHEMA VARCHAR Schema name for the specified projection.
TABLE_NAME VARCHAR Table name for the specified projection.
PROJECTION_NAME VARCHAR Name of the projection being processed.
PROJECTION_ID INTEGER Unique numeric ID assigned by the Vertica catalog, which identifies the projection.
COLUMN_ID INTEGER Identifier for the column for the associated projection being processed.
EARLIEST_CONTAINER_START_EPOCH INTEGER Populated for mergeout and purge operations only. For an automatically-invoked mergeout, for example, the returned value represents the lowest epoch of containers involved in the mergeout.
LATEST_CONTAINER_END_EPOCH INTEGER Populated for mergeout and purge_partitions operations. For an automatically-invoked mergeout, for example, the returned value represents the highest epoch of containers involved in the mergeout.
ROS_COUNT INTEGER Number of ROS containers.
TOTAL_ROS_USED_BYTES INTEGER Size in bytes of all ROS containers in the mergeout operation. (Not applicable for other operations.)
PLAN_TYPE VARCHAR

One of the following:

  • Analyze Statistics

  • DVMergeout

  • Mergeout

  • Partitioning

  • Rebalance

  • Recovery Replay Delete

  • Replay Delete

SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
TRANSACTION_ID INTEGER Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
IS_EXECUTING BOOLEAN Distinguishes between actively-running (t) and completed (f) tuple mover operations.
RUNTIME_PRIORITY VARCHAR

Determines how many run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to running queries in the resource pool, one of the following:

  • HIGH

  • MEDIUM

  • LOW

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

Examples

=> SELECT node_name, operation_status, projection_name, plan_type
    FROM TUPLE_MOVER_OPERATIONS;
 node_name         | operation_status | projection_name  | plan_type
-------------------+------------------+------------------+-----------
 v_vmart_node0001  | Running          | p1_b2            | Mergeout
 v_vmart_node0002  | Running          | p1               | Mergeout
 v_vmart_node0001  | Running          | p1_b2            | Replay Delete
 v_vmart_node0001  | Running          | p1_b2            | Mergeout
 v_vmart_node0002  | Running          | p1_b2            | Mergeout
 v_vmart_node0001  | Running          | p1_b2            | Replay Delete
 v_vmart_node0002  | Running          | p1               | Mergeout
 v_vmart_node0003  | Running          | p1_b2            | Replay Delete
 v_vmart_node0001  | Running          | p1               | Mergeout
 v_vmart_node0002  | Running          | p1_b1            | Mergeout

See also

117 - UDFS_EVENTS

Records information about events involving the S3, HDFS, GCS, and Azure file systems.

Records information about events involving the S3, HDFS, GCS, and Azure file systems.

Column Name Data Type Description
START_TIME TIMESTAMPTZ Event start time
END_TIME TIMESTAMPTZ Event end time
NODE_NAME VARCHAR Name of the node that reported the event
SESSION_ID VARCHAR Identifies the event session, unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INTEGER Identifies the event user.
TRANSACTION_ID* INTEGER Identifies the event transaction within the SESSION_ID-specified session, if any; otherwise NULL.
STATEMENT_ID* INTEGER Uniquely identifies the current statement, if any; otherwise NULL.
REQUEST_ID* INTEGER Uniquely identifies the event request in the user session.
FILESYSTEM VARCHAR Name of the file system, such as S3
PATH VARCHAR Complete file path
EVENT VARCHAR

The function call that was made. For example:

virtual size_t SAL::S3FileOperator::read(void*, size_t)

STATUS VARCHAR Status of the event: OK, CANCEL, or FAIL
DESCRIPTION VARCHAR Other event details, for internal use only
ACTIVITY VARCHAR Points to the component that was active and logged the event, for internal use only.
PLAN_ID VARCHAR Uniquely identifies the node-specific Optimizer plan for this event.
OPERATOR_ID INTEGER Identifier assigned by the Execution Engine operator instance that performs the work

* In combination, TRANSACTION_ID, STATEMENT_ID, and REQUEST_ID uniquely identify an event within a given session.

Privileges

Superuser

118 - UDFS_OPS_PER_HOUR

This table summarizes the S3 file system statistics for each hour.

This table summarizes the S3 file system statistics for each hour.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node the file system is on.
FILESYSTEM VARCHAR Name of the file system, such as S3.
START_TIME TIMESTAMP Start time for statistics gathering.
END_TIME TIMESTAMP Stop time for statistics gathering.
AVG_OPERATIONS_PER_SECOND INTEGER Average number of operations per second during the specified hour.
AVG_ERRORS_PER_SECOND INTEGER Average number of errors per second during the specified hour.
RETRIES INTEGER Number of retry events during the specified hour.
METADATA_READS INTEGER Number of requests to write metadata during the specified hour. For example, S3 POST and DELETE requests are metadata writes.
METADATA_WRITES INTEGER Number of requests to write metadata during the specified hour. For example, S3 POST and DELETE requests are metadata writes.
DATA_READS INTEGER Number of read operations, such as S3 GET requests to download files, during the specified hour.
DATA_WRITES INTEGER Number of write operations, such as S3 PUT requests to upload files, during the specified hour.
UPSTREAM_BYTES INTEGER Number of bytes received during the specified hour.
DOWNSTREAM_BYTES INTEGER Number of bytes sent during the specified hour.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM UDFS_OPS_PER_HOUR;
-[ RECORD 1 ]-------------+--------------------
node_name                 | e1
filesystem                | S3
start_time                | 2018-04-06 04:00:00
end_time                  | 2018-04-06 04:00:00
avg_operations_per_second | 0
avg_errors_per_second     | 0
retries                   | 0
metadata_reads            | 0
metadata_writes           | 0
data_reads                | 0
data_writes               | 0
upstream_bytes            | 0
downstream_bytes          | 0
...

See also

UDFS_OPS_PER_MINUTE

119 - UDFS_OPS_PER_MINUTE

This table summarizes the S3 file system statistics for each minute.

This table summarizes the S3 file system statistics for each minute.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node the file system is on.
FILESYSTEM VARCHAR Name of the file system, such as S3.
START_TIME TIMESTAMP Start time for statistics gathering.
END_TIME TIMESTAMP Stop time for statistics gathering.
AVG_OPERATIONS_PER_SECOND INTEGER Average number of operations per second during the specified minute.
AVG_ERRORS_PER_SECOND INTEGER Average number of errors per second during the specified minute.
RETRIES INTEGER Number of retry events during the specified minute.
METADATA_READS INTEGER Number of requests to write metadata during the specified minute. For example, S3 POST and DELETE requests are metadata writes.
METADATA_WRITES INTEGER Number of requests to write metadata during the specified minute. For example, S3 POST and DELETE requests are metadata writes.
DATA_READS INTEGER Number of read operations, such as S3 GET requests to download files, during the specified minute.
DATA_WRITES INTEGER Number of write operations, such as S3 PUT requests to upload files, during the specified minute.
UPSTREAM_BYTES INTEGER Number of bytes received during the specified minute.
DOWNSTREAM_BYTES INTEGER Number of bytes sent during the specified minute.

Examples

=> \x
Expanded display is on.
=> SELECT * FROM UDFS_OPS_PER_MINUTE;
-[ RECORD 1 ]-------------+--------------------
node_name                 | e1
filesystem                | S3
start_time                | 2018-04-06 04:17:00
end_time                  | 2018-04-06 04:18:00
avg_operations_per_second | 0
avg_errors_per_second     | 0
retries                   | 0
metadata_reads            | 0
metadata_writes           | 0
data_reads                | 0
data_writes               | 0
upstream_bytes            | 0
downstream_bytes          | 0
...

See also

UDFS_OPS_PER_HOUR

120 - UDFS_STATISTICS

Records aggregate information about file-system and object-store operations.

Records aggregate information about file-system and object-store operations. For access through LibHDFS++, the table records information about metadata but not data.

An operation can be made up of many individual read, write, or retry requests. SUCCESSFUL_OPERATIONS and FAILED_OPERATIONS count operations; the other counters count individual requests. When an operation finishes, one of the OPERATIONS counters is incremented once, but several other counters could be incremented several times each.

Column Name Data Type Description
FILESYSTEM VARCHAR Name of the file system, such as S3 or Libhdfs++.
SUCCESSFUL_OPERATIONS INTEGER Number of successful file-system operations.
FAILED_OPERATIONS INTEGER Number of failed file-system operations.
RETRIES INTEGER Number of retry events.
METADATA_READS INTEGER Number of requests to read metadata. For example, S3 list bucket and HEAD requests are metadata reads.
METADATA_WRITES INTEGER Number of requests to write metadata. For example, S3 POST and DELETE requests are metadata writes.
DATA_READS INTEGER Number of read operations, such as S3 GET requests to download files.
DATA_WRITES INTEGER Number of write operations, such as S3 PUT requests to upload files.
DOWNSTREAM_BYTES INTEGER Number of bytes received.
UPSTREAM_BYTES INTEGER Number of bytes sent.
OPEN_FILES INTEGER Number of files that are currently open.
MAPPED_FILES INTEGER Number of currently-mapped files on S3 file systems. This value shows the number of streaming connections for reading data from S3. This value will be 0 for non-S3 file systems.
READING INTEGER The number of currently-running read operations.
WRITING INTEGER The number of currently-running writer operations.

Examples

The following query gets the total number of metadata RPCs for Libhdfs++ operations:

=> SELECT SUM(metadata_reads) FROM UDFS_STATISTICS WHERE filesystem = 'Libhdfs++';

121 - UDX_EVENTS

Records information about events raised from the execution of user-defined extensions.

Records information about events raised from the execution of user-defined extensions.

A UDx populates the __RAW__ column using ServerInterface::logEvent() (C++ only). VMap support is provided by Flex Tables, which must not be disabled.

Column Name Data Type Description
REPORT_TIME TIMESTAMPTZ Time the event occurred.
NODE_NAME VARCHAR Name of the node that reported the event
SESSION_ID VARCHAR Identifies the event session, unique within the cluster at any point in time but can be reused when the session closes.
USER_ID INTEGER Identifies the user running the UDx.
USER_NAME VARCHAR Identifies the user running the UDx.
TRANSACTION_ID* INTEGER Identifies the event transaction within the SESSION_ID-specified session, if any; otherwise NULL.
STATEMENT_ID* INTEGER Uniquely identifies the current statement, if any; otherwise NULL.
REQUEST_ID* INTEGER Uniquely identifies the event request in the user session.
UDX_NAME VARCHAR Name of the UDx, as specified in the corresponding CREATE FUNCTION statement.
RAW VARBINARY VMap containing UDx-specific values.

122 - UDX_FENCED_PROCESSES

Provides information about processes Vertica uses to run user-defined extensions in fenced mode.

Provides information about processes Vertica uses to run user-defined extensions in fenced mode.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
PROCESS_TYPE VARCHAR

Indicates what kind of side process this row is for and can be one of the following values:

  • UDxZygoteProcess — Master process that creates worker side processes, as needed, for queries. There will be, at most, 1 UP UDxZygoteProcess for each Vertica instance.

  • UDxSideProcess — Indicates that the process is a worker side process. There could be many UDxSideProcesses, depending on how many sessions there are, how many queries, and so on.

SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
LANGUAGE VARCHAR The language of the UDx. For example 'R' or 'C++';
MAX_MEMORY_JAVA_KB INTEGER The maximum amount of memory in KB that can be used for the Java heap file on the node.
PID INTEGER Linux process identifier of the side process (UDxSideProcess).
PORT VARCHAR For Vertica internal use. The TCP port that the side process is listening on.
STATUS VARCHAR

Set to UP or DOWN, depending on whether the process is alive or not.

After a process fails, Vertica restarts it only on demand. So after a process failure, there might be periods of time when no side processes run.

Privileges

None

123 - USER_LIBRARIES

Lists the user libraries that are currently loaded.

Lists the user libraries that are currently loaded. These libraries contain user-defined extensions (UDxs) that provide additional analytic functions.

Column Name Data Type Description
SCHEMA_NAME VARCHAR(8192) The name of the schema containing the library.
LIB_NAME VARCHAR(8192) The name of the library.
LIB_OID INTEGER The object ID of the library.
AUTHOR VARCHAR(8192) The creator of the library file.
OWNER_ID INTEGER The object ID of the library's owner.
LIB_FILE_NAME VARCHAR(8192) The name of the shared library file.
MD5_SUM VARCHAR(8192)

The MD5 checksum of the library file, used to verify that the file was correctly copied to each node.

SDK_VERSION VARCHAR(8192) The version of the Vertica SDK used to compile the library.
REVISION VARCHAR(8192) The revision of the Vertica SDK used to compile the library.
LIB_BUILD_TAG VARCHAR(8192) Internal information set by library developer to track the when the library was compiled.
LIB_VERSION VARCHAR(8192) The version of the library.
LIB_SDK_VERSION VARCHAR(8192) The version of the Vertica SDK intended for use with the library. The developer sets this value manually. This value may differ from the values in the SDK_VERSION and REVISION, which are set automatically during compilation.
SOURCE_URL VARCHAR(8192) A URL that contains information about the library.
DESCRIPTION VARCHAR(8192) A description of the library.
LICENSES_REQUIRED VARCHAR(8192) The licenses required to use the library.
SIGNATURE VARCHAR(8192) The signature used to sign the library for validation.
DEPENDENCIES VARCHAR (8192) External libraries on which this library depends. These libraries are maintained by Vertica, just like the user libraries themselves.

124 - USER_LIBRARY_MANIFEST

Lists user-defined functions contained in all loaded user libraries.

Lists user-defined functions contained in all loaded user libraries.

Column Name Data Type Description
SCHEMA_NAME VARCHAR The name of the schema containing the function.
LIB_NAME VARCHAR The name of the library containing the UDF.
LIB_OID INTEGER The object ID of the library containing the function.
OBJ_NAME VARCHAR The name of the constructor class in the library for a function.
OBJ_TYPE VARCHAR The type of user defined function (scalar function, transform function)
ARG_TYPES VARCHAR A comma-delimited list of data types of the function's parameters.
RETURN_TYPE VARCHAR A comma-delimited list of data types of the function's return values.

Privileges

None

125 - USER_SESSIONS

Returns user session history on the system.

Returns user session history on the system.

Column Name Data Type Description
NODE_NAME VARCHAR Name of the node that is reporting the requested information.
USER_NAME VARCHAR Name of the user at the time Vertica recorded the session.
SESSION_ID VARCHAR Identifier for this session. This identifier is unique within the cluster at any point in time but can be reused when the session closes.
TRANSACTION_ID VARCHAR Identifier for the transaction within the session, if any. If a session is active but no transaction has begun, TRANSACTION_ID returns NULL.
STATEMENT_ID VARCHAR Unique numeric ID for the currently-running statement. NULL indicates that no statement is currently being processed. The combination of TRANSACTION_ID and STATEMENT_ID uniquely identifies a statement within a session.
RUNTIME_PRIORITY VARCHAR

Determines the amount of run-time resources (CPU, I/O bandwidth) the Resource Manager should dedicate to queries already running in the resource pool. Valid values are:

  • HIGH

  • MEDIUM

  • LOW

Queries with a HIGH run-time priority are given more CPU and I/O resources than those with a MEDIUM or LOW run-time priority.

SESSION_START_TIMESTAMP TIMESTAMPTZ Value of session at beginning of history interval.
SESSION_END_TIMESTAMP TIMESTAMPTZ Value of session at end of history interval.
IS_ACTIVE BOOLEAN Denotes if the operation is executing.
CLIENT_OS_HOSTNAME VARCHAR The hostname of the client as reported by their operating system.
CLIENT_HOSTNAME VARCHAR

The IP address and port of the TCP socket from which the client connection was made; NULL if the session is internal.

Vertica accepts either IPv4 or IPv6 connections from a client machine. If the client machine contains mappings for both IPv4 and IPv6, the server randomly chooses one IP address family to make a connection. This can cause the CLIENT_HOSTNAME column to display either IPv4 or IPv6 values, based on which address family the server chooses.

CLIENT_PID INTEGER

Linux process identifier of the client process that issued this connection.

Note: The client process could be on a different machine from the server.

CLIENT_LABEL VARCHAR User-specified label for the client connection that can be set when using ODBC. See Label in DSN Parameters.
SSL_STATE VARCHAR

Indicates if Vertica used Secure Socket Layer (SSL) for a particular session. Possible values are:

  • None – Vertica did not use SSL.

  • Server – Sever authentication was used, so the client could authenticate the server.

  • Mutual – Both the server and the client authenticated one another through mutual authentication.

See Implementing Security and TLS protocol.

AUTHENTICATION_METHOD VARCHAR

Type of client authentication used for a particular session, if known. Possible values are:

  • Unknown

  • Trust

  • Reject

  • Kerberos

  • Password

  • MD5

  • LDAP

  • Kerberos-GSS

  • Ident

See Security and authentication and Configuring client authentication.

CLIENT_TYPE VARCHAR

The type of client from which the connection was made. Possible client type values:

  • ADO.NET Driver

  • ODBC Driver

  • JDBC Driver

  • vsql

CLIENT_VERSION VARCHAR Returns the client version.
CLIENT_OS VARCHAR Returns the client operating system.
CLIENT_OS_USER_NAME VARCHAR The name of the user that logged into, or attempted to log into, the database. This is logged even when the login attempt is unsuccessful.

Privileges

Non-superuser: No explicit privileges required. You only see records for tables that you have privileges to view.

See also