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