3.2.3 TRACE Statement

A performance trace can be obtained using the trace statement modifier. The default action is to collect all the trace information in a table produced by tracelog(), which can be queried using ordinary SQL statements. Its definition is given below:

create function 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
        "user" int,             -- client identifier
        ticks integer,      -- time in microseconds
        reads integer,      -- number of blocks read
        writes integer,     -- number of blocks written
        rbytes integer,     -- amount of bytes touched
        wbytes integer,     -- amount of bytes written
        type string,        -- return types
        stmt string         -- actual statement executed
    )
    external name sql.dump_trace;

By default the performance tables are reset at each query. They can be kept around by inclusion of the pseudo property 'keep' in the SQL trace variable.

The direct output of the trace can be controlled by setting the SQL 'trace' variable. It contains a list of properties to be displayed. The set consists of the attribute names of the tracelog() table. The output of trace can be inlined with the query using the property 'show'.

sql> set trace = 'show,pc,ticks';
sql> trace select count(*) from tables;
&1 0 1 1 1
% .tables # table_name
% L2 # name
% wrd # type
% 2 # length
[ 26	]
# Performance profiling stopped
#---------------------------------#
# t			t	  # name
# str			lng	  # type
#---------------------------------#
[ "user.s3_0[2]",	  15	  ]
[ "user.s3_0[3]",	  5	  ]
[ "user.s3_0[22]",	  5	  ]
 ...
[ "user.s3_0[69]",	  3	  ]
[ "user.s3_0[70]",	  38	  ]
[ "user.s3_0[71]",	  7	  ]
[ "user.main[4]",	  4690	  ]

>select event,pc,ticks,stmt from tracelog() where ticks >100;
&1 1 2 4 2
% .,	.,	.,	. # table_name
% event,	pc,	ticks,	stmt # name
% int,	varchar,	int,	clob # type
% 3,	12,	4,	35 # length
[ 130,	"user.s3_0[1]",	4093,	"barrier _99 := language.dataflow();"	]
[ 142,	"user.main[4]",	4690,	"user.s3_0();"	]