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

9 Comments

  1. Bertrand says:

    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

  2. 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

  3. Connor says:

    dbms_utility allows getting a hash value for a yet-to-be-run SQL…maybe that could leveraged in some way ? Just thinking aloud

  4. osborne says:

    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

      • osborne says:

        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

  5. Bertrand says:

    Good to know the number of bad news has decreased by one. Looking forward to hear from the translate feature.

    Bertrand

  6. 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

Leave a Reply