My Oracle Support Banner

Fusion Applications (SAAS) BI Publisher Custom Report Performance and Availability Reference (Doc ID 2614917.1)

Last updated on APRIL 16, 2020

Applies to:

Oracle Fusion General Ledger Cloud Service - Version 11.13.19.01.0 and later
Oracle Fusion Order Management Cloud Service - Version 11.13.19.01.0 and later
Oracle Fusion Global Human Resources Cloud Service - Version 11.13.19.01.0 and later
Oracle Fusion Application Toolkit Cloud Service - Version 11.13.19.01.0 and later
Information in this document applies to any platform.

Purpose

Some Fusion Applications customers (or their application integration teams) create custom BI Publisher data models and reports in order to meet specific business requirements.  Unfortunately, due to the dynamic nature of the Fusion Applications environment, the performance of these custom reports may vary over time.  The purpose of this document is to help customers understand some of the reasons why this happens, as well as explain Oracle Support's position on BI Publisher custom reporting performance in Fusion Applications.   This document was created clearly outline Oracle's approach toward these types of issues and describe what Oracle can and cannot do as it pertains to custom report performance.

Scope

This document is intended for Fusion Applications administrators and users.   The reader should be familiar with BI Publisher administration, report/data model development procedures as well as database and sqlplus concepts including the following terms:

- SQL_ID:

For a specific SQL statement, the unique identifier of the parent cursor in the library cache. A hash function applied to the text of the SQL statement generates the SQL ID. The V$SQL.SQL_ID column displays the SQL ID.  The SQL_ID for the same sql code will be comparable as long as the path in the BI Catalog is the same.  For example this is to say that the SQL_ID for the SQL data set(s) in  /Shared Folders/Custom/My Folder/MyDataModel.xdm will be the same as long as the SQL code is not changed, parameters used are the same (bind variables should be used in most cases) and the path and Datamodel name are the same.  If the sql changes, different parameters are used, the Data Model is moved to a different path or the Data Model name is changed, then the SQL_ID may change.  This is important because part of analyzing and optimizing the execution of sql requires like:like comparisons.  Having a consistent SQL_ID will help with this.

- Execution/Explain Plan:

The combination of steps used by the database to execute a SQL statement. Each step either retrieves rows of data physically from the database or prepares them for the session issuing the statement. You can override execution plans by using a hint.

- Optimizer:

Built-in database software that determines the most efficient way to execute a SQL statement by considering factors related to the objects referenced and the conditions specified in the statement.

 

Source: Database SQL Tuning Guide 12C R1

 

Notes: 

The assistance which Oracle Support can provide on custom reports performance is limited to existing reports (same SQLID) with 30 days or more of history.  Reports in development (less than 30 days) or new reports can not be analyzed.

Since Fusion Apps R12, it has not been necessary to gather statistics on schema objects.  Oracle Fusion Apps has this statistics maintenance schedule:  Weekly stats are collected on Sunday morning for all tables. In addition, there is a special job to collect stats for rapidly changing tables that runs every 1/2 hour.

If you're creating custom reports to meet a business need and you think it might be something which other customers would like to have as well, you can suggest for Oracle to develop a seeded report so that you don't have to deal with maintaining custom code.  Go to https://cloudcustomerconnect.oracle.com/pages/home (login with your Oracle ID) and click on the Ideas menu item.

 

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.