HFM: Performance Tuning on Application

There are three Planning Applications (seven Plan Types) are already there in Production, all perform well. After HFM was migrated from SIT to the Production environment recently, we immediately noticed the performance of HFM is not well. Normally it takes 15 seconds to open a HFM application in SIT, in production it takes over a minute to open (if after the services are recycled, it takes more than four minutes to open a HFM application). However, the consolidation time is sort of normal. (two to five minutes to consolidate a month, which contains about 400K of records).

Following is the list of tuning activities, with regards to HFM tuning -

1) From DB side
     a) Ask DBA to confirm that Auto Create Statistics is enabled for the HFM schema.
     b) Have DBA check how frequently the SQL Server is doing automatic statistics maintenance.
     c) Ask the DBA if the indexes may need to be defraged.
DBA reported back -
    a) Auto Create Statistics are enabled for all the databases by default
    b) SQL server will do auto update of statistics based upon certain conditions and its different from     different tables
    c) Indexes are fragmented badly and requires rebuild of indexes and requires to update statistics as well.

2) Log file sizes
    a) Cross check the hsveventlog file on all HFM application servers and if the size of the files are more than 25 MB please do rename it so that a new file gets created when you access the HFM application. (it may need to shut down HFM services)
    b) Cross check the hfm.odl.log file on all HFM application servers and if the size of the files are more than 25 MB please do rename it so that a new file gets created when you access the HFM application. (it may need to shut down HFM services)
    c) Cross check the tables APPLICATIONNAME_DATA_AUDIT TABLE and APPLICATIONNAME_TASK_AUDIT,HFM_ERRORLOG tables if the records in it are less than 500,000. If its more than that please do take a backup of the existing Database and truncate the records in it.
DBA reported back -
select COUNT(1) from HFM_ERRORLOG with(nolock) - 65832
select COUNT(1) from GOGOHFM_DATA_AUDIT with(nolock) - 172455
select COUNT(1) from GOGOHFM_TASK_AUDIT with(nolock) - 589

3) Registry Configuration
    From HFM server, CMD line, enter REGEDIT

Create the rest as listed below

EnableServerLocking=1
MaxDataCacheSizeinMB = 5000
MaxNumConcurrentConsolidations=4
MaxNumCubesInRAM= 100000
MaxNumDataRecordsInRAM= 30000000
MinDataCacheSizeinMB= 3000
NumConsolidationThreads=4

4) Edit IIS

Double click ASP to open

Set Response Buffering Limit to 4194304

Set StuckThreadMaxTime
Step1) Log in to Weblogic Administrator (user account EPM_ADMIN)
Step2) # Click "Lock and Edit"
# Select Servers under environment and click on HFMWeb(N)
# Select the Tuning tab Change "Stuck Thread Max Time" to 1200
# Change "Stuck Thread Timer Interval" to 1200
# Select "Activate Changes"
# The HFMWeb0 server will need to be restarted.

Where HFMWeb(N) is something like HFMWeb0, HFMWeb1, depending on how many HFM web servers are deployed and which server you are updating
Default value is as follows

Stuck Thread Max Time=600
Stuck Thread Timer Interval=60
Max Stuck Thread Time=600

Suggested value as per Oracle® EPM, Fusion Edition infrastructure Performance Tuning Guide APPLIES TO EPM RELEASE 11.1.2.2, 11.1.2.3 page 22.

Stuck Thread Max Time=1200
Stuck Thread Timer Interval=1200
Max Stuck Thread Time=1200

Step3) #When done, click Release Configuration

After the above setting change, there is no noticeable performance improvement, which meant we are not hitting the right spot. Let's try DCOM users.
Step1) From HFM server, edit IIS