Oracle Analytics Publisher

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Publisher

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Execution Plan is deferent in pre prod and prod for Custom BIP Report in Oracle ERP/CRM

Received Response
7
Views
1
Comments
Pradeep Challa
Pradeep Challa Rank 1 - Community Starter
in Oracle Analytics Publisher

We have a Custom BIP Report (involving large data in Incentive Compensation) is running longer in Prod than pre Prod. I see more full table scan in Prod than in pre prod which is recently cloned from Prod. When I compare the Execution Plans they are deferent in pre prod and prod for Custom BIP Report in Oracle ERP/CRM. I see more full table scan in Prod than in pre prod . Also Plan hash value is defferent. In stand alone database I can flush out the query to force oracle to pick a new execution plan but I dont have access in Oracle SaaS ERP. How do I force

DEV28_SUCCESS.log
PROD_FAILED.log

Oracle SaaS BIP report to pick same execution plan. Attached are the logs of pre prod (SUCCESS) and prod (FAILED)

Answers

  • Rajasekhar Bandaru-Oracle
    Rajasekhar Bandaru-Oracle Rank 6 - Analytics Lead

    Hi Pradeep

    Log shows the below error details

    Processing Exceptions: java.sql.SQLException: ORA-56735: elapsed time limit exceeded - call aborted

    Review the given documentation link about the Guard rail

    KB127409 FA-SAAS SQL Guardrails for Long-Running BI Reports and Queries

    Also, please review the documentation about Custom BI Report performance

    KB61670 Fusion Applications (SAAS) BI Publisher Custom Report Performance and Availability Reference

    Generally Sub-optimal SQL Queries cause this kind of issue hence we recommend you optimize custom SQL used in the datamodel by reviewing the validate Log option in the BIP datamodel.
    https://docs.oracle.com/en/cloud/saas/otbi/otbi-pub-design/validate-data-models.html#GUID-1FB8BC31-3546-4237-867C-4A592425395F
    Doc ID 2800118.1 Oracle Business Intelligence Publisher Performance Recommendations for Fusion Cloud Applications.