Finding the slowest SQL queries on IBM INFORMIX instance

Technote (troubleshooting)


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

Related information

A Japanese translation is available

Rate this page:

(0 users)Average rating

Add comments

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:

2013-05-17

Translate my page

Machine Translation

Content navigation