Customize Saving SQLTRACE Data - Long running SQL's

jfilippi's picture

​Here is a way to save SQLTRACE information, but to only save SQL statement that run greater than "X" number of seconds.

This way you can look at your slowest SQL to look at tuning it.

​You will be implementing a task in the SYSADMIN database and creating the table in that database also.

​Below is create table statement, you can size the column "sql_statement" to what you think is the largest SQL you will have.

 

 

create raw table "informix".save_sqltrace

  (

    date_time datetime year to second,

    sql_id int8,

    sql_runtime float,

    sql_sid int8,

    sql_uid int8,

    sql_statement char(2000),

    sql_database char(30)

  ) in sqltrace  extent size 99996 next size 99996 lock mode row;

 

create index "informix".idx_savesql1 on "informix".save_sqltrace    (date_time) using btree  in sqltrace;

create index "informix".idx_savesql2 on "informix".save_sqltrace    (sql_runtime) using btree  in sqltrace;

create index "informix".idx_savesql3 on "informix".save_sqltrace    (sql_id) using btree  in sqltrace;

 

NOTE: Create a new dbspace so that if the table fills up the dbspace it does not affect any other process.

Then insert the row below into the “ph_task” table.  This will run the task every minute.

 

0|save_trace|Saves SQL Trace when run time greater than set value.|TASK|9251|||sysadmin|insert into save_sqltrace select current, sql_id, sql_runtime, sql_finishtime, sql_sid, sql_uid, sql_statement, sql_database from sysmaster:syssqltrace where (sql_runtime > 5 and (sql_finishtime > (select max(sql_finishtime) from save_sqltrace)) or (sql_runtime > 5 and ((select count(*) from save_sqltrace) = 0)))| 30 00:00:00|00:00:00||  0 00:01:00|2017-07-18  14:54:17|9237|0|t|t|t|t|t|t|t|400|PERFORMANCE|t|0|