Query History and System Queue
The tables below provide insight into queries compiled and executed.
See also: timing
sys.querylog_catalog
| name | type | references | description |
|---|
| "id" | OID | | The internal query identifier. |
| "owner" | VARCHAR | sys.users.name | The user defining it. |
| "defined" | TIMESTAMP | | Time when the query was added to the catalog. |
| "query" | CLOB | | The complete SQL query statement. |
| "pipe" | CLOB | sys.optimizers.name | The MAL optimizer pipeline. |
| "plan" | CLOB | | The MAL execution plan. |
| "mal" | INTEGER | | Size of MAL plan |
| "optimize" | BIGINT | | Optimization time in micro-seconds. |
sys.querylog_calls
| name | type | references | description |
|---|
| "id" | OID | sys.querylog_catalog.id | Reference to the querylog definition. |
| "start" | TIMESTAMP | | Time the statement was started. |
| "stop" | TIMESTAMP | | Time the statement was completely finished. |
| "arguments" | CLOB | | The actual call structure. |
| "tuples" | BIGINT | | The number of tuples in the result set |
| "run" | BIGINT | | The time spent (in usec) until the result export. |
| "ship" | BIGINT | | The time spent (in usec) to ship the result set. |
| "cpu" | INTEGER | | The average cpu load percentage during execution. |
| "io" | INTEGER | | The percentage time waiting for IO to finish. |
sys.querylog_history
| name | type | references | description |
|---|
| "id" | OID | sys.querylog_catalog.id | Reference to the querylog definition. |
| "owner" | VARCHAR | sys.users.name | The user defining it. |
| "defined" | TIMESTAMP | | Time when the query was added to the catalog. |
| "query" | CLOB | | The complete SQL query statement. |
| "pipe" | CLOB | sys.optimizers.name | The MAL optimizer pipeline. |
| "plan" | CLOB | | The MAL execution plan. |
| "mal" | INTEGER | | Size of MAL plan |
| "optimize" | BIGINT | | Optimization time in micro-seconds. |
| "start" | TIMESTAMP | | Time the statement was started. |
| "stop" | TIMESTAMP | | Time the statement was completely finished. |
| "arguments" | CLOB | | The actual call structure. |
| "tuples" | BIGINT | | The number of tuples in the result set |
| "run" | BIGINT | | The time spent (in usec) until the result export. |
| "ship" | BIGINT | | The time spent (in usec) to ship the result set. |
| "cpu" | INTEGER | | The average cpu load percentage during execution. |
| "io" | INTEGER | | The percentage time waiting for IO to finish. |
sys.queue
| name | type | references | description |
|---|
| "tag" | BIGINT | | Unique internal query call identifier. |
| "sessionid" | INTEGER | sys.sessions.sessionid | The internal session identifier. |
| "username" | VARCHAR | sys.users.name | The name of the user responsible for the call. |
| "started" | TIMESTAMP | | The date and time the query was started. |
| "status" | VARCHAR | | running or finished or paused. |
| "query" | CLOB | | The SQL query itself. |
| "finished" | TIMESTAMP | | The date and time the query finished, else null. |
| "maxworkers" | INTEGER | | The maximum number of worker threads available. |
| "footprint" | INTEGER | | The amount of memory used in MB based on history. |
The sys.queue structure has been enhanced from release Jun2020 (11.37.7) onwards. Older versions will show less and some different columns.