How to find resource intensive and time consuming queries in WX2?

Question

Is there a way to find the resource intensive and time consuming queries in WX2?

I tried to check SYS.IPE_COMMAND and SYS.IPE_TRANSACTION tables but of no help.

Answer 1

The best way to identify such queries when they are still running is to connect as SYS with Kognitio Console and use Tools | Identify Problem Queries. This runs a number of queries against Kognitio virtual tables to understand how long current queries have been running, how much RAM they are using, etc. The most intensive queries are at the top of the list, ranked by the final column, "Relative Severity".

For queries which ran in the past, you can look in IPE_COMMAND to see duration but only for non-SELECT queries - this is because SELECT queries default to only logging the DECLARE CURSOR statement, which basically just measures compile time rather than run time. To see details for SELECT queries you should join to IPE_TRANSACTION to find the start and end time for the transaction.

For non-SELECT queries, IPE_COMMAND contains a breakdown of the time taken in a number of columns (all times in ms):

• SM_TIME shows the compile time
• TM_TIME shows the interpreter time
• QUEUE_TIME shows the time the query was queued
• TOTAL_TIME aggregates the above information

If it is for historic view image commands as mentioned in the comments, you can query

... SYS.IPE_COMMAND WHERE COMMAND IMATCHING 'create view image' AND TOTAL_TIME > 300000"

If it is for currently running commands you can look in SYS.IPE_CURTRANS and join to IPE_TRANSACTION to find the start time of the transaction (assuming your CVI runs in its own transaction - if not, you will need to look in IPE_COMMAND to find when the last statement in this TNO completed and use that as the start time)