Concurrency control at the workload level using work action
sets
At the workload level, you control the concurrency of incoming
work using concurrency thresholds applied with a work action set defined
on the workload. You can combine workload-level concurrency control
with priority aging at the service class level.
Figure 1 illustrates an example scenario using work action
sets to control the concurrency of incoming work based on the source
of the connection while all work in the database is controlled using
priority aging.Figure 1. Concurrency control
at the workload level using work action sets
In the example scenario, two workloads are created to
identify and differentiate the work coming from different sources.
Connections to the database from the sales, accounting, and IT departments
are mapped to the Regular workload. Connections to the database from
management and critical applications are mapped to the Important workload.
Work from the Important workload has higher priority and needs to
be able to complete within the shortest amount of time. To ensure
the database has sufficient capacity for work in the Important workload,
concurrency thresholds are placed on the work in the Regular workload.
A workload level work action set, called Regular workload level work
action set, is created on the Regular workload and is applied to a
work class set that has two work classes. Load activities are mapped
to one work class, while all other activities are mapped to the other
work class. A CONCURRENTDBCOORDACTIVITIES threshold is created as
a work action in the Regular workload level work action set to allow
only one load activity in the system at a time while queuing the other
load activities. In addition, another CONCURRENTDBCOORDACTIVITIES
threshold is created as a work action in the Regular workload level
work action set to allow a maximum of 500 concurrent activities, while
activities exceeding the maximum are queued.
Note: If a threshold action
of CONTINUE is specified for a queuing threshold, it effectively makes
the size of the queue unbounded, regardless of any hard value specified
for the queue size.
Connections to the database from
both the Regular and Important workloads are mapped to the Priority
Aging service superclass. This service superclass is created to implement
priority aging that favors short activities. The Priority Aging service
class work action set is created for the Priority Aging service superclass
to separate the long-running load activities from all the short-running
activities. All activities, other than load, are mapped to the Short
service subclass. The Short service subclass is configured to have
the highest agent, prefetch, and buffer pool priorities. A CPUTIMEINSC
threshold is created on the Short service subclass to remap an activity
to the Medium service subclass after it consumes more than 1 second
of processor time in the Short service subclass. The Medium service
subclass has medium agent, prefetch, and buffer pool priorities. A
CPUTIMEINSC threshold is created on the Medium service subclass to
remap an activity to the Long service subclass after it consumes more
than 5 seconds of processor time in the Medium service subclass. The
Long service subclass has the lowest agent, prefetch, and buffer pool
priorities. Load activities are mapped directly to the Long service
subclass by the Priority Aging service class work action set because
load activities can be long running, resource intensive, and less
time critical for completion.
