Case Study: How to Return a Good SQL Execution Plan from 10g Days After an 11g Migration

3 min read

Nov 12, 2012 12:00:00 AM

Any database upgrade is supposed to change SQLs' execution plans for the better. In 99% of cases, this is exactly what happens. What to do with leftover 1% of the SQLs? This blog post is about an unlucky case such as these that I have resolved today. I hope that you will learn something from it. As always, let me know what you think in the comments section at the end of the post. I was called to troubleshoot a SQL statement that, instead of completing in 10 minutes like in the old 10G database, took 28h after migration from 10.2.0.4 to 11.2.0.3. Just to add an additional challenge, the 10G database wasn't available to validate the developer's statement and possibly transfer the 10G execution plan to a new database.

Confirm that the 10G execution plan is/was better

This time, I was lucky enough and had all I needed to execute the SQL (e.g. schema access, binds, etc.). The only thing I was missing was a good execution plan. It would take me hours to understand the business logic behind the SQL since it is a fairly complex piece of development art. (SQL consisted of 77 lines; the new SQL execution plan had 178 lines.) As I had an input that this is a 10G=>11G migration performance regression case, I tried to adjust the optimizer_features_enable init.ora parameter on the session level first.

alter session set optimizer_features_enable='10.2.0.4';

Luckily enough, Oracle finished the SQL execution in less than 6 minutes. At this point I knew that SQL plan with hash value 810205201 was a good execution plan that I needed to force 11G to use.

How to get 11G to use the 10G execution plan

The next challenge was to get SQL with exactly the same SQL text as the application used (exactly the same SQL_ID) and set a SQL plan baseline. I work from Australia and the client's team is based in North America. Therefore, I couldn't just call the developer and ask to re-execute the SQL. I had to come with a method that would allow me to find the exact SQL text without executing it. The SQL's execution took a lot of resources Oracle captured SQL in AWR repository. (The client had a Diagnostic license.) I used the following statement to get the SQL and associated "good" execution plan in a shared pool.

alter session set optimizer_features_enable='10.2.0.4';
 declare
 v_sql varchar2(8000);
  c NUMBER;
 begin
 select sql_text into v_sql from DBA_HIST_SQLTEXT where sql_id='djkbyr8vkc64h';
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c, v_sql, dbms_sql.NATIVE);
  dbms_sql.close_cursor(c);
 end;

I confirmed that the "good" execution plan had been used by the following SQL:

select sql_id,LAST_LOAD_TIME,PLAN_HASH_VALUE, exact_matching_signature, sysdate from v$sqlarea where sql_id='djkbyr8vkc64h';
 
 SQL_ID LAST_LOAD_TIME PLAN_HASH_VALUE EXACT_MATCHING_SIGNATURE SYSDATE
 ------------- ------------------- --------------- ------------------------ -------------------
 djkbyr8vkc64h 2012.11.12 01:25:51 810205201 14465951278806438046 2012.11.12 01:26:04

The final bit was to create a SQL plan baseline based on the pair of SQL_ID and PLAN_HASH_VALUE. From there, it was too easy. :)

declare
 n number;
 begin
 n:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE (
 sql_id => 'djkbyr8vkc64h',
 plan_hash_value => '810205201'
 FIXED => 'YES',
 ENABLED => 'YES');
 dbms_output.put_line(n);
 end;
 /
 1
 
 PL/SQL procedure successfully completed.

To validate that the base line was created for the SQL:

col version for a10
 col SQL_HANDLE for a25
 col CREATED for a30
 col sb.last_executed for a30
 SELECT
 sb.sql_handle,
 sb.plan_name,
 sb.origin,
 sb.version,
 cast(sb.created as date) created,
 cast(sb.last_executed as date) last_executed,
 sb.enabled,
 sb.accepted
 FROM
 dba_sql_plan_baselines sb
 WHERE
 sb.signature = 14465951278806438046;
 
 SQL_HANDLE PLAN_NAME ORIGIN VERSION CREATED LAST_EXECUTED ENA ACC
 ------------------------- ------------------------------ -------------- ---------- ------------------------------ ------------------- --- ---
 SQL_c8c1620b4f5d909e SQL_PLAN_cjhb21d7pv44y27600b06 MANUAL-LOAD 11.2.0.3.0 2012.11.12 01:31:58 YES YES

Just in case we would need to rollback the change, we just need to run the following PL/SQL block:

set output on
 DECLARE
  i NATURAL;
 BEGIN
  i := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
 sql_handle=>'SQL_c8c1620b4f5d909e',
 PLAN_NAME=> 'SQL_PLAN_cjhb21d7pv44y27600b06',
 attribute_name=>'enabled',attribute_value=>'no');
  dbms_output.put_line(i);
 END;
 /

Credits

I would like to mention people whose work helped me resolve today's issue:

Gavin Soorma whose presentation "Upgrade with confidence – 11g Optimizer Plan Stability" hinted to try adjusting optimizer_features_enable back to 10g value on a session level. Thank you my friend Gavin. :)
Maxym Kharchenko whose blog post "How to find SPM baseline by sql_id" pointed me to the relationship between sql_id, v$sqlarea.exact_matching_signature, and dba_sql_plan_baselines.signature. Thank you my friend Max. :)

Max and Gavin are good technical folks. If you happen to be at the same conference as them, I would suggest that you have a beer or two with each of them.