EXECUTION_ENGINE_PROFILES

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 the "COUNTER_NAME Values" section below this table. 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 for the `NetworkSend` operator.
`COUNTER_VALUE`	`INTEGER`	Value of the counter.
`IS_EXECUTING`	`BOOLEAN`	Indicates whether the profile is active or completed, where t is active and f is completed.

Privileges

No explicit privileges are required. You only see the records for tables that you have privileges to view.

COUNTER_NAME Values

The value of COUNTER_NAME can be any of the following:

COUNTER_NAME	Description
`active threads`	A counter of the LoadUnion operator, which indicates the number of input threads (Load operators) that are currently processing input.
`blocks analyzed by SIPs expression`	The number of data blocks analyzed by SIPS expression from the Scan operator.
`blocks filtered by SIPs expression`	The number of data blocks filtered by SIPS expression from the Scan operator.
`blocks filtered by SIPs value lists`	The 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`] The amountof data read (locally or remotely) from ROS containers on disk.
`bytes read from disk cache`	`[Scan]` The amount of data read from cache.
`bytes received`	The 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.
`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`	The number of rows being processed by fast aggregations in the hash groupby operator (no group/aggregation).
`file handles`	The number of file handles in use for an operator. Deprecated. See peak file handles or current file handles.
`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`] The number of bytes read from an `hdfs` source (using libhdfs++).
`Hadoop FS bytes read through webhdfs`	[`Scan, Load`] The number of bytes read from a `webhdfs` source.
`Hadoop FS bytes written through webhdfs`	[`DataTarget`] The number of bytes written to `webhdfs` storage.
`Hadoop FS hdfs:// operations that used native libhdfs++ calls`	[`Scan, Load, DataTarget`] The 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`] The 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`] The 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`] The number of times a native libhdfs++ source encountered an error and retried
`Hadoop FS read operations through webhdfs failure count`	[`Scan, Load`] The number of times a webhdfs source encountered an error and gave up
`Hadoop FS read operations through webhdfs retry count`	[`Scan, Load`] The number of times a webhdfs source encountered an error and retried
`Hadoop FS write operations through webhdfs failure count`	[`DataTarget`] The number of times a webhdfs write encountered an error and gave up
`Hadoop FS write operations through webhdfs retry count`	[`DataTarget`] The 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)`	The 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`	The number of sources processed by a Load operator.
`intermediate rows to process`	The number of rows to be processed in a phase as determined by a sort or `GROUP BY (HASH)`.
`join inner clock time (µs)`	The 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)`	The time spent for building the hash table for the inner input of the join operator.
`join inner hash table collisions`	The 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`	The 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)`	The real clock time spent on processing the outer input of the join operator (including doing the join).
`join outer execution time (µs)`	The 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 allocated (bytes)`	Memory allocated by this operator. Deprecated.
`memory reserved (bytes)`	Memory reserved by this operator. Deprecated.
`network wait (µs)`	[`NetworkSend, NetworkRecv`] Time in microseconds spent waiting on the network.
`number of cancel requests received`	The number of cancel requests received (per operator) when cancelling a call to the execution engine.
`number of invocations`	The number of times a UDSF function was invoked.
`number of storage containers opened`	`[Scan]` The 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 size of 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 filtered by SIPs expression`	The number of rows filtered by the SIPS expression from the Scan operator.
`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`	The 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 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`	The number of rows rejected by the `Load` operator.
`rows sent`	[`NetworkSend`] Number of rows sent over the network.
`rows to process`	The 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`	The total number of rows being read out of the sort facility in Join.
`rows written in sort`	The 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`	The total number of rows being put into the sort facility in Join.
`total rows read in sort total`	The 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)`	The 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.
`WOS bytes acquired`	Number of bytes acquired from the WOS by a `DataTarget` operator. Note: This is usually more but can be less than `WOS bytes written` if an earlier statement in the transaction acquired some WOS memory.
`WOS bytes written`	Number of bytes written to the WOS by a `DataTarget` operator.
`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          |          12 | end time                 | 397916465478595
 Scan          |           9 | end time                 | 397916465478510
 Scan          |          12 | start time               | 397916465462098
 Scan          |           9 | start time               | 397916465447998
 Scan          |          14 | bytes read from disk     |        28044535
 Scan          |          14 | bytes read from disk     |        28030212
 Scan          |          12 | rows processed           |         5000000
 Scan          |          12 | estimated rows produced  |         4999999
 Scan          |          18 | rows produced            |         1074828
 Scan          |          18 | rle rows produced        |         1074828
 Scan          |           3 | memory allocated (bytes) |         1074568
 Scan          |           7 | rows produced            |          799526
 Scan          |           7 | rle rows produced        |          799526
 Scan          |           7 | memory allocated (bytes) |          682592
 Scan          |          12 | clock time (µs)          |          673806
 Scan          |           7 | execution time (µs)      |          545717
 Scan          |           3 | memory allocated (bytes) |          537400
 Scan          |          12 | clock time (µs)          |          505315
 Scan          |          14 | execution time (µs)      |          495176
 Scan          |           3 | bytes read from disk     |          452403
 Scan          |          14 | execution time (µs)      |          420189
 Scan          |          12 | execution time (µs)      |          404184
 Scan          |          18 | clock time (µs)          |          398751
 Scan          |          18 | execution time (µs)      |          339321
(24 rows) 

=> SELECT DISTINCT counter_name FROM execution_engine_profiles;
                     counter_name                      
-------------------------------------------------------
 end time
 clock time (µs)
 rle rows produced
 bytes read from disk
 start time
 rows processed
 memory allocated (bytes)
 estimated rows produced
 rows produced
 execution time (µs)
(10 rows)

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;

 operator_name | path_id |       counter_name        | counter_value 
---------------+---------+---------------------------+---------------
 Join          |       1 | estimated rows produced   |         10000
 Join          |       1 | file handles              |             0
 Join          |       1 | memory allocated (bytes)  |       2405824
 Join          |       1 | memory reserved (bytes)   |       1769472
 Join          |       1 | rle rows produced         |             3
 Join          |       1 | rows produced             |             3
 Join          |       1 | clock time (µs)           |         24105
 Join          |       1 | execution time (µs)       |           235

EXECUTION_ENGINE_PROFILES

Privileges

COUNTER_NAME Values

Examples

See Also

Version 9.2.x Documentation