Query HistoryQuery History mk Sat, 03/09/2013 - 17:36
The SQL implementation comes with a simple query profiler to detect expensive queries. It is centered around two predefined internal tables that store the definitions of all executed SQL queries definitions and their execution time.
Query logging can be started by calling the procedure querylog_enable(), which saves some major compilation information of a query in the 'querylog_catalog' table:
pipe string, -- Query pipeline
"plan" string, -- Name of MAL plan
mal integer, -- size of MAL plan in number of statements
optimize bigint -- time in microseconds for optimizer pipeline
Query logging can be stoped by calling procedure querylog_disable().
The query performance is stored in the table 'querylog_calls'. The owner of the query definition is also the one that will be referenced implicitly from the call events. The key timing attributes are 'run', i.e. the time to prepare the result set , and 'ship', i.e. the time to render the result set and sent it to the client. All timing in microseconds.
The remaining parameters illustrate the resource claims. The 'tuples' attribute denotes the size of the result set in number of rows. The 'space' depicts the total size of all temporary columns created during query execution. Note, the space is allocated and freed during the query execution, leading to a much less demanding actual memory footprint. The 'cpu' load is derived from the operating system system statistics (Linux only) and is given as a percentage. The same holds for the io waiting time.
table sys.querylog_calls (
id oid, -- references query plan
"start" timestamp,-- time the statement was started
"stop" timestamp,-- time the statement was completely finished
arguments string, -- actual call structure
tuples bigint, -- number of tuples in the result set
run bigint, -- time spent (in usec) until the result export
ship bigint, -- time spent (in usec) to ship the result set
cpu int, -- average cpu load percentage during execution
io int -- percentage time waiting for IO to finish
create view sys.querylog_history as
select qd.*, ql."start",ql."stop", ql.arguments, ql.tuples, ql.run, ql.ship, ql.cpu, ql.space, ql.io
from sys.querylog_catalog() qd, sys.querylog_calls() ql
where qd.id = ql.id and qd.owner = user;
The following code snippet illustrates its use.
sql>select * from sys.querylog_catalog;
sql>select * from sys.querylog_calls;
sql>select * from sys.querylog_history;
sql>select id, query, avg(run) from sys.querylog_history group by id,query;