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