Oracle EBS 12.2.14: How Bind Variable Peeking Silently Destroyed Query Performance Root Cause, Diagnosis, and Permanent Fix

At 09:15 on a Tuesday in an Oracle EBS 12.2.14 environment, the helpdesk started receiving calls. Customer service agents were using the same order inquiry screen, but experiencing completely different performance.

Some users were getting results in under a second. Others were hitting 30-second timeouts.

Same application. Same database. Same SQL.

So what changed?

This type of inconsistent behavior is a classic sign of bind variable peeking combined with data skew, where the optimizer locks in a plan that only works for a subset of values.

Problem Description

Environment

• Oracle E-Business Suite 12.2.14

• Oracle Database 19c (19.29 RU)

• Parsing schema: APPS

• Query: Order inquiry by customer ID

• Premium customers: < 1 second

• Standard customers: ~90 seconds

Important EBS Note

In Oracle EBS, application queries run under the APPS schema, but most tables are owned by product schemas such as ONT, AR, or INV.

👉 Always resolve the base table owner using DBA_SYNONYMS before checking statistics or indexes.

The Query

SELECT o.order_id,
       o.order_date,
       o.order_status,
       o.order_total,
       oi.unit_price,
       oi.quantity,
       p.product_name
FROM   apps.orders      o
JOIN   apps.order_items oi ON o.order_id  = oi.order_id
JOIN   apps.products    p  ON oi.product_id = p.product_id
WHERE  o.customer_id   = :customer_id
AND    o.order_status NOT IN ('CANCELLED', 'RETURNED')
ORDER BY o.order_date DESC;

Root Cause

Oracle performs bind variable peeking during the first hard parse.

• First execution used a low-volume customer → 4 rows

• Optimizer chose nested loops

• Plan stored and reused

Later:

• Same SQL used for high-volume customer → 800+ rows

• Same plan reused → catastrophic performance

👉 The optimizer was not wrong it was misled

Diagnosis

Step 1: Identify SQL Behavior

SELECT sql_id,
       child_number,
       executions,
       ROUND(elapsed_time/NULLIF(executions,0)/1e6,2) avg_secs,
       is_bind_sensitive,
       is_bind_aware
FROM   v$sql
WHERE  parsing_schema_name = 'APPS';

Step 2: Execution Plan Analysis

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

👉 Key finding:

• E-Rows: 4

• A-Rows: 847

👉 Huge mismatch

Step 3: Adaptive Cursor Sharing

SELECT *
FROM   v$sql_cs_statistics
WHERE  sql_id = 'your_sql_id';

👉 Shows multiple plans for different bind values

Step 4: Check Selectivity Ranges

SELECT *
FROM   v$sql_cs_selectivity
WHERE  sql_id = 'your_sql_id';

👉 Confirms Oracle detected different data patterns

Step 5: Histogram Check

SELECT column_name,
       histogram
FROM   dba_tab_col_statistics
WHERE  owner = 'APPS'
AND    table_name = 'ORDERS'
AND    column_name = 'CUSTOMER_ID';

👉 Result: NONE

👉 No histogram = optimizer cannot understand skew

Solution

Fix 1: Create Histogram

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname       => 'APPS',
    tabname       => 'ORDERS',
    method_opt    => 'FOR COLUMNS CUSTOMER_ID SIZE AUTO',
    no_invalidate => FALSE
  );
END;
/

👉 Histogram is critical when data is skewed and used in selective predicates

Fix 2: Re-execute SQL

👉 After statistics update, Oracle generates a new execution plan automatically

👉 Avoid manually purging shared pool in production unless under controlled DBA procedures

Fix 3: (Optional) Stabilize Plan

BEGIN
  DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
    sql_id => 'your_sql_id'
  );
END;
/

Validation

Expected Result

Key DBA Lessons

• Bind variable peeking can cause inconsistent performance

• Histograms are critical for skewed columns

• Always check E-Rows vs A-Rows

• Use Adaptive Cursor Sharing views

• Never change optimizer parameters in EBS without validation

Important EBS Guidance

Oracle EBS environments follow strict optimizer settings (MOS Note 169935.1).

👉 Do NOT change:

• CURSOR_SHARING

• OPTIMIZER_MODE

• Hidden optimizer parameters

without validation

Conclusion

This issue demonstrates how Oracle can produce drastically different performance outcomes for the same SQL due to data distribution.

The optimizer did exactly what it was designed to do but without a histogram, it lacked the information needed to choose the right plan for all cases.

Once corrected:

• Plans stabilized

• Performance normalized

• User experience restored
  

👉 SQL tuning is about understanding optimizer behavior not forcing it.