{"id":4394,"date":"2013-03-29T14:09:40","date_gmt":"2013-03-29T06:09:40","guid":{"rendered":"http:\/\/www.orczhou.com\/?p=4394"},"modified":"2013-03-29T14:18:15","modified_gmt":"2013-03-29T06:18:15","slug":"how-oracle-gen-sql-id-from-sql-text","status":"publish","type":"post","link":"https:\/\/www.orczhou.com\/index.php\/2013\/03\/how-oracle-gen-sql-id-from-sql-text\/","title":{"rendered":"Oracle\u5982\u4f55\u6839\u636eSQL_TEXT\u751f\u6210SQL_ID"},"content":{"rendered":"

\u672c\u6587\u7eaf\u5c5e\u516b\u5366\uff0c\u57fa\u672c\u6ca1\u6709\u4efb\u4f55\u5b9e\u7528\u4ef7\u503c\u3002Oracle\u603b\u662f\u90fd\u4f1a\u901a\u8fc7SQL_ID\u6765\u6807\u5fd7\u4e00\u4e2a\u552f\u4e00\u7684SQL\u3002SQL_ID\u4e0eSQL_TEXT\u4e00\u4e00\u5bf9\u5e94\u3002\u5982\u679c\u4e24\u4e2aSQL\u6587\u672c\u6709\u4efb\u4f55\u4e0d\u540c\uff0c\u5305\u62ec\u7a7a\u683c\u7b49\u4efb\u4f55\u4e0d\u53ef\u89c1\u5b57\u7b26\uff0c\u90fd\u4f1a\u5bfc\u81f4SQL_ID\u4e0d\u540c\u3002\u672c\u6587\u516b\u5366\u7684\u5185\u5bb9\u662f\uff1aOracle\u5982\u4f55\u6839\u636eSQL_TEXT\u5185\u5bb9\u6563\u5217\u6210\u4e00\u4e2a13\u4f4d\u7684\u5b57\u7b26\u4e32\u3002\u4e3a\u4ec0\u4e48\u8fd9\u4e2a\u5b57\u7b26\u4e32\u4f1a\u662f13\u4f4d\uff1f\u4e3a\u4ec0\u4e48\u8fd9\u4e2a\u5b57\u7b26\u7ecf\u5e38\u4ee5\u6570\u5b57\u5f00\u5934\uff1f<\/p>\n

\u672c\u6587\u53c2\u8003TANEL PODER\u548cSlavik\u7684\u4e24\u7bc7\u4ecb\u7ecd(1<\/a>\uff0c2<\/a>)\uff0c\u8be6\u7ec6\u4ecb\u7ecd\u8f6c\u6362\u539f\u7406\uff0c\u987a\u4fbf\u7ed9\u51faPHP\/Perl\u5b9e\u73b0\u4ee3\u7801\u3002<\/p>\n\n

0. \u6982\u8ff0<\/h3>\n

Oracle\u5148\u8ba1\u7b97SQL_TEXT\u7684md5\u6563\u5217\u503c\uff1b\u53d6\u6563\u5217\u503c\u7684\u4f4e64\u4f4d(bits)\uff0c\u6bcf\u6b21\u53d65\u4f4d(\u6700\u540e\u4e00\u6b214\u4f4d)\uff0c\u4f7f\u7528Base32\u5c06\u5176\u4f9d\u6b21\u8f6c\u6362\u6210\u53ef\u89c1\u5b57\u7b26\uff0c\u5c31\u662f\u4f60\u6700\u7ec8\u770b\u5230\u7684SQL_ID\u3002\u539f\u7406\u5c31\u662f\u8fd9\u6837\u3002<\/p>\n

\u4e0d\u8fc7\u5b9e\u9645\u8f6c\u6362\u8fc7\u7a0b\u4e2d\u6709\u4e00\u4e9b\u8981\u6ce8\u610f\u7684\u4e8b\u9879\uff1a<\/p>\n

(a) Oracle\u5728\u8ba1\u7b97md5\u6563\u5217\u65f6\uff0c\u4f1a\u5728SQL_TEXT\u672b\u5c3e\u52a0\u4e00\u4e2a\u4e0d\u53ef\u89c1\u5b57\u7b26\\0\uff0cAWR\u62a5\u8868\u4e2d\u7ecf\u5e38\u6709\u8fd9\u6837\u7684SQL_TEXT<\/p>\n

(b) \u6ce8\u610flittle-endian\u7684\u95ee\u9898<\/p>\n

(c) Base32\u8f6c\u7801\u7684\u53ef\u89c1\u5b57\u7b26\u4e3a0123456789abcdfghjkmnpqrstuvwxyz<\/p>\n

(d) \u7f16\u5199\u7a0b\u5e8f\u7684\u65f6\u5019\u9700\u8981\u6ce8\u610f\u5927\u6570\u7cbe\u5ea6\u7684\u95ee\u9898\uff0c\u672c\u6587\u4e2dPerl\/PHP\u7a0b\u5e8f\u90fd\u4f7f\u7528\u4e86\u6570\u5b66\u5927\u6570\u5904\u7406\u51fd\u6570<\/p>\n

1. \u8be6\u7ec6\u8fc7\u7a0b<\/h3>\n

1.1 \u793a\u4f8b<\/h4>\n

\u6211\u4eec\u8003\u8651\u5982\u4e0b\u7ed9\u5b9aSQL\uff1a<\/p>\n

select sysdate from dual;<\/blockquote><\/pre>\n
\u5728Oracle 10g\u4e2d\u6267\u884c\u5e76\u67e5\u8be2v$SQL\uff0c\u53ef\u4ee5\u770b\u5230\u8fd9\u4e2aSQL\u7684SQL_ID\u662f<\/p>\n
SQL > select sql_id, hash_value from v$sql\r\n  2  where sql_text = 'select sysdate from dual';\r\n\r\nSQL_ID\t      HASH_VALUE\r\n------------- ----------\r\n7h35uxf5uhmm1 2343063137<\/blockquote><\/pre>\n
1.2 SQL_ID\u8ba1\u7b97\u7684\u8be6\u7ec6\u8fc7\u7a0b<\/h4>\n
1.2.1 \u6563\u5217\u503c\u7684\u8ba1\u7b97<\/h5>\n
\u5c06SQL_TEXT\u672b\u5c3e\u52a0\u4e0a\u4e00\u4e2a\u7a7a\u5b57\u7b26\\0\uff0c\u7136\u540e\u8fdb\u884cmd5\u6563\u5217\uff1a<\/p>\n
use Digest::MD5  qw(md5 md5_hex md5_base64);\r\n$stmt = \"select sysdate from dual\\0\";\r\n$hash = md5 $stmt;<\/blockquote><\/pre>\n
select sysdate from dual\\0\u7684MD5\u6563\u5217\u503c\u4e3aabd4dbb3096b15f1ebba0c78614ea88b\uff0c\u5171128\u4f4d(\u660e\u660e\u662f32\u4f4d\uff0c\u600e\u4e48\u8bf4128\u4f4d?<\/a>)\uff0c\u53d6\u4f4e64\u4f4d\u4e3a\uff1a”ebba0c78 614ea88b”\u3002<\/p>\n
md5\u6563\u5217\u7684\u5b57\u8282\u7801\u5982\u4e0b(128\u4f4d)\uff1a<\/p>\n
|10101011|11010100|11011011|10110011|\r\n|00001001|01101011|00010101|11110001|\r\n\r\n|11101011|10111010|00001100|01111000|\r\n|01100001|01001110|10101000|10001011|<\/blockquote><\/pre>\n
1.2.2 \u53d6\u4f4e64\u4f4d\u6574\u6570<\/h5>\n
md5\u6563\u5217\u503c\u7684\u4f4e64\u4f4d\u4e3a\uff1a<\/p>\n
|11101011|10111010|00001100|01111000|\r\n|01100001|01001110|10101000|10001011|<\/blockquote><\/pre>\n
\u5206\u4e3a\u4e24\u90e8\u5206\uff0c\u9ad832\u4f4d\u548c\u4f4e32\u4f4d\uff0c\u5206\u522b\u4e3a\uff1aebba0c78 614ea88b\uff0c\u5bf9\u5e94\u4e8c\u8fdb\u5236\u5b57\u8282\u6d41\u4e3a\uff1a|11101011|10111010|00001100|01111000|\u548c|01100001|01001110|10101000|10001011|\u3002\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528unpack\u51fd\u6570\u5c06\u6563\u5217\u503c\u89e3\u5f00\u3002\u8fd9\u91cc\u9700\u8981\u6ce8\u610f\uff0c\u53d6\u6a21\u8fd0\u7b97\u4e3a\u6574\u6570\u8fd0\u7b97\uff0c\u800c\u6211\u8fd9\u91cc\u7684\u73af\u5883\u662fx86_64 little-endian\uff0c\u6240\u4ee5\u53d6\u6a21\u8fd0\u7b97\u65f6\u5bf9\u5e94\u7684\u6574\u6570\u5b57\u8282\u5e8f(\u4eba\u8bfb\u53d6\u7684\u65f6\u5019)\uff1a<\/p>\n
|01111000|00001100|10111010|11101011|\r\n|10001011|10101000|01001110|01100001|<\/blockquote><\/pre>\n
perl\u4ee3\u7801\uff1a<\/p>\n
my($a,$b,$msb,$lsb) = unpack(\"V*\",$hash);<\/blockquote><\/pre>\n
1.2.3 Base32\u8f6c\u6362\u4e3a\u53ef\u89c1\u5b57\u7b26<\/h5>\n
Oracle\u4f7f\u7528\u4e86Base32<\/a>\u5c06\u5b57\u8282\u6d41\u8f6c\u6362\u4e3a\u53ef\u89c1\u5b57\u7b26\u3002<\/p>\n\n
\u4e00\u4e2aBase32\u5b57\u7b26\u5bf9\u5e94\u5b57\u8282\u6d41\u76845\u4f4d(bits)\uff0c\u8fd9\u91cc\u603b\u8ba164\u4f4d\uff0c\u6240\u4ee5\u662f64\/5\uff0c\u4e00\u517113\u4e2a\u5b57\u7b26\u3002\u5176\u4e2d12\u4e2a\u5b57\u7b26\u4e3a5\u4f4d\uff0c\u6709\u4e00\u4e2a\u5b57\u7b26\u603b\u662f\u56db\u4f4d(SQL_ID\u7684\u7b2c\u4e00\u4f4d)\u3002<\/p>\n
\u6211\u4eec\u6765\u770b\u672c\u6848\u4f8b\u7684\u5b57\u8282\u6d41\uff0c\u6bcf\u4e94\u4f4d\u8f6c\u6362\u4e3a\u4e00\u4e2aBase32\u7684\u7f16\u7801\uff0c\u53d6\u6700\u540e5\u4e3a00001(\u5341\u8fdb\u52361)\uff0c\u5bf9\u5e94Base32\u7f16\u7801\u4e3a1\uff1b\u53d6\u5012\u6570\u7b2c\u4e8c\u4e2a\u4e94\u4f4d10011(\u5341\u8fdb\u523619)\uff0c\u53d6\u5012\u6570\u7b2c\u4e09\u4e2a\u4e94\u4f4d\u4e3a010011(\u5341\u8fdb\u523619)…<\/p>\n
Oracle\u4f7f\u7528\u7684Base32\u5bf9\u5e94\u7f16\u7801\u5b57\u7b26\u4e3a\uff1a<\/p>\n
0123456789abcdfghjkmnpqrstuvwxyz<\/blockquote><\/pre>\n
\u7f16\u7801\u548c\u5b57\u7b26\u5bf9\u5e94\u5173\u7cfb<\/p>\n
\u7f16\u7801  00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15\r\n\u5b57\u7b26   0  1  2  3  4  5  6  7  8  9  a  b  c  d  f  g\r\n-----------------------------------------------------\r\n\u7f16\u7801  16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31\r\n\u5b57\u7b26   h  j  k  m  n  p  q  r  s  t  u  v  w  x  y  z<\/blockquote><\/pre>\n
\u6240\u4ee5\uff0c\u4e0a\u9762\u7f16\u78011\u300119\u300119\u5bf9\u5e94\u7684\u5b57\u7b26\u4e3a1\u3001m\u3001m\uff0c\u8fd9\u4e5f\u6b63\u662fSQL_ID\u5bf9\u5e94\u7684\u6700\u540e\u4e09\u4f4d\uff1a<\/p>\n
7h35uxf5uhmm1<\/blockquote><\/pre>\n
3 \u4ee3\u7801\u7247\u6bb5<\/h3>\n
3P\u7a0b\u5e8f\u4ee3\u7801\u5982\u4e0b\uff1a<\/p>\n
3.1 Perl<\/h4>\n
#!\/usr\/bin\/perl -w\r\nuse Digest::MD5  qw(md5 md5_hex md5_base64);\r\nuse Math::BigInt;\r\nmy $stmt = \"select sysdate from dual\\0\";\r\nmy $hash = md5 $stmt;\r\nmy($a,$b,$msb,$lsb) = unpack(\"V*\",$hash);\r\nmy $sqln = $msb*(2**32)+$lsb;\r\nmy $stop = log($sqln) \/ log(32) + 1;\r\nmy $sqlid = '';\r\nmy $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';\r\nmy @chars = split '', $charbase32;\r\nfor($i=0; $i < $stop-1; $i++){\r\n  my $x = Math::BigInt->new($sqln);\r\n  my $seq = $x->bdiv(32**$i)->bmod(32);\r\n  $sqlid = $chars[$seq].$sqlid;\r\n}\r\nprint \"SQL is:\\n    $stmt \\nSQL_ID is\\n    $sqlid\\n\"; <\/blockquote><\/pre>\n
3.2 PHP<\/h4>\n
function stmt_2_sqlid($stmt){\r\n  $h = md5($stmt.\"\\0\",TRUE);\r\n  $un = unpack(\"V*\",$h);\r\n  $msb = $un[3] + 0; if($msb < 0) {$msb = pow(2,32) + $msb;}\r\n  $lsb = $un[4] + 0; if($lsb < 0) {$lsb = pow(2,32) + $lsb;}\r\n  $sqln = bcadd(bcmul($msb , bcpow(2,32)) , $lsb);\r\n  $stop = log($sqln) \/ log(32) + 1;\r\n  $sqlid = '';\r\n  $alphabet = '0123456789abcdfghjkmnpqrstuvwxyz';\r\n  for($i = 0; $i < $stop-1; $i++){\r\n    $seq = bcmod((bcdiv($sqln,bcpow(32,$i),5)),32);\r\n    $sqlid = $alphabet[$seq].$sqlid;\r\n  }\r\n  return $sqlid;\r\n}\r\n$stmt = 'select sysdate from dual';\r\necho stmt_2_sqlid($stmt);<\/blockquote><\/pre>\n
3.3 Python<\/h4>\n
\u53c2\u8003\uff1aOracle sql_id and hash value<\/a><\/p>\n
4. \u6700\u540e<\/h3>\n
\u4e00\u4e2a\u7565\u6709\u8da3\u7684\u4e8b\u5b9e\uff0cSQL_ID\u7684\u7b2c\u4e00\u4f4d\u7ecf\u5e38\u4f1a\u662f\u6570\u5b57\u3002\u8fd9\u662f\u56e0\u4e3a\u662f64\u4f4d(bits)\uff0c\u6309\u71675\u4f4d\u4e00\u4e2a\u5b57\u7b26\u5212\u5206\uff0c\u6700\u540e\u4e00\u4e2a\u5b57\u7b26\u603b\u662f\u53ea\u67094\u4f4d\uff0c\u8303\u56f4\u603b\u662f0\u523015\uff0c\u5bf9\u5e94\u5b57\u7b26\u4e3a0123456789abcdfg\uff0c\u4e5f\u5c31\u662f\u8bf4\u8d85\u8fc750%\u7684SQL_ID\u90fd\u662f\u4ee5\u6570\u5b57\u5f00\u5934\u7684\u3002<\/p>\n
\u597d\u4e86\uff0c\u516b\u5366\u7ed3\u675f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u672c\u6587\u7eaf\u5c5e\u516b\u5366\uff0c\u57fa\u672c\u6ca1\u6709\u4efb\u4f55\u5b9e\u7528\u4ef7\u503c\u3002Oracle\u603b\u662f\u90fd\u4f1a\u901a\u8fc7SQL_ID\u6765\u6807\u5fd7\u4e00\u4e2a\u552f\u4e00\u7684SQL\u3002SQL_ID\u4e0eSQL_TEXT\u4e00\u4e00\u5bf9\u5e94\u3002\u5982\u679c\u4e24\u4e2aSQL\u6587\u672c\u6709\u4efb\u4f55\u4e0d\u540c\uff0c\u5305\u62ec\u7a7a\u683c\u7b49\u4efb\u4f55\u4e0d\u53ef\u89c1\u5b57\u7b26\uff0c\u90fd\u4f1a\u5bfc\u81f4SQL_ID\u4e0d\u540c\u3002\u672c\u6587\u516b\u5366\u7684\u5185\u5bb9\u662f\uff1aOracle\u5982\u4f55\u6839\u636eSQL_TEXT\u5185\u5bb9\u6563\u5217\u6210\u4e00\u4e2a13\u4f4d\u7684\u5b57\u7b26\u4e32\u3002\u4e3a\u4ec0\u4e48\u8fd9\u4e2a\u5b57\u7b26\u4e32\u4f1a\u662f13\u4f4d\uff1f\u4e3a\u4ec0\u4e48\u8fd9\u4e2a\u5b57\u7b26\u7ecf\u5e38\u4ee5\u6570\u5b57\u5f00\u5934\uff1f \u672c\u6587\u53c2\u8003TANEL PODER\u548cSlavik\u7684\u4e24\u7bc7\u4ecb\u7ecd(1\uff0c2)\uff0c\u8be6\u7ec6\u4ecb\u7ecd\u8f6c\u6362\u539f\u7406\uff0c\u987a\u4fbf\u7ed9\u51faPHP\/Perl\u5b9e\u73b0\u4ee3\u7801\u3002 0. \u6982\u8ff0 Oracle\u5148\u8ba1\u7b97SQL_TEXT\u7684md5\u6563\u5217\u503c\uff1b\u53d6\u6563\u5217\u503c\u7684\u4f4e64\u4f4d(bits)\uff0c\u6bcf\u6b21\u53d65\u4f4d(\u6700\u540e\u4e00\u6b214\u4f4d)\uff0c\u4f7f\u7528Base32\u5c06\u5176\u4f9d\u6b21\u8f6c\u6362\u6210\u53ef\u89c1\u5b57\u7b26\uff0c\u5c31\u662f\u4f60\u6700\u7ec8\u770b\u5230\u7684SQL_ID\u3002\u539f\u7406\u5c31\u662f\u8fd9\u6837\u3002 \u4e0d\u8fc7\u5b9e\u9645\u8f6c\u6362\u8fc7\u7a0b\u4e2d\u6709\u4e00\u4e9b\u8981\u6ce8\u610f\u7684\u4e8b\u9879\uff1a (a) Oracle\u5728\u8ba1\u7b97md5\u6563\u5217\u65f6\uff0c\u4f1a\u5728SQL_TEXT\u672b\u5c3e\u52a0\u4e00\u4e2a\u4e0d\u53ef\u89c1\u5b57\u7b26\\0\uff0cAWR\u62a5\u8868\u4e2d\u7ecf\u5e38\u6709\u8fd9\u6837\u7684SQL_TEXT (b) \u6ce8\u610flittle-endian\u7684\u95ee\u9898 (c) Base32\u8f6c\u7801\u7684\u53ef\u89c1\u5b57\u7b26\u4e3a0123456789abcdfghjkmnpqrstuvwxyz (d) \u7f16\u5199\u7a0b\u5e8f\u7684\u65f6\u5019\u9700\u8981\u6ce8\u610f\u5927\u6570\u7cbe\u5ea6\u7684\u95ee\u9898\uff0c\u672c\u6587\u4e2dPerl\/PHP\u7a0b\u5e8f\u90fd\u4f7f\u7528\u4e86\u6570\u5b66\u5927\u6570\u5904\u7406\u51fd\u6570<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_eb_attr":"","inline_featured_image":false,"_tocer_settings":[],"footnotes":""},"categories":[6,13],"tags":[61,107,108],"class_list":["post-4394","post","type-post","status-publish","format-standard","hentry","category-mysql","category-oracle","tag-oracle-2","tag-sqlid","tag-sql_text"],"_links":{"self":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/4394","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/comments?post=4394"}],"version-history":[{"count":24,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/4394\/revisions"}],"predecessor-version":[{"id":4418,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/posts\/4394\/revisions\/4418"}],"wp:attachment":[{"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/media?parent=4394"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/categories?post=4394"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.orczhou.com\/index.php\/wp-json\/wp\/v2\/tags?post=4394"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}