QueryLog catalog, calls, history, queue

QueryLog catalog, calls, history, queue mk Fri, 03/29/2013 - 14:12

The tables below provide insight into queries compiled and executed. See also: /Documentation/ServerAdministration/QueryTiming/QueryHistory

sys.querylog_catalog
name type references description
id oid   The internal query identifier.
owner string sys.users.name The user defining it.
defined timestamp   Time when the query was added to the catalog.
query string   The complete SQL query statement.
pipe string sys.optimizers.name The MAL optimizer pipeline.
plan string   The MAL execution plan.
mal int   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 string   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 int   The average cpu load percentage during execution.
io int   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 string sys.users.name The user defining it.
defined timestamp   Time when the query was added to the catalog.
query string   The complete SQL query statement.
pipe string sys.optimizers.name The MAL optimizer pipeline.
plan string   The MAL execution plan.
mal int   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 string   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 int   The average cpu load percentage during execution.
io int   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 paused.
"query" VARCHAR   The SQL query itself.
"progress" INTEGER   Percentage of completion based on history.
"workers" INTEGER   The number of worker threads based on history.
"memory" INTEGER   The amount of memory used in MB based on history.

Note: the sys.queue structure has been enhanced from release Jun2020 (11.37.7) onwards. Older versions will show less and some different columns.