2.3.7 Query Runtimes and Estimates

To view HeatWave query runtimes and runtime estimates use HeatWave Autopilot Advisor Auto Query Time Estimation, see: Section 2.7.6, “Auto Query Time Estimation”, or query the performance_schema.rpd_query_stats table. Runtime data is useful for query optimization, troubleshooting, and estimating the cost of running a particular query or workload.

HeatWave query runtime data includes:

• Runtimes for successfully executed queries

• Runtime estimates for EXPLAIN queries

• Runtime estimates for queries cancelled using Ctrl+C

• Runtime estimates for queries that fail due to an out-of-memory error

Runtime data is available for queries in the HeatWave query history, which is a non-persistent store of information about the last 1000 executed queries.

To use Auto Query Time Estimation:

• To view runtime data for all queries in the HeatWave history:

  mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", true));

• To view runtime data for queries executed by the current session only:

  mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", true, 
            "query_session_id", JSON_ARRAY(connection_id())));

See: Section 2.7.6, “Auto Query Time Estimation”.

To use the rpd_query_stats table:

• To view runtime data for all queries in the HeatWave query history:

  mysql> SELECT query_id,
            JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id, 
            JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.accumulatedRapidCost'),'$[0]') AS time_in_ns, 
            JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message
            FROM performance_schema.rpd_query_stats;

• To view runtime data for a particular HeatWave query, filtered by query ID:

  mysql> SELECT query_id,
            JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.sessionId'),'$[0]') AS session_id, 
            JSON_EXTRACT(JSON_UNQUOTE(qkrn_text->'$**.accumulatedRapidCost'),'$[0]') AS time_in_ns, 
            JSON_EXTRACT(JSON_UNQUOTE(qexec_text->'$**.error'),'$[0]') AS error_message
            FROM performance_schema.rpd_query_stats
            WHERE query_id = 1;

• EXPLAIN output includes the query ID. You can also query the performance_schema.rpd_query_stats table for query IDs:

  mysql> SELECT query_id, LEFT(query_text,160)
            FROM performance_schema.rpd_query_stats;