Documentation Home
HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 2.0Mb
PDF (A4) - 2.0Mb


HeatWave User Guide  /  ...  /  Query Runtimes and Estimates

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;