Page 1 of 1
Finding queries that are executed many times
Posted: Thu Mar 06, 2014 5:25 pm
by hopewell
Hello,
Is there a simple way in Vertica to get a list of the queries that get executed over and over again? I guess what I need is a list of queries and the number of times they've been executed per day. I'm trying to identify which queries are being used the most and target them for tuning if needed.
Thanks in advance.
Re: Finding queries that are executed many times
Posted: Thu Mar 06, 2014 5:48 pm
by NorbertKrupa
Could be as simple as:
Code: Select all
SELECT request, COUNT(*)
FROM v_monitor.query_requests
GROUP BY request
ORDER BY COUNT(*) DESC
LIMIT 10;
You could expand that to exclude certain request types, average request duration, etc.
Re: Finding queries that are executed many times
Posted: Fri Mar 07, 2014 2:31 am
by scutter
The “problem” with a simple count(*) like this is that it doesn’t account for queries that are essentially the same but have different predicates. I’ve always been interested in being able to easily lump queries together that are the same but with different date predicates for example, but I’ve never found an easy way to do that across the board for all queries. Ideas anyone?
—Sharon
Re: Finding queries that are executed many times
Posted: Fri Mar 07, 2014 2:54 am
by NorbertKrupa
Sounds like a challenge; perhaps could be done with a UDF. I usually just fetch the LEFT 100 characters of the request
I wonder if it would just be possible to compare requests based on their profiles.
Re: Finding queries that are executed many times
Posted: Fri Mar 07, 2014 3:57 am
by scutter
Query labels are one possibility for situations in which people have control over the sql that is generated, but that doesn’t help with most tools that generate sql for you….