Oracle的学习心得和知识总结（三十三）|Oracle数据库数据库的SQL ID的底层计算原理分析

目录结构

注：提前言明 本文借鉴了以下博主、书籍或网站的内容，其列表如下：

1、参考书籍：《Oracle Database SQL Language Reference》
2、参考书籍：《PostgreSQL中文手册》
3、EDB Postgres Advanced Server User Guides，点击前往
4、PostgreSQL数据库仓库链接，点击前往
5、PostgreSQL中文社区，点击前往

---

1、本文内容全部来源于开源社区 GitHub和以上博主的贡献，本文也免费开源（可能会存在问题，评论区等待大佬们的指正）
2、本文目的：开源共享 抛砖引玉 一起学习
3、本文不提供任何资源 不存在任何交易 与任何组织和机构无关
4、大家可以根据需要自行 复制粘贴以及作为其他个人用途，但是不允许转载 不允许商用 （写作不易，还请见谅 💖）

---

---

---

文章快速说明索引

学习目标：

目的：接下来这段时间我想做一些兼容Oracle数据库Real Application Testing (即：RAT)上的一些功能开发，本专栏这里主要是学习以及介绍Oracle数据库功能的使用场景、原理说明和注意事项等，基于PostgreSQL数据库的功能开发等之后 由新博客进行介绍和分享！

---

学习内容：（详见目录）

1、Oracle数据库数据库的SQL ID的底层计算原理分析

---

学习时间：

2025年02月09日 18:06:19

---

学习产出：

1、Oracle数据库数据库的SQL ID的底层计算原理分析
2、CSDN 技术博客 1篇

---

注：下面我们所有的学习环境是Centos7+PostgreSQL15.0+Oracle19c+MySQL5.7

postgres=# select version();version                                   
-----------------------------------------------------------------------------PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.1.0, 64-bit
(1 row)postgres=##-----------------------------------------------------------------------------#SQL> select * from v$version; BANNER									    BANNER_FULL 								BANNER_LEGACY									CON_ID
--------------------------------------------------------------------------- --------------------------------------------------------------------------- --------------------------------------------------------------------------- ----------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production	Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production		     0Version 19.3.0.0.0SQL>
#-----------------------------------------------------------------------------#mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.19    |
+-----------+
1 row in set (0.06 sec)mysql>

---

背景说明案例展示

在使用Oracle数据库的时候经常会看到SQL ID这样的东西，如下：

或者是如下SQL语句：

[oracle@local64 ~]$ sqlplus '/as sysdba'SQL*Plus: Release 11.2.0.1.0 Production on Fri Feb 7 14:10:57 2025Copyright (c) 1982, 2009, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> select 'SONG BAOBAO' from dual;'SONGBAOBAO
-----------
SONG BAOBAOSQL> select 'SONGBAobao' from DUAL;'SONGBAOBA
----------
SONGBAobaoSQL> select sql_id, sql_text from v$sql where sql_text like '%SONG%';SQL_ID		     SQL_TEXT
-------------------- ----------------------------------------------------------------------------------------------------
dfrun6x61sj3g	     select 'SONGBAobao' from DUAL
2fsps80qfadc3	     select sql_id, sql_text from v$sql where sql_text like '%SONG%'
dgs6n0z31avcp	     select 'SONG BAOBAO' from dualSQL>

或者通过package函数，如下：

SQL> select dbms_sqltune_util0.sqltext_to_sqlid('select ''SONG BAOBAO'' from dual'||chr(0)) sql_id from dual;SQL_ID
--------------------
dgs6n0z31avcpSQL> select dbms_sqltune_util0.sqltext_to_sqlid('select ''SONGBAobao'' from DUAL'||chr(0)) sql_id from dual;SQL_ID
--------------------
dfrun6x61sj3gSQL>

---

如上都是简单SQL语句，下面看一下上面AWR图的一个例子，如下：

7r7636982atn9	UPDATE INVENTORIES SET QUANTITY_ON_HAND = QUANTITY_ON_HAND - :B1 WHERE PRODUCT_ID = :B3 AND WAREHOUSE_ID = :B2 

---

接下来，我们今天分析一下Oracle SQL_ID的生成过程以及相关注意事项，如下：

一、SQL_ID生成过程

1. 计算MD5散列值：

   • Oracle会对SQL_TEXT计算MD5散列值，并在SQL_TEXT末尾添加一个不可见字符\0（空字符）如上所述。
   • 这个步骤确保了即使SQL_TEXT内容相同，生成的SQL_ID也会因为末尾的空字符而不同。

2. 取MD5散列值的低64位(请注意大小端的问题)：

   • MD5散列值是一个128位的值，Oracle只取其中的低64位（即最后8个字节）。
   • 由于MD5是128位的，低64位就是最后64位（即最后8个字节）。

3. 将64位分成5位一组（最后一组4位）：

   • 将这64位分成13组，前12组每组5位，最后一组4位。
   • 每组5位的值范围是0到31，最后一组4位的值范围是0到15。

4. Base32编码：

   • 使用Base32将每组5位（或4位）的值转换为可见字符。
   • Oracle使用的Base32字符集为：0123456789abcdfghjkmnpqrstuvwxyz。
   • 注意：这个字符集与标准的Base32字符集不同，标准的Base32字符集通常包含字母A-Z和数字2-7。

二、注意事项

(a) MD5散列时的空字符：

• 在计算MD5时，Oracle会在SQL_TEXT末尾添加一个空字符\0。这意味着即使两个SQL_TEXT内容相同，生成的SQL_ID也会不同，因为末尾的空字符会导致MD5散列值不同。

(b) Little-endian问题：

• 在处理字节序时，Oracle使用的是小端序（Little-endian）。这意味着在读取字节时，低位字节在前，高位字节在后。
• 在编写程序时，需要确保正确处理字节序，特别是在处理64位值时。

© Base32字符集：

• Oracle使用的Base32字符集为：0123456789abcdfghjkmnpqrstuvwxyz。
• 这个字符集与标准的Base32字符集不同，因此在编写程序时需要特别注意使用正确的字符集。

(d) 大数精度问题：

• 在处理64位值时，可能会遇到大数精度问题。特别是在一些编程语言中，默认的整数类型可能无法处理64位值。
• 在Perl、PHP等语言中，可以使用大数处理函数（如GMP或BCMath扩展）来确保精度。

---

底层实现案例分析

SQL> select dbms_sqltune_util0.sqltext_to_sqlid('select ''SONG BAOBAO'' from dual'||chr(0)) sql_id from dual;SQL_ID
--------------------
dgs6n0z31avcpSQL>

我的环境，如下：

[oracle@local64 mydata]$ lscpu | grep -i byte
Byte Order:            Little Endian
[oracle@local64 mydata]$

---

第一步，计算散列值

• Digest::MD5 相关函数说明，点击前往

先计算一下其md5值，如下：

[oracle@local64 mydata]$ cat mytest3.pl 
use Digest::MD5 qw(md5_hex);
$stmt = "select 'SONG BAOBAO' from dual\0";
$hash_hex = md5_hex $stmt;
print "$hash_hex\n";[oracle@local64 mydata]$ perl mytest3.pl 
10131690c894385807d4e0d7956d15c6
[oracle@local64 mydata]$10131690c8943858
07d4e0d7956d15c6

• 进制转换在线，点击前往

其二进制转换为：

00010000000100110001011010010000110010001001010000111000010110000000011111010100111000001101011110010101011011010001010111000110

---

第二步，取低64位

07d4e0d7956d15c60000011111010100111000001101011110010101011011010001010111000110

---

第三步，转换为易读字符串

Oracle使用了Base32将字节流转换为可见字符，一一对应如下：

字节流十进制	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

---

注： 这里给大家推荐一个好用的网站(可以直接复制粘贴到markdown)：

• 表格转换工具，点击前往

---

0000011111010100111000001101011110010101011011010001010111000110# 划分一下，如下：
00000111 11010100 11100000 11010111
10010101 01101101 00010101 11000110# 因为是小端11010111 11100000 11010100 00000111
11000110 00010101 01101101 10010101# 合并
1101011111100000110101000000011111000110000101010110110110010101# 其十六进制
d7e0d407c6156d95

然后切分，以及转换 如下：

1101 01111 11000 00110 10100 00000 11111 00011 00001 01010 11011 01100 10101
13   15    24    6     20    0     31    3     1     10    27    12    21 
d    g     s     6     n     0     z     3     1     a     v     c     pdgs6n0z31avcp

---

最后看一下转换一个SQL的SQLID逻辑，如下：

#!/usr/bin/perl -w
use Digest::MD5  qw(md5 md5_hex md5_base64);
use Math::BigInt;
my $stmt = "select 'SONG BAOBAO' from dual\0";
my $hash = md5 $stmt;
my($a,$b,$msb,$lsb) = unpack("V*",$hash);
print "a is $a, b is $b; msb is $msb, lsb is $lsb\n";my $sqln = $msb*(2**32)+$lsb;
my $stop = log($sqln) / log(32) + 1;
print "sqln is $sqln, stop is $stop\n";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"; 

执行一下，如下：

[oracle@local64 mydata]$ perl mytest1.pl 
a is 2417365776, b is 1480103112; msb is 3621835783, lsb is 3323293077
sqln is 15555666242790845845, stop is 13.7508147978182
SQL is:select 'SONG BAOBAO' from dual 
SQL_ID isdgs6n0z31avcp
[oracle@local64 mydata]$

解释一下，上面sqln = 15555666242790845845，该十进制的十六进制转换，如下：

[oracle@local64 mydata]$ cat convert_uint64_to_str.c
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <stdint.h>int main()
{const char *map = "0123456789abcdfghjkmnpqrstuvwxyz";int i = 0;char str[14] = {0};uint64_t sqlid = (uint64_t)15555666242790845845ULL;for(; i<13;++i){int j = (sqlid >> (60 - i * 5)) & 0x1F;str[i] = map[j];}printf("str is %s\n", str);return 0;
}
[oracle@local64 mydata]$

[oracle@local64 mydata]$ gcc -o convert convert_uint64_to_str.c -g -O0
[oracle@local64 mydata]$ 
[oracle@local64 mydata]$ ./convert 
str is dgs6n0z31avcp
[oracle@local64 mydata]$

---

若是用C语言实现上面perl的效果，如下：

[oracle@local64 mydata]$ cat mytest2.c 
#include <stdio.h>
#include <string.h>
#include <openssl/md5.h>
#include <math.h>
#include <stdint.h>// 用于计算 MD5 的函数
void md5_hash(const unsigned char *input, size_t length, unsigned char *output)
{MD5_CTX mdContext;MD5_Init(&mdContext);MD5_Update(&mdContext, input, length);MD5_Final(output, &mdContext);
}int main()
{int step = 0;// 包含 \0 的原始字节数组const unsigned char stmt[] = "select 'SONG BAOBAO' from dual\0";size_t stmt_len = sizeof(stmt) - 1; // 计算不包括末尾 \0 的实际长度// 用于存放 MD5 哈希结果unsigned char hash[MD5_DIGEST_LENGTH];md5_hash(stmt, stmt_len, hash); // 计算 MD5// 解包 MD5 哈希值uint32_t a = (hash[0] << 0) | (hash[1] << 8) | (hash[2] << 16) | (hash[3] << 24);uint32_t b = (hash[4] << 0) | (hash[5] << 8) | (hash[6] << 16) | (hash[7] << 24);uint32_t msb = (hash[8] << 0) | (hash[9] << 8) | (hash[10] << 16) | (hash[11] << 24);uint32_t lsb = (hash[12] << 0) | (hash[13] << 8) | (hash[14] << 16) | (hash[15] << 24);printf("a is %u, b is %u; msb is %u, lsb is %u\n", a, b, msb, lsb);// 计算 sqln 和 stopuint64_t sqln = ((uint64_t)msb << 32) + lsb;double stop = log((double)sqln) / log(32.0) + 1;printf("sqln is %llu, stop is %.06f\n", sqln, stop);// Base32 字符集char charbase32[] = "0123456789abcdfghjkmnpqrstuvwxyz";char sqlid[100] = {0};step = (int)ceil(stop);// 生成 SQL_IDfor (int i = 0; i < step - 1; i++){unsigned long long pow_result = pow(32, i);unsigned long long x = sqln;unsigned long long div_result = x / pow_result;unsigned int seq = div_result % 32;// 插入到 sqlid 字符串的前面memmove(sqlid + 1, sqlid, strlen(sqlid) + 1);sqlid[0] = charbase32[seq];}printf("SQL is:\n    %s\nSQL_ID is\n    %s\n", stmt, sqlid);return 0;
}
[oracle@local64 mydata]$ gcc -o test2 mytest2.c -lssl -lcrypto -lm -g -O0
[oracle@local64 mydata]$ 
[oracle@local64 mydata]$ ./test2 
a is 2417365776, b is 1480103112; msb is 3621835783, lsb is 3323293077
sqln is 15555666242790845845, stop is 13.750815
SQL is:select 'SONG BAOBAO' from dual
SQL_ID isdgs6n0z31avcp
[oracle@local64 mydata]$