Identifying active queries

Moderator: NorbertKrupa

Post Reply
Timbo
Intermediate
Intermediate
Posts: 53
Joined: Thu Jun 21, 2012 11:05 am
Location: London, UK

Identifying active queries

Post by Timbo » Wed Sep 25, 2013 4:48 pm

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

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Identifying active queries

Post by JimKnicely » Wed Sep 25, 2013 6:19 pm

Hi,

Try:

Code: Select all

select statement_start, node_name, current_statement
  from sessions
 where current_statement <> '';
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Identifying active queries

Post by id10t » Wed Sep 25, 2013 9:07 pm

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
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

Post Reply

Return to “Vertica Performance Tuning”