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

EXECUTION_ENGINE_PROFILES

Privileges

Counter names

Examples

See also