Oracle如何根据SQL_TEXT生成SQL

0. 概述

Oracle先计算SQL_TEXT的md5散列值；取散列值的低64位(bits)，每次取5位(最后一次4位)，使用Base32将其依次转换成可见字符，就是你最终看到的SQL_ID。原理就是这样。

不过实际转换过程中有一些要注意的事项：

(a) Oracle在计算md5散列时，会在SQL_TEXT末尾加一个不可见字符\0，AWR报表中经常有这样的SQL_TEXT

(b) 注意little-endian的问题

(d) 编写程序的时候需要注意大数精度的问题，本文中Perl/PHP程序都使用了数学大数处理函数

1. 详细过程

1.1 示例

我们考虑如下给定SQL：

select sysdate from dual;

在Oracle 10g中执行并查询v$SQL，可以看到这个SQL的SQL_ID是

SQL > select sql_id, hash_value from v$sql
  2  where sql_text = 'select sysdate from dual';

SQL_ID	      HASH_VALUE
------------- ----------
7h35uxf5uhmm1 2343063137

1.2 SQL_ID计算的详细过程

1.2.1 散列值的计算

将SQL_TEXT末尾加上一个空字符\0，然后进行md5散列：

use Digest::MD5  qw(md5 md5_hex md5_base64);
$stmt = "select sysdate from dual\0";
$hash = md5 $stmt;

select sysdate from dual\0的MD5散列值为abd4dbb3096b15f1ebba0c78614ea88b，共128位(明明是32位，怎么说128位?)，取低64位为："ebba0c78 614ea88b"。

md5散列的字节码如下(128位)：

|10101011|11010100|11011011|10110011|
|00001001|01101011|00010101|11110001|

|11101011|10111010|00001100|01111000|
|01100001|01001110|10101000|10001011|

1.2.2 取低64位整数

md5散列值的低64位为：

|11101011|10111010|00001100|01111000|
|01100001|01001110|10101000|10001011|

分为两部分，高32位和低32位，分别为：ebba0c78 614ea88b，对应二进制字节流为：|11101011|10111010|00001100|01111000|和|01100001|01001110|10101000|10001011|。可以直接使用unpack函数将散列值解开。这里需要注意，取模运算为整数运算，而我这里的环境是x86_64 little-endian，所以取模运算时对应的整数字节序(人读取的时候)：

|01111000|00001100|10111010|11101011|
|10001011|10101000|01001110|01100001|

perl代码：

my($a,$b,$msb,$lsb) = unpack("V*",$hash);

1.2.3 Base32转换为可见字符

Oracle使用了Base32将字节流转换为可见字符。

一个Base32字符对应字节流的5位(bits)，这里总计64位，所以是64/5，一共13个字符。其中12个字符为5位，有一个字符总是四位(SQL_ID的第一位)。

我们来看本案例的字节流，每五位转换为一个Base32的编码，取最后5为00001(十进制1)，对应Base32编码为1；取倒数第二个五位10011(十进制19)，取倒数第三个五位为010011(十进制19)...

Oracle使用的Base32对应编码字符为：

0123456789abcdfghjkmnpqrstuvwxyz

编码和字符对应关系

编码  00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15
字符   0  1  2  3  4  5  6  7  8  9  a  b  c  d  f  g
-----------------------------------------------------
编码  16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31
字符   h  j  k  m  n  p  q  r  s  t  u  v  w  x  y  z

所以，上面编码1、19、19对应的字符为1、m、m，这也正是SQL_ID对应的最后三位：

7h35uxf5uhmm1

3 代码片段

3P程序代码如下：

3.1 Perl

#!/usr/bin/perl -w
use Digest::MD5  qw(md5 md5_hex md5_base64);
use Math::BigInt;
my $stmt = "select sysdate from dual\0";
my $hash = md5 $stmt;
my($a,$b,$msb,$lsb) = unpack("V*",$hash);
my $sqln = $msb*(2**32)+$lsb;
my $stop = log($sqln) / log(32) + 1;
my $sqlid = '';
my $charbase32 = '0123456789abcdfghjkmnpqrstuvwxyz';
my @chars = split '', $charbase32;
for($i=0; $i < $stop-1; $i++){
  my $x = Math::BigInt->new($sqln);
  my $seq = $x->bdiv(32**$i)->bmod(32);
  $sqlid = $chars[$seq].$sqlid;
}
print "SQL is:\n    $stmt \nSQL_ID is\n    $sqlid\n";

3.2 PHP

function stmt_2_sqlid($stmt){
  $h = md5($stmt."\0",TRUE);
  $un = unpack("V*",$h);
  $msb = $un[3] + 0; if($msb < 0) {$msb = pow(2,32) + $msb;}
  $lsb = $un[4] + 0; if($lsb < 0) {$lsb = pow(2,32) + $lsb;}
  $sqln = bcadd(bcmul($msb , bcpow(2,32)) , $lsb);
  $stop = log($sqln) / log(32) + 1;
  $sqlid = '';
  $alphabet = '0123456789abcdfghjkmnpqrstuvwxyz';
  for($i = 0; $i < $stop-1; $i++){
    $seq = bcmod((bcdiv($sqln,bcpow(32,$i),5)),32);
    $sqlid = $alphabet[$seq].$sqlid;
  }
  return $sqlid;
}
$stmt = 'select sysdate from dual';
echo stmt_2_sqlid($stmt);