2.7.10 Advisor Report Table

MySQL 8.0.32 deprecates the heatwave_advisor_report table, and replaces it with the heatwave_autopilot_report table in the sys schema. A future release will remove it. See Section 2.7.9, “Autopilot Report Table”.

When MySQL runs Advisor, it sends detailed output to the heatwave_advisor_report table in the sys schema.

The heatwave_advisor_report table is a temporary table. It contains data from the last execution of Advisor. Data is only available for the current session and is lost when the session terminates or when the server is shut down.

Advisor Report Table Query Examples

Query the heatwave_advisor_report table after MySQL runs Advisor, as in the following examples:

• View Advisor warning information:

  mysql> SELECT log FROM sys.heatwave_advisor_report WHERE type="warn";

• View error information if Advisor stops unexpectedly:

  mysql> SELECT log FROM sys.heatwave_advisor_report WHERE type="error";

• View the generated DDL statements for Advisor recommendations:

  mysql> SELECT log->>"$.sql" AS "SQL Script" 
            FROM sys.heatwave_advisor_report 
            WHERE type = "sql" 
            ORDER BY id;

• Concatenate Advisor generated DDL statements into a single string to copy and paste for execution. The group_concat_max_len variable sets the result length in bytes for the GROUP_CONCAT() function to accommodate a potentially long string. The default group_concat_max_len setting is 1024 bytes.

  mysql> SET SESSION group_concat_max_len = 1000000;
  mysql> SELECT GROUP_CONCAT(log->>"$.sql" SEPARATOR ' ') 
            FROM sys.heatwave_advisor_report 
            WHERE type = "sql" 
            ORDER BY id;

• Retrieve Auto Query Time Estimation data in JSON format:

  mysql> SELECT log 
            FROM sys.heatwave_advisor_report 
            WHERE stage = "QUERY_INSIGHTS" AND type = "info";

• Retrieve Auto Query Time Estimation data in SQL table format:

  mysql> SELECT log->>"$.query_id" AS query_id,
            log->>"$.session_id" AS session_id,
            log->>"$.query_text" AS query_text,
            log->>"$.runtime_estimated_ms" AS runtime_estimated_ms,
            log->>"$.runtime_executed_ms" AS runtime_executed_ms,
            log->>"$.comment" AS comment
            FROM sys.heatwave_advisor_report 
            WHERE stage = "QUERY_INSIGHTS" AND type = "info"
            ORDER BY id;