[20141212]关于sql_id.txt
http://blog.itpub.net/267265/viewspace-1357292/
http://blog.itpub.net/267265/viewspace-1220996/
--昨天别人问一些sql_id计算的问题，实际上我也不懂具体的算法，我给他看了上面的链接。
--他问的问题，还是通过例子来说明：
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> select 1 from dual;
1
----------
1
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 520mkxqpf15q8, child number 0
-------------------------------------
select 1 from dual
Plan hash value: 1388734953
-------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
-------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | FAST DUAL | | 1 | 2 (0)|
-------------------------------------------------------
SCOTT@test> select sql_text,sql_id,hash_value,executions from v$sql where sql_id='520mkxqpf15q8';
SQL_TEXT SQL_ID HASH_VALUE EXECUTIONS
------------------------------------------------------------ ------------- ---------- ----------
select 1 from dual 520mkxqpf15q8 2866845384 1
--sql_id=520mkxqpf15q8,HASH_VALUE=2866845384.
--通过另外的视图可以获得full_hash_value。
SCOTT@test> select hash_value, to_char(hash_value,'xxxxxxxx') hex_hash_value, full_hash_value,substr(full_hash_value,-8) hash_x
from GV$DB_OBJECT_CACHE where name='select 1 from dual';
HASH_VALUE HEX_HASH_ FULL_HASH_VALUE HASH_X
----------- --------- -------------------------------- ----------------
2866845384 aae096c8 7d4dc9b423f0bcfb510272edaae096c8 aae096c8
2866845384 aae096c8 7d4dc9b423f0bcfb510272edaae096c8 aae096c8
--sql_id的计算是使用MD5算法进行哈希，生成一个128位的Hash Value，其中低32位作为HASH VALUE显示，SQL_ID则取了后64位。
--低3位对应16进制就是取8个字符,对比上面的输出hex_hash_value结果与是正确的。按照这样讲510272edaae096c8 (FULL_HASH_VALUE的后16位
--就是sql_id),问的问题是为什么不等于sql_id.
--实际上sql_id是使用32进制，而FULL_HASH_VALUE使用的16进制，要做一个进制转换就可以得出结论。
--先转换为10进制。
SCOTT@test> set numw 20
SCOTT@test> select to_number(substr(full_hash_value,-16),'xxxxxxxxxxxxxxxxxxxx') sql_id10, full_hash_value
from GV$DB_OBJECT_CACHE where name='select 1 from dual' and rownum SQL_ID10 FULL_HASH_VALUE
-------------------- --------------------------------
5837354432125245128 7d4dc9b423f0bcfb510272edaae096c8
--10进制如何转32进制，sql语句不好写。写一个递归看看(11G才支持这种功能)。
SELECT SUBSTR ('0123456789abcdfghjkmnpqrstuvwxyz', a + 1, 1) c,rownum
FROM (WITH data (a, b)
AS (SELECT MOD (&1, 32) a, TRUNC (&1 / 32) b FROM DUAL
UNION ALL
SELECT MOD (b, 32) a, TRUNC (b / 32) b
FROM data
WHERE b 0)
SELECT a
FROM data);
Enter value for 1: 5837354432125245128
Enter value for 1: 5837354432125245128
old 3: AS (SELECT MOD (&1, 32) a, TRUNC (&1 / 32) b FROM DUAL
new 3: AS (SELECT MOD (5837354432125245128, 32) a, TRUNC (5837354432125245128 / 32) b FROM DUAL
C ROWNUM
-- ----------
8 1
q 2
5 3
1 4
f 5
p 6
q 7
x 8
k 9
m 10
0 11
2 12
5 13
13 rows selected.
--从下往上看，与sql_id=520mkxqpf15q8正好对上。完善一下，使用wmsys.wm_concat(注：使用它不是很好，只要我太懒)
--[141204]11G关于使用wmsys.wm_concat的问题 http://blog.itpub.net/267265/viewspace-1356256/
select replace(wmsys.wm_concat(c),',') from (
select c from (
SELECT SUBSTR ('0123456789abcdfghjkmnpqrstuvwxyz', a + 1, 1) c,rownum rn
FROM (WITH data (a, b)
AS (SELECT MOD (&1, 32) a, TRUNC (&1 / 32) b FROM DUAL
UNION ALL
SELECT MOD (b, 32) a, TRUNC (b / 32) b
FROM data
WHERE b !=0
)
SELECT a
FROM data)) order by rn desc);
REPLACE(WMSYS.WM_CONCAT(C),',')
--------------------------------
520mkxqpf15q8
--正好对上。为什么sql_id使用32进制，不使用16进制，这个我就不知道了。