OBIA 11g Performance Issue With PLP_PLP_ARINCREMENTALACTIVITYLOAD and PLP_PLP_APINCREMENTALACTIVITYLOAD

Applies to:

Business Intelligence Applications Consumer - Version 11.1.1.8.1 and later
Information in this document applies to any platform.

Symptoms

OBIA 11.1.1.8.1 DataWarehouse
Data source - EBS
OBIA configured with the following Modules -

• Oracle Financial Analytics
• Oracle Procurement and Spend Analytics
• Oracle Project Analytics

When performing full initial load on the modules listed above, the following two steps have been running for almost 12 hours now -

• PLP_PLP_ARINCREMENTALACTIVITYLOAD
• PLP_PLP_APINCREMENTALACTIVITYLOAD

Is there a way to obtain better performance?

Cause

Poor Out of the Box configuration led to poor performance in the ETL

• Bug 18952097 PERFORMANCE ISSUE WITH PLP_APINCREMENTALACTIVITYLOAD.W_AP_BALANCE_F_TMP
• Bug 17509570 RFA - HOW TO CALCULATE THE TEMP TABLESPACE REQUIRED TO RUN A SCENARIO?

These BUGs also offer suggestions on how to resolve the issue

Solution

Solution Steps:
ODI Changes to AP and AR:
1. Create Custom Folder and Modify AP/AR PLP Mappings
For AP:

1) Login to ODI repository from the ODI studio and go to “BI Apps Project”
2) Under “Mappings” folder, create a new custom SDE adaptor folder based on the appropriate EBS release version in your environment.

a. Right-click the Mappings folder and select New Sub-Folder
b. Set Name as CUSTOM_<Original Folder Name>. For example, if this is for PLP, create custom folder CUSTOM_PLP. This represents the custom PLP folder for the original PLP folder.
c. Click the Connect Navigator button in the Designer tab.
d. Select Edit Release Tags.
e. Select the release tag that corresponds to your source. For example, BIA_11.
f. Select the custom PLP folder you created and add it to the release tag.
g. Click Next
h. Click Finish

3) Enable versioning for the preconfigured Task Folder, PLP_APIncrementalActivityLoad, to be customized. The version comment should indicate this is the base version of the task. Subsequent patches applied to this task in the future would require increasing the version in the comment so that it can be compared to the original task to identify any changes.

a. Go to appropriate PLP folder, right-click the Task folder PLP_APIncrementalActivityLoad and select Version > Create Version.
b. Accept the default version number, 1.0.0.0.
c. Add a description indicating that this is the original version of this task.

4) Duplicate the Task folder to be customized (i.e. PLP_APIncrementalActivityLoad) by copying it. Cut and paste the copied task folder to the Custom adaptor folder you have created (e.g. CUSTOM_PLP), and rename it to remove the 'Copy of…' prefix.
5) Using the same method as in step 3, enable versioning of copied Task folder. The version comment should indicate this is the original version. This versioning enables comparison of the customized task to a copy of the original version to determine all changes that have been introduced.
6) Create another version of the copied task PLP_APIncrementalActivityLoad. The version comment should indicate this is the customized version. Use the same steps as above.
7) Expand the map in the custom adaptor folder, Expand “Interfaces” to find TWO entries as below.
8) Double click a particular interface (PLP_APIncrementalActivityLoad.W_AP_BALANCE_F_TMP_SQ_PLP_AP_INCR_ACTIVITY_LOAD) opens ‘Overview’ Page on right hand side. Now click on ‘Quick-Edit’ tab
available in bottom section of this page
9) Go to the Join section and expand for the joins to be visible. Change the highlighted join condition with the below by clicking on the join expression.

(CASE WHEN W_AP_XACT_F.ACCOUNTING_DT_WID=0
THEN
W_AP_XACT_F.POSTED_ON_DT_WID
ELSE
W_AP_XACT_F.ACCOUNTING_DT_WID END)=W_DAY_D.ROW_WID 

10) Apply and Save the Interface
Next, delete the scenario associated with the original, out-of-the-box task and generate a new scenario for the custom task using the same scenario name as the original. ODI enforces unique scenario names and we want to use the same scenario name for the custom task so that the load plan executes this ETL task rather than the out-of-the-box ETL task.

Delete the out-of-the-box scenario:

• Navigate to the out-of-the-box Task folder - Packages - <Package Name> - Scenarios - <Scenario Name>
• Make a note of the Scenario Name (you can double click and use CTRL-C to copy the scenario name)
• Right click the scenario and select 'Delete'

i) Before regenerating the scenario, go to the original task folder PLP_APIncrementalActivityLoad in the OOTB mapping folder (e.g. PLP), delete the existing OOTB scenario (e.g. PLP_PLP_APINCREMENTALACTIVITYLOAD).

Generate a scenario for the custom task:

• Navigate to the custom Task folder - Packages - <Package Name>
• Right click the package and select 'Generate Scenario'
• Use the original, out-of-the-box scenario name (CTRL-V to paste the name if you copied it previously)
• Check the 'Generate scenario as if all underlying objects are materialized' box
• Click 'OK'
• Select 'Use All' from the 'Startup Parameters' drop down box
• Click 'OK'

When you execute the load plan, it will now execute the custom task rather than the original task.

ii) Go back to the modified PLP_APIncrementalActivityLoad in the custom folder, go to Packages and generate the scenario using the option to generate the scenario as if all underlying objects are materialized. Rename the scenario name to use the original out of box scenario name. For example, PLP_PLP_APINCREMENTALACTIVITYLOAD for PLP.
In the future if you make changes to any of the interfaces or the package, you can either regenerate the existing scenario or generate a new scenario. Unless you need separate scenarios, it is recommended that you regenerate the existing scenario. To do this, right-click the scenario and select Regenerate.

Along with the above changes let them add the below HINT1: Hardcode HINT1 value in the Load Plan component

For AP:
FULL=/*+ USE_HASH( W_AP_XACT_F W_GL_ACCOUNT_D)*/

For  AR:

Similar change should be done in the custom adaptor folder:
1) Open the map PLP_ARIncrementalActivityLoad Expand this map and find “Interfaces” inside this folder and expand “Interfaces” to find TWO entries.
2) Double clicking a particular interface (PLP_ARIncrementalActivityLoad.W_AR_BALANCE_F_TMP_SQ_PLP_AR_INCR_ACTIVITY_LOAD) opens ‘Overview’ Page on right hand side. Now click on ‘Quick-Edit’ tab available in bottom section of this page 3) Go to the Join section and expand for the joins to be visible. Change the highlighted join condition with the below by clicking on the join expression.

(CASE WHEN W_AR_XACT_F.ACCOUNTING_DT_WID=0
THEN
W_AR_XACT_F.POSTED_ON_DT_WID
ELSE
W_AR_XACT_F.ACCOUNTING_DT_WID END)=W_DAY_D.ROW_WID

4) Apply and Save the Interface Delete the scenario associated with the original, out-of-the-box task and generate a new scenario for the custom task using the same scenario name as the original. ODI enforces unique scenario names and we want to use the same scenario name for the custom task so that the load plan executes this ETL task rather than the out-of-the-box ETL task.
Delete the out-of-the-box scenario:

•  Navigate to the out-of-the-box Task folder - Packages - <Package Name> - Scenarios - <Scenario Name>
•  Make a note of the Scenario Name (you can double click and use CTRL-C to copy the scenario name)
•  Right click the scenario and select 'Delete'

i) Before regenerating the scenario, go to the original task folder PLP_ARIncrementalActivityLoad in the OOTB mapping folder (e.g. PLP), delete the existing OOTB scenario (e.g. PLP_PLP_ARINCREMENTALACTIVITYLOAD).

Generate a scenario for the custom task:

•  Navigate to the custom Task folder - Packages - <Package Name>
•  Right click the package and select 'Generate Scenario'
•  Use the original, out-of-the-box scenario name (CTRL-V to paste the name if you copied it previously)
•  Check the 'Generate scenario as if all underlying objects are materialized' box
•  Click 'OK'
•  Select 'Use All' from the 'Startup Parameters' drop down box
•  Click 'OK'

When you execute the load plan, it will now execute the custom task rather than the original task.

ii) Go back to the modified PLP_ARIncrementalActivityLoad in the custom folder, go to Packages and generate the scenario using the option to generate the scenario as if all underlying objects are materialized. Rename the scenario name to use the original out of box scenario name. For example, PLP_PLP_ARINCREMENTALACTIVITYLOAD for PLP.

In the future if you make changes to any of the interfaces or the package, you can either regenerate the existing scenario or generate a new scenario. Unless you need separate scenarios, it is recommended that you regenerate the existing scenario. To do this, right-click the scenario and select Regenerate.

Along with the above changes add the below HINT1: Hardcode HINT1 value in the Load Plan component
For AR:
FULL=/*+ USE_HASH( W_AR_XACT_F W_GL_ACCOUNT_D)*/
But before doing this they have to follow the below instructions:
Instructions:
In the CUSTOM folder:
1) Go to the Flow tab of the yellow interface:PLP_ARIncrementalActivityLoad%
2) Navigate to properties of the IKM.Go to OBI_HINT_NESTED and add #HINT1 in expression editor.Save
3) Regenerate the scenario.
4) Select #HINT1 when the checkbox shows up during scenario regeneration.
5) Now go to the Load Plan component and pass the HINT1 value as mentioned above.