Display Database Statement Counts by Type

Jim_KnicelyJim_Knicely Administrator
edited April 2019 in Tips from the Team

There are many types of statements that can be executed in Vertica. Examples include queries, DDL (Data Definition Language) and utility.

To summarize the all of types of statements being executed, you can query the QUERY_PROFILES system table.

Example:

I'd like to view a count of each statement type across my cluster since the first of the month.

dbadmin=> SELECT query_type,
dbadmin->        COUNT(*)
dbadmin->   FROM query_profiles
dbadmin->  WHERE TRUNC(query_start::TIMESTAMP) >= DATE_TRUNC('MONTH', SYSDATE)
dbadmin->  GROUP BY 1
dbadmin->  ORDER BY 1;
query_type  |   COUNT
-------------+---------
DDL         |     195
LOAD        |     812
QUERY       |  512561
SET         |  924530
SHOW        |      58
TRANSACTION |     112
TRUNCATE    |      52
UTILITY     |     140
(8 rows)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_PROFILES.htm

Have fun!

Sign In or Register to comment.