12c – New SQL_ID Calculation

Updated 7/7/13: Well I’m a doofus! This is not a generic problem. It is a bug but only happens when using a specific new feature I was playing with on my 12.1 database (SQL Translation Framework). No need to worry about this unless using that feature. (thanks to Stefen for pointing this out) So you probably don’t need to read this at all. The comments might be worth looking at though. :)

=========================================================

Shoot! SQL_ID calculation is different between 11.2 and 12.1. This is a bummer because we’ve gotten used to being able to go back and forth between versions to verify plans after upgrading to 11g for example. It was also convenient to be able to track changes in performance statistics before and after an upgrade. Fortunately there is a work around. The old_hash_value column has been carried through to 12c. See here:

 
SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 5 18:51:43 2013
 
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1123          26-JUN-2013 19:02 05-JUL-2013 18:51    8.99     776979
 
SYS@LAB1123> select sql_id, hash_value, old_hash_value, plan_hash_value, sql_text from v$sql where sql_text = 'select 1 from dual';
 
SQL_ID        HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- -------------- --------------- ----------------------------------------
520mkxqpf15q8 2866845384      271604965      1388734953 select 1 from dual
 
========================================================
 
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jul 5 18:33:12 2013
 
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
 
 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options
 
 
INSTANCE_NAME    STARTUP_TIME      CURRENT_TIME         DAYS    SECONDS
---------------- ----------------- ----------------- ------- ----------
LAB1211          02-JUL-2013 10:21 05-JUL-2013 18:33    3.34     288715
 
SYS@LAB1211> select sql_id, hash_value, old_hash_value, plan_hash_value, sql_text from v$sql where sql_text = 'select 1 from dual';
 
SQL_ID        HASH_VALUE OLD_HASH_VALUE PLAN_HASH_VALUE SQL_TEXT
------------- ---------- -------------- --------------- ----------------------------------------
3zcn52u5tvfqh 2342370000      271604965      1388734953 select 1 from dual

So as you can see, the sql_id and hash_value have changed between versions but the old_hash_value remains consistent. It also appears that the plan_hash_value calculation is unchanged, at least for simple plans. Anyway, a little reworking of some scripts should allow us to do the same sorts of things we’ve done in the past, albeit with a little more effort. Maybe Tanel will do us all a favor and write a function to calculate the old sql_id in 12c. That would make it a little easier. :)

Leave a Reply