[20220322]探究oracle sequence 2.txt

[20220322]探究oracle sequence 2.txt

--//對oracle sequence做一個簡單探究.sequence一般使用更新表的主鍵.

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

--//drop SEQUENCE SCOTT.SEQ1A2B3C;
CREATE SEQUENCE SCOTT.SEQ1A2B3C
  START WITH 88888888
  INCREMENT BY 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 77777777
  NOCYCLE
  CACHE 2222
  ORDER;

--//注:這樣主要目的看看對應的堆裡面是否可以看到相關資訊.

2.測試:

SCOTT@book> select seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88888888

--//注:前面的測試已經知道sott.seq1a2b3c HASH_VALUE=4074355949,這次直接使用.

SYS@book> @curheapp '' 4074355949
  KGLNAHSH KGLHDPAR         SQL_ID            CHILD# KGLHDADR         KGLOBHD0            SIZE0    SIZE1    SIZE2    SIZE3 KGLOBHD4            SIZE4    SIZE5 KGLOBHD6            SIZE6    SIZE7     STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
4074355949 000000007BDD51D0                       10 000000007BDD51D0 000000007BDD4CE8     4728        0        0        0 00                      0        0 00                      0        0          1

HEAP  ADDR                   INDX    INST_ID   KSMCHIDX   KSMCHDUR KSMCHCOM         KSMCHPTR           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR
----- ---------------- ---------- ---------- ---------- ---------- ---------------- ---------------- ---------- -------- ---------- ----------------
HEAP0 00007F2A8F2E0A48      16015          1          1          1 KGLH0^f2d9bced   000000007BDD3C80       4096 recr           4095 000000007BDD4CE8

no rows selected
no rows selected

$ cat aa.txt
select seq1a2b3c.nextval from dual ;
host sleep &&1

SYS@book> @ fchaz 000000007BDD4CE8
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_END
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- -----------------
SGA 000000007BDD4C80          1          1 KGLDA                   240 freeabl           0 00               000000007BDD4D6F

$seq 150 | xargs -IQ -P 150 bash -c "sqlplus scott/book @aa.txt 100"


SYS@book> @ fchaz 000000007BDD4CE8
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_END
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- -----------------
SGA 000000007BDD4C80          1          1 KGLDA                   240 freeabl           0 00               000000007BDD4D6F

--//堆描述符大小沒有變化。

3.前面的測試提到回話seq的當前值應該在使用者的回話區UGA,我當時發現在一個很大的chunk裡面,昨天的測試時間問題,沒有把我的測試結
果貼出來,今天重複測試看看.

SCOTT@book> select seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88889039

--//88889039-88888888 = 151

SCOTT@book> select dump(88889039,16) from dual ;
DUMP(88889039,16)
---------------------------
Typ=2 Len=5: c4,59,59,5b,28

SCOTT@book> alter session set events 'immediate trace name heapdump level 0x1004';
Session altered.

--//檢查轉儲發現
7F9BF35DB700 F35DB6F8 00007F9B F35C6F98 00007F9B  [..]......o\.....]
7F9BF35DB710 F35C6F98 00007F9B 45530009 32413151  [.o\.......SEQ1A2]
7F9BF35DB720 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB730 00000000 00000000 45530009 32413151  [..........SEQ1A2]
7F9BF35DB740 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB750 00000000 00000000 43530005 0054544F  [..........SCOTT.]
7F9BF35DB760 00000000 00000000 00000000 00000000  [................]
7F9BF35DB770 00000000 00000000 7BDD51D0 00000000  [.........Q.{....]
7F9BF35DB780 7BEDBE60 00000000 00051116 5959C409  [`..{..........YY]
                                        ~~~~~~~~
7F9BF35DB790 0000285B 00000000 00000000 00000000  [[(..............]
             ~~~~~~~~
7F9BF35DB7A0 00000005 00000000 00000000 00000000  [................]

--//開啟另外的會話使用SYS使用者查詢：
SYS@book> @ fchaz 7F9BF35DB790
no rows selected
--//確實查詢不到對應的chunk。

SCOTT@book> select seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88889040

SCOTT@book> select dump(88889040,16) from dual ;
DUMP(88889040,16)
---------------------------
Typ=2 Len=5: c4,59,59,5b,29
 

SCOTT@book> @ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_43803_0001.trc

SCOTT@book> alter session set events 'immediate trace name heapdump level 0x1004';
Session altered.

7F9BF35DB710 F35C6F98 00007F9B 45530009 32413151  [.o\.......SEQ1A2]
7F9BF35DB720 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB730 00000000 00000000 45530009 32413151  [..........SEQ1A2]
7F9BF35DB740 00433342 00000000 00000000 00000000  [B3C.............]
7F9BF35DB750 00000000 00000000 43530005 0054544F  [..........SCOTT.]
7F9BF35DB760 00000000 00000000 00000000 00000000  [................]
7F9BF35DB770 00000000 00000000 7BDD51D0 00000000  [.........Q.{....]
7F9BF35DB780 7BEDBE60 00000000 00051116 5959C409  [`..{..........YY]
                                        ~~~~~~~~~
7F9BF35DB790 0000295B 00000000 00000000 00000000  [[)..............]
             ~~~~~~~~
7F9BF35DB7A0 00000005 00000000 00000000 00000000  [................]

--//基本可以確定這個位置就是記錄了當前會話的seq的currval值。

4.換成sys使用者重複測試看看:

--//drop SEQUENCE SCOTT.SEQ1A2B3C;
CREATE SEQUENCE SCOTT.SEQ1A2B3C
  START WITH 88888888
  INCREMENT BY 1
  MAXVALUE 9999999999999999999999999999
  MINVALUE 77777777
  NOCYCLE
  CACHE 2222
  ORDER;

--//重啟資料庫略.

SYS@book> select scott.seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88888888

SYS@book> select dump(88888888,16) from dual ;
DUMP(88888888,16)
---------------------------
Typ=2 Len=5: c4,59,59,59,59

SYS@book> alter session set events 'immediate trace name heapdump level 0x1004';
Session altered.

--//檢查轉儲可以發現：
7F226EF71900 45530009 32413151 00433342 00000000  [..SEQ1A2B3C.....]
7F226EF71910 00000000 00000000 00000000 00000000  [................]
7F226EF71920 45530009 32413151 00433342 00000000  [..SEQ1A2B3C.....]
7F226EF71930 00000000 00000000 00000000 00000000  [................]
7F226EF71940 43530005 0054544F 00000000 00000000  [..SCOTT.........]
7F226EF71950 00000000 00000000 00000000 00000000  [................]
7F226EF71960 7BEE9400 00000000 7BF2E900 00000000  [...{.......{....]
7F226EF71970 00051118 5959C409 00005959 00000000  [......YYYY......]
                      ~~~~~~~~~~~~~~~~~~
SYS@book> @ fchaz 7F226EF71970
LOC KSMCHPTR           KSMCHIDX   KSMCHDUR KSMCHCOM           KSMCHSIZ KSMCHCLS   KSMCHTYP KSMCHPAR         KSMCHPTR_END
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ---------------- -----------------
UGA 00007F226EF70040                       permanent memor        7344 perm              0 00               00007F226EF71CEF
--//再開啟一個sys使用者查詢看看。

SYS@book> @ fchaz 7F226EF71970
no rows selected
--//噢這樣就很容易理解了，其它使用者即使是sys也看不到其它sys使用者的UGA chunk。

SYS@book> oradebug peek 0x7F226EF71974 8
[7F226EF71974, 7F226EF7197C) = 5959C409 00005959

SYS@book> oradebug peek 0x7F226EF71978 4
[7F226EF71978, 7F226EF7197C) = 00005959

--//做一個crack看看，修改該值。

SYS@book> oradebug poke 0x7F226EF71978 4 0x00006459
BEFORE: [7F226EF71978, 7F226EF7197C) = 00005959
AFTER:  [7F226EF71978, 7F226EF7197C) = 00006459

--//0x64相當於十進位制99.  0x64 = 100,100-1 = 99.

SYS@book> select scott.seq1a2b3c.currval from dual ;
   CURRVAL
----------
  88888899
--//你可以發現查詢當前值不再是前面的88888888，而是88888899,也驗證了我修改正確，並且該位置記錄的就是當前值。
--//開啟新的會話執行：
SYS@book> select scott.seq1a2b3c.nextval from dual ;
   NEXTVAL
----------
  88888889

--//下一個值還是88888889。