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


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

2.3.6 Query Runtimes and Estimates

You can view HeatWave query runtimes and runtime estimates using HeatWave Advisor Query Insights feature or by querying 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 query insights:

  • 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())));

For additional information about using Query Insights, see Section 2.8.4, “Query Insights”.

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;