Home > Article > php教程 > Oracle如何根据SQL_TEXT生成SQL_ID

Oracle如何根据SQL_TEXT生成SQL_ID

WBOYOriginal: 2016-06-06 20:08:481131browse

本文纯属八卦，基本没有任何实用价值。Oracle总是都会通过SQL_ID来标志一个唯一的SQL。SQL_ID与SQL_TEXT一一对应。如果两个SQL文本有任何不同，包括空格等任何不可见字符，都会导致SQL_ID不同。本文八卦的内容是：Oracle如何根据SQL_TEXT内容散列成一个13位

本文纯属八卦，基本没有任何实用价值。Oracle总是都会通过SQL_ID来标志一个唯一的SQL。SQL_ID与SQL_TEXT一一对应。如果两个SQL文本有任何不同，包括空格等任何不可见字符，都会导致SQL_ID不同。本文八卦的内容是：Oracle如何根据SQL_TEXT内容散列成一个13位的字符串。为什么这个字符串会是13位？为什么这个字符经常以数字开头？

本文参考TANEL PODER和Slavik的两篇介绍(1，2)，详细介绍转换原理，顺便给出PHP/Perl实现代码。

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
  where sql_text = 'select sysdate from dual';
SQL_ID	      HASH_VALUE
------------- ----------
h35uxf5uhmm1 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对应编码字符为：

abcdfghjkmnpqrstuvwxyz

编码和字符对应关系

编码  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对应的最后三位：

h35uxf5uhmm1

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 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 
<h4>3.3 Python</h4>
<p>参考：Oracle sql_id and hash value</p>
<h3>4. 最后</h3>
<p>一个略有趣的事实，SQL_ID的第一位经常会是数字。这是因为是64位(bits)，按照5位一个字符划分，最后一个字符总是只有4位，范围总是0到15，对应字符为0123456789abcdfg，也就是说超过50%的SQL_ID都是以数字开头的。</p>
<p>好了，八卦结束。</p>
    <p class="copyright">
        原文地址：Oracle如何根据SQL_TEXT生成SQL_ID, 感谢原作者分享。
    </p>

Statement：

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Previous article：PHP生成长微博Next article：yiibooster+bsie

See more