Query Execution Times

The SYS.QUERYSTATS table provides basic information about the execution time of queries in the GemFire XD distributed system.

Procedure

QUERYSTATS provides an easy way to determine how much time is spent executing queries in the GemFire XD cluster. You can use information in QUERYSTATS to identify the longest-running queries over a period of time, and then focus on those queries for further performance tuning using the techniques in Evaluating Query Execution Plans.

By default GemFire XD does not collect statistics in SYS.QUERYSTATS. You must enable collection using the SYS.SET_QUERYSTATS procedure. Keep in mind that enabling query statistics collection stores information for all queries in the distributed system directly in the SYS.QUERYSTATS virtual table. Collect query statistics in this manner only during a fixed period of time, or in order to evaluate a sequence of queries, and then disable collection. To collect longer-term statistics on statement performance, use the techniques in Evaluating Statistics for the System and Applications to collect statistics in external files and analyze them using the VSD tool.

To collect and use query statistics in the SYS.QUERYSTATS table:
  1. Start gfxd and execute the SYS.SET_QUERYSTATS procedure to enable statistics collection in SYS.QUERYSTATS:
    $ gfxd
    gfxd> connect client 'localhost:1527';
    gfxd> call sys.set_querystats(1);
  2. Execute the series of queries that you want to compare, or wait for a period of time to collect statistics about active queries in the system.
  3. Query SYS.QUERYSTATS to display all queries, ordered by the longest total execution times:
    gfxd> select sum(totaltime)/avg(numinvocations) t, query from sys.querystats 
    > where numinvocations > 0 group by query order by t desc; 
    As an alternative, display only the top 10 or fewer queries by adding the FETCH FIRST clause. For example:
    gfxd> select sum(totaltime)/avg(numinvocations) t, query from sys.querystats 
    > where numinvocations > 0 group by query order by t desc fetch first 10 rows only; 
  4. Finally, disable statistics collection in SYS.QUERYSTATS:
    gfxd> call sys.set_querystats(0);
  5. Using the information you have about long-running queries, consider using the techniques in Evaluating Statistics for the System and Applications to improve query performance.