Enabling Profiling

You can enable profiling at three scopes: 

• Globally across all database sessions
• The current session
• Individual SQL statements

Vertica meta-function SHOW_PROFILING_CONFIG shows whether profiling is enabled at global and session scopes. In the following example, the function shows that profiling is disabled across all categories for the current session, and enabled globally across all categories:

=> SELECT SHOW_PROFILING_CONFIG();
SHOW_PROFILING_CONFIG
------------------------------------------
 Session Profiling: Session off, Global on
 EE Profiling:      Session off, Global on
 Query Profiling:   Session off, Global on
(1 row)

Global Profiling

When global profiling is enabled or disabled for a given category, that setting persists across all database sessions. You set global profiling with ALTER DATABASE, as follows:

ALTER DATABASE db-spec SET profiling-category = {0 | 1}

profiling-category specifies a profiling category with one of the following arguments:

For example, the following statement globally enables query profiling:

=> ALTER DATABASE DEFAULT SET GlobalQueryProfiling = 1;

Session Profiling

Session profiling can be enabled for the current session, and persists until you explicitly disable profiling, or the session ends. You set session profiling with the following Vertica meta-functions:

profiling-category specifies a profiling category with one of the following arguments:

For example, the following statement enables session-scoped profiling for the execution run of each query:

=> SELECT ENABLE_PROFILING('ee');
   ENABLE_PROFILING
----------------------
 EE Profiling Enabled
(1 row)

Statement Profiling

You can enable profiling for individual SQL statements by prefixing them with the keyword PROFILE. You can profile a SELECT statement, or any DML statement such as INSERT, UPDATE, COPY, and MERGE. For detailed information, see Profiling Single Statements.

Precedence of Profiling Scopes

Vertica checks session and query profiling at the following scopes in descending order of precedence:

1. Statement profiling (highest)
2. Sesssion profiling (ignored if global profiling is enabled)
3. Global profiling (lowest)

Regardless of query and session profiling settings, Vertica always saves a minimum amount of profiling data in the pertinent system tables: QUERY_PROFILES, QUERY_PLAN_PROFILES, and SESSION_PROFILES.

For execution engine profiling, Vertica first checks the setting of configuration parameter SaveDCEEProfileThresholdUS. If the query runs longer than the specified threshold (by default, 60 seconds), Vertica gathers execution engine data for that query and saves it to system tables QUERY_CONSUMPTION and EXECUTION_ENGINE_PROFILES. Vertica uses profiling settings of other scopes (statement, session, global) only if the query's duration is below the threshold.