Finding the slowest SQL queries on IBM INFORMIX instance

Problem(Abstract)

The following query is to identify the 10 slowest SQL queries from syssqltrace. 

Note : SQLTRACE in $ONCONFIG need to be enabled in order to trace the SQL Queries.

Resolving the problem

To find the SQL Queries:

SELECT FIRST 10 * FROM sysmaster:syssqltrace  WHERE sql_runtime > 0  ORDER BY sql_runtime DESC; 

Then you can use the sql_id to query the sqltrace_iter tables to get the SQL statement iterators info. 

Syntax:
select * from sysmaster:syssqltrace_iter where sql_id = <sid> 

Example:
> select * from syssqltrace_iter where sql_id = 4; 

sql_id             4 
sql_address        504403159339495528 
sql_itr_address    504403159339512048 
sql_itr_id         1 
sql_itr_left       0 
sql_itr_right      0 
sql_itr_cost       9 
sql_itr_estrows    100 
sql_itr_numrows    1 
sql_itr_type       1 
sql_itr_misc       65672 
sql_itr_info       Seq Scan 
sql_itr_time       4.16157768e-05 
sql_itr_partnum    38 
sql_itr_sender     0 
sql_itr_nxtsender  0

http://www-01.ibm.com/support/docview.wss?uid=swg21637469