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


HeatWave User Guide  /  Workload Optimization using Advisor  /  Advisor Report Table

8.5 Advisor Report Table

When running Advisor, detailed output is sent 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

The heatwave_advisor_report table can be queried after running Advisor, as in the following examples:

  • View Advisor warning information:

    SELECT log FROM sys.heatwave_advisor_report WHERE type="warn";
  • View error information in case Advisor stops unexpectedly:

    SELECT log FROM sys.heatwave_advisor_report WHERE type="error";
  • View the generated DDL statements for Advisor recommendations:

    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 that can be copied and pasted 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.)

    SET SESSION group_concat_max_len = 1000000;
    SELECT GROUP_CONCAT(log->>"$.sql" SEPARATOR ' ') FROM sys.heatwave_advisor_report 
      WHERE type = "sql" ORDER BY id;
  • Retrieve Query Insights data in JSON format:

    SELECT log FROM sys.heatwave_advisor_report WHERE stage = "QUERY_INSIGHTS" AND type = "info";
  • Retrieve Query Insights data in SQL table format:

    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;