一次SQL_ID和HASH_VALUE转换尝试引发的误区

这篇文章中曾谈到一个隐藏问题：

引用原文：

“使用@dbsnake大牛的SQL可以知道SQL_ID和HASH_VALUE的一一对应关系：

隐藏问题1：

这里的截图可能有点问题，结果并不准确，问题就出在这个SQL中使用的算法中，在另一篇博文中会仔细说明这个问题。”

问题背景：

这里使用以下两个SQL获取SQL_ID对应的HASH_VALUE值：

select
lower(trim('a43zhpuddcxwh')) sql_id,
trunc(mod(sum((instr('0123456789abcdefghijklmnopqrstuvwxyz', substr(lower(trim('a43zhpuddcxwh')), level, 1)) - 1) * power(32, length(trim('a43zhpuddcxwh')) - level)), power(2, 32))) hash_value
from dual
connect by level <= length(trim('a43zhpuddcxwh'));

select
lower(trim('a43zhpuddcxwh')) sql_id,
trunc(mod(sum((instr('0123456789abcdefghjkmnpqrstuvwxyz', substr(lower(trim('a43zhpuddcxwh')), level, 1)) - 1) * power(32, length(trim('a43zhpuddcxwh')) - level)), power(2, 32))) hash_value
from dual
connect by level <= length(trim('a43zhpuddcxwh'));
结果是第一个错误，第二个正确。看似相同的两条SQL为什么结果返回错误呢？

解惑：

1. 何为SQL_ID以及HASH_VALUE？

这两个字段都是来自于V$SQL视图，Oracle官方解释是，

SQL_ID           VARCHAR2(13)      SQL identifier of the parent cursor in the library cache

HASH_VALUE NUMBER               Hash value of the parent statement in the library cache

9i及以前版本，一般用HASH_VALUE表明一条SQL，从10g及以后版本，一般用SQL_ID表明一条SQL。说白了，是表示这条SQL在库缓存(Library Cache)中的对象名。因为SQL进入Oracle内部后会被放入库缓存中，然后再进行执行计划的匹配、存储等操作。这样看，HASH_VALUE和SQL_ID都可以表明一条SQL，但由于10g以后，HASH_VALUE的算法有了不同，因此10g的V$SQL中还多了一个OLD_HASH_VALUE字段，为的就是向下兼容(主要目的可以看做9i到10g版本迁移时，用于查询同一条SQL对应的执行计划或统计信息)：

OLD_HASH_VALUE     NUMBER      Old SQL hash value

2. SQL_ID和HASH_VALUE如何转换？

上述说明SQL_ID和HASH_VALUE都可以表明一条SQL，主要都是根据SQL文本，Oracle使用MD5算法进行哈希，取不同的位数作为SQL_ID和HASH_VALUE，实际就是代表这条SQL对应的库缓存对象，具体可参见Tanel Poder的揭秘：

“Basically all I do is take the SQL ID, interpret it as a 13 character base-32 encoded number and then take only the lowest 4 bytes worth of information (4 bytes in base-256) out of that number and that’s the hash value.
“

So, SQL_ID is just another hash value of the library cache object name.

Actually, since 10g the full story goes like this:

1) Oracle hashes the library cache object name with MD5, producing a 128 bit hash value
2) Oracle takes last 64 bits of the MD5 hash and this will be the SQL_ID (but it’s shown in base-32 for brevity rather than in hex or as a regular number)
3) Oracle takes last 32 bits of the MD5 hash and this will be the hash value (as seen in v$sql.hash_value).”

译文：

将SQL_ID解释为一个13个字节的base-32编码数值，然后取其中的低4个字节(base-256的4个字节)，作为HASH_VALUE。

SQL_ID是库缓存对象名的另一种HASH值。

从10g开始，算法变更为：

1) Oracle使用MD5对库缓存对象名进行哈希，产生一个128位的哈希值。

2) Oracle取MD5哈希值的后64位，作为SQL_ID(但是它是以base-32编码简单展示的，而不是使用十六进制或常规数值)。

3) Oracle取MD5哈希值的后32位，作为HASH_VALUE(即v$sql.hash_value)。

经常说到base-X编码，说实话，我也不太懂，引一些前人对这种编码原理的介绍，自认为无特殊需求，也不必太深究，关注最需要关注的地方：

”Base32的原理和Base64一模一样，所以先看一下Base64编码是怎么一回事。

Base64顾名思义就是用64个可显示字符表示所有的ASC字符，64也就是6Bits，而ASC字符一共有256个，也就是8Bits，很简单了，取一下最小公约数，24位，言下之意就是用4个Base64的字符来表示3个ASC字符。即在编码时，3个一组ASC字符，产生4个Base64字符，解码时4个一组，还原3个ASC字符。根据这个原理Base64编码之后的字符串应该比原先增加1/3的长度。

这里所谓的编码就是一次取6Bits，换算出来的值作为索引号，利用这个索引数，到预先定义的长度为64的字符数组中取相应的字符替换即可；解码就是逆运算，根据字符取在预定义数组中的索引值，然后按8Bits一组还原ASC字符。

Base32和Base64相比只有一个区别就是，用32个字符表示256个ASC字符，也就是说5个ASC字符一组可以生成8个Base字符，反之亦然。

Base64通常由“a-z”、“A-Z”、0-9以及“+”和“=”这些符号组成。“

再重新叙述上面的转换过程，就是Oracle计算SQL文本的MD5哈希值，取后64位作为SQL_ID，这里使用base-32编码进行转换，其中base-32转码的可见字符是0123456789abcdfghjkmnpqrstuvwxyz。然后会取后哈希值的32位，作为HASH_VALUE。但实际上通常会在SQL文本结尾加一个不可见字符'\0'，然后再进行哈希。

Tanel Poder说了如下：

”Library cache is physically still organized by the hash value, not SQL_ID. When you query views like X$KGLOB or V$SQL by SQL_ID, then Oracle just extracts the low 4 bytes from the SQL_ID and still does the lookup by hash value.

So, despite only SQL_ID showing up everywhere in Enterprise Manager and newest Oracle views and scripts, the hash_value isn’t going anywhere, it’s a fundamental building block of the library cache hash table.“

即尽管10g以后通常使用SQL_ID代表一条SQL，但实际上库缓存物理上还是使用HASH_VALUE组织的。使用SQL_ID查询X$KGLOB或V$SQL视图时，Oracle也是仅仅抽取出SQL_ID的低4个字节，仍旧通过HASH_VALUE值进行检索的。

因此，尽管在EM以及Oracle视图和脚本中到处可见SQL_ID，但实际HASH_VALUE仍起着作用，它才是构建库缓存哈希表的基础。

他也给出了一个用于SQL_ID和HASH_VALUE转换的脚本，用的就是如下SQL：

select
    lower(trim('&1')) sql_id
  , trunc(mod(sum((instr('0123456789abcdfghjkmnpqrstuvwxyz',substr(lower(trim('&1')),level,1))-1)
                       *power(32,length(trim('&1'))-level)),power(2,32))) hash_value
from
    dual
connect by
    level <= length(trim('&1'))
/

现在我们就能知道instr中这一串的字符是什么意思了，其实就是base-32转码的可见字符。也就能说明文章开始的两条SQL为什么看似相同，但结果不同了，其实就是base-32转码使用的不对。

总结：

凡事都有因果，开始碰到这么一条SQL时，想当然认为就是0-9，a-z连续的字符，但其实这里用到的是base-32转码，并不是连续的字符，因此理解其背后的原理，才有助于清楚这里为什么这么用，而不是那么用。