Resource configuration for a MIXED workload type service superclass

MIXED service superclasses are pre-configured to handle activities of varying types and complexity. Different service subclasses are used for different sizes of queries. Resources are reserved for smaller queries.

To create a service superclass for a mixed workload, issue the following statement:
CREATE SERVICE CLASS superclass_name FOR WORKLOAD TYPE MIXED
For the MIXED workload type service superclass, the following objects are created:
SYSDEFAULTSUBCLASS
A service subclass used for execution of DML activities with estimated runtime in the range 0 to 30 seconds.
MEDIUMSUBCLASS
A service subclass used for execution of DML activities with estimated runtime in the range 30 seconds to 10 minutes
COMPLEXSUBCLASS
A service subclass used for execution of DML activities with estimated runtime > 10 minutes.
LOADSUBCLASS
A service subclass for load
superclass_name_MIXEDWCS
A work class set used to classify queries as short (SIMPLEDML work class), medium (MEDIUMDML work class) or long (COMPLEXDML work class) based on estimated runtime, or as load (LOAD work class) activities
superclass_name_MIXEDWAS
A work action set used to map queries based on their runtime classification service classes for short (MAPSIMPLEDML work action), medium (MAPMEDIUMDML work action), long (MAPCOMPLEXDML work action) queries or load (MAPLOAD work action) activities.
superclass_name_REMAPTOMEDIUM
A remap threshold used to map queries running longer than 30 seconds from the short service subclass to the medium service subclass
superclass_name_REMAPTOCOMPLEX
A remap threshold used to map queries running longer than 10 minutes from the medium service subclass to the complex service subclass.

Classification of queries is performed by work classes in the SYSDEFAULTUSERWCS work class set.

Figure 1. Workload distribution for a superclass for a MIXED workload
Queries for load jobs are routed to SYSLOADSUBCLASS. Queries with estimated run times from 0 to 30 seconds are routed to SYSDEFAULTSUBCLASS. Queries with estimated run times from 30 seconds to 10 minutes are routed to SYSMEDIUMSUBCLASS. Queries with estimated run times greater than 10 minutes are routed to SYSCOMPLEXSUBCLASS. If a query's actual runtime exceeds the maximum run time for the group into which it was placed initially, it is remapped to the next group.
Table 1. Default resource shares, entitlements, minimum resource share, and queue order for each of the child service subclasses that are created.
Subclass Name Resource Shares Minimum resource share Admission Queue Order Resource share type
SYSDEFAULTSUBCLASS 2500 20% of entitlement Latency SOFT
MEDIUMSUBCLASS 2500 30% of entitlement FIFO SOFT
COMPLEXSUBCLASS 3500 0% FIFO SOFT
LOADSUBCLASS 1500 0% FIFO SOFT