Workload management functions

• 1: ANALYZE_WORKLOAD
• 2: CHANGE_CURRENT_STATEMENT_RUNTIME_PRIORITY
• 3: CHANGE_RUNTIME_PRIORITY
• 4: MOVE_STATEMENT_TO_RESOURCE_POOL
• 5: SLEEP

1 - ANALYZE_WORKLOAD

Runs Workload Analyzer, a utility that analyzes system information held in system tables.

Workload Analyzer intelligently monitors the performance of SQL queries and workload history, resources, and configurations to identify the root causes for poor query performance. ANALYZE_WORKLOAD returns tuning recommendations for all events within the scope and time that you specify, from system table TUNING_RECOMMENDATIONS.

Tuning recommendations are based on a combination of statistics, system and data collector events, and database-table-projection design. Workload Analyzer recommendations can help you quickly and easily tune query performance.

See Workload analyzer recommendations for the common triggering conditions and recommendations.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

ANALYZE_WORKLOAD ( '[ scope ]' [, 'since-time' | save-data ] );

Parameters

scope

Specifies the catalog objects to analyze, as follows:

[[database.]schema.]table

If set to an empty string, Vertica returns recommendations for all database objects.

If you specify a database, it must be the current database.

since-time

Specifies the start time for the analysis time span, which continues up to the current system status, inclusive. If you omit this parameter, ANALYZE_WORKLOAD returns recommendations on events since the last time you called this function.

Note

You must explicitly cast strings to TIMESTAMP or TIMESTAMPTZ. For example:

SELECT ANALYZE_WORKLOAD('T1', '2010-10-04 11:18:15'::TIMESTAMPTZ);
SELECT ANALYZE_WORKLOAD('T1', TIMESTAMPTZ '2010-10-04 11:18:15');

save-data

Specifies whether to save returned values from ANALYZE_WORKLOAD:

• false (default): Results are discarded.

• true: Saves the results returned by ANALYZE_WORKLOAD. Subsequent calls to ANALYZE_WORKLOAD return results that start from the last invocation when results were saved. Object events preceding that invocation are ignored.

Return values

Returns aggregated tuning recommendations from TUNING_RECOMMENDATIONS.

Privileges

Superuser

Examples

See Getting tuning recommendations.

See also

• Analyzing workloads
• Workload analyzer recommendations

2 - CHANGE_CURRENT_STATEMENT_RUNTIME_PRIORITY

Changes the run-time priority of an active query.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

CHANGE_CURRENT_STATEMENT_RUNTIME_PRIORITY(transaction-id, 'value')

Parameters

transaction-id

Identifies the transaction, obtained from the system table SESSIONS.

*value*

The RUNTIMEPRIORITY value: HIGH, MEDIUM, or LOW.

Privileges

• Superuser: None

• Non-superusers can only change the runtime priority of their own queries, and cannot raise the runtime priority of a query to a level higher than that of the resource pool.

Examples

See Changing runtime priority of a running query.

3 - CHANGE_RUNTIME_PRIORITY

Changes the run-time priority of a query that is actively running. Note that, while this function is still valid, you should instead use CHANGE_CURRENT_STATEMENT_RUNTIME_PRIORITY to change run-time priority. CHANGE_RUNTIME_PRIORITY will be deprecated in a future release of Vertica.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

CHANGE_RUNTIME_PRIORITY(TRANSACTION_ID,STATEMENT_ID, 'value')

Parameters

TRANSACTION_ID

An identifier for the transaction within the session.

TRANSACTION_ID cannot be NULL.

You can find the transaction ID in the Sessions table.

STATEMENT_ID

A unique numeric ID assigned by the Vertica catalog, which identifies the currently executing statement.

You can find the statement ID in the Sessions table.

You can specify NULL to change the run-time priority of the currently running query within the transaction.

'value'

The RUNTIMEPRIORITY value. Can be HIGH, MEDIUM, or LOW.

Privileges

No special privileges required. However, non-superusers can change the run-time priority of their own queries only. In addition, non-superusers can never raise the run-time priority of a query to a level higher than that of the resource pool.

Examples

=> SELECT CHANGE_RUNTIME_PRIORITY(45035996273705748, NULL, 'low');

4 - MOVE_STATEMENT_TO_RESOURCE_POOL

Attempts to move the specified query to the specified target pool.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

MOVE_STATEMENT_TO_RESOURCE_POOL (session_id , transaction_id, statement_id, target_resource_pool_name)

Parameters

session_id

Identifier for the session where the query you want to move is currently executing.

transaction_id

Identifier for the transaction within the session.

statement_id

Unique numeric ID for the statement you want to move.

target_resource_pool_name

Name of the existing resource pool to which you want to move the specified query.

Outputs

The function may return the following results:

Privileges

Superuser

Examples

The following example shows how you can move a specific statement to a resource pool called my_target_pool:

=> SELECT MOVE_STATEMENT_TO_RESOURCE_POOL ('v_vmart_node0001.example.-31427:0x82fbm', 45035996273711993, 1, 'my_target_pool');

See also:

• Manually moving queries to different resource pools
• RESOURCE_POOL_MOVE

5 - SLEEP

Waits a specified number of seconds before executing another statement or command.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Syntax

SLEEP( seconds )

Parameters

seconds

The wait time, specified in one or more seconds (0 or higher) expressed as a positive integer. Single quotes are optional; for example, SLEEP(3) is the same as SLEEP('3').

Notes

• This function returns value 0 when successful; otherwise it returns an error message due to syntax errors.

• You cannot cancel a sleep operation.

• Be cautious when using SLEEP() in an environment with shared resources, such as in combination with transactions that take exclusive locks.

Examples

The following command suspends execution for 100 seconds:

=> SELECT SLEEP(100);
 sleep
-------
     0
(1 row)