Query Analyzer Commands
ANALYZER PLAN ID
The ANALYZER PLAN ID command is used to gather information about the indexes used in the query plan of a query execution. Usually a SCAN tablename entry in the detail column, indicates that no indexes are found and a full table scan must be performed. The NODE argument forces the execution of the command to a specific node of the cluster.
Syntax
ANALYZER PLAN ID query_id [NODE nodeid]
Privileges
DBADMINReturn
A Rowset with an analysis about the query id.
Example
> ANALYZER PLAN ID 57
----|--------|---------|----------------|
id | parent | notused | detail |
----|--------|---------|----------------|
2 | 0 | 0 | SCAN customers |
----|--------|---------|----------------|ANALYZER RESET
The ANALYZER RESET command resets the statistics about a specific query, a group of queries or a database. When the command is called with the ALL argument, it resets all the statistics. The NODE argument forces the execution of the command to a specific node of the cluster.
Syntax
ANALYZER RESET [ID query_id] [GROUPID query_id] [DATABASE database_name] [ALL] [NODE nodeid]
Privileges
DBADMINReturn
OK string or error value (see SCSP protocol).
Example
> ANALYZER RESET
OKANALYZER SUGGEST ID
The ANALYZER SUGGEST ID command analyzes a query_id and returns a suggestion about the optimal index to use to speed up that query. The PERCENTAGE argument reduces the number of rows to analyze. The APPLY argument writes the suggested index into the database automatically. The NODE argument forces the execution of the command to a specific node of the cluster.
Syntax
ANALYZER SUGGEST ID query_id [PERCENTAGE percentage] [APPLY] [NODE nodeid]
Privileges
DBADMINReturn
A Rowset with the following columns:
- statement: reference to original statement (when multiple suggestions are returned)
- type: 1 means SQL, 2 means INDEX, 3 means PLAN and 4 means CANDIDATE
- report: sql or suggestion computed by the SQLite engine
Example
> ANALYZER SUGGEST ID 57
----------|-------|----------------------------------------------------------------------------------------------------------------------------------------------|
statement | type | report |
----------|-------|----------------------------------------------------------------------------------------------------------------------------------------------|
0 | 1 | SELECT C.CUSTOMERID, SUM(I.TOTAL) FROM customers C JOIN invoices I ON C.CUSTOMERID = I.CUSTOMERID GROUP BY 1 ORDER BY 2 DESC; |
0 | 2 | CREATE INDEX customers_idx_4f4310b6 ON customers(CustomerId DESC); |
0 | 3 | SCAN C USING COVERING INDEX customers_idx_4f4310b6 SEARCH I USING INDEX IFK_InvoiceCustomerId (CustomerId=?) USE TEMP B-TREE FOR ORDER BY |
0 | 4 | CREATE INDEX customers_idx_4f4310b6 ON customers(CustomerId DESC); -- stat1: 59 1 |
----------|-------|----------------------------------------------------------------------------------------------------------------------------------------------|LIST ANALYZER
The LIST ANALYZER command returns a rowset with the slowest queries performed on the connected server. The result of the LIST ANALYZER command can be further filtered using the GROUPID, DATABASE, and GROUPED options. This command is usually performed with the GROUPED flag to group the slowest queries and reduce the output. The NODE argument forces the execution of the command to a specific node of the cluster.
Syntax
LIST ANALYZER [GROUPID group_id] [DATABASE database_name] [GROUPED] [NODE nodeid]
Privileges
DBADMINReturn
A Rowset with columns that depend on the command flags.
Example
> LIST ANALYZER GROUPED
----------|--------------------------------------|--------------------|-------------------|-----------------|-------------------|
group_id | sql | database | AVG(query_time) | MAX(query_time) | COUNT(query_time) |
----------|--------------------------------------|--------------------|-------------------|-----------------|-------------------|
57 | SELECT*FROM customers; | chinook-enc.sqlite | 2.02896333333333 | 2.462731 | 3 |
54 | SELECT*FROM customers; | chinook.sqlite | 1.907214 | 1.907214 | 1 |
62 | SELECT*FROM t1 WHERE _rowid_=?; | db1.sqlite | 1.238739 | 1.238739 | 1 |
82 | SELECT*FROM albums; | chinook.sqlite | 0.924273967741935 | 2.081847 | 31 |
52 | SELECT*FROM artists; | chinook.sqlite | 0.820239 | 0.944221 | 2 |
77 | SELECT*FROM t1; | db1.sqlite | 0.6965005 | 0.706278 | 2 |
34 | SELECT*FROM artists WHERE _rowid_=?; | chinook.sqlite | 0.659359 | 0.659359 | 1 |
66 | SELECT*FROM playlists; | chinook.sqlite | 0.634047666666667 | 0.720039 | 3 |
----------|--------------------------------------|--------------------|-------------------|-----------------|-------------------|
> LIST ANALYZER GROUPID 57
----|------------------------|--------------------|------------|---------------------|
id | sql | database | query_time | datetime |
----|------------------------|--------------------|------------|---------------------|
57 | SELECT*FROM customers; | chinook-enc.sqlite | 1.633654 | 2022-12-27 20:42:04 |
56 | SELECT*FROM customers; | chinook-enc.sqlite | 1.990505 | 2022-12-27 20:42:03 |
55 | SELECT*FROM customers; | chinook-enc.sqlite | 2.462731 | 2022-12-27 20:41:43 |
----|------------------------|--------------------|------------|---------------------|