Oracle Fusion BI Publisher SQL Performance: 12 Ways to Make Reports Faster

Slow Oracle Fusion BI Publisher reports can come from several layers: SQL, data-model design, XML volume, template processing, or simply returning far more data than the report needs. SQL is often a major part of the problem, but tuning starts with correctness: a fast query that joins the wrong Fusion objects is still the wrong query.

This guide focuses on SQL and data-model choices that are practical in Oracle Fusion Cloud. The examples below use current Fusion table and column names, distinguish Business Unit, ledger, and legal entity correctly, and avoid inventing execution plans or timing claims that cannot be reproduced in your pod.

Illustrative Case Study: Fix the Join Path Before You Tune

A Common but Incorrect Join Pattern

SELECT ai.invoice_num,
       ai.invoice_date,
       ac.check_number,
       pn.display_name
FROM   ap_invoices_all ai
JOIN   ap_checks_all ac
       ON ac.vendor_id = ai.vendor_id
JOIN   per_all_people_f p
       ON TO_CHAR(p.person_id) = ai.created_by
JOIN   per_person_names_f pn
       ON pn.person_id = p.person_id
WHERE  ai.invoice_date >= TRUNC(SYSDATE) - 365;

Correct Fusion Join Path

SELECT ai.invoice_num,
       ai.invoice_date,
       ai.invoice_amount,
       ai.invoice_currency_code,
       ai.payment_status_flag,
       hp.party_name          AS supplier_name,
       ac.check_number,
       ac.check_date,
       aip.amount             AS invoice_payment_amount,
       ai.created_by          AS created_by_username,
       pn.display_name        AS created_by_name
FROM   ap_invoices_all ai
JOIN   ap_invoice_payments_all aip
       ON  aip.invoice_id = ai.invoice_id
       AND aip.org_id     = ai.org_id
JOIN   ap_checks_all ac
       ON  ac.check_id = aip.check_id
       AND ac.org_id   = ai.org_id
LEFT JOIN poz_suppliers ps
       ON  ps.vendor_id = ai.vendor_id
LEFT JOIN hz_parties hp
       ON  hp.party_id = ps.party_id
LEFT JOIN per_users pu
       ON  pu.username = ai.created_by
LEFT JOIN per_person_names_f pn
       ON  pn.person_id = pu.person_id
       AND pn.name_type = 'GLOBAL'
       AND TRUNC(SYSDATE) BETWEEN pn.effective_start_date
                              AND pn.effective_end_date
WHERE  ai.org_id = :p_org_id
AND    ai.set_of_books_id = :p_ledger_id
AND    ai.invoice_date >= TRUNC(CAST(:p_from_date AS DATE))
AND    ai.invoice_date <  TRUNC(CAST(:p_to_date AS DATE)) + 1
AND    ai.cancelled_date IS NULL;

The bridge between invoice and payment is AP_INVOICE_PAYMENTS_ALL: INVOICE_ID identifies the invoice and CHECK_ID identifies the payment. Its AMOUNT is the amount applied to that invoice/payment relationship. By contrast, AP_CHECKS_ALL.AMOUNT is the total payment amount.

If the report must distinguish voided or reversed payments, add business rules for AP_INVOICE_PAYMENTS_ALL.REVERSAL_FLAG and/or the payment status in AP_CHECKS_ALL; the exact filter depends on whether the report should show reversals, original payments, or both.

Why Oracle Fusion BI Publisher Reports Become Slow

The recurring causes are usually a combination of data correctness and workload size:

• Missing effective-date predicates on date-tracked HCM objects
• Incorrect one-to-many joins that multiply rows
• Using the wrong Fusion object or joining audit usernames as person IDs
• Returning unnecessary columns or rows
• Non-SARGable predicates on large transaction tables
• Unbounded parameters or filters that do not match the report's Business Unit / ledger scope
• Excessive linked or nested data sets
• Unnecessary sorting and XML generation

Oracle Fusion schemas are highly normalized, HCM is extensively date-effective, and ERP transaction volumes can become very large. The best tuning work therefore starts by validating table semantics and join cardinality before changing SQL syntax.

12 SQL Optimization Techniques

Tables such as PER_ALL_PEOPLE_F, PER_PERSON_NAMES_F, and PER_ALL_ASSIGNMENTS_M contain historical versions. Each date-effective object needs its own as-of-date condition. For PER_ALL_ASSIGNMENTS_M, which allows multiple changes in a day, use EFFECTIVE_LATEST_CHANGE = 'Y' when you want the latest row for that effective date.

SELECT p.person_number,
       n.display_name,
       a.assignment_number
FROM   per_all_people_f p
JOIN   per_person_names_f n
       ON  n.person_id = p.person_id
       AND n.name_type = 'GLOBAL'
       AND :p_as_of_date BETWEEN n.effective_start_date
                             AND n.effective_end_date
JOIN   per_all_assignments_m a
       ON  a.person_id = p.person_id
       AND :p_as_of_date BETWEEN a.effective_start_date
                             AND a.effective_end_date
       AND a.effective_latest_change = 'Y'
       AND a.primary_flag = 'Y'
       AND a.assignment_type = 'E'
WHERE  :p_as_of_date BETWEEN p.effective_start_date
                         AND p.effective_end_date;

Wide Fusion tables can generate much larger JDBC result sets and XML payloads than the template needs. Select only the fields used by the layout, bursting logic, grouping, or downstream calculations.

SELECT *
FROM   ap_invoices_all;

SELECT invoice_id,
       invoice_num,
       invoice_date,
       invoice_amount,
       payment_status_flag
FROM   ap_invoices_all;

Do not treat Business Unit, ledger, and legal entity as interchangeable. On AP_INVOICES_ALL, ORG_ID identifies the Business Unit, SET_OF_BOOKS_ID identifies the ledger, and LEGAL_ENTITY_ID identifies the legal entity. Filter by the dimension the report actually requires.

WHERE ai.org_id = :p_org_id
AND   ai.set_of_books_id = :p_ledger_id
AND   ai.invoice_date >= TRUNC(CAST(:p_from_date AS DATE))
AND   ai.invoice_date <  TRUNC(CAST(:p_to_date AS DATE)) + 1

Apply only filters that are semantically valid for the report. A filter is not a security substitute; BI Publisher data access still depends on the data source and report security model.

Applying a function to a search column can make a normal index less useful. It is not true that functions always prevent index access—Oracle supports function-based indexes—but in Fusion SaaS you do not control the application indexes. For date ranges, a half-open range is usually the safer pattern.

WHERE TRUNC(ai.invoice_date) = TRUNC(SYSDATE)

WHERE ai.invoice_date >= TRUNC(SYSDATE)
AND   ai.invoice_date <  TRUNC(SYSDATE) + 1

Oracle currently documents AP_INVOICES_N5 on (INVOICE_DATE, ORG_ID); do not invent index names or column orders in published execution-plan examples.

Two tables sharing VENDOR_ID does not mean that VENDOR_ID describes the transaction relationship between them. For invoice-to-payment reporting, use AP_INVOICE_PAYMENTS_ALL.

FROM   ap_invoices_all ai
JOIN   ap_invoice_payments_all aip
       ON aip.invoice_id = ai.invoice_id
JOIN   ap_checks_all ac
       ON ac.check_id = aip.check_id

This prevents the classic many-to-many multiplication caused by joining all invoices and all payments of the same supplier together.

EXISTS is useful when you only need to know whether a related row exists. It avoids joining child rows into the result and then using DISTINCT merely to undo duplicates. Do not replace every join or subquery with EXISTS; Oracle can also transform or unnest subqueries, so compare plans and cardinality.

SELECT p.person_id
FROM   per_all_people_f p
WHERE  :p_as_of_date BETWEEN p.effective_start_date
                         AND p.effective_end_date
AND    EXISTS (
    SELECT 1
    FROM   per_all_assignments_m a
    WHERE  a.person_id = p.person_id
    AND    :p_as_of_date BETWEEN a.effective_start_date
                             AND a.effective_end_date
    AND    a.effective_latest_change = 'Y'
    AND    a.primary_flag = 'Y'
    AND    a.assignment_type = 'E'
);

In current Fusion Procurement, the supplier base table is POZ_SUPPLIERS. It stores VENDOR_ID and PARTY_ID; the supplier party name is held in HZ_PARTIES.PARTY_NAME. Do not use the E-Business Suite-era AP_SUPPLIERS name in Fusion examples.

SELECT ps.vendor_id,
       ps.segment1     AS supplier_number,
       hp.party_name   AS supplier_name
FROM   poz_suppliers ps
JOIN   hz_parties hp
       ON hp.party_id = ps.party_id;

For translated _TL objects, include the appropriate language predicate when you join the translation table directly. Use documented _VL views when they fit the reporting requirement rather than assuming every _VL object is a simple table alias.

BI Publisher supports a Date parameter type and binds Date parameters as timestamp objects. Avoid implicit string-to-date conversion, and keep functions off the Fusion filter column when you can. For an inclusive calendar-date range against a DATE column, normalize the bind side and use a half-open range.

WHERE ai.invoice_date >= TRUNC(CAST(:p_from_date AS DATE))
AND   ai.invoice_date <  TRUNC(CAST(:p_to_date AS DATE)) + 1

If you intentionally define the Publisher parameter as a String, use a controlled format and explicit conversion on the bind. Also keep multi-value parameters bounded: Publisher validation warns when a parameter expands to excessive bind values because parsing and execution can degrade.

Multiple BI Publisher data sets are useful when the data really comes from separate sources or the structure cannot be produced cleanly in one query. They are not a default performance optimization. Oracle Publisher guidance recommends reducing the number of data sets/queries where possible, and nested parent-child data sets can execute the child query for every parent row.

Build complex SQL in stages and inspect row counts, but do not expect every join to leave the count unchanged. A legitimate one-to-many relationship should increase rows. The question is whether the increase matches the expected business cardinality.

-- Baseline invoices
SELECT COUNT(*)
FROM   ap_invoices_all ai
WHERE  ai.org_id = :p_org_id;

-- Compare relationship count after adding invoice payments
SELECT COUNT(*)
FROM   ap_invoices_all ai
JOIN   ap_invoice_payments_all aip
       ON aip.invoice_id = ai.invoice_id
WHERE  ai.org_id = :p_org_id;

If the second count rises, that can be correct because one invoice can have multiple payment rows. Validate the grain explicitly: invoice, invoice payment, payment, distribution, assignment, and so on.

Sorting a large data set can consume CPU and memory, so do not add ORDER BY merely out of habit. But do not automatically push every sort into the template either: Oracle Publisher specifically recommends sorting at data generation time when the report needs a known group-break or final sort order. Keep SQL sorting when it is part of the report semantics; remove it when the layout does not depend on row order.

-- Do not sort only because the template happens to display this order
SELECT ai.invoice_num,
       ai.invoice_date,
       ai.invoice_amount
FROM   ap_invoices_all ai
WHERE  ai.org_id = :p_org_id;

Reports evolve. Fields, LOVs, and data sets that were once required can remain long after the layout stops using them. Remove unused select-list columns and unused data sets where possible, review large XML outputs, and use Publisher validation to catch common data-model issues such as SELECT *.

Oracle Fusion-Specific Performance Considerations

Generic Oracle SQL tuning still applies, but Fusion adds object semantics that matter just as much as the execution plan.

Anti-Pattern Example: Five Mistakes in One Query

This first query is intentionally wrong. Every referenced object and column exists, but the business relationships and filtering strategy are poor.

-- 1: SELECT * from wide tables
-- 2: Missing effective-date filters on HCM tables
-- 3: Supplier-level key used as if it were an invoice-payment relationship
-- 4: Who-column username treated like a person identifier
-- 5: Function applied to the invoice date and no BU/ledger scope
SELECT *
FROM   ap_invoices_all ai
JOIN   ap_checks_all ac
       ON ac.vendor_id = ai.vendor_id
JOIN   per_all_people_f p
       ON TO_CHAR(p.person_id) = ai.created_by
JOIN   per_person_names_f pn
       ON pn.person_id = p.person_id
WHERE  TRUNC(ai.invoice_date) >= TRUNC(SYSDATE) - 365;

SELECT ai.invoice_num,
       ai.invoice_date,
       ai.invoice_amount,
       hp.party_name       AS supplier_name,
       ac.check_number,
       ac.check_date,
       aip.amount          AS invoice_payment_amount,
       ai.created_by       AS created_by_username,
       pn.display_name     AS created_by_name
FROM   ap_invoices_all ai
JOIN   ap_invoice_payments_all aip
       ON  aip.invoice_id = ai.invoice_id
       AND aip.org_id     = ai.org_id
JOIN   ap_checks_all ac
       ON  ac.check_id = aip.check_id
       AND ac.org_id   = ai.org_id
LEFT JOIN poz_suppliers ps
       ON ps.vendor_id = ai.vendor_id
LEFT JOIN hz_parties hp
       ON hp.party_id = ps.party_id
LEFT JOIN per_users pu
       ON pu.username = ai.created_by
LEFT JOIN per_person_names_f pn
       ON  pn.person_id = pu.person_id
       AND pn.name_type = 'GLOBAL'
       AND :p_as_of_date BETWEEN pn.effective_start_date
                             AND pn.effective_end_date
WHERE  ai.org_id = :p_org_id
AND    ai.set_of_books_id = :p_ledger_id
AND    ai.invoice_date >= TRUNC(CAST(:p_from_date AS DATE))
AND    ai.invoice_date <  TRUNC(CAST(:p_to_date AS DATE)) + 1
AND    ai.cancelled_date IS NULL;

Notice what changed: the payment bridge is explicit, supplier name comes from the Trading Community party model, CREATED_BY resolves through PER_USERS, HCM names are date-effective, and the ERP scope uses the actual Fusion columns.

Use BI Publisher's Native Performance Controls

SQL tuning is only one part of the Publisher pipeline. Oracle exposes data-model controls and diagnostics that help distinguish a slow database query from excessive XML generation or layout processing.

Validate and Optimize SQL Before BI Publisher

A repeatable workflow is more reliable than tuning by intuition:

Technical References

For schema-sensitive examples, use Oracle's current Tables and Views documentation as the source of truth. The most relevant references for this article are:

• AP_INVOICES_ALL — invoice columns, foreign keys, and indexes
• AP_INVOICE_PAYMENTS_ALL — invoice/payment bridge
• AP_CHECKS_ALL — payment header
• POZ_SUPPLIERS — supplier master and PARTY_ID
• PER_ALL_ASSIGNMENTS_M — date-effective assignment model
• Validate Data Models — Publisher validation warnings and explain plans
• Best Practices for SQL Datasets — data volume, multiple/nested datasets, bind values, grouping and sorting
• Data Model Properties — SQL pruning, Skip Unused Dataset Query, tracing and XML pruning
• Tune SQL Query — explain plans, SQL Monitor and scheduler diagnostics

Final Thoughts

Oracle Fusion BI Publisher performance is not about memorizing a list of 'fast SQL' tricks. Correct object semantics, correct row grain, correct join paths, bounded parameters, Publisher data-model settings, and measured execution plans matter more than cosmetic rewrites.

Start by proving that the SQL returns the right rows. Then reduce unnecessary work, validate the data model, inspect the actual plan, and measure the full report pipeline. That approach produces reports that are both faster and more trustworthy as Fusion data volumes grow.

Try FusionLens SQL to explore Oracle Fusion schemas, discover join paths, and test SQL before moving it into BI Publisher.