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:

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 (for scan operations) or files (for load operations) analyzed by SIPS expression.
blocks filtered by SIPs expression
Number of data blocks (for scan operations) or files (for load operations) filtered by SIPS expression.
blocks filtered by SIPs value lists
Number of data blocks (for scan operations) or files (for load operations) filtered by SIPS sorted value lists.
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)
current unbalanced memory capacity (bytes)
current unbalanced memory overhead (bytes)
current unbalanced memory requested (bytes)
Pooled version of "current memory XXX" counters.
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)
exceptions rows cumulative size of temp files (bytes)
exceptions rows current size of temp files (bytes)
Counters that store the total or current size of exception data.
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
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)
See the completed merge phases counter.
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 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)
Peak value of the corresponding "current XXX" counters.
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)
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)
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)
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