Scribd Logo
Upload

Welcome to Scribd!

  • 153 views

    Instructions 18802097 Gatherstats Recon FS SIL 11.1.1.7.1

    Uploaded by

    Feras Alswairky
    This document describes a solution to improve the performance of DiffManLoad mappings in the reconciliation module of Oracle Business Analytics - Financial Analytics. The issue was that these mappings were running slowly during incremental loads. The solution is to add a step to gather statistics on the reconciliation staging tables before the DiffManLoad mappings run. The steps provided add an EXEC_TABLE_MAINT_PROC scenario before the DiffManLoad scenario to gather statistics on the appropriate reconciliation staging table for each module. This ensures the optimizer has up-to-date statistics to choose an efficient execution path for the DiffManLoad mappings.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd

    Instructions 18802097 Gatherstats Recon FS SIL 11.1.1.7.1

    Uploaded by

    Feras Alswairky
    153 views3 pages
    This document describes a solution to improve the performance of DiffManLoad mappings in the reconciliation module of Oracle Business Analytics - Financial Analytics. The issue was that these mappings were running slowly during incremental loads. The solution is to add a step to gather statistics on the reconciliation staging tables before the DiffManLoad mappings run. The steps provided add an EXEC_TABLE_MAINT_PROC scenario before the DiffManLoad scenario to gather statistics on the appropriate reconciliation staging table for each module. This ensures the optimizer has up-to-date statistics to choose an efficient execution path for the DiffManLoad mappings.

    Original Description:

    Instructions 18802097 Gatherstats Recon FS SIL 11.1.1.7.1

    Copyright

    © © All Rights Reserved

    Available Formats

    DOC, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    This document describes a solution to improve the performance of DiffManLoad mappings in the reconciliation module of Oracle Business Analytics - Financial Analytics. The issue was that these mappings were running slowly during incremental loads. The solution is to add a step to gather statistics on the reconciliation staging tables before the DiffManLoad mappings run. The steps provided add an EXEC_TABLE_MAINT_PROC scenario before the DiffManLoad scenario to gather statistics on the appropriate reconciliation staging table for each module. This ensures the optimizer has up-to-date statistics to choose an efficient execution path for the DiffManLoad mappings.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    153 views3 pages

    Instructions 18802097 Gatherstats Recon FS SIL 11.1.1.7.1

    Uploaded by

    Feras Alswairky
    This document describes a solution to improve the performance of DiffManLoad mappings in the reconciliation module of Oracle Business Analytics - Financial Analytics. The issue was that these mappings were running slowly during incremental loads. The solution is to add a step to gather statistics on the reconciliation staging tables before the DiffManLoad mappings run. The steps provided add an EXEC_TABLE_MAINT_PROC scenario before the DiffManLoad scenario to gather statistics on the appropriate reconciliation staging table for each module. This ensures the optimizer has up-to-date statistics to choose an efficient execution path for the DiffManLoad mappings.

    Copyright:

    © All Rights Reserved
    Download as DOC, PDF, TXT or read online from Scribd
    Download as doc, pdf, or txt
    You are on page 1of 3

    Background and Issue Description

    Bug Number: 18802097


    Product: Oracle Business Application - Financial Analytics
    OBIA Release: 11.1.1.7.1
    OLTP Source: EBS, PSFT
    Issue(s):
    DiffmanLoad maps in reconciliation module runs slow in incremental loads.
    This issue is seen in AP, AR, GLREVN, GLCOGS module.

    Solution:
    DiffManLoad mappings are sourced from the reconciliation staging tables
    (RECON_FS tables mentioned below) which are populated by the
    DiffManDerive mappings for each module. Once the DiffManDerive mappings
    are complete in the LP, the statistics are not gathered for the RECON_FS
    tables thus causing the optimizer to use the last available stats on the
    RECON_FS tables which could be from a previous run and hence the optimizer
    chooses a wrong path which leads to performance issues.
    So, as a solution we will add a step to gather stats on RECON_FS table before
    the actual DiffManLoad mappings run.
    Steps: ODI Changes
    1) Login to ODI Designer tab.
    2) Open Load Plans and Scenarios and then expand BIAPPS Load
    Plan and find Load Plan Dev Components
    3) Expand SIL option inside Load Plan Dev Components
    4) This tech note explains the steps by considering AP module as an
    example. Please follow the same steps for each module. All the necessary
    information for each module is given in the table in step 9 below.
    5) Find the load plan component 3 SIL Fact APTRANS_FG for AP. Open this
    load plan component to edit it.
    6) You will have to add the EXEC_TABLE_MAINT_PROC scenario just before
    the DiffManLoad scenario.

    EXEC_TABLE_MAINT_PROC scenario can be found in ODI in below path as


    shown in screenshot below. Drag and drop it to the particular SIL Load
    plan component.

    7) After adding it to Load plan component, carry out these actions:


    1. Change the name of scenario from EXEC_TABLE_MAINT_PROC to
    EXEC_TABLE_MAINT_PROC - GATHER_STATS
    2. Change these options available in property Inspector for this scenario
    as shown in screenshot.
    a. DDL_RUN_MODE to GATHER_STATS
    b. DDL_TABLE_LIST to W_AP_XACT_RECON_FS
    3. Make the version of this scenario as -1.

    4. Change restart option as Restart from failed step


    8) Finally the new scenario added would appear as below. Validate and save
    your changes.

    9) Repeat the steps from 5 through 8 for each module mentioned in the table
    below. The necessary information for each module is present in the same
    table.
    MODU
    LE
    AP
    AR
    GLREV
    N
    GLCOG
    S

    Load Plan
    Components
    3 SIL Fact
    APTRANS_FG
    3 SIL Fact
    ARTRANS_FG
    3 SIL Fact GLREVN_FG
    3 SIL Fact GLCOGS_FG

    RECON_FS table
    for each module
    W_AP_XACT_RECON
    _FS
    W_AR_XACT_RECO
    N_FS
    W_GL_REVN_RECO
    N_FS
    W_GL_COGS_RECO
    N_FS

    Name of DiffManLoad
    Scenario in LP
    SIL_APTRANSACTIONFACT_DIFFMA
    NLOAD
    SIL_ARTRANSACTIONFACT_DIFFMA
    NLOAD
    SIL_GLREVENUEFACT_DIFFMANLO
    AD
    SIL_GLCOGSFACT_DIFFMANLOAD

    10)
    Generate a new load plan after making these changes and carry out
    incremental load.

    You might also like