Resource configuration for the default workload manager environment

By default, all user queries run in the SYSDEFAULTUSERCLASS service superclass, which has 1000 soft resource shares. As long as it is the only service superclass participating in resource based admission control, it is entitled to 100% of all the database resources.

If user-defined service superclasses are created, its resource entitlement is reduced. For example, if a user creates a second service superclass for user work with 2000 resource shares, the resource entitlement for SYSDEFAULTUSERCLASS would be 1000/(1000+2000)*100=33%.

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.

Figure 1. Workload distribution for the SYSDEFAULTUSERCLASS service superclass
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.

The division of queries of different sizes across different service subclasses allows for subdivision of database resources. The adaptive workload manager can perform more intelligent scheduling and thereby minimize the impact of long running queries on shorter queries. Similarly, the service classes running shorter queries have a predefined resource reserve to ensure a minimum level of responsiveness. The shortest queries also use a latency-based queue order to help prioritize interactive queries where an immediate response is required.

Table 1. Default resource shares, entitlements, minimum resource share, and queue order for each of the default service subclasses.
Subclass Name Resource Shares Minimum resource share Admission Queue Order Resource share type
SYSDEFAULTSUBCLASS 2500 20% of entitlement Latency SOFT
SYSMEDIUMSUBCLASS 2500 30% of entitlement FIFO SOFT
SYSCOMPLEXSUBCLASS 3500 0% FIFO SOFT
SYSLOADSUBCLASS 1500 0% FIFO SOFT

Because the resource share type for each of the default service subclasses is SOFT, when there is spare capacity available any of the service classes can make use of those available resources.

The resource shares and minimum resource share for the SYSDEFAULTUSERCLASS service superclass and any of its subclasses can be modified using the ALTER SERVICE CLASS DDL statement. Similarly, the admission queue order type for subclasses can be modified using the ALTER SERVICE CLASS DDL statement.

Examples

  • Example 1: To increase the number of resource shares assigned to the SYSDEFAULTUSERCLASS service superclass from 1000 to 2000, issue the following DDL statement:
    ALTER SERVICE CLASS SYSDEFAULTUSERCLASS RESOURCE SHARES 2000
  • Example 2: To change the minimum resource share on the SYSMEDIUMSUBCLASS subclass under the SYSDEFAULTUSERCLASS service superclass from 30% of resource entitlement to 0, issue the following DDL statement: 
    ALTER SERVICE CLASS SYSMEDIUMSUBCLASS UNDER SYSDEFAULTUSERCLASS MINIMUM RESOURCE SHARE 0 PERCENT