IQ 15.4 - SP to find out long running SQL over 10 min

[former_member187856]

Is there any way to capture any query running against IQ longer than....let say 10 or 15 min? I can't seen to find the query execution time in the svrlog or iqmsg files. It would be nice to be able to such monitoring in place to capture bad or long running query early from the server side.

Does this monitored SQL make sense here?

select T.Userid, T.ConnHandle, C.ConnCreateTime, C.LastReqTime, C.IQCmdType, datediff(minute, LastReqTime, now()) as elapsed

from sp_iqtransaction() T

join sp_iqconnection() C on T.state != 'COMMITTED'

-- and C.ReqType = 'FETCH'

and T.ConnHandle = C.ConnHandle

and (C.IQCmdType not like 'IQU%' and C.IQCmdType != 'NONE')

and datediff( minute, LastReqTime, now()) > 10

And how do we know this is report select SQL or batch (insert, update, delete)?

[tayeb_hadjou]

Enabling sql request logging could help. However this will capture all queries. You need then filter and keep aside only info related to long running queries.

call sa_get_request_times('/mytraces/iq_zr.log');

select * from satmp_request_time

where millisecs >= 600000

order by millisecs desc;

Logging Server Requests

Regards,

[markmumy]

Yes! 

QUERY_PLAN_MIN_TIME Option

QUERY_PLAN_MIN_TIME Option

Specifies a threshold for query execution. The post-query plan is generated only if query execution time exceeds the threshold.

Allowed Values

Integer, in milliseconds.

Default

0

Scope

Option can be set at the database (PUBLIC) or user level. When set at the database level, the value becomes the default for any new user, but has no impact on existing users. When set at the user level, overrides the PUBLIC value for that user only. No system privilege is required to set option for self. System privilege is required to set at database level or at user level for any user other than self.

Requires the SET ANY PUBLIC OPTION system privilege to set this option. Can be set temporary for an individual connection or for the PUBLIC role. Takes effect immediately.

Remarks

A query with a very short execution time (a micro query) executes faster if a query plan is not generated. This option can be set to avoid the generation of query plans, and the associated query plan generation costs for these queries. The QUERY_PLAN_MIN_TIME option is ignored unless the following options are also set:

• QUERY_PLAN = ON or QUERY_PLAN_AS_HTML = ON
• QUERY_PLAN_AFTER_RUN = ON
• QUERY_TIMING = ON

When these options are set, setting a QUERY_PLAN_MIN_TIME query execution threshold prevents the generation of query plans for queries whose execution times do not exceed the specified threshold.

[former_member187856]

That is a new feature for IQ 16, not for IQ 15.4.

So this will write out query plan to srvlog, if the sql run exceeds the QUERY_PLAN_MIN_TIME setting.

Most of long run sql is from Cognos server, are you suggesting we set this in session level or server-wide?

[markmumy]

When you move to IQ 16 (15.4 is EOL/out of support in June/July), set it server wide if you wish or at least for the user that the Cognos users are coming in from.

We brought this into IQ for precisely your use case.

In IQ 15.4, there's no way to do this other than always generate query plans and then delete the ones with a runtime that is below a threshold.  You cannot grab a query plan from another connection, unfortunately, so it has to be turned on before the query began.

Mark

[former_member187856]

We are waiting for couple CR fixed 1st before upgrade DEV server to IQ 16.

Loading gz data file, IQ 16 is way much slower than IQ 15.4.

Actually, we do not need the query plan to generate (write to srvlog); what we want is to capture long running SQL here.

So my post monitored sql, can do the silly trick or not in IQ 15.4?