| title | Query Analyzer Commands |
|---|---|
| description | These commands are used to analyze the performance of queries executed on the server. |
| category | reference |
| status | publish |
| slug | query-analyzer-commands |
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.
ANALYZER PLAN ID query_id [NODE nodeid]
DBADMIN
A Rowset with an analysis about the query id.
> ANALYZER PLAN ID 57
----|--------|---------|----------------|
id | parent | notused | detail |
----|--------|---------|----------------|
2 | 0 | 0 | SCAN customers |
----|--------|---------|----------------|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.
ANALYZER RESET [ID query_id] [GROUPID query_id] [DATABASE database_name] [ALL] [NODE nodeid]
DBADMIN
OK string or error value (see SCSP protocol).
> ANALYZER RESET
OKThe 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.
ANALYZER SUGGEST ID query_id [PERCENTAGE percentage] [APPLY] [NODE nodeid]
DBADMIN
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
> 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 |
----------|-------|----------------------------------------------------------------------------------------------------------------------------------------------|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.
LIST ANALYZER [GROUPID group_id] [DATABASE database_name] [GROUPED] [NODE nodeid]
DBADMIN
A Rowset with columns that depend on the command flags.
> 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 |
----|------------------------|--------------------|------------|---------------------|