Hi,
What is the best way of identify currently active queries?
The following appears to give the correct information, is there a different/better way?
SELECT query_start, node_name, query FROM QUERY_PROFILES
WHERE IS_EXECUTING and query not like '%autocommit%';
The following should work, but the current_statement is never “null”, the manual says that the current_statement field will be “null” if there is nothing running. This just lists out all sessions:-
select statement_start, node_name, current_statement from sessions
where current_statement is not null;
Thanks
Tim
Identifying active queries
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Identifying active queries
Hi,
Try:
Try:
Code: Select all
select statement_start, node_name, current_statement
from sessions
where current_statement <> '';
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Identifying active queries
Hi!
1. For table QUERY_PROFILES you need to enable query profiling
https://my.vertica.com/docs/6.1.x/HTML/ ... #18383.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #14370.htm
2. If you don't need profiling and just want to know what is running now, so use in QUERY_REQUESTS table in the same manner like QUERY_PROFILES.
https://my.vertica.com/docs/6.1.x/HTML/ ... #17581.htm
3. If you have Vertica 6.1.2 and above you can use in QUERY_PLAN_PROFILES (IMHO: very useful table)
https://my.vertica.com/docs/6.1.x/HTML/ ... #19779.htm
4. Read documentation: there are described other options and provided some example scripts.
https://my.vertica.com/docs/6.1.x/HTML/ ... #14311.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #14310.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #14312.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #19805.htm
1. For table QUERY_PROFILES you need to enable query profiling
https://my.vertica.com/docs/6.1.x/HTML/ ... #18383.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #14370.htm
2. If you don't need profiling and just want to know what is running now, so use in QUERY_REQUESTS table in the same manner like QUERY_PROFILES.
https://my.vertica.com/docs/6.1.x/HTML/ ... #17581.htm
3. If you have Vertica 6.1.2 and above you can use in QUERY_PLAN_PROFILES (IMHO: very useful table)
https://my.vertica.com/docs/6.1.x/HTML/ ... #19779.htm
QUERY_PLAN_PROFILES
Provides detailed execution status for queries that are currently running in the system. Output from the table shows the real-time flow of data and the time and resources consumed for each path in each query plan.
4. Read documentation: there are described other options and provided some example scripts.
https://my.vertica.com/docs/6.1.x/HTML/ ... #14311.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #14310.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #14312.htm
https://my.vertica.com/docs/6.1.x/HTML/ ... #19805.htm