[20170724]关于sql_id那些事.txt

[20170724]关于sql_id那些事.txt

--//昨天别人问的问题,我以前也写过许多blog,做一些总结:
http://blog.itpub.net/267265/viewspace-1357292/
http://blog.itpub.net/267265/viewspace-1365382/
http://blog.itpub.net/267265/viewspace-1701985/

1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

select * from emp where deptno=10;
--//查询可以知道sql_id='557p4j1ggw222'.

SCOTT@book> select sql_text c70,sql_id,hash_value  from v$sql where sql_id = '557p4j1ggw222';
C70                                SQL_ID        HASH_VALUE
---------------------------------- ------------- ----------
select * from emp where deptno=10  557p4j1ggw222 1593706562

SCOTT@book> select name c70,hash_value,full_hash_value from V$DB_OBJECT_CACHE where name like '%emp%' and hash_value=1593706562;
C70                               HASH_VALUE FULL_HASH_VALUE
--------------------------------- ---------- --------------------------------
select * from emp where deptno=10 1593706562 8bb974871a4f8c88529ea4885efe0842
select * from emp where deptno=10 1593706562 8bb974871a4f8c88529ea4885efe0842

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

--//我当时想当然以为,执行如下:
$ echo -e -n 'select * from emp where deptno=10' | md5sum
3d7f68d68130e573b9b77f10f79c9ca7  -

--//后来知道需要在sql语句后面补上chr(0),再运算.

$ echo -e -n 'select * from emp where deptno=10\0' | md5sum
8774b98b888c4f1a88a49e524208fe5e  -

--//可以发现与前面看到FULL_HASH_VALUE不同,如果你足够仔细,可以发现实际上大小头对调,显示就与视图 V$DB_OBJECT_CACHE 显示的full_hash_value 一致了.

$ echo -e -n 'select * from emp where deptno=10\0' | md5sum | sed 's/  -//' | xxd -r -p | od -t x4
0000000 8bb97487 1a4f8c88 529ea488 5efe0842
0000020

--//拼接在一起,结果如下:
8bb974871a4f8c88529ea4885efe0842
--//结果就能对上了.取后面8为算HASH_VALUE.

SCOTT@book> @ &r/16to10 5efe0842
16 to 10 DEC
------------
  1593706562

--//sql_id计算参考: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'))
/

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);
--//11G以后不建议使用wmsys.wm_concat,不想改脚本了.

SCOTT@book> set numw 50
SCOTT@book> SELECT TO_NUMBER (nvl('529ea4885efe0842','0'), 'xxxxxxxxxxxxxxxxxxxxxxxx') "16 to 10 DEC" FROM DUAL;
                                      16 to 10 DEC
--------------------------------------------------
                               5953376663046588482

SCOTT@book> @ aa.sql 5953376663046588482
REPLACE(WMSYS.WM_CONCAT(C),',')
-------------------------------
557p4j1ggw222

--//当然oracle内部也提供一些函数计算sql_id.

3.sql_id 不存在的字符eilo:

--//32位仅仅需要10个数字+22个字母,这样有4个字符不会出现在sql_id中.

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

--//可以推测ol与数字01太相近，比较容易混淆。一般编程都要求规避l,o作为变量.至于ei，估计也是一样的原因.

--//而且sql_id取64位，2^5表示1个32位，64/5=12.8(sql_id长度13个字符).剩下小于2^4表示sql_id第1个字符，这样sql_id第1个字符不会大于'h'.
--//因为e字符不在32位进制编码中.
SCOTT@book> select sql_id from v$sqlarea where substr(sql_id,1,1)>='h';
no rows selected

4.hash_value sql_id冲突

--//理论讲发生冲突的可能性是存在的,参考链接http://blog.itpub.net/267265/viewspace-1247619/
--//hash_value仅仅取后面8位,存在冲突的可能性更大.sql_id取16位,发生的概率小很多,如果你系统发现sql_id冲突,真的可以去买彩票了.
--//我这里贴出链接http://externaltable.blogspot.com/2012/06/hash-collisions-sql-signatures-and.html得到两条sql语句.

select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib;
select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq;

SCOTT@book> select sql_id,sql_text,hash_value,executions from V$sql where sql_id='ayr58apvbz37z';
no rows selected

SCOTT@book> select sql_id,sql_text c100 ,hash_value,executions from V$sql where sql_id='ayr58apvbz37z';
SQL_ID        C100                                                                                                 HASH_VALUE EXECUTIONS
------------- ---------------------------------------------------------------------------------------------------- ---------- ----------
ayr58apvbz37z select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib         1992264959          1
ayr58apvbz37z select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq         1992264959          1

--//sql_id一样.而访问v$sqlarea视图,仅仅看到1个.很明显oracle没有考虑冲突的因素(概率太小了).

SCOTT@book>  select sql_id,sql_text c100 ,hash_value,executions from v$sqlarea where sql_id='ayr58apvbz37z';
SQL_ID        C100                                                                                                 HASH_VALUE EXECUTIONS
------------- ---------------------------------------------------------------------------------------------------- ---------- ----------
ayr58apvbz37z select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib         1992264959          2

SCOTT@book> @ &r/dpc ayr58apvbz37z ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  ayr58apvbz37z, child number 0
-------------------------------------
An uncaught error happened in prepare_sql_statement : ORA-01422: exact fetch returns more than requested number of rows
--//也能显示执行计划,但是前面出现如下错误.

$ cat dpc.sql
select * from table(dbms_xplan.display_cursor(NVL('&1',NULL),NULL,'ALL ALLSTATS LAST PEEKED_BINDS cost partition -projection -outline &2'));

5.资源消耗问题:
--//正常每条sql语句都需要做这样运算,知道执行计划.问题是md5计算需要消耗多少资源.

$ cd /u01/app/oracle/diag/rdbms/book/book/trace
$ ls -l|wc
    325    2594   24666

--//一共325个文件.

$ time md5sum * > /dev/null
real    0m0.082s
user    0m0.066s
sys     0m0.016s

$ time find . -type f -name \* -exec md5sum {} \; >/dev/null
real    0m0.455s
user    0m0.098s
sys     0m0.198s

$ time find . -type f -name \* -exec md5sum {} \+ >/dev/null
real    0m0.083s
user    0m0.067s
sys     0m0.015s

--//注:如果按照第2种方式执行,相当于调用md5sum 325次. 而第1,3种次数就没有这么多.我的理解oracle应该以某种函数的形式计算这个结果.
--//我仅仅从我们生产系统估算,大约30000条语句(估计不到)需要1秒.当然这个与cpu主频有关.我的测试机器
$ cat /proc/cpuinfo
...
processor       : 23
vendor_id       : GenuineIntel
cpu family      : 6
model           : 62
model name      : Intel(R) Xeon(R) CPU E5-2630 v2 @ 2.60GHz
stepping        : 4
cpu MHz         : 2593.795
cache size      : 15360 KB
physical id     : 1
siblings        : 12
core id         : 5
cpu cores       : 6
apicid          : 43
initial apicid  : 43
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 cx16 xtpr pdcm dca sse4_1 sse4_2 x2apic popcnt aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
bogomips        : 5186.81
clflush size    : 64
cache_alignment : 64
address sizes   : 46 bits physical, 48 bits virtual
power management:

--// 1/30000*325=.01083333333333333225,与time计算的sys消耗比较吻合.