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

Question

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)

Rajasekhar Bandaru-Oracle · Answer

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.