Beginning in 11.1.2.4 Essbase, there is a new configuration setting that should not be forgotten – “WORKERTHREADS”. Why is this configuration setting important you may ask. Previously, Essbase operations that could run in parallel (calculations, data loads etc) used dynamically created threads for their work. Administrators need not bother setting thread pools or assigning threads. Now in 11.1.2.4, the thread pools from which these parallel processes rely on can be set explicitly in the Essbase configuration file. You may ask, “What if i don’t explicitly set this value in the config file”. If the WORKERTHREADS setting is not explicitly set, it will default to half the size of the “SERVERTHREADS” config settings. If you don’t have the “SERVERTHREADS” setting explicitly set, “SERVERTHREADS” will default to 20, implicitly setting “WORKERTHREADS” to 10. You may ask “Whats the problem with the default settings?”. Well, with a pool of only ten threads, you are limited to a total of 10 thread in parallel across the entire server. So if you try to run two calculations at the same time each with a CALCPARALLEL setting of 8, the total parallelism of these two calculations running at the same time will be limited to 10 threads instead of the requested 16. With the powerful servers and 64 bit architecture we’re seeing with Essbase today, limiting yourself to 10 total threads for parallel operations may leave a LOT of performance on the table.
Now that we know this setting should be addressed when setting up your Essbase environment, the question arises of what should the value for WORKERTHREADS be set to. The physical architecture of the Essbase server, the number of applications and amount of parallel processing will drive the optimal settings. Testing will be important to ascertain the most optimum setting in for each environment. However, Oracle does have some guidelines that can be used for initial baselines. Below is a list of example server architectures and recommended starting points for WORKERTHREADS and associated configuration settings:
32-bit (8 cores)
|
1 2 3 4 5 6 7 |
WORKERTHREADS 50 SERVERTHREADS 100 AGENTTHREADS 30 DLTHREADSPREAPRE 2 DLTHREADSWRITE 2 EXPORTTHREADS 8 RESTRUCTURETHREADS 8 |
64-bit (16 cores)
|
1 2 3 4 5 6 7 |
WORKERTHREADS 50 SERVERTHREADS 100 AGENTTHREADS 30 DLTHREADSPREAPRE 2 DLTHREADSWRITE 2 EXPORTTHREADS 8 RESTRUCTURETHREADS 8 |
64-bit (32 cores)
|
1 2 3 4 5 6 7 |
WORKERTHREADS 50 SERVERTHREADS 100 AGENTTHREADS 30 DLTHREADSPREAPRE 2 DLTHREADSWRITE 2 EXPORTTHREADS 8 RESTRUCTURETHREADS 8 |
64-bit (16 cores) and Two Databases
|
1 2 3 4 5 6 7 |
WORKERTHREADS 50 SERVERTHREADS 100 AGENTTHREADS 30 DLTHREADSPREAPRE 2 DLTHREADSWRITE 2 EXPORTTHREADS 8 RESTRUCTURETHREADS 8 |
Oracle Exalytics In-Memory Machine, 40 core, X2-4
Block storage:
|
1 2 3 4 5 6 7 |
WORKERTHREADS 50 SERVERTHREADS 100 AGENTTHREADS 30 DLTHREADSPREAPRE 2 DLTHREADSWRITE 2 EXPORTTHREADS 40 RESTRUCTURETHREADS 20 |
Aggregate storage:
|
1 2 3 4 5 6 7 |
WORKERTHREADS 50 SERVERTHREADS 100 AGENTTHREADS 30 DLTHREADSPREAPRE 24 DLTHREADSWRITE 2 EXPORTTHREADS 40 RESTRUCTURETHREADS 20 |
Oracle Exalytics In-Memory Machine, SPARC 128 core
Block storage:
|
1 2 3 4 5 6 7 |
WORKERTHREADS 100 SERVERTHREADS 200 AGENTTHREADS 30 DLTHREADSPREAPRE 2 DLTHREADSWRITE 2 EXPORTTHREADS 40 RESTRUCTURETHREADS 20 |
Aggregate storage:
|
1 2 3 4 5 6 7 |
WORKERTHREADS 100 SERVERTHREADS 200 AGENTTHREADS 30 DLTHREADSPREAPRE 24 DLTHREADSWRITE 2 EXPORTTHREADS 40 RESTRUCTURETHREADS 20 |
Oracle Exalytics In-Memory Machine, 60 core, X4-4
Block storage:
|
1 2 3 4 5 6 7 |
WORKERTHREADS 60 SERVERTHREADS 120 AGENTTHREADS 30 DLTHREADSPREAPRE 2 DLTHREADSWRITE 2 EXPORTTHREADS 40 RESTRUCTURETHREADS 20 |
Aggregate storage:
|
1 2 3 4 5 6 7 |
WORKERTHREADS 60 SERVERTHREADS 120 AGENTTHREADS 30 DLTHREADSPREAPRE 24 DLTHREADSWRITE 2 EXPORTTHREADS 40 RESTRUCTURETHREADS 20 |
Hi,
Will X4-4 settings be OK for X5-4?
When setting these in .cfg file do you just set the ASO ones? How would you differentiate between BSO and ASO when setting them? Confused there 🙂 Also, is that a typo on DLTHREADSPREAPRE and should be DLTHREADSPREPARE?
Hi Sam,
I do believe the X4-4 settings would be a good starting point for a X5-4 machine. Since the X5-4 machine is even stouter, you can probably play around a little and see if you get some improvements from increasing settings.
If you’re using the same value for a setting between ASO and BSO applications and you want it to apply universally to all apps, then you don’t need to specify APPs or DBs specifically. If you want to have these settings use different values for a BSO application versus and ASO application, you will need to specify these settings individually by APP or DB. Or even from ASO to ASO app, you will need to have a line for each APP or DB in the configuration file. And yes that is a typo for the DLTHREADSPREPARE (that’s a cut and paste from Oracle own document no less 🙂 )