Case Study: How to return a good SQL execution plan from 10g days after an 11g migration

Any database upgrade suppose to change SQLs’ execution plans for the better. In 99 cases out of 100 this is exactly what happens. What to do with rest 1% of the SQLs? This blog post is exactly about such unlucky case I have resolved today. Hope that you will learn something  out of it. As always let me know what you think using the comments section at the end of the post.

I was called to troubleshoot a SQL statement that instead of completing in 10 minutes 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 original 10G database wasn’t available to validate the developer’s statement and possibly transfer 10G execution plan to a new database.

Confirm that 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 is a good execution plan. It would take me hours to understand the business logic behind the SQL as it was fairly complex piece of development art (sql consisted of 77 lines, new sql execution plan had 178 lines). As I had an input that this is a 10G=>11G migration performance regression case I have tried to adjust optimizer_features_enable init.ora parameter on 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 I need to force 11G to use.

How to get 11G to use 10G execution plan

The next challenge was to get SQL with exactly the same SQL text as application used (exactly the same SQL_ID) and set a SQL plan baseline. I am working 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. As the SQL’s execution took a lot of resources Oracle captured SQL in AWR repository (the client had Diagnostic license). I have 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 only final bit that I had to complete is to create a SQL plan baseline based on the pair of SQL_ID and PLAN_HASH_VALUE. From here 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 have got 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 to resolve today’s issue:

• Gavin Soorma whose presentation “Upgrade with confidence – 11g Optimizer Plan Stability” gave me a hint 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 I would suggest you to have a beer or two with each of them.