Oracle 11g SQL计划基线未生效问题排查求助

Let's break down the possible issues and actionable fixes based on your scenario:

1. Verify Exact SQL Text Match #

SQL Plan Baselines rely on character-perfect matching of the SQL text—this includes whitespace, capitalization, bind variable names, and even invisible control characters. It's surprisingly easy for seemingly identical SQL statements to have subtle differences that break baseline matching.

To confirm:

• Compare the binary dump of the SQL text in the baseline with what's in the shared pool to catch hidden discrepancies:

  -- Get baseline SQL text binary dump
  SELECT dump(sql_text, 16) 
  FROM dba_sql_plan_baselines 
  WHERE sql_id = '1234567890abc';
  
  -- Get shared pool SQL text binary dump
  SELECT dump(sql_text, 16) 
  FROM v$sqlarea 
  WHERE sql_id = '1234567890abc';
  

If the dumps don't match, the SQL you're executing isn't the same as the one tied to the baseline. Capture the exact SQL text from your application first, reload its plan into the baseline, and try again.

2. Check the Baseline's ACCEPTED Status #

Even if you've marked a baseline as FIXED, it won't be used unless the ACCEPTED flag is set to YES—this is a common oversight.

Run this query to verify:

SELECT sql_handle, plan_name, accepted, enabled, fixed 
FROM dba_sql_plan_baselines 
WHERE sql_id = '1234567890abc';

If ACCEPTED shows NO, accept the baseline with:

DECLARE
  v_accepted NUMBER;
BEGIN
  v_accepted := DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE(
    sql_handle => 'YOUR_SQL_HANDLE_FROM_THE_QUERY_ABOVE',
    plan_name => 'YOUR_PLAN_NAME_FROM_THE_QUERY_ABOVE'
  );
END;
/

3. Known Bug in Oracle 11.2.0.1 #

Oracle 11.2.0.1 has unresolved bugs related to SQL Plan Baselines, including Bug 9954061 which causes valid baselines to be ignored entirely. This bug is fixed in 11.2.0.2 and later releases.

As a temporary workaround (test in non-production first), try mimicking the optimizer feature set from the next patch level:

-- Test at session level first
ALTER SESSION SET optimizer_features_enable = '11.2.0.2';
-- If it works, apply at system level (with change control approval)
ALTER SYSTEM SET optimizer_features_enable = '11.2.0.2' SCOPE=BOTH;

The long-term fix here is to patch your Oracle installation to a supported 11gR2 release (11.2.0.4 is the last supported version).

4. Validate Execution Environment Consistency #

The execution environment for your SQL must match the environment used when the baseline was created. Key parameters to cross-check:

• optimizer_mode: Compare the value in dba_sql_plan_baselines.optimizer_mode with v$sqlarea.optimizer_mode for your SQL.
• optimizer_index_caching/optimizer_index_cost_adj: These can alter plan choices even with a baseline in place.

Run this to compare:

SELECT optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj
FROM dba_sql_plan_baselines 
WHERE sql_id = '1234567890abc';

SELECT optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj
FROM v$sqlarea 
WHERE sql_id = '1234567890abc';

5. Fix Your Shared Pool Purge Command #

Your purge command likely used the wrong object type code. Use 'C' for cursor objects (not 'Z', which is for packages/procedures), and double-check the address/hash_value for typos:

-- Get the exact address and hash_value
SELECT address, hash_value 
FROM v$sqlarea 
WHERE sql_id = '1234567890abc';

-- Purge the cursor correctly (no spaces in the string)
EXECUTE SYS.DBMS_SHARED_POOL.PURGE('0000000000ABCDEF,0987654321', 'C');

For test environments, flushing the entire shared pool is simpler (avoid in production):

ALTER SYSTEM FLUSH SHARED_POOL;

6. Ensure the SQL is Eligible for Baselines #

Some SQL types aren't supported by SQL Plan Baselines, including:

• Recursive SQL generated by Oracle internally
• SQL with inconsistent dynamic text (even minor changes break matching)
• SQL using temporary tables in unsupported configurations

Double-check that your SQL doesn't fall into these categories.

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