Service class minimum resource share

The minimum resource share for a service class is a percentage of the service class resource entitlement that is reserved for use by the service class.

Resources represented by the minimum resource share are available only to queries in that service class and are not considered as surplus or available to other service classes even when not in use. If no minimum resource share is configured for a service class, no resources are held in reserve for the service class. The minimum resource share is specified as a percentage of the target resource entitlement using the MINIMUM RESOURCE SHARE clause on the CREATE or ALTER SERVICE CLASS statement. Use a minimum resource share for high priority service classes to ensure a minimum amount of resources are available for work in the service class, thus guaranteeing a minimum level of throughput.

For example, suppose a service class A has a resource entitlement of 80% and a minimum resource share of 50% and a service class B has a soft resource entitlement of 20%. The adaptive workload manager reserves 80% x .5 = 40% of the database resources for service class A. Work admitted into service class B can exceed it’s 20% entitlement if there are resources available in the database because it has a soft entitlement. However, the work in B is not allowed to consume the 40% of resources held in reserve for service class A (that is, work in B is admitted using at most 60% of the database resources; leaving at least 40% available for A). Consider if no work is running in A and 3 queries arrive in B that each require 25% of the database resources. The first 2 queries are admitted for execution bringing the resource utilization to 50% for B. The third query in B is queued, because admitting this query would require service class B to access the resources reserved for service class A.

A minimum resource share can be specified on a service superclass or a service subclass, with behaviors as follows:
  • If a minimum resource share is specified on a service subclass, only queries in that subclass can access the reserved resources.
  • If a minimum resource share is specified on a service superclass, all subclasses under the service superclass can access the reserved resources.
  • If a minimum resource share is specified on both a service superclass and a service subclass under the service superclass, only the superclass reserves in excess of the subclass reserves are accessible to all other subclasses under that superclass.

For example, consider a configuration where a service superclass A has a 50% entitlement to database resources and a 50% reserve (that is, 25% of database resources are reserved for work in service class A). The superclass has 4 subclasses, B, C, D and E, each defined with 1000 shares, so having an equal entitlement of 12.5% apiece. Service class E has a minimum resource share of 50% meaning that it has 6.25% of the database resources reserved. In this example, only the portion of the superclass reserves that are not reserved by a specific subclass (here 25% - 6.25% = 18.75%) are available to all 4 service subclasses. The remaining 6.25% of the superclass reserve is wholly owned by service subclass E and cannot be accessed by the other subclasses.

Scenarios

The following scenarios show how the CREATE and ALTER SERVICE CLASS DDL statements can be used to set and modify the MINIMUM RESOURCE SHARE for a service class.
  • Scenario 1: Create two service classes with an equal distribution of resources. Reserve 50% of the entitlement for the first service class so that there are always some resources available for work in this service class.
    The two service classes are created with an identical share value so that they have the same resource entitlement. A MINIMUM RESOURCE SHARE of 50% is applied to service class SC1 to reserve half of its entitlement.
    CREATE SERVICE CLASS SC1 SOFT RESOURCE SHARES 1000 MINIMUM RESOURCE SHARE 50 PERCENT
CREATE SERVICE CLASS SC2 SOFT RESOURCE SHARES 1000
  • Scenario 2: Remove the reserved resources for service class SC1
    Continuing from scenario 1, alter the SC1 service class to remove the reserved resources that were originally defined by setting a MINIMUM RESOURCE SHARE of 0%.
    ALTER SERVICE CLASS SC1 MINIMUM RESOURCE SHARE 0 PERCENT