Query Progress

There are a few system tables you could check based on transaction ID and statement ID, for example:
resource_acquisitions (getting new resources?)
resource_queues (waiting for resources?)
execution_engine_profiles (what is it doing now?)
vs_plan_activities,vs_plan_activity_details (like EE profiles but finer-grained)

Hi Bryan_H, can you provide an example of using execution_engine_profiles? I'm looking at an example query and find there are ~32k rows related to it.
It's very difficult to determine what is going on.
Thanks

To debug the query which is active for so long is progressing at all or not you can try the below query.

-- 1. Are the running queries moving? (run twice, ~60 seconds apart)
SELECT q.session_id, q.transaction_id, q.statement_id, e.counter_name,
       SUM(e.counter_value) AS value, LEFT(q.request, 60) AS query
FROM execution_engine_profiles e
JOIN query_requests q USING (transaction_id, statement_id)
WHERE q.is_executing
  AND e.counter_name IN ('rows produced', 'execution time (us)', 'bytes received', 'bytes sent')
GROUP BY 1,2,3,4,6
ORDER BY 1,4;

-- 2. Stale connections (open transaction, nothing running)
SELECT session_id, user_name, client_hostname, transaction_start,
       LEFT(last_statement, 80) AS last_statement
FROM sessions
WHERE (current_statement IS NULL OR current_statement = '')
  AND transaction_id IS NOT NULL;

-- 3. Queries blocked by a lock
SELECT object_name, transaction_id, lock_mode, lock_scope, grant_timestamp,
       LEFT(transaction_description, 80) AS txn
FROM locks;

-- 4. End a confirmed stuck/stale session (agree with the owner first)
SELECT INTERRUPT_STATEMENT('<session_id>', <statement_id>);
SELECT CLOSE_SESSION('<session_id>');