Skip to main content

TRACE SQL STMT

A performance trace can be obtained using the TRACE statement modifier. It collects all the trace information in a table reproduced by tracelog(), which can be queried using ordinary SQL statements. The internal trace table is reset at each query being traced. Its definition is given below:

create function sys.tracelog()
    returns table (
        event integer,      -- event counter
        clk varchar(20),    -- wallclock, no mtime in kernel
        pc varchar(50),     -- module.function[nr]
        thread int,         -- thread identifier
        ticks bigint,       -- time in microseconds
        rrsMB bigint,       -- resident memory in MB
        vmMB bigint,        -- virtual size in MB
        reads bigint,       -- number of blocks read
        writes bigint,      -- number of blocks written
        minflt bigint,      -- minor page faults
        majflt bigint,      -- major page faults
        nvcsw bigint,       -- non-volantary context switch
        stmt string         -- actual statement executed
    )
    external name sql.dump_trace;

  Field Description Example
1 event An event counter 38
2 clk The wall-clock time in microseconds "13:11:16.710180",
3 pc Name of the query execution plan name; followed by the program counter in the execution plan (denoted in the square brackets ‘[‘ and ‘]’) indicating the position of the MAL instruction in its defining block; and finally a unique call identifier to distinguish recursion. "user.s5_1[14]12",
4 thread Id of the worker thread processing this MAL instruction. 3
5 usec The actual execution time (at "done") for the MAL instruction, measured in microseconds 207,
6 rssMB Memory Resident Set Size (RSS), i.e., the portion of memory occupied by a process that is held in main memory, in MB. 54
7 vmMB Estimated cumulative query plan footprint, in MB. For a query, the maximal value of “tmpspace” gives a fairly good estimation of the memory consumption of this query. 0,
8 reads The number of disk blocks read ** 0,
9 writes The number of disk blocks written ** 0,
10 majflt The number of major page faults 0,
11 switch The number of context switches 0,
12 stmt The MAL statement being executed, with argument statistics "sql.exportResult(X_21=\"104d2\":streams,X_16=4:int);”

 

* In addition, the “state” field denotes two special events. The “wait” event is received when the worker threads cannot find an eligible MAL instruction and have to wait for other worker threads to deliver results. The “ping” event provides a synopsis of the CPU processing loads.

** Please be aware that these values reflect system wide activities, thus they include not only MonetDB activities. Additionally, these I/O counts do not reflect the actual amount of data read/written from/to the hardware drives, as this information is generally not available to all users of a Linux system.