Let's break down why you're seeing such a drastic performance gap between your test and production environments—even with identical optimizer parameters—and what you can do to fix it at the Oracle level.

Possible Root Causes #

Matching optimizer settings don't guarantee identical execution plans. Here are the most likely culprits:

1. Outdated or Inaccurate Statistics #

The optimizer relies entirely on table and index statistics to calculate costs. If your test environment's stats for D_VIEW (or its underlying base tables) are stale, missing histograms, or don't reflect real-world data distribution:

• The optimizer might miscalculate the selectivity of D.A_ID or D.C_ID, deciding a full table scan is cheaper than index access.
• Production likely has up-to-date stats that accurately capture how many rows match the OR condition, making index-based access look far more cost-effective.

2. Bind Variable Peeking #

Your query uses the bind variable :TYPE0. Oracle's bind variable peeking locks in an execution plan based on the first value passed to the bind variable. If:

• Test's first execution used a :TYPE0 value that returns a massive subset of A_VIEW, the optimizer chose a full scan to handle high row volume.
• Production's first execution used a more selective :TYPE0 value, leading the optimizer to pick index-based access instead.

3. SQL Plan Baseline Differences #

Since optimizer_use_sql_plan_baselines is enabled:

• Production might have an accepted baseline that preserves the efficient index-based plan.
• Test either lacks this baseline, or has a baseline generated from a suboptimal execution (like the full table scan) that's being enforced.

4. Subtle Physical Differences #

Even with identical schema structures, small physical gaps can matter:

• Index fragmentation in test might make index access appear more costly to the optimizer.
• Production's indexes could have a better clustering factor (alignment between index and table rows), reducing the cost of index range scans.

Troubleshooting Steps #

To narrow down the cause:

• Verify Statistics:
  Compare table and index stats across environments:

  -- Check D_VIEW table stats
  SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME = 'D_VIEW';
  -- Check D_VIEW index stats
  SELECT OWNER, INDEX_NAME, TABLE_NAME, NUM_ROWS, LAST_ANALYZED, CLUSTERING_FACTOR FROM DBA_INDEXES WHERE TABLE_NAME = 'D_VIEW';
  

• Inspect Bind Variable Values:
  Use V$SQL_BIND_CAPTURE to see what :TYPE0 values were used when the plan was generated:

  SELECT SQL_ID, NAME, VALUE_STRING FROM V$SQL_BIND_CAPTURE 
  WHERE SQL_ID = (SELECT SQL_ID FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT A.Id as ObjectID%');
  

• Check SQL Plan Baselines:
  List baselines tied to your query to see if test is using a suboptimal accepted plan:

  SELECT SQL_HANDLE, PLAN_NAME, ACCEPTED, ENABLED FROM DBA_SQL_PLAN_BASELINES 
  WHERE SQL_TEXT LIKE '%SELECT A.Id as ObjectID%';
  

• Compare Execution Plan Costs:
  Generate and compare explain plans in both environments to see why the optimizer chose different paths:

  EXPLAIN PLAN FOR -- Paste your full query here;
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

Oracle-Level Fixes #

Once you've identified the root cause, here are actionable fixes:

1. Refresh Statistics #

Update stats for D_VIEW and related tables in test to match production's data distribution:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
      ownname => 'YOUR_SCHEMA',
      tabname => 'D_VIEW',
      estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
      method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- Captures histograms where needed
      cascade => TRUE -- Gathers stats for indexes too
  );
  -- Repeat for A_VIEW, B_VIEW, C_VIEW base tables
END;
/

2. Synchronize SQL Plan Baselines #

Export the optimal plan from production and import it into test:

1. Export from Production:

   DECLARE
     l_sql_handle VARCHAR2(100);
   BEGIN
     SELECT SQL_HANDLE INTO l_sql_handle FROM DBA_SQL_PLAN_BASELINES 
     WHERE SQL_TEXT LIKE '%SELECT A.Id as ObjectID%';
     DBMS_SPM.EXPORT_SQL_PLAN_BASELINE(
         sql_handle => l_sql_handle,
         filename => 'OPTIMAL_PLAN.dmp',
         directory => 'YOUR_DIRECTORY_OBJECT'
     );
   END;
   /
   

2. Import into Test:

   BEGIN
     DBMS_SPM.IMPORT_SQL_PLAN_BASELINE(
         filename => 'OPTIMAL_PLAN.dmp',
         directory => 'YOUR_DIRECTORY_OBJECT'
     );
   END;
   /
   

This will enforce the efficient plan in test.

3. Use Optimizer Hints (Temporary Workaround) #

If you need an immediate fix without changing stats or baselines, add hints to force index usage or OR condition expansion:

SELECT /*+ USE_CONCAT INDEX(D IDX_D_A_ID IDX_D_C_ID) */
  A.Id as ObjectID,
  A.GlobalId as GlobalId,
  A.Type as Type,
  (SELECT listagg(CLASS, ',') within group (order by CLASS) from D_VIEW D_SEPARATE_QUERY where D_SEPARATE_QUERY.A_ID = A.OBJECTID) as D_CLASS_LIST,
  Max(CASE WHEN (D.REASON = 1) Then 1 Else 0 END) as CONDITION_1,
  Max(CASE WHEN (D.REASON in (2, 3, 4, 5, 6, 7, 8, 9, 504)) Then 1 Else 0 END) as CONDITION_2
FROM A_VIEW A
LEFT OUTER JOIN B_VIEW B on B.A_GLOBALID = A.GLOBALID
LEFT OUTER JOIN C_VIEW C on C.B_GLOBALID = B.GLOBALID
LEFT OUTER JOIN D_VIEW D on D.C_ID = C.OBJECTID or D.A_ID = A.OBJECTID
WHERE 1 = 1 AND A.TYPE=:TYPE0
GROUP BY A.ObjectID

Replace IDX_D_A_ID and IDX_D_C_ID with your actual index names on D.A_ID and D.C_ID.

4. Validate Adaptive Plans #

Since optimizer_adaptive_plans is enabled, test might not be adapting to the optimal plan. Check if the plan is adaptive:

SELECT SQL_ID, IS_ADAPTIVE_PLAN FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT A.Id as ObjectID%';

If it's adaptive, test disabling adaptive plans temporarily for the session:

ALTER SESSION SET optimizer_adaptive_plans = FALSE;

Only consider permanent disabling if you confirm it's the root cause.

内容的提问来源于stack exchange，提问作者Vanhis