Display Database Statement Counts by Type

Posted April 9, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

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

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


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/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/QUERY_PROFILES.htm

Have fun!