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" 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.
"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.