[20220321]探究oracle sequence.txt
[20220321]探究oracle sequence.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
CREATE SEQUENCE SCOTT.SEQ1A2B3C
START WITH 88888888
INCREMENT BY 111111
MAXVALUE 9999999999999999999999999999
MINVALUE 77777777
NOCYCLE
CACHE 22222222
ORDER;
--//注:這樣主要目的看看對應的堆裡面是否可以看到相關資訊.
2.測試:
SCOTT@book> select seq1a2b3c.nextval from dual ;
NEXTVAL
----------
88888888
3.分析:
SCOTT@book> SELECT * FROM V$DB_OBJECT_CACHE WHERE NAME = 'SEQ1A2B3C'
2 @pr
==============================
OWNER : SCOTT
NAME : SEQ1A2B3C
DB_LINK :
NAMESPACE : TABLE/PROCEDURE
TYPE : SEQUENCE
SHARABLE_MEM : 4728
LOADS : 1
EXECUTIONS : 0
LOCKS : 1
PINS : 0
KEPT : NO
CHILD_LATCH : 113901
INVALIDATIONS : 0
HASH_VALUE : 4074355949
LOCK_MODE : NULL
PIN_MODE : NONE
STATUS : VALID
TIMESTAMP : 2022-03-21/09:31:33
PREVIOUS_TIMESTAMP :
LOCKED_TOTAL : 2
PINNED_TOTAL : 3
PROPERTY :
FULL_HASH_VALUE : 1d266b5d5dcc1827e00d941ef2d9bced
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PL/SQL procedure successfully completed.
--//CHILD_LATCH = HASH_VALUE % 2^17 , 4074355949 %2^17 = 113901.
--//手工計算FULL_HASH_VALUE看看,是否可以對上.
--//sequence namespace與table namespace一樣等於1,這樣seq的FULL_HASH_VALUE的計算字串使用seq_name.owner\01\0\0\0.
$ echo -e -n 'SEQ1A2B3C.SCOTT\01\0\0\0' | md5sum | sed 's/ -//' | xxd -r -p | od -t x4 | sed -n -e 's/^0\+ //' -e 's/ //gp'
1d266b5d5dcc1827e00d941ef2d9bced
--//對比前面下劃線的內容，正好一致,intel系列的cpu要做一個大小頭對調,其他的cpu我不是很清楚.
SYS@book> @sharepool/shp4 0 4074355949
TEXT KGLHDADR KGLHDPAR C40 KGLHDLMD KGLHDPMD KGLHDIVC KGLOBHD0 KGLOBHD6 KGLOBHS0 KGLOBHS6 KGLOBT16 N0_6_16 N20 KGLNAHSH KGLOBT03 KGLOBT09
--------------------- ---------------- ---------------- ---------------------------------------- ---------- ---------- ---------- ---------------- ---------------- ---------- ---------- ---------- --------- ---------- ---------- ------------- ----------
parent handle address 000000007BCA5918 000000007BCA5918 SEQ1A2B3C 1 0 0 000000007BCA5430 00 4728 0 0 4728 4728 4074355949 10
SYS@book> @curheapp '' 4074355949
KGLNAHSH KGLHDPAR SQL_ID CHILD# KGLHDADR KGLOBHD0 SIZE0 SIZE1 SIZE2 SIZE3 KGLOBHD4 SIZE4 SIZE5 KGLOBHD6 SIZE6 SIZE7 STATUS
---------- ---------------- ------------- ---------- ---------------- ---------------- -------- -------- -------- -------- ---------------- -------- -------- ---------------- -------- -------- ----------
4074355949 000000007BCA5918 10 000000007BCA5918 000000007BCA5430 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 00007F9CFC0DC558 16529 1 1 1 KGLH0^f2d9bced 000000007BCA43C8 4096 recr 4095 000000007BCA5430
--//堆0在0x000000007BCA43C8開始，大小4096。
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug dump heapdump_addr 1 0x000000007BCA5430
Statement processed.
--//檢視轉儲:
*** 2022-03-21 09:42:03.142
Processing Oradebug command 'dump heapdump_addr 1 0x000000007BCA5430'
******************************************************
HEAP DUMP heap name="KGLH0^f2d9bced" desc=0x7bca5430
extent sz=0xfe8 alt=32767 het=56 rec=9 flg=2 opc=0
parent=0x60001190 owner=0x7bca53e0 nex=(nil) xsz=0xfd0 heap=(nil)
fl2=0x26, nex=(nil), dsxvers=1, dsxflg=0x0
dsx first ext=0x7bca43f8
EXTENT 0 addr=0x7bca43f8
Chunk 07bca4408 sz= 80 perm "perm " alo=80
Chunk 07bca4458 sz= 3952 perm "perm " alo=448
Total heap size = 4032
FREE LISTS:
Bucket 0 size=0
Chunk 07bca4428 sz= 0 kghdsx
Total free space = 0
UNPINNED RECREATABLE CHUNKS (lru first):
++++++++++++++++++++++++++++++++++++++
PERMANENT CHUNKS:
Chunk 07bca4458 sz= 3952 perm "perm " alo=448
Chunk 07bca4408 sz= 80 perm "perm " alo=80
Permanent space = 4032
******************************************************
SYS@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_36541_0002.trc
SYS@book> oradebug peek 0x000000007BCA43C8 4096 1
[07BCA43C8, 07BCA53C8) = 00001001 80B38F00 7BCA33C8 00000000 7BCA54C8 00000000 7BDB8B70 00000000 00000000 00000000 00000001 00060FFF 7BCA5430 00000000 ...
*** 2022-03-21 09:43:22.517
Processing Oradebug command 'peek 0x000000007BCA43C8 4096 1'
[07BCA43C8, 07BCA53C8) = 00001001 80B38F00 7BCA33C8 00000000 7BCA54C8 ...
Dump of memory from 0x07BCA43DC to 0x07BCA53C8
07BCA43D0 00000000 [....]
07BCA43E0 7BDB8B70 00000000 00000000 00000000 [p..{............]
07BCA43F0 00000001 00060FFF 7BCA5430 00000000 [........0T.{....]
07BCA4400 00000000 00000000 00000051 40B38F00 [........Q......@]
07BCA4410 00000000 00000000 00000000 00000000 [................]
07BCA4420 00000050 00000000 00000001 C0B38F00 [P...............]
07BCA4430 00000000 00000000 7BCA54A8 00000000 [.........T.{....]
07BCA4440 7BCA54A8 00000000 00000001 00000000 [.T.{............]
07BCA4450 7BCA43F8 00000000 00000F71 50B38F00 [.C.{....q......P]
07BCA4460 7BCA4408 00000000 7BCA4408 00000000 [.D.{.....D.{....]
07BCA4470 000001C0 00000000 7BCA5918 00000000 [.........Y.{....]
07BCA4480 00000000 00000000 00040000 00000000 [................]
07BCA4490 7BCA53E0 00000000 00000000 00000000 [.S.{............]
07BCA44A0 00000000 00000000 00000000 00000000 [................]
Repeat 6 times
07BCA4510 00000000 00000000 00000006 00000000 [................]
07BCA4520 7BCA4560 00000000 00000000 00000000 [`E.{............]
07BCA4530 00000000 00000000 00051100 FFFFFFFF [................]
07BCA4540 00000000 00000000 00000000 00000000 [................]
07BCA4550 00000053 00000053 00000053 00000000 [S...S...S.......]
07BCA4560 00000005 00051100 0004000A 000F0005 [................]
07BCA4570 00080005 0C0C0CC3 00000000 00000000 [................]
--------
07BCA4580 00000000 4EC40000 004E4E4E 00000000 [.......NNNN.....]
07BCA4590 00000000 00000000 646464CE 64646464 [.........ddddddd]
07BCA45A0 64646464 00646464 17C40000 00171717 [ddddddd.........]
07BCA45B0 00000000 00000000 00000000 5D2F03C7 [............../]]
~~~~~~~~
07BCA45C0 1F4C1417 00000000 00000000 2D2D0000 [..L...........--]
~~~~~~~~
07BCA45D0 2D2D2D2D 2D2D2D2D 2D2D2D2D 2D2D2D2D [----------------]
07BCA45E0 2D2D2D2D 2D2D2D2D 2D2D2D2D 00002D2D [--------------..]
07BCA45F0 00000000 59C40005 00646464 00000000 [.......Yddd.....]
+++++++++++++++++
07BCA4600 00000000 00000000 00000000 00000000 [................]
Repeat 217 times
07BCA53A0 00000000 00000000 00000021 D0B38F00 [........!.......]
07BCA53B0 7BCA4458 00000000 0A994C30 00000000 [XD.{....0L......]
07BCA53C0 00000000 00000000 [........]
SCOTT@book> select dump(88888888,16), dump(111111,16), dump(9999999999999999999999999999,16), dump(77777777,16), dump(22222222,16) from dual ;
DUMP(88888888,16) DUMP(111111,16) DUMP(9999999999999999999999999999,16) DUMP(77777777,16) DUMP(22222222,16)
--------------------------- --------------------- ---------------------------------------------------------- --------------------------- ---------------------------
Typ=2 Len=5: c4,59,59,59,59 Typ=2 Len=4: c3,c,c,c Typ=2 Len=15: ce,64,64,64,64,64,64,64,64,64,64,64,64,64,64 Typ=2 Len=5: c4,4e,4e,4e,4e Typ=2 Len=5: c4,17,17,17,17
CREATE SEQUENCE SCOTT.SEQ1A2B3C
START WITH 88888888
INCREMENT BY 111111
MAXVALUE 9999999999999999999999999999
MINVALUE 77777777
NOCYCLE
CACHE 22222222
ORDER;
--//其它值都可以在堆0裡面找到，而START WITH值沒有查詢到。
SYS@book> @ ddl scott.seq1a2b3c
C300
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE SEQUENCE "SCOTT"."SEQ1A2B3C" MINVALUE 77777777 MAXVALUE 9999999999999999999999999999 INCREMENT BY 111111 START WITH 2469222197530 CACHE 22222222 ORDER NOCYCLE ;
--//噢，實際上nextval後已經變成了2469222197530，為什麼呢？
SCOTT@book> select seq1a2b3c.nextval from dual ;
NEXTVAL
----------
88999999
--//(2469222197530-88888888)/22222222 = 111111,實際上你可以看出cache是"快取"seq的數量。
SCOTT@book> select dump(2469222197530,16) from dual ;
DUMP(2469222197530,16)
-----------------------------------
Typ=2 Len=8: c7,3,2f,5d,17,14,4c,1f
--//注意看下劃線就是對應值，也就是seq載入後在共享池裡面記錄seq的一些屬性。
--//那麼應該有一個地方記錄seq的下一個值88999999,注我沒有再次轉儲，如果再次轉儲記錄的值應該對應88999999+111111 = 89111110.
SCOTT@book> select dump(88999999,16) from dual;
DUMP(88999999,16)
---------------------------
Typ=2 Len=5: c4,59,64,64,64
--//注意看++++++++是對應值，注意大小頭對調。現在記錄的是
SYS@book> oradebug peek 0x07BCA45F4 8
[07BCA45F4, 07BCA45FC) = 5AC40005 000B0C0C
SCOTT@book> select dump(89111110,16) from dual;
DUMP(89111110,16)
------------------------
Typ=2 Len=5: c4,5a,c,c,b
--//仔細比較完全可以對上。如果我修改如下，相當於在原來基礎上增加了16.
SYS@book> oradebug poke 0x07BCA45F8 4 0x001B0C0C
BEFORE: [07BCA45F8, 07BCA45FC) = 000B0C0C
AFTER: [07BCA45F8, 07BCA45FC) = 001B0C0C
--//這樣下一次記錄應該如下：
SCOTT@book> select dump(89111110+16+111111,16) from dual;
DUMP(89111110+16+111111,16)
---------------------------
Typ=2 Len=5: c4,5a,17,17,26
SCOTT@book> select seq1a2b3c.nextval from dual ;
NEXTVAL
----------
89111126
--//89111110+16 = 89111126
SYS@book> oradebug peek 0x07BCA45F4 8
[07BCA45F4, 07BCA45FC) = 5AC40005 00261717
--//可以和前面dump(89111110+16+111111,16)對上.
--//這樣存在一個疑問會話的當前值在那裡，一定不記錄在該堆裡面，而是記錄在使用者的會話區，繼續測試:
SCOTT@book> select seq1a2b3c.currval from dual ;
CURRVAL
----------
89111126
SCOTT@book> select seq1a2b3c.currval from dual ;
CURRVAL
----------
89111126
SCOTT@book> select dump(89111110+16,16) from dual;
DUMP(89111110+16,16)
-------------------------
Typ=2 Len=5: c4,5a,c,c,1b
--//在scoot使用者下執行
SCOTT@book> alter session set events 'immediate trace name heapdump level 0x1004';
Session altered.
Chunk 7f88bd5d00f8 sz= 47496 perm "perm " alo=47496
Dump of memory from 0x00007F88BD5D00F8 to 0x00007F88BD5DBA80
7F88BD5D00F0 0000B989 40B38F00 [.......@]
7F88BD5D0100 BD5D00A8 00007F88 BD5D00A8 00007F88 [..].......].....]
...
7F88BD5DB710 BD5C6F98 00007F88 45530009 32413151 [.o\.......SEQ1A2]
7F88BD5DB720 00433342 00000000 00000000 00000000 [B3C.............]
7F88BD5DB730 00000000 00000000 45530009 32413151 [..........SEQ1A2]
7F88BD5DB740 00433342 00000000 00000000 00000000 [B3C.............]
7F88BD5DB750 00000000 00000000 43530005 0054544F [..........SCOTT.]
7F88BD5DB760 00000000 00000000 00000000 00000000 [................]
7F88BD5DB770 00000000 00000000 7BCA5918 00000000 [.........Y.{....]
7F88BD5DB780 7BEBD788 00000000 00051100 0C5AC409 [...{..........Z.]
~~~~~~~~
7F88BD5DB790 00001B0C 00000000 00000000 00000000 [................]
~~~~~~~~~
7F88BD5DB7A0 00000005 00000000 BD5DB168 00007F88 [........h.].....]
7F88BD5DB7B0 BD5C61A0 00007F88 00001000 00000000 [.a\.............]
7F88BD5DB7C0 00000000 00000000 00000000 00000000 [................]
--//注意看下劃線,在一個很大的chunk裡面，有一點奇怪的是使用fcha指令碼無法定位。不過應該可以確定下劃線內容就是該seq的當前值。
SYS@book> @ fcha 7f88bd5d00f8
Find in which heap (UGA, PGA or Shared Pool) the memory address 7f88bd5d00f8 resides...
Press ENTER to continue, CTRL+C to cancel...
no rows selected
--//你可以再做一個select seq1a2b3c.nextval from dual ;,然後再做一次轉儲,就可以驗證我的判斷,結果不再貼出了,
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2883428/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220322]探究oracle sequence 2.txtOracle
- [20210126]探究oracle記憶體分配.txtOracle記憶體
- [20210126]探究oracle記憶體分配3.txtOracle記憶體
- [20210126]探究oracle記憶體分配4.txtOracle記憶體
- 週六直播充電：探究Oracle分割槽表建立和使用Oracle
- oracle交換分割槽所引起的索引失效問題探究測試Oracle索引
- synchronized探究synchronized
- webAR 探究Web
- ZigBee 安全探究
- Hello World探究
- 探究Spring原理Spring
- JAVA 探究NIOJava
- Flutter BuildContext 探究FlutterUIContext
- iOS Block探究iOSBloC
- 探究Java集合Java
- context包探究Context
- Oracle優化案例-從Exadata遷移到國產一體機一般方法探究（四）Oracle優化
- HttpContext探究之RequestServicesHTTPContext
- Mach-O 探究Mac
- Go unsafe 包探究Go
- iOS 深入探究 AutoreleasePooliOS
- 探究 Kotlin 類代理Kotlin
- Flutter Dart mixins 探究FlutterDart
- 探究-ping指令的使用
- Django - 探究FBV 檢視Django
- 責任鏈模式探究模式
- 探究Flutter Engine除錯Flutter除錯
- 物件導向再探究物件
- iOS - Block探究系列一iOSBloC
- Flutter shared_preferences 探究Flutter
- .Netcore HttpClient原始碼探究NetCoreHTTPclient原始碼
- MySQL複合索引探究MySql索引
- zookeeper使用和原理探究
- vuex持久化方案探究Vue持久化
- RunLoop底層原理探究OOP
- Webpack Tree shaking 深入探究Web
- 深入探究Object.definePropertyObject
- 探究grid_sample函式函式