Related Documentation Download this Manual
PDF (US Ltr) - 0.6Mb
PDF (A4) - 0.6Mb


8.3 Query Insights

Query Insights provides:

  • 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 can be used for query optimization, troubleshooting, or to estimate the cost of running a particular query or workload on HeatWave.

Query Insights Syntax

CALL sys.heatwave_advisor ([options]);
  
 options: {
     JSON_OBJECT("key","value"[,"key","value"] ...)
        "key","value": 
        ["output",{"normal"|"silent"|"help"}]
        ["target_schema",JSON_ARRAY({"schema_name"[,"schema_name"]}]
        ["exclude_query",JSON_ARRAY("query_id"[,"query_id"] ...)]
        ["query_session_id",JSON_ARRAY("query_session_id"[,"query_session_id"] ...)]
        ["query_insights",{TRUE|FALSE}]
}

For syntax examples, see Section 8.4, “Advisor Examples”.

Advisor options are specified as key-value pairs in JSON-object format. Options include:

  • output: Defines how Advisor produces output. Permitted values are:

  • target_schema: Defines one or more schemas. The list is specified in JSON-array format. If a target schema is not specified, all schemas in HeatWave are considered.

  • exclude_query: Defines the IDs of queries to exclude. To identify query IDs, query the performance_schema.rpd_query_stats table. For a query example, see Section 8.4, “Advisor Examples”.

  • query_session_id: Defines session IDs for filtering queries by session ID. To identify session IDs, query the performance_schema.rpd_query_stats table. For a query example, see Section 8.4, “Advisor Examples”.

  • query_insights: Provides runtimes for successfully executed queries and runtime estimates for EXPLAIN queries, queries cancelled using Ctrl+C, and queries that fail due to an out-of-memory error. The default setting is FALSE.

Running Query Insights

For Query Insights to provide runtime data, a query history must be available. Query Insights provides runtime data for up to 200 queries, which is the HeatWave query history limit. To view the current HeatWave query history, query the performance_schema.rpd_query_stats table:

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

The following example shows how to retrieve runtime data for the entire query history using Query Insights. In this example, there are three queries in the query history: a successfully executed query, a query that failed due to an out of memory error, and a query that was cancelled using Ctrl+C. For an explanation of Query Insights data, see Query Insights Data.

mysql> CALL sys.heatwave_advisor(JSON_OBJECT("query_insights", TRUE));
+-------------------------------+
| INITIALIZING HEATWAVE ADVISOR |
+-------------------------------+
| Version: 1.12                 |
|                               |
| Output Mode: normal           |
| Excluded Queries: 0           |
| Target Schemas: All           |
|                               |
+-------------------------------+
6 rows in set (0.01 sec)

+---------------------------------------------------------+
| ANALYZING LOADED DATA                                   |
+---------------------------------------------------------+
| Total 8 tables loaded in HeatWave for 1 schemas         |
| Tables excluded by user: 0 (within target schemas)      |
|                                                         |
| SCHEMA                            TABLES        COLUMNS |
| NAME                              LOADED         LOADED |
| ------                            ------         ------ |
| `tpch128`                              8             61 |
|                                                         |
+---------------------------------------------------------+
8 rows in set (0.02 sec)

+-------------------------------------------------------------------------------------+
| QUERY INSIGHTS                                                                      |
+-------------------------------------------------------------------------------------+
| Queries executed on Heatwave: 4                                                     |
| Session IDs (as filter): None                                                       |
|                                                                                     |
| QUERY-ID  SESSION-ID  QUERY-STRING             EXEC-RUNTIME  COMMENT                |
| --------  ----------  ------------             ------------  -------                |
|        1          32  SELECT COUNT(*)                                               |
|                       FROM tpch128.LINEITEM    0.628                                |
|        2          32  SELECT COUNT(*)                                               |                                           
|                       FROM tpch128.ORDERS      0.114 (est.)  Explain.               |
|        3          32  SELECT COUNT(*)                                               |
|                       FROM tpch128.ORDERS,                                          |
|                       tpch128.LINEITEM         5.207 (est.)  Out of memory          |
|                                                              error during           |
|                                                              query execution        |
|                                                              in RAPID.              |
|        4          32  SELECT COUNT(*)                                               |
|                       FROM tpch128.SUPPLIER,                                        |
|                       tpch128.LINEITEM         3.478 (est.)  Operation was          |
|                                                              interrupted by         |
|                                                              the user.              |
| TOTAL ESTIMATED:   3   EXEC-RUNTIME:       8.798 sec                                |
| TOTAL EXECUTED:    1   EXEC-RUNTIME:       0.628 sec                                |
|                                                                                     |
|                                                                                     |
| Retrieve detailed query statistics using the query below:                           |
|     SELECT log FROM sys.heatwave_advisor_report WHERE stage = "QUERY_INSIGHTS" AND  |
|     type = "info";                                                                  |
|                                                                                     |
+-------------------------------------------------------------------------------------+

mysql> SELECT log FROM sys.heatwave_advisor_report WHERE stage = "QUERY_INSIGHTS" 
       AND type = "info";
+--------------------------------------------------------------------------------------+
| log                                                                                  |
+--------------------------------------------------------------------------------------+
| {"comment": "", "query_id": 1, "query_text": "SELECT COUNT(*) FROM tpch128.LINEITEM",|
|  "session_id": 32, "runtime_executed_ms": 627.6099681854248,                         |
|  "runtime_estimated_ms": 454.398817}                                                 |
|                                                                                      |
| {"comment": "Explain.", "query_id": 2, "query_text": "SELECT COUNT(*)                |
|   FROM tpch128.ORDERS", "session_id": 32, "runtime_executed_ms": null,               |   
|  "runtime_estimated_ms": 113.592768}                                                 |
|                                                                                      |
| {"comment": "Out of memory error during query execution in RAPID.", "query_id": 3,   |
|  "query_text": "SELECT COUNT(*) FROM tpch128.ORDERS, tpch128.LINEITEM",              |
|  "session_id": 32, "runtime_executed_ms": null, "runtime_estimated_ms": 5206.80822}  |
|                                                                                      |
| {"comment": "Operation was interrupted by the user.", "query_id": 4,                 |
|  "query_text": "SELECT COUNT(*) FROM tpch128.SUPPLIER, tpch128.LINEITEM",            |
|  "session_id": 32, "runtime_executed_ms": null, "runtime_estimated_ms": 3477.720953} |
+--------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

For Query Insights CALL statements examples that filter results by schema and session ID, see Section 8.4, “Advisor Examples”.

Query Insights Data

Query Insights provides the following data:

  • QUERY-ID

    The query ID.

  • SESSION-ID

    The session ID that issued the query.

  • QUERY-STRING

    The query string. EXPLAIN, if specified, is not displayed in the query string.

  • EXEC-RUNTIME

    The query execution runtime in seconds. Runtime estimates are differentiated from actual runtimes by the appearance of the following text adjacent to the runtime: (est.). Actual runtimes are shown for successfully executed queries. Runtime estimates are shown for EXPLAIN queries, queries cancelled by Ctrl+C, and queries that fail with an out-of-memory error.

  • COMMENT

    Comments associated with the query. Comments may include:

    • Explain: The query was run with EXPLAIN.

    • Operation was interrupted by the user: The query was successfully offloaded to HeatWave but was interrupted by a Ctrl+C key combination.

    • Out of memory error during query execution in RAPID: The query was successfully offloaded to HeatWave but failed due to an out-of-memory error.

  • TOTAL-ESTIMATED and EXEC-RUNTIME

    The total number of queries with runtime estimates and total execution runtime (estimated).

  • TOTAL-EXECUTED and EXEC-RUNTIME

    The total number of successfully executed queries and total execution runtime (actual).

  • Retrieve detailed statistics using the query below

    The query retrieves detailed query statistics from the sys.heatwave_advisor_report table. For an example of the detailed statistics, see Running Query Insights.

Query Insights data can be retrieved in machine readable format for use in scripts; see Section 8.4, “Advisor Examples”. Query Insights data can also be retrieved in JSON format or SQL table format by querying the sys.heatwave_advisor_report table. See Section 8.5, “Advisor Report Table”.