WORKERTHREADS

Syntax

WORKERTHREADS [appname] value

appname (optional)—Application name. If you do not specify an application, the specified number of threads applies to all applications.
value—The number of threads to make available in the thread pool. The minimum value is 5. The maximum value is 2048. The default value, if the WORKERTHREADS setting is not present, is half of the SERVERTHREADS value.

Description

Historically, Essbase dynamically created threads for parallel operations such as parallel calculation, parallel data load, and parallel restructure. However, beginning in Release 11.1.2.4.000, the following parallel operations do not dynamically create threads, but instead use a set number of threads from a pre-created pool of threads:

Parallel calculation, with CALCPARALLEL or FIXPARALLEL
Parallel data load, for aggregate storage and block storage cubes
Parallel export, for block storage cubes
Parallel restructuring

Guidelines for Threaded Operations

Oracle recommends the following settings, depending on your system architecture.

Table 2-1 Guidelines for Threaded Operations

Parameter	64-bit (16 cores)	64-bit (32 cores)	64-bit (16 cores) and Two Cubes	Oracle Exalytics In-Memory Machine, 40 core, X2-4	Oracle Exalytics In-Memory Machine, SPARC 128 core	Oracle Exalytics In-Memory Machine, 60 core, X4-4
SERVERTHREADS	100^Foot 1	100^Foot 1	100^Foot 1	100^Foot 1	200^Foot 1	120^Foot 1
WORKERTHREADS	50	50	50	50	100	60
AGENTTHREADS	30^Foot 1	30^Foot 1	30^Foot 1	30^Foot 1	30^Foot 1	30^Foot 1
DLTHREADSPREPARE	2	2	2	2 ^Foot 2	2 ^Foot 2	2 ^Foot 2
DLTHREADSWRITE	2^Foot 3	2^Foot 3	2^Foot 3	2^Foot 3	2^Foot 3	2^Foot 3
EXPORTTHREADS ^Foot 4	8	8	8	40	40	40
RESTRUCTURETHREADS	4	4	4	8	8	8
CALCPARALLEL maximum^Foot 5	8	8	8	32^Foot 6	32	32^Foot 6
FIXPARALLEL maximum^Foot 7	8	8	8	32^Foot 6	32	32^Foot 6

^Footnote 1

May need adjustment based on partitioning and concurrency

^Footnote 2

24 on aggregate storage

^Footnote 3

1 if using .txt load

^Footnote 4

In addition to setting EXPORTTHREADS, you must specify multiple data files in the data export

^Footnote 5

SET CALCPARALLEL must be set in the calculation script

^Footnote 6

16 if three or more cubes are running concurrent calculations

^Footnote 7

FIXPARALLEL must be set in the calculation script

Notes

WORKERTHREADS is a configuration setting to manipulate the number of threads available in the thread pool. Whether this availability threshold is set explicitly in the configuration, or left to its default value of SERVERTHREADS/2, if the effective setting is less than what is demanded by a requested parallel operation, Essbase implicitly lowers the parallelism of the requested operation so that it fits within the parameters of the thread pool.

For example, consider loading data:

The concept of a pipeline is relevant to Essbase data loads. A pipeline is a series of data processing elements in memory that may be executed serially or in parallel. An Essbase data load operation uses a pipeline consisting of 5 stages. Therefore, all data load operations need a minimum of 5 threads.

If you use DLTHREADSPREPARE or DLTHREADSWRITE, it increases the minimum number of data load threads needed. For example, if you set DLTHREADSPREPARE to 4 and DLTHREADSWRITE to 2, you need 9 threads (the minimal 5, plus the increase of 4).

If the number of threads requested for all pipelines is 9, as in the case above, but WORKERTHREADS is set to only 8, then Essbase implicitly sets DLTHREADSPREPARE and DLTHREADSWRITE to 1, and the data load runs with a total thread requirement of 5 (the minimum and default).
Similarly, if you run a parallel calculation, a parallel export, or a parallel restructure, these operations execute with parallelism not exceeding the number of available threads.

For example:
- If WORKERTHREADS is set to 8, and you attempt CALCPARALLEL 16, the parallel calculation runs as CALCPARALLEL 8.
- If WORKERTHREADS is set to 5, and you attempt EXPORTTHREADS 12, the parallel export runs as EXPORTTHREADS 5.
- If WORKERTHREADS is set to 16, and you attempt RESTRUCTURETHREADS 32, the parallel restructure runs as RESTRUCTURETHREADS 16.
If you run concurrent parallel operations on the same application, the total parallelism is limited by the number of threads in the thread pool. For example, if you set WORKERTHREADS to 16, and run two concurrent requests of CALCPARALLEL 16, the two calculation requests share the 16 threads. The calculations will run using up to 16 threads, but because they share the thread pool of 16, they are running at close to half the capacity that was requested.
If you have multiple cubes within an application, Oracle recommends setting WORKERTHREADS slightly higher than the parallel threads you need for one cube. For example, if WORKERTHREADS is set to 16 and the number of CPUs on the system is 16, you should set the CALCPARALLEL value to less than 16.
If cubes on different servers reference each other using @XREF or @XWRITE, ensure that the source cube of the cross reference has a higher number of SERVERTHREADS than of parallel calculation threads (CALCPARALLEL or FIXPARALLEL).
With thread pooling for parallel operations, increased resources are needed to start an Essbase application. If you encounter the error Unable to Request Server Thread during startup, adjust the operating system settings that control the number of threads per process and the total number of threads in the system (for example, on Linux, tune nproc using ulimit).

Example

WORKERTHREADS Sample 32