Hey JD, sorry to hear you're hitting this performance snag after upgrading to 19c—this is a super common gotcha when jumping between major Oracle versions, but let's work through it step by step to get your view running smoothly again.

1. First: Pinpoint Execution Plan Differences #

Start by capturing and comparing the working 11g execution plan and the problematic 19c plan to see exactly what changed. Use these tools to get clear insights:

• Generate basic plans with:

  EXPLAIN PLAN FOR SELECT * FROM your_target_view;
  SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
  

• For deeper runtime details (like logical reads, elapsed time, and actual row counts), enable SQL Trace (10046 event) in both environments.

Focus on these key differences that often cause performance drops:

• Access paths (e.g., index scan vs. full table scan)
• Join order of tables in the view
• Join type (nested loop vs. hash join vs. merge join)
• Mismatches between estimated and actual row counts (a big gap usually points to stale stats)

2. Refresh Stale or Inaccurate Statistics #

Oracle 19c’s optimizer relies way more heavily on up-to-date table/index statistics than 11g. If you didn’t refresh stats post-upgrade, this is almost certainly the root cause:

• Gather stats for all tables referenced by the view:

  EXEC DBMS_STATS.GATHER_TABLE_STATS(
      OWNNAME => 'YOUR_SCHEMA_NAME',
      TABNAME => 'TABLE_IN_VIEW',
      CASCADE => TRUE, -- Includes index stats
      ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE,
      METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO'
  );
  

• For larger schemas, use GATHER_SCHEMA_STATS to refresh all stats in one go.

After gathering stats, re-run the view and check if the execution plan improves.

3. Test Optimizer Parameter Adjustments #

19c changed default values for several optimizer parameters that can shift plan choices. Try these quick tests to isolate the issue:

• Temporarily revert to 11g optimizer behavior:

  ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE = '11.2.0.4';
  

  If the view’s performance bounces back, the problem is tied to optimizer version changes. You can either:

  • Keep this setting for the affected session (or add it to your app’s connection string)
  • Use SQL Plan Management (SPM) to lock in the good 11g-style plan without rolling back all 19c optimizer features.

• Disable adaptive optimization:
  19c’s adaptive features (like adaptive joins) sometimes make poor choices for complex views. Test turning them off:

  ALTER SESSION SET ADAPTIVE_OPTIMIZATION = OFF;
  

4. Rewrite the View for 19c Compatibility #

Occasionally, 11g-era view syntax confuses the 19c optimizer. Look for these fixable patterns:

• Implicit data type conversions (e.g., comparing a VARCHAR2 column to a NUMBER literal) — rewrite with explicit TO_NUMBER/TO_CHAR calls.
• Correlated subqueries that can be replaced with JOIN clauses; 19c handles joins more reliably than nested subqueries in some cases.
• Deprecated functions or unindexed function calls (e.g., UPPER() on an indexed column — wrap it in a function-based index if needed).

5. Lock in the Good Execution Plan with SQL Plan Management (SPM) #

If you confirm the 11g execution plan is optimal, you can preserve it in 19c using SPM:

1. Export the good plan from 11g via DBMS_SPM.EXPORT_SQL_PLAN_BASELINE and import it into 19c.
2. Or, if you can temporarily get the view to run with the good plan in 19c, capture it from the cursor cache:

   DECLARE
     l_plans_loaded NUMBER;
   BEGIN
     l_plans_loaded := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(
         SQL_TEXT => 'SELECT * FROM your_target_view;',
         FIXED => TRUE -- Locks the plan so the optimizer can't alter it
     );
   END;
   /
   

Final Notes #

If none of these steps work, check for known optimizer bugs in your 19c patch set — Oracle regularly releases patches to fix plan regression issues after major upgrades.

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