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. 🙂
Hello Kerry,
Thanks for sharing this, this is mandatory to know !
That’s a good post, and also a bad news to compare / verify plan after an upgrade (even if there is work around as you said).
Another bad news coming with 12.1: The ASM preferred feature is somehow broken with Flex ASM, see:
http://bdrouvot.wordpress.com/2013/07/02/flex-asm-12c-12-1-and-extended-rac-be-careful-to-unpreferred-read/
Hopefully there is also a lot of good news coming with 12c !
Bertrand
Hi Kerry,
i am not able to reproduce your observations (and have not seen this in various tests i have performed on 12c until yet). Here is just my short demo (one with dual and one with dba_users) on 12.1.0.1 and 11.2.0.3.6.
12.1.0.1
==============================================================================================================
SYS@T12DB:332> select 1 from dual;
SYS@T12DB:332> select SQL_ID, HASH_VALUE, OLD_HASH_VALUE, SQL_TEXT from V$SQL where SQL_TEXT like ‘select 1 from dual’;
SQL_ID HASH_VALUE OLD_HASH_VALUE SQL_TEXT
————- ———- ————– ——————————
520mkxqpf15q8 2866845384 271604965 select 1 from dual
SYS@S12DB:332> select kglobt03, kglnahsh, kglobt46, kglnahsv from x$kglcursor_child where kglobt03 = ‘520mkxqpf15q8’;
KGLOBT03 KGLNAHSH KGLOBT46 KGLNAHSV
————- ———- ———- ——————————–
520mkxqpf15q8 2866845384 271604965 7d4dc9b423f0bcfb510272edaae096c8
SYS@T12DB:332> select username from dba_users;
SYS@T12DB:332> select SQL_ID, HASH_VALUE, OLD_HASH_VALUE, SQL_TEXT from V$SQL where SQL_TEXT like ‘select username from dba_users’;
SQL_ID HASH_VALUE OLD_HASH_VALUE SQL_TEXT
————- ———- ————– ——————————
chvsmttqjzjkn 1830798932 2084732694 select username from dba_users
SYS@T12DB:332> select kglobt03, kglnahsh, kglobt46, kglnahsv from x$kglcursor_child where kglobt03 = ‘chvsmttqjzjkn’;
KGLOBT03 KGLNAHSH KGLOBT46 KGLNAHSV
————- ———- ———- ——————————–
chvsmttqjzjkn 1830798932 2084732694 fb10d13845dee8d4c86f13ce6d1fc654
==============================================================================================================
11.2.0.3.6
==============================================================================================================
SYS@T11DB:91> select 1 from dual;
SYS@T11DB:91> select SQL_ID, HASH_VALUE, OLD_HASH_VALUE, SQL_TEXT from V$SQL where SQL_TEXT like ‘select 1 from dual’;
SQL_ID HASH_VALUE OLD_HASH_VALUE SQL_TEXT
————- ———- ————– ——————————
520mkxqpf15q8 2866845384 271604965 select 1 from dual
SYS@T11DB:91> select kglobt03, kglnahsh, kglobt46, kglnahsv from x$kglcursor_child where kglobt03 = ‘520mkxqpf15q8’;
KGLOBT03 KGLNAHSH KGLOBT46 KGLNAHSV
————- ———- ———- ——————————–
520mkxqpf15q8 2866845384 271604965 7d4dc9b423f0bcfb510272edaae096c8
SYS@T11DB:91> select username from dba_users;
SYS@T11DB:91> select SQL_ID, HASH_VALUE, OLD_HASH_VALUE, SQL_TEXT from V$SQL where SQL_TEXT like ‘select username from dba_users’;
SQL_ID HASH_VALUE OLD_HASH_VALUE SQL_TEXT
————- ———- ————– ——————————
chvsmttqjzjkn 1830798932 2084732694 select username from dba_users
SYS@T11DB:91> select kglobt03, kglnahsh, kglobt46, kglnahsv from x$kglcursor_child where kglobt03 = ‘chvsmttqjzjkn’;
KGLOBT03 KGLNAHSH KGLOBT46 KGLNAHSV
————- ———- ———- ——————————–
chvsmttqjzjkn 1830798932 2084732694 fb10d13845dee8d4c86f13ce6d1fc654
==============================================================================================================
As you can see the (MD5) hash value of the SQL text (+ appended NULL) is the same across 11g and 12g in my environment. Not quite sure where the difference come from in your scenario.
Regards
Stefan
dbms_utility allows getting a hash value for a yet-to-be-run SQL…maybe that could leveraged in some way ? Just thinking aloud
Ah – Stefen is right. There is a specific thing that I did to my 12.1 instance that triggered this behavior (that’s probably uncovering a bug but the support guys may say it’s as designed). So I over reacted and did do enough checking before posting this. Sorry. Please pay no attention to the man behind the curtain. I’ll post on what I was messing with that caused the SQL_ID to change soon.
Kerry
Hi Kerry,
great to know. Just out of curiosity … is this special behavior related to the “SQL Translation Framework” ? I have not tested this feature until yet, but it sounds reasonable, if the SQL_TEXT in V$SQL shows the original SQL text. :-))
Enjoy the remaining sunday.
Regards
Stefan
Very good guess. The Translate feature was enabled and that’s the issue, but the statement is not actually translated (i.e. it was just passed straight through). So it’s probably a bug, but could be intentional behavior.
Kerry
Good to know the number of bad news has decreased by one. Looking forward to hear from the translate feature.
Bertrand
[…] « 12c – New SQL_ID Calculation […]
Kerry,
only slightly connected, but as you mentioned HASH_VALUE and OLD_HASH_VALUE I guess Jonathan Lewis explained the reason for the (new) HASH_VALUE here:
https://www.youtube.com/watch?v=qDhCJThlmUY&feature=player_detailpage&t=3280
Martin