Workload Management and Service Classes

Service classes define limits on groups in the System. You can design service classes using these limits. Use these parameters to control the limits.

Parameter	Description	Values
`MAX_TEMP_DISK_USAGE`	Limit the percentage of temporary disk space used by the total service class relative to the amount of remaining free space. If all running queries for a particular service class exceed the given percentage, queries fail.	A percentage integer from 0-100.
`MAX_ELAPSED_TIME`	Limit query runtime. The database process kills any queries that exceed this limit.	-1 for unlimited, or a positive integer for the number of seconds.
`MAX_CONCURRENT_QUERIES`	Limit the maximum number of queries that can run concurrently for a given service class. The database process queues additional queries.	-1 for unlimited, or a positive integer for the number of queries.
`MAX_ROWS_RETURNED`	Limit number of rows returned by a query. The database process kills queries that exceed this limit.	-1 for unlimited, or a positive integer for the number of rows.
`MINIMIZE_QUERY_DEBUG_RECORDS`	Stop the load of the `sys.completed_operator_instances` system catalog table and the generation of specific debug logs associated with the query.	Boolean `true` for minimizing the capture of metadata and logs, and `false` or unset (default) for capturing this data.
`SCHEDULING_PRIORITY`	Limit initial effective query priority.	Any decimal value between the `MIN_PRIORITY` and `MAX_PRIORITY`.
`CACHE_MAX_BYTES`	Maximum number of bytes in a result set if the result set can be stored in the cache. This value is the number of bytes in the wire-protocol representation, or what is sent to a client.	-1 for none, or a positive integer for the number of bytes.
`CACHE_MAX_TIME`	Maximum time rows are cached.	-1 for none, or a positive integer for the number of seconds.
`MAX_ELAPSED_TIME_FOR_CACHING`	Maximum elapsed time for caching. If you set this parameter, queries that exceed maximum elapsed time return no results, but continue to run in the background until either the query reaches the maximum elapsed time for caching or the query successfully completes and caches the result set. For details, see documentation.	-1 for none, or a positive integer for the number of seconds.
`MAX_COLUMNS_IN_RESULT_SET`	Limit number of columns returned by a query. The database process kills queries that exceed this limit.	-1 for unlimited, or a positive integer for the number of columns.
`PRIORITY_ADJUSTMENT_FACTOR`	Percentage amount to adjust query priority. You can override this limit at the query level or the session level. Formula is: `(new_priority = current_priority * priority_adjustment_factor)`	0 to disable dynamic priority adjustment (default), or a positive double value that is greater than 0. Values less than 1 decrease priority over time until the priority reaches the service class minimum priority, while values greater than 1 increase priority over time until the priority reaches the service class maximum priority.
`PRIORITY_ADJUSTMENT_TIME`	Time period that indicates the frequency for the adjustment of query priority during execution of the query. You can override this limit at the query level or the session level.	0 to disable dynamic priority adjustment (default), or a positive integer (unsigned 32-bit integer) for the number of seconds that is greater than 0.
`MIN_PRIORITY`	Limit the minimum query priority. The current effective priority cannot be smaller than this value.	0 (default) or any decimal value (double) greater than 0.
`MAX_PRIORITY`	Limit the maximum query priority. The current effective priority cannot be greater than this value.	-1 (default) that indicates infinity or any decimal value (double) greater than 0.
`STATEMENT_TEXT`	The system uses the specified service class for any queries that match the specified text pattern. The system uses service classes with statement text before any service classes that lack statement text. If there are multiple service classes with statement text available, the system attempts to match them in alphabetical order. This matching allows you to set up service classes so that the system can assign certain query types automatically to higher-priority service classes.	A string that uses either LIKE or REGEX pattern matching. For example, `STATEMENT_TEXT REGEX ".my_table.\|.your_table."` would match any queries that include the strings `my_table` or `your_table`.
`LOW_LATENCY`	Optional settings that enable you to manage low-latency service classes.	Boolean `true` or `false`, or a Key-Value setting map.
`MEMORY_OPTIMAL_STRATEGY`	Direct the Ocient System to reduce query memory usage, even if the operation might increase execution time. Contact Ocient Support when you change this parameter.	Boolean `true` instructs the system to apply stricter scheduling and execution rules to minimize memory consumption and avoid excessive concurrent usage. Otherwise, setting this parameter to `false` instructs the system to disable such strategies, even if the service class default enables them. By default, this parameter is unset. If you leave this parameter unset, the system reverts to higher-level default behavior or other service class settings.

Service Class Setting Applicability

When a user is present in multiple groups with different service class limits, the system applies the least restrictive limit for each parameter. For example, if User A is in groups G1 and G2, which have service class time limits of 10 seconds and 20 seconds, when User A runs a query, the query has a time limit of 20 seconds. This behavior is slightly different for concurrency. If the service class concurrency limits for the groups the user belongs to are 1 and 2, a query can use a concurrency slot from either service class. A user with multiple service classes can experience a query executing with a slot from one service class but using a time limit or other limits from a different service class. Additionally, the system does not apply service class concurrency limits when a query affects only system tables.

Choose Parameter Values

If you set a service class at the session or query level, the system uses the value set by that service class, with the query-level service class taking precedence. Otherwise, if you set a value for this parameter at the session or query level, the system uses the most restrictive of the two. If the Ocient System chooses the value from multiple service classes, the system takes the least restrictive value from all service classes. Then, the system takes the most restrictive of that value and the query or session level value, if it exists.

This selection does not hold true for parameters related to result set caching: cache_max_time and cache_max_bytes. For details about the behavior, see Result Set Caching.

If you choose a service class using statement text matching, the system compares its value to the query or session level value, and the system does not review any other service classes.

Next Steps

A tutorial for designing a set of service classes for workload management is available in the Workload Management Walkthrough. See Users, Groups, and Service Classes for service-class-related DDL statement references, such as creating, altering, and applying a service class. Data Definition Language (DDL) Statement Reference

​Service Class Setting Applicability

​Choose Parameter Values

​Next Steps

​Related Links

Service Class Setting Applicability

Choose Parameter Values

Next Steps

Related Links