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

Posted in: Technical Track

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 to 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='';

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='';
v_sql varchar2(8000);
 c             NUMBER;
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);

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';

------------- ------------------- --------------- ------------------------ -------------------
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. :)

n number;
sql_id          => 'djkbyr8vkc64h',
plan_hash_value => '810205201'
FIXED		=> 'YES',
ENABLED         => 'YES');

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
	cast(sb.created as date) created,
	cast(sb.last_executed as date) last_executed,
	dba_sql_plan_baselines sb
	sb.signature = 14465951278806438046;

SQL_HANDLE                PLAN_NAME                      ORIGIN         VERSION    CREATED                LAST_EXECUTED       ENA ACC
------------------------- ------------------------------ -------------- ---------- ------------------------------ ------------------- --- ---
SQL_c8c1620b4f5d909e      SQL_PLAN_cjhb21d7pv44y27600b06 MANUAL-LOAD 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
	PLAN_NAME=> 'SQL_PLAN_cjhb21d7pv44y27600b06',


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

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.

View Yury Velikanov's profile on LinkedIn

Interested in working with Yury? Schedule a tech call.

About the Author

Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 14 years ago (1997). For the past 10 years (since 2001) his main area of expertise is Oracle e-Business Suite. Yury is an OCP 7,8,9,10g and OCM 9i,10g. He is a frequent presenter at Oracle related conferences such as Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Bloging, Facebook) he is the primary organizer of Sydney Oracle Meetup group (250 people). So if you happen to be in Sydney (Australia) drop Yury a message and stop by at one of his Meetups.

8 Comments. Leave new

Mahir M. Quluzade
November 12, 2012 6:52 am

Good job Yuri !




Appreciate your feedback folks. Your support keeps me going.


Brilliant piece of work !!

– Yasser


That’s a beauty, Yury! Thanks!


Thanks Yury, Nice post and have learnt the signature based and how to get from the running things.
Similarly have written pre-upgrade and post-upgrade comparision reports, stepbystep here


Hi Yury.

I read with interest your method of doing the DBMS_SQL.PARSE to generate the plan for the SQL ID, however when I tried this, it produce a row in V$SQL but the plan_hash_value column was 0.

I modified your code very slightly to grab the SQL_TEXT from dba_sqlset_statements. This is because I am trying to test SPM via STS.

v_sql varchar2(8000);
select sql_text into v_sql from dba_sqlset_statements where sql_id=’6rxmf1m33xkbc’;
c := dbms_sql.open_cursor;
dbms_sql.parse(c, v_sql, dbms_sql.NATIVE);

select hash_value, plan_hash_value from v$sql where sql_id = ‘6rxmf1m33xkbc’;
3326003564 0

PL/SQL procedure successfully completed.


Thanks Williams for the comment. It worked for me once. I will troubleshoot if I have time or next time I face the problem. If you have a solution please do not hesitate to share it.


Awesome Yuri! Thanks for this blog! I’m having problems getting the optimiser to recognise and pick up the baselined ( 10g) plan. It uses the 10g plan no problem when I use the alter session set_10g optimiser. Is there any init.ora parameter that needs setting for this to happen?


Leave a Reply

Your email address will not be published. Required fields are marked *