[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
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1247619/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL_ID怎麼轉化成HASH_VALUESQL
- svn檔案衝突,樹衝突詳解
- [20200801]sql hint衝突.txtSQL
- [20150427]tmux與INPUTRC定義衝突.txtUX
- commit 衝突MIT
- 雜湊衝突
- [20190515]熱備份模式與rman衝突.txt模式
- Git 解決衝突Git
- jQuery的$命名衝突jQuery
- git 解決衝突Git
- 查詢maven衝突Maven
- jQuery多庫衝突jQuery
- [20181130]hash衝突導致查詢緩慢.txt
- 程式衝突及其解決
- Activemq和Rabbitmq埠衝突MQ
- git pull 衝突解決Git
- Java依賴版本衝突Java
- lvm 名稱衝突LVM
- Manjaro更新出現衝突JAR
- 雜湊衝突詳解
- windows解決埠衝突Windows
- IP衝突解決方案
- SVN 版本衝突解決
- shell檢測ip衝突
- 出現型別衝突型別
- RMAN並行度衝突並行
- Oracle Latch及latch衝突Oracle
- 解衝突用到的命令
- 處理併發衝突
- 用層級理解衝突
- hash衝突解決方法
- Git衝突解決技巧Git
- Git 衝突了怎麼辦,如何高效快速的解決程式碼衝突?Git
- [20170724]關於sql_id那些事.txtSQL
- RecyclerView 、ViewPager 左右滑動衝突Viewpager
- css命名衝突解決方法CSS
- SVN解決衝突 記錄
- js檔案命名衝突理解JS