Oracle EBS on Oracle Database 26ai: Diagnosing and Fixing Wrong Access Path Choices Full Table Scan vs Index Scan

At 14:22 on a Wednesday, the Oracle E-Business Suite (EBS) support team reported that order lookup screens were no longer meeting the expected SLA of under 1 second. Users experienced delays, and concurrent requests began to queue.

The application team confirmed there were no recent code changes. The issue appeared suddenly.

Within minutes of connecting to the database, session wait events indicated heavy I/O activity. However, the root cause was not a missing index or stale statistics.

The optimizer was choosing a full table scan over a valid composite index.

The actual cause was a table-level parallelism setting that unintentionally biased the optimizer toward full scans.

Problem Description

Environment

• Oracle E-Business Suite (EBS)

• Oracle Database 26ai

• Schema: APPS

• Table: OE_ORDER_HEADERS_ALL (~40M rows)

• Table: HZ_CUST_ACCOUNTS (~5M rows)

• Query type: order lookup by customer and status

• Expected response time: < 1 second

• Actual response time: ~50 seconds

The Failing Query

SELECT ooh.header_id,
       ooh.order_number,
       ooh.ordered_date,
       ooh.flow_status_code,
       hca.account_number,
       hca.account_name
FROM   apps.oe_order_headers_all ooh
JOIN   apps.hz_cust_accounts     hca
       ON ooh.sold_to_org_id = hca.cust_account_id
WHERE  ooh.sold_to_org_id = :p_customer_id
AND    ooh.flow_status_code IN ('BOOKED','ENTERED')
ORDER BY ooh.ordered_date DESC;

Root Cause Analysis

Common reasons for full table scan:

👉 In this case: parallelism

Diagnosis Using Oracle Views

Step 1: Check Active Sessions

SELECT sid,
       serial#,
       event,
       sql_id
FROM   v$session
WHERE  username = 'APPS'
AND    status   = 'ACTIVE';

👉 Observed wait event: db file scattered read

👉 Indicates full table scan

Step 2: Identify High-Load SQL

SELECT sql_id,
       executions,
       ROUND(elapsed_time/executions/1e6,2) avg_secs,
       buffer_gets/executions avg_buffer_gets,
       px_servers_executions
FROM   v$sql
WHERE  parsing_schema_name = 'APPS'
AND    executions > 0
ORDER BY elapsed_time DESC
FETCH FIRST 5 ROWS ONLY;

👉 Observed:

• ~50 seconds execution

• High buffer gets

• Parallel execution

Step 3: Execution Plan Analysis

SELECT *
FROM   TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    'your_sql_id',
    NULL,
    'ALLSTATS LAST +PREDICATE +NOTE'
  )
);

Key Findings

• TABLE ACCESS FULL on OE_ORDER_HEADERS_ALL

• Parallel execution (DOP > 1)

• Predicate applied as FILTER, not ACCESS

👉 Oracle is scanning all rows and filtering later

Step 4: Verify Index

SELECT index_name, status
FROM   dba_indexes
WHERE  owner = 'APPS'
AND    table_name = 'OE_ORDER_HEADERS_ALL';

👉 Index exists and is usable

Step 5: Check Table Parallelism

SELECT table_name, degree
FROM   dba_tables
WHERE  owner = 'APPS'
AND    table_name = 'OE_ORDER_HEADERS_ALL';

👉 DEGREE > 1 → Parallel enabled

👉 Root cause confirmed

Solution

Fix 1: Disable Parallelism

ALTER TABLE apps.oe_order_headers_all NOPARALLEL;

Fix 2: Gather Fresh Statistics

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => 'APPS',
    tabname => 'OE_ORDER_HEADERS_ALL'
  );
END;
/

Validation

Execution Plan After Fix

SELECT *
FROM   TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    'your_sql_id',
    NULL,
    'ALLSTATS LAST'
  )
);

👉 Expected:

• INDEX RANGE SCAN

• TABLE ACCESS BY ROWID

• No parallel execution

Performance Improvement

Understanding Access Paths

Oracle uses different access paths depending on data distribution and cost:

👉 Key concept:

• ACCESS predicate → Efficient (uses index)

• FILTER predicate → Inefficient (applied after scan)
  

👉 In this issue:

• Before fix → FILTER

• After fix → ACCESS
  

Key DBA Lessons

• Do not enable PARALLEL on OLTP EBS tables

• Always check DBA_TABLES.DEGREE

• Always validate execution plan changes after performance issues

👉 Oracle often shows the root cause—you just need to read the plan correctly

Conclusion

This issue demonstrates how configuration not SQL can break performance.

In Oracle EBS environments:

• OLTP queries must use index access paths

• Parallel full scans can severely impact response time

• Execution plan analysis is critical for accurate troubleshooting
  

👉 Performance tuning is not guesswork it is structured analysis based on real execution data.