原文地址：https://www.soughttech.com/front/article/7159/viewArticle 今天我偶然看到了參數slave_exec_mode。從手冊中的描述可以看出，該參數與MySQL複製有關。它是一個可以動態修改的變數。預設為STRICT mode(嚴格模式)， ...
[20231121]oracle SYS_GUID的組成.txt
--//看了鏈接:http://ksun-oracle.blogspot.com/2023/08/oracle-sysguid-composition.html
--//函數SYS_GUID,不知道作者如何猜測函數SYS_GUID的組成的,自己按照作者的測試,自己重覆看看.
SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes.
On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process
or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.
The Oracle Database SYS_GUID function does not return a standard UUID since the generated GUID is not a random number.
(see: IETF RFC 4122 version 4 UUID, and Oracle dbms_crypto.randombytes and Enhancement Suggestion).
SYS_GUID生成並返回一個由16個位元組組成的全局唯一標識符(RAW值)。在大多數平臺上，生成的標識符由主機標識符、調用該函數的進程
或線程的進程或線程標識符以及該進程或線程的非重覆值(位元組序列)組成。
Oracle資料庫SYS_GUID函數不返回一個標準的UUID，因為生成的GUID不是一個隨機數。(請參見： IETF RFC 4122版本4的UUID，以及
Oracle dbms_crypto.randombytes和增強建議)。
--//註:實際上我第一次在windows測試,發現沒有任何規律可言，作者的猜測或者分析根本不適合windows系統!!
--//順便提一下我個人反對使用SYS_GUID做為主鍵的,小量使用尚可接受,大量使用我在以前文章提到過,大量使用消耗CPU資源,我看過2套
--//使用它作為主鍵的系統,一般不會使用raw類型,而是使用varchar2(32),因為使用raw類型要在寫sql語句時使用hextoraw函數轉換,這
--//樣占用空間多占1倍,索引鍵值也會變得很大,許多開發會講這樣不就消耗一點磁碟空間嗎?現在的磁碟太便宜,我遇到一個表有1X個字
--//段都是這個類型,一個查詢看到的滿屏都是類似ascii碼的東西,不知道是怎麼感覺,這樣的系統索引,表增加異常地快,意味著日誌量也
--//很大.
--//在linux下測試看看.
1.環境:
SCOTT@book> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.分析:
--//建立腳本,便於重覆執行:
$ cat guid.sql
column seq_inc_by_1 format a12
column PROC_SPID fromat a20
with sq as (select sys_guid() gid, s.logon_time, p.spid, s.sid, s.serial#, p.pid from v$session s, v$process p
where s.paddr=p.addr and (s.sid = (select sid from v$mystat where rownum=1)))
select substr(gid, 1, 12) seq_inc_by_1
,substr(gid, 13, 4)||'(='||to_number(substr(gid, 13, 4), 'XXXXXX')||')' proc_spid
,substr(gid, 17, 4) unknown_1
,substr(gid, 21, 8) unix_host_id
,substr(gid, 29, 4) unknown_2
,sq.*
from sq;
SCOTT@book> @ guid
SEQ_INC_BY_1 PROC_SPID UNKNOWN_ UNIX_HOST_ID UNKNOWN_ GID LOGON_TIME SPID SID SERIAL# PID
------------ -------------------- -------- ---------------- -------- -------------------------------- ------------------- ------ ---------- ---------- -------
0B6B3CC961E4 7E19(=32281) E063 4E64A8C0 88E6 0B6B3CC961EA7E19E0634E64A8C088E6 2023-12-01 11:19:32 32281 18 805 25
SCOTT@book> @ guid
SEQ_INC_BY_1 PROC_SPID UNKNOWN_ UNIX_HOST_ID UNKNOWN_ GID LOGON_TIME SPID SID SERIAL# PID
------------ -------------------- -------- ---------------- -------- -------------------------------- ------------------- ------ ---------- ---------- -------
0B6B3CC961EB 7E19(=32281) E063 4E64A8C0 88E6 0B6B3CC961F17E19E0634E64A8C088E6 2023-12-01 11:19:32 32281 18 805 25
--//我的測試即使密集的執行SEQ_INC_BY_1也不是按1遞增。
$ hostid
a8c04e64
$ hostid | od -t x4| xxd -r -p
0c8a46e4
--//od -t x4 轉換的結果還是不對，但是反轉過來讀就可以跟前面的UNIX_HOST_ID對上。
$ hostid | od -t x4 | xxd -r -p | strings| rev
4e64a8c0
$ hostid | od -t x4 | cut -c8-| xxd -r -p | rev
4e64a8c0
--//這樣倒是能對上。
--//按照輸出可以大致推測,來自:http://ksun-oracle.blogspot.com/2023/08/oracle-sysguid-composition.html
SEQ_INC_BY_1 ( 1-12): session own Sequence Number, increase 1 per sys_guid call, initiated by a number related to
v$session logon_time
PROC_SPID (13-16): v$process.spid
UNKNOWN_1 (17-20): (E063 or E064)
UNIX_HOST_ID (21-28): hostid command output (Linux little endian, 4 bytes reverse order)
UNKNOWN_2 (29-32) :
--//退出會話重覆執行：
SCOTT@book> @ guid
SEQ_INC_BY_1 PROC_SPID UNKNOWN_ UNIX_HOST_ID UNKNOWN_ GID LOGON_TIME SPID SID SERIAL# PID
------------ -------------------- -------- ---------------- -------- -------------------------------- ------------------- ------ ---------- ---------- -------
0B6BA3598106 8037(=32823) E063 4E64A8C0 B041 0B6BA359810C8037E0634E64A8C0B041 2023-12-01 11:48:14 32823 18 807 25
SCOTT@book> @ guid
SEQ_INC_BY_1 PROC_SPID UNKNOWN_ UNIX_HOST_ID UNKNOWN_ GID LOGON_TIME SPID SID SERIAL# PID
------------ -------------------- -------- ---------------- -------- -------------------------------- ------------------- ------ ---------- ---------- -------
0B6BA359810D 8037(=32823) E063 4E64A8C0 B041 0B6BA35981138037E0634E64A8C0B041 2023-12-01 11:48:14 32823 18 807 25
SCOTT@book> @ guid
SEQ_INC_BY_1 PROC_SPID UNKNOWN_ UNIX_HOST_ID UNKNOWN_ GID LOGON_TIME SPID SID SERIAL# PID
------------ -------------------- -------- ---------------- -------- -------------------------------- ------------------- ------ ---------- ---------- -------
0B6BA3598114 8037(=32823) E063 4E64A8C0 B041 0B6BA359811A8037E0634E64A8C0B041 2023-12-01 11:48:14 32823 18 807 25
--//似乎SEQ_INC_BY_1 按+7遞增。
--//6 =6 D = 13 14 = 20
--//E4 = 228 EB = 235
--//0B6BA359810D = 12556929958157
--//0B6BA3598114 = 12556929958164
with sq1 as (select /*+ materialize */ level nr, substr(sys_guid(), 1, 12) guid_12 from dual connect by level <= 1e6)
,sq2 as (select /*+ materialize */ level + 1*1e6 nr, substr(sys_guid(), 1, 12) guid_12 from dual connect by level <= 1e6)
,sq3 as (select /*+ materialize */ level + 2*1e6 nr, substr(sys_guid(), 1, 12) guid_12 from dual connect by level <= 1e6)
select min(nr) min_nr, max(nr) max_nr
,min(guid_12) min_guid_12, max(guid_12) max_guid_12
,count(*) nr_count
,to_number(max(guid_12), 'xxxxxxxxxxxx') - to_number(min(guid_12), 'xxxxxxxxxxxx') + 1 nr_count
from
(select * from sq1
union
select * from sq2
union
select * from sq3);
MIN_NR MAX_NR MIN_GUID_12 MAX_GUID_12 NR_COUNT NR_COUNT
---------- ---------- ------------------------ ------------------------ ---------- ----------
1 3000000 0BE7D7EAE1FF 0BE7D818A8BE 3000000 3000000
--//這樣執行SEQ_INC_BY_1確實按照+1增加的。
--//按照作者介紹,SEQ_INC_BY_1
--//來自:http://ksun-oracle.blogspot.com/2023/08/oracle-sysguid-composition.html
So SEQ_INC_BY_1 is a Sequence Number, increasing 1 per sys_guid call, initiated by a number related to epoch time of
v$session logon_time (probably cached in each v$process.spid).
SEQ_INC_BY_1 is 12 hex digits, with maximum decimal value:
SCOTT@book> set numw 15
SCOTT@book> select to_number(lpad('F', 12, 'F'), lpad('X', 12, 'X')) n20 from dual;
N20
---------------
281474976710655
The last 6 digits represents a pure calling sequence number, the rest prefix digits are UNIX epoch seconds. So the
maximum seconds is:
281474976
Since 281474976 seconds is about 3258 days (281474976/86400) or about 9 years, sys_guid is wrapped on overflow about
each 9 years. The first 10 reset datetime can be projected as follows:
--//281474976/86400/365 = 8.92551293759512937595
select level NR#
,to_date('1970-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') + level*281474976/86400 datetime
from dual connect by level <= 10;
NR# DATETIME
--------------- -------------------
1 1978-12-02 19:29:36
2 1987-11-03 14:59:12
3 1996-10-04 10:28:48
4 2005-09-05 05:58:24
5 2014-08-07 01:28:00
6 2023-07-08 20:57:36
7 2032-06-08 16:27:12
8 2041-05-10 11:56:48
9 2050-04-11 07:26:24
10 2059-03-13 02:56:00
10 rows selected.
16 bytes sys_guid is a 32 long raw hex value. In each interval, 10/16 of them are starting with number 0-9, 6/16 with
A-F.
Given a sys_guid, we can estimate its datetime by:
SCOTT@book> @ guid
SEQ_INC_BY_1 PROC_SPID UNKNOWN_ UNIX_HOST_ID UNKNOWN_ GID LOGON_TIME SPID SID SERIAL# PID
------------ -------------------- -------- ---------------- -------- -------------------------------- ------------------- ------ ---------- ---------- -------
0B6BA3598114 8037(=32823) E063 4E64A8C0 B041 0B6BA359811A8037E0634E64A8C0B041 2023-12-01 11:48:14 32823 18 807 25
--//代入0B6BA359811A8037E0634E64A8C0B041。
with sq as (select to_number(substr('0B6BA359811A8037E0634E64A8C0B041', 1, 12), lpad('X', 12, 'X'))/1e6 epoch_reminder from dual)
select epoch_reminder
,to_date('2023*JUL*08 20:57:36', 'YYYY*MON*DD hh24:mi:ss') + epoch_reminder/86400 estimated_datetime
from sq;
EPOCH_REMINDER ESTIMATED_DATETIME
--------------- -------------------
12556929.95817 2023-12-01 04:59:46
$ xdate '2023-12-01 11:48:14' 2
1701402494.000000000
$ xdate '2023-12-01 04:59:46' 2
1701377986.000000000
--//相差 1701402494-1701377986 = 24508
SCOTT@book> @ guid
SEQ_INC_BY_1 PROC_SPID UNKNOWN_ UNIX_HOST_ID UNKNOWN_ GID LOGON_TIME SPID SID SERIAL# PID
------------ -------------------- -------- ---------------- -------- -------------------------------- ------------------- ------ --------------- --------------- -------
0BE7D818A905 2FFC(=12284) E063 4E64A8C0 FA2B 0BE7D818A90B2FFCE0634E64A8C0FA2B 2023-12-07 15:59:10 12284 36 7121 26
with sq as (select to_number(substr('0BE7D818A90B2FFCE0634E64A8C0FA2B', 1, 12), lpad('X', 12, 'X'))/1e6 epoch_reminder from dual)
select epoch_reminder
,to_date('2023*JUL*08 20:57:36', 'YYYY*MON*DD hh24:mi:ss') + epoch_reminder/86400 estimated_datetime
from sq;
EPOCH_REMINDER ESTIMATED_DATETIME
--------------- -------------------
13090390.845707 2023-12-07 09:10:47
$ xdate '2023-12-07 15:59:10' 2
1701935950.000000000
$ xdate '2023-12-07 09:10:47' 2
1701911447.000000000
--//1701935950-1701911447 = 24503
--//放棄!!不再探究..
