|Managing and Monitoring GemFire XD / Getting Information from GemFire XD System Tables|
The SYS.QUERYSTATS table provides basic information about the execution time of queries in the GemFire XD distributed system.
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.
$ gfxd gfxd> connect client 'localhost:1527'; gfxd> call sys.set_querystats(1);
gfxd> select sum(totaltime)/avg(numinvocations) t, query from sys.querystats > where numinvocations > 0 group by query order by t desc;
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;
gfxd> call sys.set_querystats(0);