If you've spent any time with Oracle in a professional environment, you've hit this wall. The SQL is identical. The indexes exist. The schema matches. And production is crawling while development flies.

The shallow answer is "update your stats." The real answer is more interesting, and understanding it will change how you approach Oracle performance.

The Short Version

Oracle's query optimizer doesn't execute your SQL blindly. It builds an execution plan, a step-by-step strategy for retrieving your data, and it bases that plan on statistics, parameters, and runtime conditions that are almost never truly identical between development and production. Change any one of those inputs, and you can get a completely different plan with completely different performance.

Cause 1: Optimizer Statistics Are Different

This is the most common culprit, and it's often misunderstood.

Oracle's Cost-Based Optimizer uses statistics (row counts, column value distributions, index clustering factors) to estimate how many rows a given operation will return. Those cardinality estimates drive every plan decision: whether to use an index or a full table scan, which join order to choose, whether to use nested loops or a hash join.

In development, your tables might have 10,000 rows. In production, they have 50 million with a completely different distribution of values. Even if the statistics were gathered at the same point in time, the underlying data tells a different story, and the optimizer makes different choices.

What to check: 

-- Compare row counts and last stats gather time between environments
SELECT table_name, num_rows, last_analyzed
FROM dba_tables
WHERE owner = 'YOUR_SCHEMA'
ORDER BY table_name;

If last_analyzed is NULL or very old on key tables in production, that's your first problem. 

-- Gather fresh statistics on a table
BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname   => 'YOUR_SCHEMA',
        tabname   => 'YOUR_TABLE',
        cascade   => TRUE,
        method_opt => 'FOR ALL COLUMNS SIZE AUTO'
    );
END;
/

Cause 2: Bind Variable Peeking

This one surprises a lot of developers, especially those coming from other databases.

When Oracle first executes a query with bind variables (:start_date, :customer_id, etc.), it peeks at the actual values being passed and uses them to estimate cardinality. It then hard-codes a plan in the shared pool based on those values.

The problem: every subsequent execution reuses that cached plan, even if the bind values are radically different. A query optimized for :status = 'CLOSED' (99% of rows) will use the same plan when :status = 'PENDING' (0.1% of rows). That plan is probably wrong for the second case.

This is why performance can be inconsistent and hard to reproduce. It depends on which execution populated the shared pool first. 

-- Find the execution plan currently cached for a query
SELECT sql_id, child_number, executions, elapsed_time/executions avg_elapsed,
       plan_hash_value
FROM v$sql
WHERE sql_text LIKE '%your_table%'
AND parsing_schema_name = 'YOUR_SCHEMA'
ORDER BY elapsed_time DESC;

-- Then view the plan
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('your_sql_id', NULL, 'ALLSTATS LAST'));

Cause 3: Adaptive Cursor Sharing

Oracle introduced Adaptive Cursor Sharing (ACS) in 11g specifically to address bind variable peeking. The idea: Oracle monitors executions, detects when a cached plan performs poorly for different bind values, and creates multiple child cursors, each optimized for a different value range.

In theory, this is elegant. In practice, it can create cursor proliferation and instability. Plans flip between executions, and the "right" plan for a given bind value keeps changing as Oracle recalibrates.

If you see a high number of child cursors for a single sql_id, ACS may be in play: 

-- Check for cursor proliferation
SELECT sql_id, COUNT(*) child_cursors
FROM v$sql
WHERE parsing_schema_name = 'YOUR_SCHEMA'
GROUP BY sql_id
HAVING COUNT(*) > 10
ORDER BY child_cursors DESC;

Cause 4: Missing or Misleading Histograms

Histograms tell the optimizer about data skew, when values aren't evenly distributed. Without them, the optimizer assumes uniform distribution, which can cause catastrophic cardinality misestimates on skewed columns.

Classic example: a STATUS column where 95% of rows are 'CLOSED' and 5% are 'OPEN'. Without a histogram, the optimizer assumes each value represents roughly 50% of rows. With a histogram, it knows 'OPEN' is rare and can plan accordingly.

Development data is often cleaner and more uniform than production data. Histograms gathered on dev data may not represent production skew at all. 

-- Check histogram coverage on a table's columns
SELECT column_name, histogram, num_distinct, num_nulls, last_analyzed
FROM dba_tab_col_statistics
WHERE owner = 'YOUR_SCHEMA'
AND table_name = 'YOUR_TABLE'
ORDER BY column_name;

NONE in the HISTOGRAM column means no histogram exists. FREQUENCY or HEIGHT BALANCED means one does.

Cause 5: System Statistics Are Different

This is an advanced and frequently overlooked cause.

Oracle's optimizer doesn't just use object statistics. It also uses system statistics that describe the hardware: how fast I/O is, how fast the CPU is, how many blocks a multiblock read fetches. These are stored in sys.aux_stats$ and gathered via DBMS_STATS.GATHER_SYSTEM_STATS.

If dev runs on a laptop or a small VM and production runs on fast SAN storage with many CPUs, the optimizer's cost calculations will produce different results, even with identical object statistics and SQL. A full table scan that's "cheap" on fast I/O in production might look expensive to the optimizer if it was calibrated against slow dev storage. 

-- Check current system statistics
SELECT pname, pval1
FROM sys.aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

Key values to compare between environments: IOSEEKTIM, IOTFRSPEED, CPUSPEED, MBRC (multiblock read count).

Cause 6: Initialization Parameter Differences

Several Oracle parameters directly affect optimizer behavior. If dev and production have different values, you can get different plans even with identical data and statistics. 

-- Run in both environments and compare
SELECT name, value
FROM v$parameter
WHERE name IN (
    'optimizer_mode',
    'optimizer_features_enable',
    'db_file_multiblock_read_count',
    'parallel_degree_policy',
    'cursor_sharing',
    '_optim_peek_user_binds',
    'optimizer_adaptive_plans',
    'optimizer_adaptive_statistics'
)
ORDER BY name;

Pay particular attention to cursor_sharing. If it's set to FORCE in one environment and EXACT in the other, literal SQL will behave very differently.

Cause 7: SQL Plan Baselines and Outlines

In production environments managed by a DBA team, execution plans are sometimes pinned using SQL Plan Baselines (SPM) or the older SQL Profiles and Outlines. These override what the optimizer would naturally choose.

If a plan baseline exists in production for your query, the optimizer will use it regardless of what plan it would otherwise generate. This can be a good thing (stability) or a bad thing (a baseline from three years ago that no longer reflects current data volumes). 

-- Check for plan baselines affecting your query
SELECT sql_handle, plan_name, enabled, accepted, fixed, last_executed
FROM dba_sql_plan_baselines
WHERE sql_text LIKE '%your_table%'
ORDER BY last_executed DESC;

The Diagnostic Workflow

When you hit this problem in production, work through it in this order:

  1. Get the actual execution plan from production. Not what you think it should be. What it actually is. Use DBMS_XPLAN.DISPLAY_CURSOR with ALLSTATS LAST to see actual vs. estimated row counts.
  2. Compare actual vs. estimated cardinality. Large gaps between E-Rows (estimated) and A-Rows (actual) tell you where the optimizer went wrong.
  3. Check statistics freshness. When were stats last gathered on the key tables and indexes?
  4. Check for histograms on the columns in your WHERE clause predicates.
  5. Compare initialization parameters between environments.
  6. Look for plan baselines or profiles that might be pinning a bad plan.
  7. Check system statistics if all else matches but costs still differ.
-- The most useful single query when diagnosing a slow query in production
SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        'your_sql_id',
        NULL,
        'ALLSTATS LAST +PEEKED_BINDS +OUTLINE'
    )
);

The +PEEKED_BINDS flag shows you what bind values were peeked when the plan was compiled. The +OUTLINE flag shows hints that would reproduce the plan, useful for pinning a good plan once you find one.

The Underlying Lesson

Oracle's optimizer is a sophisticated cost-based engine making decisions under uncertainty. It does a remarkable job. But it's only as good as the information it has. The gap between development and production is almost always a gap in that information: different data, different statistics, different parameters, different hardware calibration.

The fix is rarely "the optimizer is broken." It's almost always "the optimizer made a reasonable decision based on incomplete or inaccurate inputs." Fix the inputs, and the optimizer usually fixes itself.

When it doesn't, you have tools: SQL profiles, plan baselines, hints. But reach for those only after you've addressed the root cause. Pinning a bad situation is not the same as fixing it.