Display Database Statement Counts by Type

Jim_Knicely · April 2019

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!