[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博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2883428/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
