Oracle如何根据SQL_TEXT生成SQL_ID

0. 概述

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

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

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

(b) 注意little-endian的问题

(c) Base32转码的可见字符为0123456789abcdfghjkmnpqrstuvwxyz

(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);

3.3 Python

参考:Oracle sql_id and hash value

4. 最后

一个略有趣的事实,SQL_ID的第一位经常会是数字。这是因为是64位(bits),按照5位一个字符划分,最后一个字符总是只有4位,范围总是0到15,对应字符为0123456789abcdfg,也就是说超过50%的SQL_ID都是以数字开头的。

好了,八卦结束。

时间: 2025-01-06 14:20:22

Oracle如何根据SQL_TEXT生成SQL_ID的相关文章

Oracle动态交叉表生成_oracle

正在看的ORACLE教程是:Oracle动态交叉表生成.Oracle是应用最广的大型数据库,而在范式下进行Oracle数据库设计则可以大大减少数据冗余,使数据库维护更方便,可惜范式下的数据表一般不能直接输出.今天我们就来探讨一下范式下的数据表的动态交叉表生成的方法. 范式下的Oracle数据库设计 数据关系的复杂性导致了表中数据冗余的存在,数据冗余增加了维护数据库的负担,也占用了大量的磁盘空间,直接造成性能下降.为了消除这些负面影响,就应该对数据库表格进行规范化,使其遵守一定的规则的,尤其是数据

ORACLE实现自定义序列号生成的方法_oracle

实际工作中,难免会遇到序列号生成问题,下面就是一个简单的序列号生成函数 (1)创建自定义序列号配置表如下: --自定义序列 create table S_AUTOCODE ( pk1 VARCHAR2(32) primary key, atype VARCHAR2(20) not null, owner VARCHAR2(10) not null, initcycle CHAR(1) not null, cur_sernum VARCHAR2(50) not null, zero_flg VAR

oracle:如何用sql生成日历

BI分析中,经常需要将事实表与时间维度表关联起来,按年/月/日来逐层展示,常用的做法是创建一张日历表,结构类似如下: create table T_BAS_CALENDAR ( d_year NUMBER(4) not null, d_month NUMBER(2) not null, d_day NUMBER(2) not null ); comment on table T_BAS_CALENDAR is '日历表'; comment on column T_BAS_CALENDAR.d_y

oracle使用sql脚本生成csv文件案例学习_oracle

脚本内容如下: 复制代码 代码如下: set linesize 3000; set heading off; set feedback off; set term off; set pagesize 0; set trimspool on; spool a.csv; select c1_name||','||c2_name from dual; select c1||','||c2 from tbtest spool off;

《Oracle高性能SQL引擎剖析:SQL优化与调优机制详解》一1.1 生成执行计划

1.1 生成执行计划 在Oracle中,任何一条语句在解析过程中都会生成一个唯一的数值标识,即SQL_ID.而同一条语句,在解析过程中,可能会因为执行环境的改变(例如某些优化参数被改变)而生成多个版本的游标,不同的游标会有不同的执行计划.每个游标都会按顺序赋予一个序列号,即CHILD_NUMBER,一条语句生成的第一个游标的CHILD_NUMBER为0:相应的,Oracle会为每个执行计划生成一个哈希值以作区分.而多个不同版本的游标,其执行计划可能会相同,也可能不同. 因此,我们可以知道,一条合

Oracle学习记录之使用自定义函数和触发器实现主键动态生成_oracle

很早就想自己写写Oracle的函数和触发器,最近一个来自课本的小案例给了我这个机会.现在把我做的东西记录下来,作为一个备忘或者入门的朋友们的参考. 案例介绍: 招投标管理系统(数据库设计). 数据表有以下两张: 招标书(招标书编号.项目名称.招标书内容.截止日期.状态). 投标书(投标书编号.招标书编号.投标企业.投标书内容.投标日期.报价.状态). "招标书编号"为字符型,编号规则为 ZBYYYYMMDDNNN, ZB是招标的汉语拼音首字母,YYYYMMDD是当前日期,NNN是三位流

Oracle 性能相关的几个 视图 和 参数

原文转自:http://blog.csdn.net/tianlesoftware/article/details/5867276 一.性能视图          性能视图是Oracle中一些记录数据库性能方面的视图,通过查看这些视图,获得数据库当前或历史上某个时间的性能数据. 它比SQL_TRACE,AWR报告获取数据更及时,便捷. 1.1 V$SQL          V$SQL 视图是一个DBA 使用频率非常高的动态视图,它通常和V$SESSION 一起使用来获得当前会话的一些SQL执行情况

[20170724]关于sql_id那些事.txt

[20170724]关于sql_id那些事.txt --//昨天别人问的问题,我以前也写过许多blog,做一些总结: http://blog.itpub.net/267265/viewspace-1357292/ http://blog.itpub.net/267265/viewspace-1365382/ http://blog.itpub.net/267265/viewspace-1701985/ 1.环境: SCOTT@book> @ &r/ver1 PORT_STRING      

Oracle中的游标、硬解析、软解析、软软解析、解析失败

Oracle中的游标.硬解析.软解析.软软解析.解析失败 一. 游标的分类及共享游标 游标(Cursor)是Oracle数据库中SQL解析和执行的载体,它可以分为共享游标(Shared Cursor)和会话游标(Session Cursor).共享游标可以细分为父游标(Parent Cursor)和子游标(Child Cursor),可以通过视图V$SQLAREA和V$SQL来查看当前缓存在库缓存(Library Cache)中的父游标和子游标,其中V$SQLAREA用于查看父游标,V$SQL用