Oracle BI Publisher SQL查询执行过慢及参数未生效问题求助

Let's work through your two problems step by step—the unrecognized :Username parameter and the slow query performance.

1. Fixing the :Username Parameter Recognition Problem #

The issue here is almost certainly logical operator precedence in your WHERE clause, not the parameter itself.

In Oracle, AND has higher priority than OR, so your current condition:

pu.USERNAME=:Username AND ROLE.ORG_ID IS NOT NULL OR ROLE.LEDGER_ID IS NOT NULL OR ...

Gets evaluated as:

(pu.USERNAME=:Username AND ROLE.ORG_ID IS NOT NULL) OR ROLE.LEDGER_ID IS NOT NULL OR ...

This means even if you pass a username, the query will return rows where any of the ROLE.* IS NOT NULL conditions are true—regardless of the username. That makes it look like the parameter isn't being recognized.

Fix: #

Wrap all the ROLE.* IS NOT NULL conditions in parentheses to group them correctly with the username filter:

pu.USERNAME=:Username 
AND (
    ROLE.ORG_ID IS NOT NULL 
    OR ROLE.LEDGER_ID IS NOT NULL 
    OR ROLE.BOOK_ID IS NOT NULL 
    OR ROLE.SET_ID IS NOT NULL 
    OR ROLE.INV_ORGANIZATION_ID IS NOT NULL 
    OR ROLE.CST_ORGANIZATION_ID IS NOT NULL 
    OR ROLE.ACCESS_SET_ID IS NOT NULL 
    OR ROLE.CONTROL_BUDGET_ID IS NOT NULL 
    OR ROLE.INTERCO_ORG_ID IS NOT NULL
)
AND pu.USER_GUID=role.USER_GUID

Also, double-check your BI Publisher parameter setup: ensure the parameter name matches Username (case-sensitive in some environments) and that it's configured as a string type matching the pu.username column.

2. Optimizing Query Performance #

The slow execution is caused by unrestricted Cartesian products from using old-style comma joins without explicit join conditions. Your query is joining all rows from every table together, which creates an enormous dataset before filtering.

Fix: Use Explicit LEFT JOINs #

Rewrite the query with explicit LEFT JOIN clauses to only match rows where the role's context ID matches the related table's ID. This eliminates unnecessary row combinations:

SELECT 
    pu.username, 
    ROLE.ROLE_NAME, 
    CASE 
        WHEN ROLE.ORG_ID IS NOT NULL THEN FABUV.BU_NAME
        WHEN ROLE.LEDGER_ID IS NOT NULL THEN GL.NAME
        WHEN ROLE.BOOK_ID IS NOT NULL THEN FBC.BOOK_TYPE_NAME
        WHEN ROLE.SET_ID IS NOT NULL THEN FSSV.SET_NAME
        WHEN ROLE.INV_ORGANIZATION_ID IS NOT NULL THEN IOP.ORGANIZATION_CODE
        WHEN ROLE.CST_ORGANIZATION_ID IS NOT NULL THEN CCOV.COST_ORG_CODE
        WHEN ROLE.ACCESS_SET_ID IS NOT NULL THEN GAS.NAME
        WHEN ROLE.CONTROL_BUDGET_ID IS NOT NULL THEN XCB.NAME
        WHEN ROLE.INTERCO_ORG_ID IS NOT NULL THEN FIO.INTERCO_ORG_NAME
        ELSE 'NOT_APPLICABLE' 
    END AS "Security_Context_Value"
FROM fusion.per_users pu
JOIN fusion.FUN_USER_ROLE_DATA_ASGNMNTS ROLE 
    ON pu.USER_GUID = ROLE.USER_GUID
LEFT JOIN fusion.FUN_ALL_BUSINESS_UNITS_V FABUV 
    ON ROLE.ORG_ID = FABUV.BU_ID
LEFT JOIN fusion.GL_LEDGERS GL 
    ON ROLE.LEDGER_ID = GL.LEDGER_ID
LEFT JOIN fusion.FA_BOOK_CONTROLS FBC 
    ON ROLE.BOOK_ID = FBC.BOOK_CONTROL_ID
LEFT JOIN fusion.FND_SETID_SETS_VL FSSV 
    ON ROLE.SET_ID = FSSV.SET_ID
LEFT JOIN fusion.INV_ORG_PARAMETERS iOP 
    ON ROLE.INV_ORGANIZATION_ID = iOP.ORGANIZATION_ID
LEFT JOIN fusion.CST_COST_ORGS_V CCOV 
    ON ROLE.CST_ORGANIZATION_ID = CCOV.COST_ORG_ID
LEFT JOIN fusion.gl_access_sets GAS 
    ON ROLE.ACCESS_SET_ID = GAS.ACCESS_SET_ID
LEFT JOIN fusion.XCC_CONTROL_BUDGETS XCB 
    ON ROLE.CONTROL_BUDGET_ID = XCB.CONTROL_BUDGET_ID
LEFT JOIN fusion.FUN_INTERCO_ORGANIZATIONS FIO 
    ON ROLE.INTERCO_ORG_ID = FIO.INTERCO_ORG_ID
WHERE 
    pu.USERNAME = :Username
    AND (
        ROLE.ORG_ID IS NOT NULL 
        OR ROLE.LEDGER_ID IS NOT NULL 
        OR ROLE.BOOK_ID IS NOT NULL 
        OR ROLE.SET_ID IS NOT NULL 
        OR ROLE.INV_ORGANIZATION_ID IS NOT NULL 
        OR ROLE.CST_ORGANIZATION_ID IS NOT NULL 
        OR ROLE.ACCESS_SET_ID IS NOT NULL 
        OR ROLE.CONTROL_BUDGET_ID IS NOT NULL 
        OR ROLE.INTERCO_ORG_ID IS NOT NULL
    )

Additional Performance Tips: #

• Check Indexes: Ensure there are indexes on:
  • pu.username (for fast username lookups)
  • pu.USER_GUID and ROLE.USER_GUID (for the join between users and roles)
  • All the ROLE.*_ID columns (ORG_ID, LEDGER_ID, etc.) to speed up the left joins
• Simplify the CASE Statement: You can remove the redundant ((ROLE.X IS NOT NULL) AND ROLE.X = TABLE.X) checks in the CASE, since the LEFT JOIN already ensures that TABLE.X only matches when ROLE.X is not null.
• Test with EXPLAIN PLAN: Run an EXPLAIN PLAN on the query to see where the bottlenecks are (e.g., full table scans on large tables).

内容的提问来源于stack exchange，提问作者Saubby