浅析oracle sql_id和sql hash

oracle的sql id其实是通过md5算法把sql text转化成hash值

所以sql text不变，sql id就不会变，无论在什么版本的oracle数据库中。

 

md5换算sql text为sql id的脚本：

#!/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;
}

运行

$ perl sql_id.pl
SQL is:
 select sysdate from dual 
SQL_ID is
 7h35uxf5uhmm1

数据库中的sql id： 

SQL> select SQL_TEXT,sql_id from v$sql where SQL_TEXT like 'select sysdate from dual%';

SQL_TEXT                       SQL_ID
------------------------------ -------------
select sysdate from dual       7h35uxf5uhmm1

sql id是一样的。

 

9i中sql的标识就是hash value，10g新增sql id

简言之，9i之前通过hash value标识sql，9i之后通过sql id标识sql

oracle提供了包来转换sql id和sql hash value。dbms_utility.SQLID_TO_SQLHASH

SQL> select sql_id,hash_value,dbms_utility.SQLID_TO_SQLHASH(sql_id) convert from v$sql where rownum <9;

SQL_ID        HASH_VALUE    CONVERT
------------- ---------- ----------
0gbrycmp8400u 3934388250 3934388250
d4p9hk6qvn00z 2914648095 2914648095
0uq3xcgr1h011 3994550305 3994550305
3t09d4n4us01a  162267178  162267178
72y05ahu6401f  878837806  878837806
19u4dgffqh01n 2640838708 2640838708
baz7d946qw01t  225312825  225312825
fr03m9qgnh01v 2672295995 2672295995