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.
Finding queries that are executed many times
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Finding queries that are executed many times
Could be as simple as:
You could expand that to exclude certain request types, average request duration, etc.
Code: Select all
SELECT request, COUNT(*)
FROM v_monitor.query_requests
GROUP BY request
ORDER BY COUNT(*) DESC
LIMIT 10;
Checkout vertica.tips for more Vertica resources.
Re: Finding queries that are executed many times
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
—Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Finding queries that are executed many times
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.
Checkout vertica.tips for more Vertica resources.
Re: Finding queries that are executed many times
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….
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC
Vertica Consultant, Zazz Technologies LLC