Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Published by Scroll Versions from space ML1 and version 9.2

...

ColumnDescription
nodeidThe ID of the node where the plan is cached. Each node maintains its own query cache. In many cases, queries are evenly distributed across the nodes, but in some cases a single client or process is responsible for an inordinate amount of load on a single node. In this case, you can filter on nodeid to list only its entries.
databaseThe database from which the query was executed.
statementThe text of the original query used for compilation is stored here. The query itself is parameterized when cached, meaning that the constant values supplied in the query (i.e. the N in WHERE id = N) are not included in the cached plan. When the QPC is utilized, the new parameters are substituted into the compiled query appropriately.
query_idUnique identifier assigned to the query.
cache_idUnique identifier assigned to the cache for the node. Query and cache IDs change when the cache is flushed, typically due to a DDL statement or Rebalancer activity.
plan_idUnique identifier assigned to differentiate multiple plans generated for the same query.
exec_countNumber of times this query plan has been executed since the plan was created.
exec_msAmount of CPU time (milliseconds) that has been spent executing this query plan since the plan was created. This figure does not include query compilation time.runtime_nsAmount of CPU time (nanoseconds) that has been spent executing this query plan since its inception. This excludes wait time for buffer manager or locks.

Useful Query for qpc_queries

...

sql> SELECT nodeid, exec_count, exec_ms, exec_ms/exec_count as avg_ms, left(statement,100)
     FROM system.qpc_queries 
     ORDER BY exec_ms desc 
     LIMIT 3;
+--------+------------+----------+------------+----------------------------------------------------------------------------------------------------+
| nodeid | exec_count | exec_ms  | avg_ms     | left(statement,100)                                                                                |
+--------+------------+----------+------------+----------------------------------------------------------------------------------------------------+
|      3 |         65 |  2226593 | 34255.2769 | SELECT count(*) FROM files WHERE service_id = 9788   AND mod_time > TIMESTAMPADD(MINUTE,-15,NOW()) | 
|      2 |     606833 |  2087750 |     3.4404 | UPDATE files SET mod_time=now() WHERE id = 7617351                                                 | 
|      1 |    1309577 |  1422334 |     1.0861 | SELECT * FROM files WHERE id = 12684388                                                            | 
+--------+------------+----------+------------+----------------------------------------------------------------------------------------------------+
3 rows in set (0.01 sec)

...

sql> SELECT  query_key,
             min(rank),
             max(rank),
             database,
             left(statement,100),
             sum(exec_count) as calc_exec_count,
             round(avg(avg_rows_read)) as calc_avg_rows_read,
             round(avg(avg_exec_ms)) as calc_avg_exec_ms
     FROM    clustrix_statd.qpc_history
     WHERE   timestamp BETWEEN (now() - interval 24 hour) AND now()
     AND     database !='clustrix_statd'
     GROUP BY query_key
     ORDER BY calc_exec_count     DESC,
              calc_avg_rows_read  DESC
     LIMIT 100;

...

sql> SELECT query_key,
            min(rank),
            max(rank),
            database,
            left(statement,100),
            sum(exec_count) as calc_exec_count,
            round(avg(avg_rows_read)) as calc_avg_rows_read,
            round(avg(avg_exec_ms)) as calc_avg_exec_ms
     FROM   clustrix_statd.qpc_history
     WHERE  timestamp BETWEEN (now() - interval 24 hour) AND now()
     AND    database !='clustrix_statd'
     GROUP BY query_key
     ORDER BY calc_avg_rows_read DESC,
              calc_exec_count DESC
     LIMIT 100;

...

sql> SELECT query_key,
            min(rank),
            max(rank),
            database,
            left(statement,100),
            sum(exec_count) as calc_exec_count,
            round(avg(avg_rows_read)) as calc_avg_rows_read,
            round(avg(avg_exec_ms)) as calc_avg_exec_ms
     FROM   clustrix_statd.qpc_history
     WHERE  timestamp BETWEEN (now() - interval 24 hour) AND now()
     AND    database !='clustrix_statd'
     GROUP BY query_key
     ORDER BY calc_avg_exec_ms  DESC,
              calc_exec_count   DESC
     LIMIT 100;

...

sql> SELECT timestamp,
            rank,
            database,
            left(statement,100),
            exec_count,
            avg_rows_read,
            avg_exec_ms
     FROM   clustrix_statd.qpc_history
     WHERE  rank < 4
     ORDER BY timestamp DESC, rank ASC
     LIMIT 100;

...