IBM Support

Finding the slowest SQL queries on IBM INFORMIX instance

Technote (troubleshooting)


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.

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

> 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

Related information

A Japanese translation is available

Document information

More support for: Informix Servers

Software version: 11.5, 11.7, 12.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Software edition: Developer, Enterprise, Growth, Innovator, Ultimate, Workgroup

Reference #: 1637469

Modified date: 04 May 2017

Translate this page: