Memory errors can occur occassionally when refresh requests are processed in Oracle Fusion. They are potentially caused by one, or a combination, of two things:
- An Oracle resource constraint
- The server (which hosts the Gateway/Integration Runtime/Tableau Bridge) CPU usage hits 100% at any time during refresh attempt
The causes above and the applicable solutions are shared in detail below.
Potential Cause 1 — Oracle resource constraint
Oracle has limited resources to process refresh requests from external applications. Firing many requests concurrently can make the server return a memory error.
Potential solutions that help eliminate memory errors due to Oracle resource constaints:
The memory errors are eliminated by any or a combination of the solutions shared below based on the exact scenario you have -
- Schedule automated refreshes during off-peak hours when majority users are inactive.
- Ensure a appropriate time gap between two consecutive refresh attempts of the same dataset.
For example, assume a query takes about 30 minutes to run and return the dataset with millions of records. If this query is attempted to be refreshed every 15 minutes, the subsequent refresh attempt triggers even before the previous refresh request is completed, and could potentially result in memory errors. - Spread out the refresh requests across time for different queries so you are not sending several requests at the same time.
- Keep the queries well-optimized and implement incremental strategies to ensure a quick data return. The faster a query runs and returns the data, the memory resources are freed up for the next.
- Split large queries with a common join key and join them downstream — for example, the AR Register and AR Aging split described in BI Publisher Data Model Timeouts.
- Perform transformations downstream as much as possible rather than inside the BI Publisher Data Model query or in the OTBI report.
- Additionally, ensure the queries of transformations performed downstream are not routed back to the source.
For example, merging two tables in the Power Query Editor in Power BI Desktop or Dataflow routes the query back to the source. Instead create the two tables as two separate dataflows wihtout any transformations in Power BI Service, and then connect to those dataflows from Power BI Desktop for performing transofrmations and dashboarding. This way, the transformations are applied only on the Dataflow and not routed directly to Fusion, keeping the resources available in Fusion, and also helps avoid errors raising due to query syntax mismatch between the source and destination.
The same approach works for Tableau as well by publishing the Data Source as-is and connecting to that Data Source for transformations and Dashboarding. - Reduce the number of rows per fetch in the BI Connector, when connecting to a Data Model or pulling data for a custom query built in the BI Connector IDE.
Potential Cause 2 — Server hitting full CPU capacity
Check the Gateway (or) Integration Runtime (or) Tableau Bridge server's CPU usage history around the time of the error. If it peaked at 100% at any point, the server's CPU capacity needs to be increased.
Solutions to try when server hits its full capacity during refresh attempt:
- As a best practice, dedicate a separate server to BI Connector so its resources are fully available for automating Oracle Fusion data refreshes.
- If other data sources (outside BI Connector) share the same server for refreshes, either spread those refreshes across the day, increase the CPU capacity, or both.