Fusion Apps: Identify & Troubleshoot Slow Access to Custom Objects

Introduction

Oracle Fusion Applications provide robust customization capabilities, allowing implementors and developers to create Custom Objects within CRM Cloud, ERP, and SCM Cloud using the Application Composer.

However, we’ve noticed performance issues related to slow query execution when accessing these custom objects. Specifically, there are scenarios where fields marked as “indexed” in Application Composer are not actually indexed at the database level, leading to significant performance degradation.

In this blog post, we’ll show you how to identify this indexing mismatch and provide solutions to address this common performance bottleneck.

Understanding the Issue

When creating custom objects in Application Composer, Oracle Fusion allows you to mark specific fields as indexed. This indexing is supposed to optimize database queries and enhance application performance.

Unfortunately, we’ve found cases where:

✅ Fields marked as indexed do not have corresponding database indexes.

✅ This leads to slow query performance, especially noticeable in large-scale deployments.

How to Identify Missing Indexes

To diagnose whether fields marked as indexed in Application Composer actually have database-level indexes, use the following SQL query:

select 
    tu.context_column_value object_name, 
    t.table_name, 
    cu.attribute_name, 
    c.column_name, 
    c.logical_datatype,
    c.indexed,
    (select decode(count(1), 0, 'N', 'Y') 
     from all_ind_columns ic 
     where ic.table_name = lower(t.table_name) 
     and ic.column_name = c.column_name ) real_indexed
from   
    adf_extensible_table t,
    adf_extension_column c,
    adf_extension_column_usage cu,
    adf_extensible_table_usage tu
where  
    c.table_id = t.table_id
    and cu.column_id = c.column_id
    and t.custom_object_table ='Y'
    and c.indexed = 'Y'
    and t.table_id = tu.table_id
    and tu.usage_id = cu.table_usage_id

Explanation of the Query:

• object_name: Name of the Custom Object.

• table_name: Database table corresponding to the Custom Object.

• attribute_name: Logical attribute name of the field.

• column_name: Physical database column name.

• logical_datatype: Data type of the field.

• indexed: Indicates if marked as indexed in Application Composer.

• real_indexed: Indicates (‘Y’ or ‘N’) if an actual database index exists.

If the result shows “indexed” as ‘Y’ but “real_indexed” as ‘N’, this indicates a problematic mismatch.

Resolving the Indexing Mismatch

Once identified, there are two practical solutions to resolve the indexing issue:

Solution 1: Recreate the Indexed Field

• Create a new field within Application Composer.

• Ensure it is marked as indexed and verify with the query above.

• Migrate data from the problematic field to the newly indexed field.

Important Note:

• ERP and SCM Clouds: TEXT fields marked as indexed typically do not get indexed automatically.

• CRM Cloud: TEXT fields marked as indexed usually do get indexed automatically.

Solution 2: Relocate Custom Object

• If practical, consider moving the entire Custom Object from ERP and SCM Cloud to the CRM Cloud section, where indexing functionality is consistent and reliable for TEXT fields.

Benefits of Identifying and Fixing the Issue

✅ Enhanced Performance: Immediate improvement in query response times.

✅ Improved User Experience: Faster access to data, improving operational efficiency.

✅ Optimized Database Utilization: Proper indexing helps maintain database health and scalability.

Conclusion

Ensuring that fields marked as indexed are genuinely indexed at the database level is crucial for performance in Oracle Fusion Applications. Using the provided SQL query, you can proactively identify and fix indexing mismatches, significantly improving custom object query performance.

🔹 Need assistance optimizing your Fusion Apps environment?

📢 Contact CID Solutions today for tailored solutions and performance optimization support!