[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--define SQL2='select owner, index_name from dba_indexes where rownumdefine 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 rownumselect owner, table_name from dba_tables where rownum
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 OR sql_text ='select owner, table_name from dba_tables where rownum
SQL_ID HASH_VALUE TO_CHAR(H SQL_TEXT EXECUTIONS
------------- ---------- --------- ---------------------------------------------------------------------------------------------------- ----------
ayr58apvbz37z 1992264959 76bf8cff select owner, index_name from dba_indexes where rownumayr58apvbz37z 1992264959 76bf8cff select owner, table_name from dba_tables where rownum
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 OR sql_text ='select owner, table_name from dba_tables where rownum
SQL_ID HASH_VALUE TO_CHAR(H SQL_TEXT EXECUTIONS
------------- ---------- --------- ---------------------------------------------------------------------------------------------------- ----------
ayr58apvbz37z 1992264959 76bf8cff select owner, index_name from dba_indexes where rownum
--如果查询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