BI Publisher Custom Report Performance and Availability Reference for OTM/GTM SaaS (Doc ID 2902055.1)

Last updated on MARCH 12, 2024

Applies to:

Purpose

Some customers create custom BI Publisher data models and reports in order to meet specific business requirements.  For multiple reasons, 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 OTM/GTM Applications.   This document was created to clearly outline Oracle's approach toward these types of issues and describe what Oracle can and cannot do as pertains to custom report performance.

Important:  All OTM/GTM BI Publisher report Developers and Administrators should review Oracle BI Publisher Best Practices for OTM/GTM SaaS (Doc ID 2902097.1).

Scope

This document is intended for OTM/GTM BI Publisher Administrators, Developers 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 the SQL ID for the SQL data set(s) in  /Shared Folders/Custom/My Folder/MyDataModel.xdm will not change 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 literal 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 allows this comparison.  To be considered as a performance issue Oracle Support can assist with, it is necessary that a SQL ID is in place for a minimum of 30 days.

- 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. 

- 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.

Details

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