For full design, tuning and limits details refer to the Oracle EPM Essbase Documentation Release 11.1.2.3
http://docs.oracle.com/cd/E40248_01/nav/portal_3.htm
The key Essbase guides that should be reviewed for all design, tuning and limits considerations are:
• Oracle Essbase Database Administrator's Guide
• Oracle Essbase Technical Reference
• Oracle Essbase Administration Services (Online Help)
The following topics are additional design, tuning and limits considerations to optimize Essbase Block Storage (BSO) performance; however, for full details see the guides referenced above.
6.2.1 Outline
See the Oracle Essbase Database Administrator's Guide for complete details regarding Block Storage Outline design and tuning.
http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_db.pdf
The following sections are to help guide with tuning areas. Only methodical performance and load testing can determine the optimal settings for a given application design and process.
6.2.1.1 Sparse/Dense Configuration
Determine the best dimensions for sparse/dense configuration. Typically, the best dense dimensions are found to be the Accounts and Period dimensions. Data distribution and process are typically the driving force with determination the best sparse/dense configuration.
The sparse/dense configuration drives the size of the compressed on disk and uncompressed block size in memory. This can impact several areas of performance including disk IO performance, which is why methodical testing is needed to determine the best configuration.
6.2.1.2 Dimension Order
General rule of thumb for any Essbase BSO application is to start in hour-glass order, which is dense dimensions first most to least than sparse dimensions least to most. The “most” and “least” are typically based on total number of stored members.
General rule of thumb for any Planning application is to start with dense dimensions first but then sparse dimensions with the aggregating dimensions on top than non-aggregation dimensions on the bottom.
Dimension order is critical because it impacts calculator cache usage and determines how parallel threads are split into independent tasks.
6.2.1.3 Member Properties
Determining the right Member Properties is critical in that it can impact both query and calculation performance. Placing the right Member Property flag i.e. Dynamic Calc, Label Only or Never Shares will impact the size the database thereby impacting performance. For example, implied share is enabled by default in Essbase so when “Never Share” is used on the parent member, this causes a parent and child to have their own blocks, which will increase the size of the number of blocks therefore the database size. The more blocks that are read into memory and written back to disk, the more impact there is to performance.
6.2.2 Database Level Settings
See the Oracle Essbase Database Administrator's Guide for complete details regarding Block Storage Database Settings tuning.
http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_db.pdf
The following sections are to help guide with tuning areas. Only methodical performance and load testing can determine the optimal settings for a given application design and process.
6.2.2.1 Index Cache
If there is enough memory on the Essbase server, it is recommended to set Index Cache to hold all of the ess*.ind files.
It is not recommended to set Index Cache higher than needed to cache all ess*.ind files. There will be no performance improvement and only wasting memory allocation.
6.2.2.2 Data Cache
Data Cache holds uncompressed blocks i.e. “expanded blocks”. The expanded or uncompressed block does not include dynamically calculated cells.
For Buffered IO, at the time a block is read into Essbase memory, the block is immediately uncompressed or expanded and put into the Data Cache.
For Direct IO, at the time a block is read into Essbase memory, the block remains compressed and put to the Data File Cache which will later be uncompressed and put to the Data Cache.
Sizing Data Cache should be done based on the expanded block size, which can be calculated from the number of dense cells that are not virtual members i.e. Actual Block Size in GetDBStats. See Commit Blocks for another option to size Data Cache.
Sizing Data File Cache is virtually the sum of the sizes of the ess*.pag files.
6.2.2.3 Compression Type
Typically, in most Essbase BSO applications, depending on data type, bitmap works best. The best compression type depends on the data pattern, therefore, it is recommended methodical testing be done to find the best compression type. For example:
Jan Feb Mar
50 50 50 <---- most likely will compress best under RLE 10 1000 200 <---- most likely will compress best under bitmap 6.2.2.4 Commit Blocks
For some BSO applications, default implicit commit setting (3,000) is not appropriate because compressed block size is so small that transactions are committed too often, which produces too much overhead. Given the current state of transaction, the application log provides a hint on a better commit threshold by adjusting the commit block intervals automatically.
Commit block adjustment interval is based on a range from 3,000 to 100,000.
Meaning, commit blocks will not adjust higher than 100,000. Adjustment intervals can happen with any setting if the algorithm determines block size is small and commit blocks occurs too often.
For additional information on BSO Commit Block tuning, see the blog post https://blogs.oracle.com/pa/entry/essbase_11_1_2_commit
Important Note: On some Planning apps where user concurrency is high, then the commit block setting should not be set too high in order to avoid locking issues.
6.2.2.5 Retrieval Buffer Size
The retrieval buffers are a server buffer per database that holds extracted row data cells per user query.
When the retrieval buffer is full, the rows are processed and the buffer is reused. If these buffers are too small, frequent reuse of the area can increase retrieval times. If these buffers are too large, too much memory may be used when concurrent users perform queries while also increasing retrieval times.
Important Note: It is recommended to start with default values and incrementally increase during load testing using the largest user reports. Since this setting is per user report request, it may lead to wasted memory so only set to what is needed.
Also note the retrieval buffers are allocated per thread.
Oracle recommends that you do not exceed 100 KB, although the size limit is set at 100,000 KB.
6.2.3 Calculation Commands (used in calc scripts or business rules)
See the Oracle Essbase Database Administrator's Guide for complete details regarding Block Storage Calculation Commands tuning.
http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_db.pdf
See the Oracle Essbase Technical Reference for a complete list of Calculation Commands that apply to Block Storage applications.
http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref.pdf
The following sections are to help guide with tuning areas. Only methodical performance and load testing can determine the optimal settings for a given application design and process.
6.2.3.1 Suggested BSO Calculation Commands to Review:
AGG vs. CALC DIM CALCMODE
SET CACHE
SET CALCPARALLEL SET CALCTASKDIMS SET FRMLBOTTOMUP SET FRMLRTDYNAMIC SET LOCKBLOCK
SET MSG SET NOTICE
SET REMOTECALC SET RUNTIMESUBVARS SET UPDATECALC
6.2.3.2 General Best Practices for Calculation Commands
• Unary calculations are the fastest
• Calculate dense members before sparse members
• Calculate only what is needed
• Typically, IF on dense members and FIX on sparse members
• Minimize the number of passes on the database
• Best to use parallel threads on aggregations and serial on formulas. Setting all calc scripts to parallel thread making Essbase force serial with some formulas/calc commands adds overhead to the calculation. It is best to set parallel threads only where it is used and not initiate the overhead when serial thread is forced. Be aware of user concurrency when using CALCPARALLEL.
• Make sure Schedules Tasks exceed Empty Tasks at least by 50% when using parallel threads. If Empty Tasks are close or equal to Scheduled Tasks, parallel thread overhead is not worth initializing.
For additional information on other BSO Calculation Commands, see the blog post https://blogs.oracle.com/pa/entry/essbase_11_1_2_optimisation
6.2.4 Essbase.cfg Settings
See the Oracle Essbase Technical Reference for a complete list of configuration settings that apply to Block Storage applications.
http://docs.oracle.com/cd/E40248_01/epm.1112/essbase_tech_ref.pdf . The following sections are to help guide with tuning areas. Only methodical performance and load testing can determine the optimal settings for a given application design and process.
6.2.4.1 Suggested BSO CFG Settings to Review:
CFG Setting AGENTTHREADS
AGTSVRCONNECTIONS SERVERTHREADS
For additional information on these settings, see the blog post
https://blogs.oracle.com/pa/entry/essbase_11_1_2_agtsvr connections
NETDELAY
NETRETRYCOUNT
For additional information on these settings, see the blog post https://blogs.oracle.com/pa/entry/epm_11_1_2_in2 NO_HOSTNAME_LISTCONNECT For additional information on this setting, see the blog
post https://blogs.oracle.com/pa/entry/epm_11_1_2_add
It is strongly recommended that the default value be used than incrementally increase the value during methodical performance and load testing to find the best setting.
It is strongly recommended to not randomly set Essbase configuration setting values high without understanding what the setting does and how it works. Also, methodical testing will uncover issues with higher then needed settings. For instances, it is recommended to set the NETDELAY / NETRETRYCOUNT timeout settings to default values, which is 2 minutes. If need be, the max amount of timeout that is recommended should not be above 5 minutes. The reason is because there have been customers reporting Essbase “hangs”; However, technically there is no
“hang” until this “hang” exceeds the NETDELAY / NETRETRYCOUNT settings.
Meaning, if timeout is set to 17 minutes and the “hang” resolved in 10 minutes, this would not be considered a “hang” until the hang was longer than 17 minutes. At this point, an investigation into what processes are taking that long should be done.