Real-Time Profiling

With real-time profiling, you can monitor long-running queries while they execute.

Real-time profiling counters are available for all statements while they execute, including internal operations such as mergeout, recovery, and refresh. Unless you explicitly enable profiling using the keyword PROFILE on a specific SQL statement, or generally enable profiling for the database and/or the current session, profiling counters are unavailable after the statement completes.

Queries for real-time profiling data require a transaction ID. If the transaction executes multiple statements, the query also requires a statement ID to identify the desired statement. You obtain transaction and statement IDs the SYSTEM_SESSIONS system table from columns transaction_id and statement_id, respectively:

=> SELECT transaction_id, statement_id FROM SYSTEM_SESSIONS;

Profiling Counters

The EXECUTION_ENGINE_PROFILES system table contains available profiling counters for internal operations and user statements.

Useful counters include:

You can view all available counters by querying EXECUTION_ENGINE_PROFILES:

=> SELECT DISTINCT(counter_name) FROM EXECUTION_ENGINE_PROFILES;

To monitor the profiling counters, you can run a command like the following using a retrieved transaction ID (a000000000027):

=> SELECT * FROM execution_engine_profiles
   WHERE TO_HEX(transaction_id)='a000000000027' 
   AND counter_name = 'execution time (us)' 
   ORDER BY node_name, counter_value DESC; 

The following example finds operators with the largest execution time on each node:

=> SELECT node_name, operator_name, counter_value execution_time_us FROM V_MONITOR.EXECUTION_ENGINE_PROFILES WHERE counter_name='execution time (us)' LIMIT 1 OVER(PARTITION BY node_name ORDER BY counter_value DESC);
    node_name     | operator_name | execution_time_us
------------------+---------------+-------------------
 v_vmart_node0001 | Join          |            131906
 v_vmart_node0002 | Join          |            227778
 v_vmart_node0003 | NetworkSend   |            524080
(3 rows)

Query Plan Profiles

You can obtain query-specific data by profiling the query statement and evaluating the data that is captured in system tables QUERY_PLAN_PROFILES and EXECUTION_ENGINE_PROFILES. For example, you can query QUERY_PLAN_PROFILES to determine how much time a query spends on each query plan operation. For details, see Profiling Query Plans.

Linux watch Command

You can use the Linux watch command to monitor long-running queries at frequent intervals. Common use cases include:

In the following example, watch queries operators with the largest execution time on each node. The command specifies to re-execute the query each second:

watch -n 1 -d "vsql VMart -c\"SELECT node_name, operator_name, counter_value execution_time_us 
FROM v_monitor.execution_engine_profiles WHERE counter_name='execution time (us)' 
LIMIT 1 OVER(PARTITION BY node_name ORDER BY counter_value DESC);

Every 1.0s: vsql VMart -c"SELECT node_name, operator_name, counter_value execution_time_us FROM v_monitor.execu...  Thu Jan 21 15:00:44 2016

    node_name     | operator_name | execution_time_us
------------------+---------------+-------------------
 v_vmart_node0001 | Root          |            110266
 v_vmart_node0002 | UnionAll      |             38932
 v_vmart_node0003 | Scan          |             22058
(3 rows)