Oracle EBS 12.2.14 on Oracle Database 26ai: Diagnosing and Fixing a Wrong Join Method

At 15:47 on a Thursday, the Oracle E-Business Suite support team raised a P2 incident. The customer order summary page was timing out after 30 seconds. Users could open the Order Management responsibility, but when they searched for customer order history, the page failed or returned extremely slowly.

The application team confirmed that no personalization, form change, package deployment, or custom code change had been released that day.

The query behind the screen joined Oracle EBS Order Management and Trading Community Architecture data. It should normally return around 100 to 200 rows for one customer in less than one second. Instead, it was running for around 38 seconds.

The issue was not missing indexes. It was not blocked sessions. It was not hard parsing.

The optimizer was choosing the wrong join method because of incorrect cardinality estimates and a table-level parallel setting left behind after a bulk operation.

Environment

Oracle E-Business Suite 12.2.14
Oracle AI Database 26ai
Parsing schema: APPS
Main EBS objects:
- OE_ORDER_HEADERS_ALL
- OE_ORDER_LINES_ALL
- HZ_CUST_ACCOUNTS
Functional area: Order Management
Expected response time: less than 1 second
Actual response time: around 38 seconds
Root cause: Hash join on a small result set instead of nested loops with index access

Important EBS Schema Note

In Oracle EBS, the application usually connects through the APPS schema, but many application tables are owned by product schemas such as ONT, AR, INV, or others. The APPS schema commonly accesses these objects through synonyms.

Before checking table statistics, indexes, or table degree, always resolve the real base owner.

SELECT owner,
       synonym_name,
       table_owner,
       table_name
FROM   dba_synonyms
WHERE  owner = 'APPS'
AND    synonym_name IN ('OE_ORDER_HEADERS_ALL',
                        'OE_ORDER_LINES_ALL',
                        'HZ_CUST_ACCOUNTS');

Example result:

OWNER  SYNONYM_NAME           TABLE_OWNER  TABLE_NAME
-----  ---------------------  -----------  ---------------------
APPS   OE_ORDER_HEADERS_ALL   ONT          OE_ORDER_HEADERS_ALL
APPS   OE_ORDER_LINES_ALL     ONT          OE_ORDER_LINES_ALL
APPS   HZ_CUST_ACCOUNTS       AR           HZ_CUST_ACCOUNTS

In this article, APPS is the parsing schema, while the base table owners are resolved before checking metadata.

The Failing Query

SELECT hca.account_number,
       hca.account_name,
       ooh.header_id,
       ooh.order_number,
       ooh.ordered_date,
       ooh.flow_status_code,
       SUM(NVL(ool.ordered_quantity,0) * NVL(ool.unit_selling_price,0)) AS order_value
FROM   apps.oe_order_headers_all ooh
JOIN   apps.oe_order_lines_all   ool
       ON ool.header_id = ooh.header_id
JOIN   apps.hz_cust_accounts     hca
       ON hca.cust_account_id = ooh.sold_to_org_id
WHERE  ooh.sold_to_org_id = :p_customer_id
AND    ooh.flow_status_code IN ('BOOKED','ENTERED')
GROUP BY hca.account_number,
         hca.account_name,
         ooh.header_id,
         ooh.order_number,
         ooh.ordered_date,
         ooh.flow_status_code
ORDER BY ooh.ordered_date DESC;

This query should start with a small set of order headers for one customer, then join to order lines using HEADER_ID. The best plan should normally use nested loops and index access.

Root Cause Analysis

Oracle chooses join methods based on estimated rows, available indexes, join predicates, and cost.

Join Method	Best For
Nested Loops	Small outer result set with indexed inner table
Hash Join	Large joins and full scans
Sort Merge Join	Non-equijoins or sorted row sources

In this case, Oracle estimated that the customer filter would return thousands of order headers, even though the actual result was around 150 rows. Because of that incorrect estimate, the optimizer selected a hash join and scanned a very large volume of order line data.

A table-level parallel setting on OE_ORDER_LINES_ALL made the plan worse by making parallel full scans look cheaper.

Step 1: Check Active Session Wait Events

SELECT s.sid,
       s.serial#,
       s.username,
       s.status,
       s.event,
       s.sql_id,
       ROUND((SYSDATE - s.sql_exec_start) * 1440, 2) AS mins_running
FROM   v$session s
WHERE  s.username = 'APPS'
AND    s.status = 'ACTIVE'
ORDER BY s.seconds_in_wait DESC;

Observed wait events included:

direct path read temp
PX Deq: Execute Reply

This indicated that the query was using parallel execution and spilling to temporary tablespace during a hash join.

Step 2: Identify the High-Load SQL

SELECT sql_id,
       child_number,
       executions,
       ROUND(elapsed_time / NULLIF(executions,0) / 1e6, 2) AS avg_elapsed_secs,
       ROUND(buffer_gets  / NULLIF(executions,0))          AS avg_buffer_gets,
       ROUND(disk_reads   / NULLIF(executions,0))          AS avg_disk_reads,
       px_servers_executions,
       SUBSTR(sql_text,1,100) AS sql_text
FROM   v$sql
WHERE  parsing_schema_name = 'APPS'
AND    executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 10 ROWS ONLY;

Problem symptoms:

Average elapsed time: around 38 seconds
Logical reads: very high
Physical reads: high
PX executions: present

For a customer order lookup, this is not normal. This type of query should not read millions of blocks.

Step 3: Review the Actual Execution Plan

SELECT *
FROM   TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id          => 'your_sql_id',
    cursor_child_no => NULL,
    format          => 'ALLSTATS LAST +PREDICATE +NOTE'
  )
);

Problem indicators in the plan:

HASH JOIN
PX COORDINATOR
PX BLOCK ITERATOR
TABLE ACCESS FULL OE_ORDER_LINES_ALL

The important diagnostic point was the difference between estimated rows and actual rows.

Example:

E-Rows = 45000
A-Rows = 152

The optimizer expected a large row set, but the query returned a small row set. This wrong estimate caused Oracle to choose a hash join instead of nested loops.

Step 4: Check Column Statistics on EBS Order Headers

First, resolve the real owner.

SELECT table_owner
FROM   dba_synonyms
WHERE  owner = 'APPS'
AND    synonym_name = 'OE_ORDER_HEADERS_ALL';

Assume the base owner is ONT.

Now check statistics for the important filter columns.

SELECT column_name,
       num_distinct,
       density,
       histogram,
       num_buckets,
       last_analyzed
FROM   dba_tab_col_statistics
WHERE  owner = 'ONT'
AND    table_name = 'OE_ORDER_HEADERS_ALL'
AND    column_name IN ('SOLD_TO_ORG_ID','FLOW_STATUS_CODE');

Problem indicators:

Very low NUM_DISTINCT on SOLD_TO_ORG_ID
No useful histogram on skewed status values
Old or sample-based statistics
Cardinality estimate much higher than actual rows

Step 5: Check Index Availability

SELECT i.owner,
       i.index_name,
       i.status,
       i.visibility,
       i.degree,
       ic.column_name,
       ic.column_position
FROM   dba_indexes i
JOIN   dba_ind_columns ic
       ON  i.owner = ic.index_owner
       AND i.index_name = ic.index_name
WHERE  i.table_owner = 'ONT'
AND    i.table_name IN ('OE_ORDER_HEADERS_ALL','OE_ORDER_LINES_ALL')
AND    ic.column_name IN ('SOLD_TO_ORG_ID','FLOW_STATUS_CODE','HEADER_ID')
ORDER BY i.index_name, ic.column_position;

For this query, useful indexes normally include columns such as:

OE_ORDER_HEADERS_ALL.SOLD_TO_ORG_ID
OE_ORDER_HEADERS_ALL.FLOW_STATUS_CODE
OE_ORDER_HEADERS_ALL.HEADER_ID
OE_ORDER_LINES_ALL.HEADER_ID

The indexes existed and were valid. The issue was not index absence.

Step 6: Check Table Parallelism

SELECT owner,
       table_name,
       degree,
       num_rows,
       last_analyzed
FROM   dba_tables
WHERE  owner IN ('ONT','AR')
AND    table_name IN ('OE_ORDER_HEADERS_ALL',
                      'OE_ORDER_LINES_ALL',
                      'HZ_CUST_ACCOUNTS')
ORDER BY owner, table_name;

Problem case:

OWNER  TABLE_NAME            DEGREE
-----  --------------------  ------
ONT    OE_ORDER_LINES_ALL    6

DEGREE = 6 means table-level parallelism was enabled. In an OLTP-style EBS lookup, this can push the optimizer toward parallel full scans and hash joins.

Step 7: Confirm Library Cache Is Not the Root Cause

SELECT namespace,
       gets,
       gethits,
       ROUND(gethits / NULLIF(gets,0) * 100, 2) AS hit_ratio_pct,
       reloads,
       invalidations
FROM   v$librarycache
WHERE  namespace = 'SQL AREA';

Library cache was healthy. This confirmed the issue was not hard parsing or shared pool pressure.

Solution

Fix 1: Correct Optimizer Statistics

For EBS environments, always follow your organization’s EBS statistics strategy. In many EBS systems, statistics are managed through standard maintenance procedures and concurrent programs. For a targeted DBA fix in a controlled maintenance window, gather table statistics carefully.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname          => 'ONT',
    tabname          => 'OE_ORDER_HEADERS_ALL',
    method_opt       => 'FOR COLUMNS SOLD_TO_ORG_ID SIZE AUTO FOR COLUMNS FLOW_STATUS_CODE SIZE AUTO',
    cascade          => TRUE,
    no_invalidate    => FALSE
  );
END;
/

If SOLD_TO_ORG_ID is highly skewed, a histogram may be useful. Validate after gathering:

SELECT column_name,
       num_distinct,
       density,
       histogram,
       num_buckets,
       last_analyzed
FROM   dba_tab_col_statistics
WHERE  owner = 'ONT'
AND    table_name = 'OE_ORDER_HEADERS_ALL'
AND    column_name IN ('SOLD_TO_ORG_ID','FLOW_STATUS_CODE');

Fix 2: Remove Parallelism from OLTP EBS Table

ALTER TABLE ont.oe_order_lines_all NOPARALLEL;

Confirm:

SELECT owner,
       table_name,
       degree
FROM   dba_tables
WHERE  owner = 'ONT'
AND    table_name = 'OE_ORDER_LINES_ALL';

Expected:

DEGREE = 1

Fix 3: Allow a Fresh Parse

Because NO_INVALIDATE => FALSE was used while gathering statistics, dependent cursors should be invalidated. Re-execute the query and check the new plan.

Avoid flushing the entire shared pool in production unless there is a clear emergency and approval.

Fix 4: Consider SQL Plan Baseline for Stability

If this query is business-critical, capture the corrected plan.

DECLARE
  l_plans NUMBER;
BEGIN
  l_plans := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'your_sql_id'
  );

  DBMS_OUTPUT.PUT_LINE('Plans loaded: ' || l_plans);
END;
/

Verify:

SELECT sql_handle,
       plan_name,
       enabled,
       accepted,
       fixed,
       created
FROM   dba_sql_plan_baselines
WHERE  parsing_schema_name = 'APPS'
ORDER BY created DESC
FETCH FIRST 5 ROWS ONLY;

Validation After Fix

Run the query again and check the actual plan.

SELECT *
FROM   TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id => 'your_sql_id',
    cursor_child_no => NULL,
    format => 'ALLSTATS LAST +PREDICATE +NOTE'
  )
);

Expected plan behavior:

INDEX RANGE SCAN on OE_ORDER_HEADERS_ALL customer/status index
NESTED LOOPS
INDEX RANGE SCAN on OE_ORDER_LINES_ALL header_id index
No PX COORDINATOR
No full scan on OE_ORDER_LINES_ALL
No temp spill

Expected row estimate:

E-Rows close to A-Rows

Performance Improvement

Metric	Before Fix	After Fix
Average elapsed time	38 seconds	0.3 seconds
Logical reads	Millions	Under 1,000
Disk reads	High	Near zero
Join method	Hash join	Nested loops
Parallel execution	Yes	No
Temp usage	Yes	No

Understanding Join Behavior in Oracle EBS

In EBS OLTP screens, many queries are selective. They often start from a customer, order number, header ID, inventory item, organization ID, or transaction ID.

For this type of query:

Nested loops are usually efficient
Index access is usually expected
Hash joins are usually better for large reporting or batch joins
Parallel full scans can be dangerous in OLTP navigation paths

The optimizer is not making a random decision. It is responding to the information it has:

Statistics
Histograms
Parallel degree
Index availability
Bind variable selectivity
System settings

If that information is wrong, the plan can be wrong.

Quick Reference: Join Methods

Join Method	Best Use Case
Nested Loops	Small result set with indexed inner table
Hash Join	Large equijoins and reporting queries
Sort Merge Join	Range joins or pre-sorted row sources
Semijoin	EXISTS / IN where only first match matters
Antijoin	NOT EXISTS / NOT IN style logic

DBA Lessons from This Issue

Check E-Rows vs A-Rows first.

A large mismatch usually points to statistics, histograms, or bind selectivity issues.

Do not leave PARALLEL enabled on OLTP EBS tables.

Parallelism can be useful for batch processing, but it can damage OLTP lookup performance.

Resolve APPS synonyms before checking metadata.

The SQL may parse as APPS, but the table owner may be ONT, AR, INV, or another product schema.

Read the Note section in DBMS_XPLAN.

Oracle often tells you whether adaptive plans, dynamic statistics, SQL plan baselines, or parallelism influenced the plan.

Conclusion

This Oracle EBS 12.2.14 case shows how one wrong optimizer estimate can change the entire execution strategy.

The query should have used nested loops and index access. Instead, incorrect estimates and table-level parallelism pushed Oracle toward a parallel hash join and full scan path.

After correcting statistics and removing unnecessary parallelism:

The join method changed from hash join to nested loops
Full scans were eliminated
Temp usage disappeared
Response time improved from 38 seconds to 0.3 seconds

SQL tuning is not guesswork. In Oracle EBS, it is structured investigation using execution plans, optimizer statistics, and real runtime evidence.