[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消耗比较吻合.

时间: 2024-09-20 18:51:25

[20170724]关于sql_id那些事.txt的相关文章

[20150803]无法通过sql_id找到sql语句3.txt

[20150803]无法通过sql_id找到sql语句3.txt --前一阵子,在做优化时遇到1个无法通过sql_id找到sql语句的情况: http://blog.itpub.net/267265/viewspace-1749265/ --就是因为共享池太小,执行次数少,没到取样时间,已经从共享池清除. --今天自己google,想想看看还有那种情况会出现呢?如果1条语句执行错误,会记录sql_id,当时查询v$sql视图应该也不能找到. --自己直接那生产系统看看: 1.建立测试环境: --

[20141212]关于sql_id.txt

[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        BA

[20141205]关于sql_id.txt

[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_utilit

[20150616]关于sql_id.txt

[20150616]关于sql_id.txt --我曾经提到PLSQL中使用绑定变量,oracle会格式化处理,转化为特定的格式.可以参考我以前的例子: --[20121102]PLSQL中的绑定变量.txt --http://blog.itpub.net/267265/viewspace-748190/ --我也曾经写过一篇exact_matching_signature,force_matching. --http://blog.itpub.net/267265/viewspace-1220

[20140807]hash_value sql_id冲突.txt

[20140807]hash_value sql_id冲突.txt --9i下使用v$sql仅仅有hash_value值,sql_id是到10g下才出现. --理论讲hash_values是sql_id的子集,发生冲突的可能性比使用sql_id,看了链接重复测试: http://externaltable.blogspot.com/2012/06/hash-collisions-sql-signatures-and.html SCOTT@test> @ver BANNER -----------

[20131217]从sql语句计算sql_id.txt

[20131217]从sql语句计算sql_id.txt 这个方面的内容从网上能找到许多,从sql语句可以检查出sql_id的值以及HASH_VALUE的值. http://www.johnnydb.com/2012/03/sql_id-vs-hash_value/www.dbthink.com/?p=321 自己做一些测试以及总结,记录一些简单的方法在11G下: SYS@test> @verBANNER----------------------------------------------

关于WordPress的robots.txt文件的那些事

安装完wordpress站点后关于robots.txt文件的书写一直烦恼着很多站长,Robots.txt文件协议又叫搜索引擎机器人协议,搜索引擎爬虫在爬取网站的时候,首先会看网站根目录下是否有robots.txt文件,然后遵循robots.txt协议爬取网站主希望搜索引擎爬取的内容.robots.txt文件意在告诉搜索引擎爬虫哪些页面可以爬取,哪些页面不能爬取,可以有效的保护用户的隐私,同时也有利于节省蜘蛛的带宽,从而让蜘蛛爬取更加容易,促进收录. 先来简单的说下robots.txt文件的规则:

[20170724]提示BIND_AWARE与PLSQL光标缓存

[20170724]提示BIND_AWARE与PLSQL光标缓存.txt --//[20170724]Bind Sensitivity and PLSQL cursor caching.txt --//曾经写过一个系列acs的文章.链接如下: http://blog.itpub.net/267265/viewspace-721817/ http://blog.itpub.net/267265/viewspace-1336242/ http://blog.itpub.net/267265/view

网站开发人员应该知道的61件事

有人在Stack Overflow上发问,动手开发网站之前,需要知道哪些事情? 不出意料地,他得到了一大堆回答. 通常情况下,你需要把所有人的发言从头到尾读一遍.但是,Stack Overflow有一个很贴心的设计,它允许在问题下方开设一个wiki区,让所有人共同编辑一个最佳答案.于是,就有了下面这篇文章,一共总结出六个方面共计61条"网站开发须知". 我发现,这种概述性的问题,最适合这种集合群智.头脑风暴式的回答方式了.这也是我第一次觉得,Stack Overflow做到了Wikip