HeatWave User Guide / Running Queries

Chapter 6 Running Queries

When HeatWave is enabled and the data you want to query is loaded, queries that qualify are automatically offloaded from the MySQL DB System to HeatWave for accelerated processing. No special action is required. Simply run the query from a MySQL DB System-connected MySQL client or application. (Clients and applications do not connect to HeatWave directly.) For information about connecting to a MySQL DB System, refer to the MySQL Database Service User Guide. After HeatWave processes a query, results are sent back to the MySQL DB System and to the client or application that issued the query.

Running queries is described under the following topics in this section:

For related best practices, see Chapter 9, Best Practices.

Query Offload Prerequisites

The following prerequisites apply for offloading queries:

The query must be a SELECT statement. INSERT ... SELECT and CREATE TABLE ... SELECT statements are supported, but only the SELECT portion of the statement is offloaded to HeatWave. See CREATE TABLE ... SELECT Statements, and INSERT ... SELECT Statements.
All tables accessed by the query must be defined with RAPID as the secondary engine. See Section 3.5, “Defining the Secondary Engine”.
All tables accessed by the query must be loaded in HeatWave. See Chapter 4, Loading Data.
autocommit must be enabled. If autocommit is disabled, queries are not offloaded and execution is performed on the MySQL DB System. To check the autocommit setting:
```
mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
```
Queries must only use supported functions and operators. See Section 11.2, “Supported Functions and Operators”.
Queries must avoid known limitations. See Section 11.6, “Limitations”.

If any prerequisite is not satisfied, the query is not offloaded and falls back to the MySQL DB System for execution by default. This behavior is controlled by the use_secondary_engine variable, which is set on ON by default. A use_secondary_engine=OFF setting forces a query to execute on the MySQL DB System. A use_secondary_engine=FORCED setting forces a query to execute on HeatWave or fail if that is not possible.

Running a Query

Before running a query, use EXPLAIN to determine if the query can be offloaded. If so, the Extra column of EXPLAIN output shows: “Using secondary engine RAPID”. If that information does not appear, the query cannot be offloaded.

mysql> EXPLAIN SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM orders 
       WHERE O_ORDERDATE >= DATE '1994-03-01' GROUP BY O_ORDERPRIORITY 
       ORDER BY O_ORDERPRIORITY\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 14862970
     filtered: 33.33
        Extra: Using where; Using temporary; Using filesort; Using secondary 
        engine RAPID

After using EXPLAIN to verify that the query can be offloaded, run the query and note the execution time.

Note

For information about obtaining HeatWave query runtime estimates before running a query, see Query Runtimes and Estimates.

mysql> SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM orders
       WHERE O_ORDERDATE >= DATE '1994-03-01' GROUP BY O_ORDERPRIORITY 
       ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (0.04 sec)

To compare HeatWave query execution time with MySQL DB System execution time, disable the use_secondary_engine variable and run the query again to see how long it takes to run on the MySQL DB System.

mysql> SET SESSION use_secondary_engine=OFF;

mysql> SELECT O_ORDERPRIORITY, COUNT(*) AS ORDER_COUNT FROM orders 
       WHERE O_ORDERDATE >= DATE '1994-03-01' GROUP BY O_ORDERPRIORITY 
       ORDER BY O_ORDERPRIORITY;
+-----------------+-------------+
| O_ORDERPRIORITY | ORDER_COUNT |
+-----------------+-------------+
| 1-URGENT        |     2017573 |
| 2-HIGH          |     2015859 |
| 3-MEDIUM        |     2013174 |
| 4-NOT SPECIFIED |     2014476 |
| 5-LOW           |     2013674 |
+-----------------+-------------+
5 rows in set (8.91 sec)

If a query does not offload and you cannot determine why, refer to Chapter 10, Troubleshooting, or try debugging the query using the procedure described in Debugging Queries.

Note

Concurrently issued queries are prioritized for execution. For information about query prioritization, see Auto Scheduling.

Query Runtimes and Estimates

HeatWave query runtimes and runtime estimates can be viewed 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 200 executed queries.

Using 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 8.3, “Query Insights”.

Using the rpd_query_stats Table

To view runtime data for all queries in the HeatWave query history:

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:

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:

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

CREATE TABLE ... SELECT Statements

The SELECT query of a CREATE TABLE ... SELECT statement is offloaded to HeatWave for execution, and the table is created on the MySQL DB System. Offloading the SELECT query to HeatWave reduces CREATE TABLE ... SELECT statement execution time.

The SELECT table must be loaded in HeatWave. For example, the following statement selects data from the orders table on HeatWave and inserts the result set into the orders2 table created on the MySQL DB System:

mysql> CREATE TABLE orders2 SELECT * FROM orders;

The SELECT portion of the CREATE TABLE ... SELECT statement is subject to the same HeatWave requirements and limitations as regular SELECT queries.

INSERT ... SELECT Statements

The SELECT query of an INSERT ... SELECT statement is offloaded to HeatWave for execution, and the result set is inserted into the specified table on the MySQL DB System. Offloading the SELECT query to HeatWave reduces INSERT ... SELECT statement execution time.

The SELECT table must be loaded in HeatWave, and the INSERT table must be present on the MySQL DB System. For example, the following statement selects data from the orders table on tHeatWave and inserts the result set into the orders2 table on the MySQL DB System:

mysql> INSERT INTO orders2 SELECT * FROM orders;

Usage notes:

The SELECT portion of the INSERT ... SELECT statement is subject to the same HeatWave requirements and limitations as regular SELECT queries.
Functions, operators, and attributes deprecated by MySQL Server are not supported in the SELECT query.
The ON DUPLICATE KEY UPDATE clause is not supported.
SELECT .. UNION ALL queries are not offloaded if the INSERT table is the same as the SELECT table because MySQL Server uses a temporary table in this case, which cannot be offloaded.
INSERT INTO some_view SELECT statements are not offloaded. Setting use_secondary_engine=FORCED does not cause the statement to fail with an error in this case. The statement is executed on the MySQL DB System regardless of the use_secondary_engine setting.

Auto Scheduling

HeatWave uses a workload-aware, priority-based, automated scheduling mechanism to schedule concurrently issued queries for execution. The scheduling mechanism prioritizes short-running queries but considers wait time in the queue so that costlier queries are eventually scheduled for execution. This scheduling approach reduces query execution wait times overall.

When HeatWave is idle, an arriving query is scheduled immediately for execution. It is not queued. A query is queued only if a preceding query is running on HeatWave.

A light-weight cost estimate is performed for each query at query compilation time.

Queries cancelled via Ctrl-C are removed from the scheduling queue.

For a query that you can run to view the HeatWave query history including query start time, end time, and wait time in the scheduling queue, see Section 11.5, “Metadata Queries”.

Auto Query Plan Improvement

The Auto Query Plan Improvement feature collects and stores query plan statistics in a statistics cache when a query is executed in HeatWave. When a new query shares query execution plan nodes with previously executed queries, the actual statistics collected from previously executed queries are used instead of estimated statistics, which improves query execution plans, cost estimations, execution times, and memory efficiency.

Each entry in the cache represents a query execution plan node. A query execution plan may have nodes for table scans, JOINs, GROUP BY operations, and so on.

The statics cache is an LRU structure. When cache capacity is reached, the least recently used entries are evicted from the cache as new entries are added. The number of entries permitted in the statistics cache by default is 65536, which is enough to store statistics for 4000 to 5000 unique queries of medium complexity. The maximum number of entries is defined by the rapid_stats_cache_max_entries setting, which is OCI managed.

Debugging Queries

This section describes how to enable query tracing, and how to query the INFORMATION_SCHEMA.OPTIMIZER_TRACE table for information about why a query is not offloaded to HeatWave for processing.

In the following example, optimizer trace data is retrieved for a query that uses the YEARWEEK() function, which is currently not supported.

Enable tracing by setting the optimizer_trace and optimizer_trace_offset variables:

mysql> SET SESSION optimizer_trace="enabled=on";
mysql> SET optimizer_trace_offset=-2;

Issue the query with EXPLAIN. If the Extra column does not show “Using secondary engine RAPID”, the query cannot be offloaded. For example:

mysql> EXPLAIN SELECT YEARWEEK(O_ORDERDATE) FROM orders\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: orders
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1488913
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

Query the INFORMATION_SCHEMA.OPTIMIZER_TRACE table for offload failure information:

mysql> SELECT QUERY, TRACE->'$**.Rapid_Offload_Fails' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
+------------------------------------------------+------------------------------------------+
|QUERY                                           |TRACE->'$**.Rapid_Offload_Fails'          |
+------------------------------------------------+------------------------------------------+
|EXPLAIN SELECT YEARWEEK(O_ORDERDATE) FROM orders|[{"Reason": "Function yearweek is not yet |
|                                                |  supported"}]                            |
+------------------------------------------------+------------------------------------------+

The reason reported for the offload failure depends on the limitation encountered. For the most common issues, such as unsupported clauses or functions, a specific reason is reported. For undefined issues or unsupported query transformations performed by the optimizer, the following generic reason is reported:

[{"Reason": "Currently unsupported RAPID query compilation scenario"}]

For a query that does not meet the query cost threshold for HeatWave, the following reason is reported:

[{"Reason": "The estimated query cost does not exceed secondary_engine_cost_threshold."}]

The query cost threshold prevents small queries of little cost from being offloaded to HeatWave. For information about the query cost threshold, see Chapter 10, Troubleshooting.

For a query that attempts to access a column defined as NOT SECONDARY, the following reason is reported:

[{"reason": "Column risk_assessment is marked as NOT SECONDARY."}]

Columns defined as NOT SECONDARY are excluded when a table is loaded into HeatWave. See Section 3.2, “Excluding Table Columns”.

PREV HOME UP NEXT