Tuesday Apr 10, 2012

Essbase 11.1.2 - AgtSvrConnections Essbase Configuration Setting

AgtSvrConnections is a documented Essbase configuration setting used in conjunction with the AgentThreads and ServerThreads settings. Basically, when a user logs into Essbase, the AgentThreads connects to the ESSBASE process then the AgtSvrConnections will connect the ESSBASE process to the ESSSVR application process which then the ServerThreads are used for end user activities. In Essbase 11.1.2, the default value of the AgtSvrConnections setting was changed to 5. In previous Essbase releases, the AgtSvrConnections setting default value is 1.

It is recommended that tuning the AgtSvrConnections settings be done incrementally by 1 or 2 maximum and based on the number of concurrent Set Active/Clear Active calls.

[Read More]

Thursday Mar 29, 2012

EPM 11.1.2 - EPM Infrastructure Tuning Guide v11.1.2.1

Applies To: This edition applies to only 11.1.2, 11.1.2 (PS1).

One of the most challenging aspects of performance tuning is knowing where to begin. To maximize Oracle EPM System performance, all components need to be monitored, analyzed, and tuned. This guide describe the techniques used to monitor performance and the techniques for optimizing the performance of EPM components.


Performance tuning Oracle Hyperion EPM system is a complex and iterative process. To get you started, we have created a list of recommendations to help you optimize your Oracle Hyperion EPM system performance.

This chapter includes the following sections that provide a quick start for performance tuning Oracle EPM products. Note these performance tuning techniques are applicable to nearly all Oracle EPM products such as Financial PM Applications, Essbase, Reporting and Foundation services.

1. Tune Operating Systems parameters.
2. Tune Oracle WebLogic Server (WLS) parameters.
3. Tune 64bit Java Virtual Machines (JVM).
4. Tune 32bit Java Virtual Machines (JVM).
5. Tune HTTP Server parameters.
6. Tune HTTP Server Compression / Caching.
7. Tune Oracle Database Parameters.
8. Tune Reporting And Analysis Framework (RAF) Services.

Click to Download the EPM Infrastructure Tuning Whitepaper (Right click or option-click the link and choose "Save As..." to download this pdf file)

Tuesday Mar 27, 2012

EPM 11.1.1 - EPM Infrastructure Tuning Guide v11.1.1.3

This edition applies to EPM 9.3.1,, & only.

INTRODUCTION:One of the most challenging aspects of performance tuning is knowing where to begin. To maximize Oracle EPM System performance, all components need to be monitored, analyzed, and tuned. This guide describe the techniques used to monitor performance and the techniques for optimizing the performance of EPM components.

Click to Download the EPM Infrastructure Tuning Whitepaper (Right click or option-click the link and choose "Save As..." to download this file)

Essbase 11.1.2 - JVM_OPTION settings for Essbase

When tuning the heap size for Essbase, there are two JVM_OPTIONS settings available for Essbase - one for the Essbase agent and one for the Essbase applications that are using custom-defined functions (CDFs), custom-defined macros (CDMs), data mining, triggers or external authentication.

  • ESS_JVM_OPTION setting is used for the application and mainly for CDFs, CDMs, data mining, triggers, external authentication
  • ESS_CSS_JVM_OPTION setting is used to set the heap size for the Essbase agent

Tuesday Mar 13, 2012

Essbase 11.1.2 - Optimisation of Essbase Calculation Scripts Using NOT in IF statements

In Essbase calculation scripts, when an IF statement is performed, it will perform the first element of the IF statement first, check if the combination being calculated meets that criteria and calculate it if it does or pass to the next element, i.e the next ELSEIF. This will continue through all the ELSEIF statements and then the ELSE (if present) if the combination does not meet any of the criteria of the IF or ELSEIF.

It is possible to optimise an IF statement by first analysing which condition will be met by the majority of your data and then to try and place this IF statement as near to the top of your IF statement as possible ... if possible, of course.

[Read More]

Wednesday Jan 11, 2012

Essbase 11.1.2 - Commit Block Tuning

The commit blocks setting is a tunable Block Storage (BSO) database setting. For additional information, definition and explanation what the commit blocks setting is, see the Essbase Database Administration Guide, aka Essbase DBA Guide, under the “Ensuring Data Integrity” chapter. This post is to provide additional guidance with how to tune commit blocks for a given database but does not supersede what is documented in the Essbase DBA Guide.

For some Essbase databases, the default commit block setting of 3,000 is not appropriate because the compressed block size is so small that transactions are committed too often, which produces too much overhead. If this is the case, Essbase will adjust the commit block interval automatically and the adjusted value will be recorded in the application log. This adjusted commit block interval value that is recorded in the application log provides a hint on what a better commit block threshold might be for this given database.

The 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. Adjusted block intervals can happen with any setting if the algorithm determines block size is small and commit blocks occurs too often. It is possible to set commit blocks to 0, as per the Essbase DBA Guide, if you set commit blocks to 0, the synchronization point occurs at the end of the transaction. For this post, we are only focusing on the adjusted commit block intervals.

If the data committed by one of the first transactions before this adjustment was made is less than 10 MB of data then Essbase will try to make it commit at least 10 MB of data.  The goal with each implicit commit is to commit at least 10 MB of data. If it is less than 10 MB of data then Essbase is saying there is no point in committing this early and will try to make the commit interval larger.

In general, if the commit block interval automatically sets larger than 20,000, then it is recommended to reconsider the block density. Poor selection of block density will make too many blocks and potentially the reason for increased fragmentation. Once the best outline sparse/dense configuration is determined, the next statistic to monitor would be the compression ratio. If the compression ratio it is too low, this typically means there is too much compression, which is a good indicator that there are too many #Missing blocks.

The factors that impact whether Essbase adjusts the commit blocks interval are as follows:

  • The number of blocks dirtied by the calculation is different at the first implicit commit.
  • If the number of non-missing cells changes in the compressed block at the first implicit commit, thus affects the size of the data buffers to be flushed out to the page file.
  • Under parallel thread calculations, if the first adjusted commit block interval happens to be too low, than commit blocks will continue to re-adjust. If the adjusted commit blocks value is appropriate, the same interval may be applied to all remaining calculation threads. Basically, if parallel threads are enabled then commit blocks may adjust differently based on which thread reaches that point first. .

Using commit blocks interval to determine the data cache setting:

See the Essbase DBA Guide for additional options for sizing the data cache. However, a reasonable estimate for tuning data cache based on the commit blocks interval would be:

Data Cache = uncompressed block size * commit blocks * number of parallel calculation threads

We need to use the uncompressed block size because data cache holds only uncompressed blocks.

Determining the optimal size of commit blocks interval:

This is hard to generalize and has to be tuned for the particular application and data model. Therefore, it can only be arrived at by meticulous testing.

One suggestion would be to experiment with values from 3,000 – 30,000 (in general) and see what gives better performances when the same batch calculation(s) are ran again and again. Generally a higher value will make the initial calculation run faster and subsequent calculations run slower. Also, the commit block setting may need additional tuning during performance testing with high end user concurrency. For instance, Planning applications with multiple end users running business rules concurrently would require experimenting with different settings to avoid possible block contention.

Friday Dec 16, 2011

HPCM 11.1.2 - Optimising Calculations in HPCM for Multiple POVs

Calculation scripts in Hyperion Profit and Cost Management (HPCM) are generated and executed by user Point of View (POV) e.g. by Period, , Year, Scenario and Version combination. Therefore, if your model is, for example, a forecast year, you will need to generate and execute 12 calculation scripts (i.e. one for each period). In v11.1.1.x code lines, generation of calculation scripts is done from the user interface. In 11.1.2.x code lines, it is also possible to use web services to automate the run of these calculation scripts.

The previous paragraph is the supported method for generating and executing calculations. However, it is also possible to run user defined scripts on the model. In the same way, it is possible to edit the standard scripts i.e. to create custom user defined scripts. By doing this, it is possible to run 12 periods in a single calculation script execution rather than 12 separate calculation script executions. It is possible to do this if all 12 periods use the same assignments and driver selections. If this is the case, you only need to generate one calculation script, edit it and then execute this script, or scripts, if HPCM generates multiple scripts per stage, for all 12 periods. This requires only some minor customisation of the script.

[Read More]

Tuesday Dec 13, 2011


Specifies whether optimized grid processing, which cuts the input grid into symmetric grids to create fewer symmetric queries, is enabled for spreadsheet operations. The default value is FALSE.

[Read More]

Wednesday Dec 07, 2011

EPM 11.1.2 - Understanding the Dynamic Calculations Log Messages for Essbase ASO

Essbase application log has some informative messages related to dynamic calculations that can be helpful in tuning:

  • The following message indicates an error in a formula. Fix the error message or drop these members from outline if they are not required.
    • Error encountered when loading member [<MemberName>]'s calc string [<FormulaExpression>], ignored 
  • The following message indicates invalid solve order assignment. Assign correct solve orders for members indicated in the message.
    • Invalid formula for member [<A>] (reason: depends on a member [<B>] with higher solveorder ) will be ignored during execution
  • The following message indicates that a formula is too complex for Essbase to optimize. Rewrite if possible.
    • Formula for member [% of Total] will be executed in [CELL] mode
  • The following message indicates that a formula is complex and Essbase couldn’t detect a non empty behavior for it.

EPM 11.1.2 - Increase Retrieval Buffer Sizes for Essbase (ASO/BSO)

  • Set Retrieval Buffer Size to 50 KB for 32 bit platform and 100 KB for 64 bit platform.
  • Set Retrieval Sort Buffer Size to 50 KB for 32 bit platform and 100 KB on 64 bit platform: Essbase requires a larger retrieval sort buffer size on 64-bit platforms than on 32-bit platforms.

Important Note: If you have a high-memory-use installation (Essbase 64bit), you can configure higher retrieval buffer values (i.e. 300KB). Caution: It is recommended to start with default values and a test incremental setting on largest report tuning is based on. As this setting is per user report request so it may lead to wastage of memory and only want to set as much as needed. Also note the retrieval buffer is allocated per thread, so the amount of memory needed is the number of server threads multiplied by the retrieval buffer size.

Monday Dec 05, 2011

EPM 11.1.2 - How-to monitor the EPM data source connections from WebLogic Server 10.3.x console

Monitor the EPM data source connections from Console (Deployments->Monitoring->JDBC) i.e. “Active Connections Count” and if “wait for connections” has a greater than 0 value then increase the pool size.

Wednesday Nov 16, 2011

EPM - Smartview client and HFM office provider

If your connection to the smartview provider is very slow, because the login part takes a long time (user directory slowness, ...), consider adding on the desktop side a Windows parameter:

ReceiveTimeout 300000

to avoid being prompted over and over again for username/password

This is an addition to the support doc id: "Smart View Keeps Prompting For Username And Password For Financial Management Provider [ID 1353294.1]"

EPM 11.1.2 - In WebLogic Server, Enable Native IO Performance Pack

Performance can be improved by enabling native IO in production mode. WebLogic Server benchmarks show major performance improvements when native performance packs are used on machines that host Oracle WebLogic Server instances.

Important Note:  Always enable native I/O, if available, and check for errors at startup to make sure it is being initialed properly.

Tip: The use of NATIVE performance packs are enabled by default in the configuration shipped with your distribution. You can use the Administration Console to verify that performance packs are enabled by clicking on each managed server and click on Tuning tab.

EPM Planning 11.1.2 - MassGridStatistics

A utility is available for Oracle Hyperion Planning that determines web form load times.  This utility, MassGridStatistics, opens all web forms within the Planning application.  After the forms are opened, an html page will appear showing the form options, suppression, number of row column and page members, and load times.  Any form having a load time longer than one second could potentially have scalability issues in a multi-user environment and should be considered for re-design.  Adding suppression (especially block suppression) and reducing the number of rows and columns are potential fixes that will reduce load times.

The MassGridStatistics utility is located in a .7z file called MassGridStatistics.7z.  Extract the file using 7-Zip.  A readme file is provided listing the installation instructions and the steps to run the utility.

MassGridStatistics is included with the patch set and will also be in all future releases starting with  For earlier Planning releases, an SR will be necessary to have Support provide the utility.

Monday Oct 24, 2011

Essbase Kernel Is Multi-Threaded

Although it is documented in the Essbase Database Administration Guide (DBA Guide) that the Essbase kernel is multi-threaded and provides the foundation for a variety of Essbase functions, which includes data loading, calculations, spreadsheet lock and send, partitioning, and restructuring. The Essbase kernel also reads, caches, and writes data, manages transactions, enforces transaction semantics to ensure data consistency and data integrity.  There still continues to be some confusion around the multi-thread capabilities of the Essbase kernel. 

For the documented information on the Essbase Kernel, see the Essbase DBA Guide:

·         Chapter 46. “Running Essbase Servers, Applications, and Databases”

·         Chapter 49, “Managing Database Settings”

·         Chapter 50, “Allocating Storage and Compressing Data”

·         Chapter 51, “Ensuring Data Integrity”

·         Chapter 55, “Optimizing Essbase Caches”

·         Appendix D, “Estimating Disk and Memory Requirements”


It has been continually questioned if the Essbase kernel is in fact multi-threaded.  This post is to help clarify this point and will hopefully clear up this misconception with the Essbase kernel and its multi-threaded capabilities.

The Essbase kernel has not been single threaded since the release of Essbase v6.5.  Both the Block Storage (BSO) and Aggregate Storage (ASO) kernels are multi-thread capable. This is evident with the concurrent activities run by end users i.e. queries, HBR type calculations, batch type calculations, and data updates that are typical in a Planning (BSO) type applications or heavy queries demanded in Reporting (ASO) type applications.

The Essbase kernel does not do anything by itself to create multiple threads.  AgentThreads and ServerThreads are set in the Essbase.cfg file based on design, process and end user volume.  Normally, the ServerThread default setting of 20 threads is sufficient per application.  The calculation command “SET CALCPARALLEL”, which is set in each calc script, inherently creates multi-threading inside the Essbase kernel. The Essbase kernel will service these requests concurrently and this is not a serialization point.

A caveat that can be misread that the Essbase kernel is serial is if the customer uses a slow I/O subsystem, especially an I/O subsystem that does not support parallel I/O capabilities.  A slow IO subsystem that does not support parallel IO capabilities can be a bottleneck that will eventually serialize the concurrent operations, meaning the Essbase kernel is not the serialization point.

Another caveat that can be misread is if the volume of concurrent requests on the Essbase server exceeds the server resource capacity.  It is important to do appropriate performance testing to be sure the expected design, process and end user volume does not exceed server capacity.  If the volume of concurrent activities bombards the Essbase server, this can cause CPUs to contend and thrash negatively affecting performance.