Query History and System Queue

The tables below provide insight into queries compiled and executed. See also: timing

sys.querylog_catalog

nametypereferencesdescription
"id"OIDThe internal query identifier.
"owner"VARCHARsys.users.nameThe user defining it.
"defined"TIMESTAMPTime when the query was added to the catalog.
"query"CLOBThe complete SQL query statement.
"pipe"CLOBsys.optimizers.nameThe MAL optimizer pipeline.
"plan"CLOBThe MAL execution plan.
"mal"INTEGERSize of MAL plan
"optimize"BIGINTOptimization time in micro-seconds.

sys.querylog_calls

nametypereferencesdescription
"id"OIDsys.querylog_catalog.idReference to the querylog definition.
"start"TIMESTAMPTime the statement was started.
"stop"TIMESTAMPTime the statement was completely finished.
"arguments"CLOBThe actual call structure.
"tuples"BIGINTThe number of tuples in the result set
"run"BIGINTThe time spent (in usec) until the result export.
"ship"BIGINTThe time spent (in usec) to ship the result set.
"cpu"INTEGERThe average cpu load percentage during execution.
"io"INTEGERThe percentage time waiting for IO to finish.

sys.queyrlog_history

nametypereferencesdescription
"id"OIDsys.querylog_catalog.idReference to the querylog definition.
"owner"VARCHARsys.users.nameThe user defining it.
"defined"TIMESTAMPTime when the query was added to the catalog.
"query"CLOBThe complete SQL query statement.
"pipe"CLOBsys.optimizers.nameThe MAL optimizer pipeline.
"plan"CLOBThe MAL execution plan.
"mal"INTEGERSize of MAL plan
"optimize"BIGINTOptimization time in micro-seconds.
"start"TIMESTAMPTime the statement was started.
"stop"TIMESTAMPTime the statement was completely finished.
"arguments"CLOBThe actual call structure.
"tuples"BIGINTThe number of tuples in the result set
"run"BIGINTThe time spent (in usec) until the result export.
"ship"BIGINTThe time spent (in usec) to ship the result set.
"cpu"INTEGERThe average cpu load percentage during execution.
"io"INTEGERThe percentage time waiting for IO to finish.

sys.queue

nametypereferencesdescription
"tag"BIGINTUnique internal query call identifier.
"sessionid"INTEGERsys.sessions.sessionidThe internal session identifier.
"username"VARCHARsys.users.nameThe name of the user responsible for the call.
"started"TIMESTAMPThe date and time the query was started.
"status"VARCHARrunning or finished or paused.
"query"CLOBThe SQL query itself.
"finished"TIMESTAMPThe date and time the query finished, else null.
"workers"INTEGERThe number of worker threads based on history.
"memory"INTEGERThe 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.