Service class resource shares

A resource share is a numeric attribute that represents the relative resource entitlement of a service class compared to other service classes in the database. Resource shares are used compute a target percentage resource entitlement, and the adaptive workload manager schedules work to try to achieve the configured entitlements over time.

For example, suppose there are two service classes, A and B, and that A has a 75% resource entitlement and B has the remaining 25%. If all incoming queries had the same resource requirements and runtime, the adaptive workload manager would admit 3 queries in service class A for every 1 query it admitted into service class B. On the other hand, if every query in service class A required 3x the resources of a query in service class B, the adaptive workload manager would admit 1 query in service class A for every query admitted into service class B. In both cases, the resource balance of 75/25% is maintained. Query resource usage in real-world scenarios is not so trivial or predictable, but the adaptive workload manager attempts to admit work such that the configured resource entitlements are respected. This results in varying degrees of concurrency based on the resource requirements of incoming queries.

Only service classes that are accessible have a resource entitlement and participate in the resource entitlement computations.

A service superclass is accessible if both the following conditions are met:
- At least one enabled WORKLOAD object points to the superclass or one of its child subclasses
- The service superclass is enabled.
A service subclass is accessible if its parent superclass is accessible and it is enabled.

The default maintenance service class (SYSDEFAULTMAINTENANCECLASS) and default system service class (SYSDEFAULTSYSTEMCLASS) have no resource entitlement. Their work is not controlled by the adaptive workload manager.

To determine the resource entitlement for a service superclass, use the following formula:

% resource entitlement (Superclass) = (Number of superclass shares / Total number of superclass shares for all accessible superclasses in the database) x 100

For example, suppose a database has the following 3 service superclasses:

Service class A has 5000 resource shares.
Service class B has 4000 resource shares.
Service class C has 1000 resource shares.

The resource entitlements for these superclasses are computed as:

Service class A: ( 5000 / (5000 + 4000 + 1000) ) x 100 = 50% 
Service class B: ( 4000 / (5000 + 4000 + 1000) ) x 100  = 40%
Service class C: ( 1000 / (5000 + 4000 + 1000) ) x 100 = 10%

The target resource entitlement for a service superclass is subdivided across its subclasses based on their resource shares. To determine the resource entitlement for a service subclass, use the following formula:

% resource entitlement (Subclass) = % resource entitlement (Superclass) x (Number of subclass shares / Total number of subclass shares for all accessible subclasses in the superclass)

For example, suppose that service class A from the prior example has the following 2 subclasses:

Subclass SUB1 has 4000 resource shares
Subclass SUB2 has 1000 resource shares

The resource entitlements for these subclasses are computed as:

Subclass SUB1: 50 x ( 4000 / (4000 + 1000) ) = 40%
Subclass SUB2: 50 x ( 1000 / (4000 + 1000) ) = 10%

Resource shares are configured using the RESOURCE SHARES clause on a CREATE or ALTER SERVICE CLASS statement. By default, a service class is assigned 1000 resource shares if a value is not explicitly provided at creation time. Note that the default user service class, SYSDEFAULTUSERCLASS, has 1000 resource shares. This can be changed as needed using the ALTER SERVICE CLASS statement.

Resource shares can be further qualified as hard or soft shares using the HARD/SOFT modifier on the RESOURCE SHARES clause of a CREATE or ALTER SERVICE CLASS statement. When soft shares are used, the adaptive workload manager treats the target entitlement as a soft limit. The adaptive workload manager schedules work that might consume more resources than the target entitlement if there is spare capacity available on the database. When hard shares are used, the adaptive workload manager treats the target entitlement as a hard limit. The adaptive workload manager does not admit work that results in the service class exceeding its target resource entitlement unless a query requires more resources than the service class entitlement in order to execute. In this case, the service class is allowed to exceed the target entitlement temporarily to allow the query to execute and avoid starvation. Work within the service class is drained out before the query is allowed to execute to adhere to the resource entitlement as closely as possible.

For example, consider two service classes A and B with 75% and 25% resource entitlements respectively. Suppose service class A has a soft entitlement. Further suppose that there is no work running in service class B and 4 queries arrive in A with resource requirements of 20% each. The adaptive workload manager allows all 4 queries to execute immediately even though the sum of the resource requirements (20% x 4 = 80%) exceeds the resource entitlement for A because A has a soft entitlement and there is spare capacity (25%) available.

Now consider the same example, but service class A has a hard entitlement. In this case, only the first 3 queries would be admitted. The 4th query would be queued, because allowing it to execute would exceed the 75% hard limit.

Finally consider a case where the 4th query requires 80% of the resources rather than 20%, and A still has the hard entitlement. In this case the 4th query would queue after the first 3 queries are admitted because allowing the 4th query to execute would exceed the 75% hard entitlement. After the first 3 queries complete, the 4th query is allowed to enter and temporarily overflow the hard entitlement to ensure that this query does not queue indefinitely.

Soft resource shares should be used for higher priority work where it is desirable that the work should be able to take advantage of any spare resource capacity available on the database. Hard resource shares should be used for lower priority work where it is desirable that the work stays within a predetermined resource limit to ensure spare resources remain available should any higher priority work enter the database.

The following simple scenarios show how the CREATE and ALTER SERVICE CLASS DDL statements can be used to set and modify RESOURCE SHARES for a service class.

Scenarios

Scenario 1: Create three service classes with an equal distribution of resources
To create service classes with an equal resource entitlement, assign them the same resource share value when creating them.
```
CREATE SERVICE CLASS SC1 SOFT RESOURCE SHARES 1000
CREATE SERVICE CLASS SC2 SOFT RESOURCE SHARES 1000
CREATE SERVICE CLASS SC3 SOFT RESOURCE SHARES 1000
```
Scenario 2: Create three service classes with a 50/30/20% subdivision of resources
To create service classes with specific resource entitlement, assign them resource share values that are proportional to the desired entitlement. In this example we can use 5000, 3000 and 2000
```
CREATE SERVICE CLASS SC1 SOFT RESOURCE SHARES 5000
CREATE SERVICE CLASS SC2 SOFT RESOURCE SHARES 3000
CREATE SERVICE CLASS SC3 SOFT RESOURCE SHARES 2000
```
Scenario 3: Create three service classes with an equal distribution of resources. Cap the resource usage of service class SC3 so that it cannot exceed its entitlement when there is spare capacity (that is, it must leave resources available for the other 2 service classes).
This is similar to Scenario 1, except HARD resource shares are used for service class SC3 to create a hard entitlement.
```
CREATE SERVICE CLASS SC1 SOFT RESOURCE SHARES 1000
CREATE SERVICE CLASS SC2 SOFT RESOURCE SHARES 1000
CREATE SERVICE CLASS SC3 HARD RESOURCE SHARES 1000
```
Scenario 4: Modify the resource entitlement for service class SC3 so that it can only use half as many resources as the other 2 service classes.
Continuing from Scenario 3, service classes SC1 and SC2 each have 1000 resource shares. Lower the resource shares for service class SC3 from 1000 to 500 so that its entitlement is 50% less than each of the other 2 service classes.
```
ALTER SERVICE CLASS SC3 HARD RESOURCE SHARES 500
```