Finding queries that are executed many times

Moderator: NorbertKrupa

Post Reply
hopewell
Beginner
Beginner
Posts: 29
Joined: Wed Mar 27, 2013 10:39 pm

Finding queries that are executed many times

Post by hopewell » Thu Mar 06, 2014 5:25 pm

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.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Finding queries that are executed many times

Post by NorbertKrupa » Thu Mar 06, 2014 5:48 pm

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.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Finding queries that are executed many times

Post by scutter » Fri Mar 07, 2014 2:31 am

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 Cutter
Vertica Consultant, Zazz Technologies LLC

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Finding queries that are executed many times

Post by NorbertKrupa » Fri Mar 07, 2014 2:54 am

Sounds like a challenge; perhaps could be done with a UDF. I usually just fetch the LEFT 100 characters of the request :D I wonder if it would just be possible to compare requests based on their profiles.
Checkout vertica.tips for more Vertica resources.

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Finding queries that are executed many times

Post by scutter » Fri Mar 07, 2014 3:57 am

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

Post Reply

Return to “New to Vertica”