ITPub博客

首页 > 数据库 > Oracle > [20141205]关于sql_id.txt

[20141205]关于sql_id.txt

原创 Oracle 作者:lfree 时间:2014-12-05 10:54:16 0 删除 编辑

[20141205]关于sql_id.txt

--昨天跟别人聊天,讲sql_id字串里面没有字符'o'.实际上他在学习sql_id与hash_value转换时copy 和 paste少粘贴1位,手工输入时以
--为是--字母'o',实际上是数字'0'.

--顺便找一个sql_id,把最后以为换成o,看看结果:
SYS@test> select dbms_utility.SQLID_TO_SQLHASH('f7nhbjdn5rx9o') from dual ;
select dbms_utility.SQLID_TO_SQLHASH('f7nhbjdn5rx9o') from dual
       *
ERROR at line 1:
ORA-13797: invalid SQL Id specified, f7nhbjdn5rx9o
ORA-06512: at "SYS.DBMS_UTILITY", line 1293

--做一个简单的查询:
SYS@test> select sql_id from v$sqlarea where instr(sql_id,'o')>0;
no rows selected

--很明显没有字符'o'.

--sql_id的计算是使用MD5算法进行哈希,生成一个128位的Hash Value,其中低32位作为HASH VALUE显示,SQL_ID则取了后64位。
--实际上sql_id使用32进制表示,hash_value使用10进制表示。

--既然使用32进制,0-9,a-z 总共10+26=36个字符,明显多了4个。也就是讲有4个字符不会出现在sql_id中。

--写一个简单的sql看看。

select b.x from (select chr(level+96) x from dual connect by level<=26 ) b where not exists (select 1 from v$sqlarea
  2  where instr(sql_id,b.x)>0);
X
---
e
i
l
o

--还包括eilo 4个字符。看看大师写的sql_id转换hash_value的语句就知道了。

http://blog.tanelpoder.com/2009/02/22/sql_id-is-just-a-fancy-representation-of-hash-value

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'))
/

--转换表里面没有eilo。脚本乍看有点晕,实际上很简单。实际的思路是把sql_id当作32进制,先取出单个字符,然后转成10进制,
--然后全部加起来,再与power(2,32)取mod ,结果就是hash_value.展开看看:

SYS@test> select sql_id,hash_value from v$sqlarea where rownum<=1;
SQL_ID        HASH_VALUE
------------- ----------
1fkh93md0802n 3657695316

SELECT 14-level,
    '&1' sql_id,
           SUBSTR ('&1', LEVEL, 1) x1,
           INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1  x2 ,
           (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1)*power(32,length('&1')-level) x3
      FROM DUAL
CONNECT BY LEVEL <= LENGTH (TRIM ('&1'))
13 rows selected.

14-LEVEL SQL_ID        X1  X2                   X3
-------- ------------- -- --- --------------------
      13 1fkh93md0802n 1    1  1152921504606846976
      12 1fkh93md0802n f   14   504403158265495552
      11 1fkh93md0802n k   18    20266198323167232
      10 1fkh93md0802n h   16      562949953421312
       9 1fkh93md0802n 9    9        9895604649984
       8 1fkh93md0802n 3    3         103079215104
       7 1fkh93md0802n m   19          20401094656
       6 1fkh93md0802n d   13            436207616
       5 1fkh93md0802n 0    0                    0
       4 1fkh93md0802n 8    8               262144
       3 1fkh93md0802n 0    0                    0
       2 1fkh93md0802n 2    2                   64
       1 1fkh93md0802n n   20                   20
13 rows selected.


select mod(sum(x3),power(2,32)) from (
    SELECT 14-level,
    '&1' sql_id,
           SUBSTR ('&1', LEVEL, 1) x1,
           INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1  x2 ,
           (INSTR ('0123456789abcdfghjkmnpqrstuvwxyz', SUBSTR ('&1', LEVEL, 1)) - 1)*power(32,length('&1')-level) x3
      FROM DUAL
CONNECT BY LEVEL <= LENGTH (TRIM ('&1')));

MOD(SUM(X3),POWER(2,32))
------------------------
              3657695316

--这样就很好理解了。

--而且sql_id取64位,2^5表示1个32位,剩下小于2^4表示sql_id第1个字符,这样sql_id第1个字符不会大于'h'.
SYS@test> select sql_id from v$sqlarea where substr(sql_id,1,1)>='h';
no rows selected

--为什么去掉eilo,我不知道,我的感觉就是去掉lo,主要可能就是它与数字01太相近,比较容易混淆。一般编程都要求规避l,o作为变
--量.至于ei,估计也是一样的原因,纯粹是我自己乱猜...................

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1357292/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2696
  • 访问量
    6467231