[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
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> create table dula as select * from dual ;
Table created.

$ cat hash_birthday_sql.sql
-- hash_birthday_sql.sql  runs a simple birthday attack against hash_value or sql_id to find collisions
-- Luca May 2012
-- Usage @hash_birthday_sql tabel_name iterations trailing_hash_bytes
-- examples: @hash_birthday_sql hash_1 10 4        --run a birth attack to find collisions on hash_value (32 bits)
--           @hash_birthday_sql hash_1 500000 8    --run a birth attack to find collisions on sql_id (64 bits)
--                                                Note multiple copies of the script can run concurrently, also in RAC,
--                                                just use different table names then put all together with a view
-- Prereq: create and/or customize tablespace_name (see below define tbsname)

define hashtable_name=&1
define num_steps=&2
define bytes=&3
define tbsname=users
define innerloop=10000
--define SQL1='select owner, table_name from dba_tables where rownum<=10 --'
--define SQL2='select owner, index_name from dba_indexes where rownum<=9 --'
define SQL1='select sysdate from dual --'
define SQL2='select sysdate from dula --'

drop table &hashtable_name purge;

create table &hashtable_name
  (hashval varchar2(40), sql_type number(2), sql varchar2(500))
tablespace &tbsname;

create or replace procedure calchash_&hashtable_name
as
  TYPE hashtab is TABLE OF varchar2(200) INDEX BY PLS_INTEGER;
  t_hashval1 hashtab;
  t_hashval2 hashtab;
  t_rndstrng hashtab;
begin
   for i in 1..&num_steps loop
     for j in 1..&innerloop loop
        t_rndstrng(j) := dbms_random.string('a',32);

        -- t_hashval1(j):= substr(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING =>'&SQL1'||t_rndstrng(j)||chr(0)),17-&bytes,&bytes); -- hash SQL+random+chr(0)
        -- t_hashval2(j):= substr(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING =>'&SQL2'||t_rndstrng(j)||chr(0)),17-&bytes,&bytes);
        -- modify by lfree
        t_hashval1(j):= lower(rawtohex(UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING =>'&SQL1'||t_rndstrng(j)||chr(0))))); -- hash SQL+random+chr(0)
        t_hashval2(j):= lower(rawtohex(UTL_RAW.CAST_TO_RAW(DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT_STRING =>'&SQL2'||t_rndstrng(j)||chr(0)))));
     end loop;
   FORALL i IN 1..&innerloop    -- bulk insert
     insert into &hashtable_name values (t_hashval1(i),1,t_rndstrng(i));
   FORALL i IN 1..&innerloop
     insert into &hashtable_name values (t_hashval2(i),2,t_rndstrng(i));

   commit write nowait batch; -- commit and proceed with next batch of rows
   end loop;

end;
/

set timing on
exec calchash_&hashtable_name

-- 說明:我執行原始的指令碼有問題,也許字符集的問題,我改一些.
-- hash_value取的是最後8位.

define SQL1='select sysdate from dual --'
define SQL2='select sysdate from dula --'

select hashval,substr(hashval,25,8), sql_type, decode(sql_type,1,'&SQL1',2,'&SQL2',null) ||sql||';' sql_text
  from HASHTAB
where substr(hashval,25,8) in (select substr(hashval,25,8) from HASHTAB group by substr(hashval,25,8) having count(*)>1)
order by substr(hashval,25,8);

HASHVAL                                  SUBSTR(HASHVAL,2   SQL_TYPE SQL_TEXT
---------------------------------------- ---------------- ---------- ------------------------------------------------------------
c71883ae4e818947d613d78a31eb7168         31eb7168                  1 select sysdate from dual --lKxJJcPEUTjApATJphcLUMjsJnnVVGZV;
061f5b21b17752e7023f59df31eb7168         31eb7168                  2 select sysdate from dula --AxznlSHtpUlGnoJCxeekPkMeydsUmtiQ;
8c27d1260e3d706cb00dbdbac5629d86         c5629d86                  2 select sysdate from dula --VsyFDvadOCTifNpwYzooDzXCPWGWwVIR;
9ba530a5cf4a481ee3c363acc5629d86         c5629d86                  1 select sysdate from dual --iFcKmjNHRThIpnTqkpIArflAAmYhkdtt;
fd74cb7b41a1a8e39e3f698ed4fc361e         d4fc361e                  2 select sysdate from dula --aBgBJNOAclPiVwvvIQtJCGgIxlHYuSMG;
41faa25c31e04dad21123182d4fc361e         d4fc361e                  1 select sysdate from dual --zIWuwCboIvGegurfOkcdbcORGEVTgqTp;

6 rows selected.

-- 執行sql語句看看是否複合.
SCOTT@test> select sysdate from dual --lKxJJcPEUTjApATJphcLUMjsJnnVVGZV;
SYSDATE
-------------------
2014-08-07 20:48:19

SCOTT@test> select sysdate from dula --AxznlSHtpUlGnoJCxeekPkMeydsUmtiQ;
SYSDATE
-------------------
2014-08-07 20:48:28

SELECT sql_id,hash_value,to_char(hash_value,'xxxxxxxx'),sql_text  FROM v$sqlarea
WHERE    sql_text ='select sysdate from dual --lKxJJcPEUTjApATJphcLUMjsJnnVVGZV'
       OR sql_text ='select sysdate from dula --AxznlSHtpUlGnoJCxeekPkMeydsUmtiQ';

SQL_ID        HASH_VALUE TO_CHAR(H SQL_TEXT
------------- ---------- --------- ------------------------------------------------------------
8ppsmutn73utj 1752296241  6871eb31 select sysdate from dual --lKxJJcPEUTjApATJphcLUMjsJnnVVGZV
dyq9z09n73utj 1752296241  6871eb31 select sysdate from dula --AxznlSHtpUlGnoJCxeekPkMeydsUmtiQ

-- hash_value=1752296241, 31eb7168 要對調一下,變成6871eb31
SCOTT@test> @16to10 6871eb31
16 to 10 DEC
------------
  1752296241

--如果需要sql_id衝突,需要更大的資料量.作者執行的是 @hash_birthday_sql hashtab 500000 8 .
would take about 60 hours to do such calculation (mileage may vary). A faster way is to split the execution in smaller
chunks and parallelize the execution across multiple CPU and possibly RAC nodes. See the link here for an example on 2
RAC nodes and parallelism 10 each.

--太長了.我做了@hash_birthday_sql hashtab 100 8 .也沒有找到.借用他的結果.

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

SELECT sql_id,hash_value,to_char(hash_value,'xxxxxxxx'),sql_text,executions  FROM v$sql
WHERE    sql_text ='select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib'
       OR sql_text ='select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq';

SQL_ID        HASH_VALUE TO_CHAR(H SQL_TEXT                                                                                             EXECUTIONS
------------- ---------- --------- ---------------------------------------------------------------------------------------------------- ----------
ayr58apvbz37z 1992264959  76bf8cff select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib                  1
ayr58apvbz37z 1992264959  76bf8cff select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq                  1

SELECT sql_id,hash_value,to_char(hash_value,'xxxxxxxx'),sql_text,executions  FROM v$sqlarea
WHERE    sql_text ='select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib'
       OR sql_text ='select owner, table_name from dba_tables where rownum<=10 --XhiidvehudXqDpCMZokNkScXlQiIUkUq';

SQL_ID        HASH_VALUE TO_CHAR(H SQL_TEXT                                                                                             EXECUTIONS
------------- ---------- --------- ---------------------------------------------------------------------------------------------------- ----------
ayr58apvbz37z 1992264959  76bf8cff select owner, index_name from dba_indexes where rownum<=9 --BaERRzEYqyYphBAvEbIrbYYDKkemLaib                  2

--如果查詢v$sqlarea檢視僅僅看到1條sql語句.執行次數變成了2.明視訊記憶體在錯誤，估計oracle認為發生衝突的可能性很小很小。
--使用dbms_xplan 檢視執行計劃會出現ORA-01422錯誤.

SCOTT@test> @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