[20240529]简单探究FREE LISTS列表.txt
--//简单探究shraed pool的FREE LISTS列表.
1.环境:
SYS@test> @ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
--//关闭数据库启动到mount状态,主要方便观察,不然变化影响测试观察.
SYS@test> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SYS@test> startup mount
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 8924064 bytes
Variable Size 297796704 bytes
Database Buffers 490733568 bytes
Redo Buffers 7852032 bytes
Database mounted.
2.做堆转储:
SYS@test> @ init
SYS@test> alter session set events 'immediate trace name heapdump level 2';
Session altered.
SYS@test> @ t
TRACEFILE
-----------------------------------------------------------------
D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_3708.trc
--//查看相关chunk的内容:
--//看看Bucket 3的情况size=56,后面出现是RESERVED FREE LISTS:
$ sed -n "/^ Bucket 3 /,/^ Bucket 4 /p" test_ora_3708.trc
Bucket 3 size=56
Chunk 7ff0197df68 sz= 56 free " "
Chunk 7ff02b8b4a8 sz= 56 free " "
Chunk 7ff037a8368 sz= 56 free " "
Bucket 4 size=64
Bucket 3 size=56
Bucket 4 size=64
--//顺便贴一个FREE LISTS的情况,仅仅启动到mount.
Total heap size =176156736
FREE LISTS:
Bucket 0 size=32
Chunk 7ff0b000088 sz= 0 kghdsx
Bucket 1 size=40
Bucket 2 size=48
Chunk 7ff01ffdf90 sz= 48 free " "
Chunk 7ff033310c0 sz= 48 free " "
Bucket 3 size=56
Chunk 7ff0197df68 sz= 56 free " "
Chunk 7ff02b8b4a8 sz= 56 free " "
Chunk 7ff037a8368 sz= 56 free " "
Bucket 4 size=64
Chunk 7ff013bb058 sz= 64 free " "
Bucket 5 size=72
...
Bucket 30 size=272
Chunk 7ff013bd300 sz= 272 free " "
Bucket 31 size=280
...
Bucket 254 size=65560
Chunk 7ff00834000 sz= 3898152 free " "
Total free space = 3898752
--//仅仅启动到mount,自由空间全部在最后一个bucket 254,前面仅仅存在几个free chunk,这样好做分析.
$ sed -n "/^ Bucket 3 /,/^ Bucket 4 /p" test_ora_3708.trc | awk '/Chunk/{print "oradebug peek 0x" toupper($2),32, 1 }'
oradebug peek 0x7FF0197DF68 32 1
oradebug peek 0x7FF02B8B4A8 32 1
oradebug peek 0x7FF037A8368 32 1
--//退出会话重新登陆,执行如下:
SYS@test> @ t
TRACEFILE
-----------------------------------------------------------------
D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_7324.trc
SYS@test> oradebug setmypid
Statement processed.
SYS@test> oradebug peek 0x7FF0197DF68 32 1
[7FF0197DF68, 7FF0197DF88) = 00000039 C0B38F00 0197DF28 000007FF 0AC5DD48 00000000 02B8B4B8 000007FF
~~~~~~~~~~~~~~~~~ ++++++++++++++++
SYS@test> oradebug peek 0x7FF02B8B4A8 32 1
[7FF02B8B4A8, 7FF02B8B4C8) = 00000039 C0B38F00 02B7B9D0 000007FF 0197DF78 000007FF 037A8378 000007FF
~~~~~~~~~~~~~~~~~ ++++++++++++++++
SYS@test> oradebug peek 0x7FF037A8368 32 1
[7FF037A8368, 7FF037A8388) = 00000039 C0B38F00 03434000 000007FF 02B8B4B8 000007FF 0AC5DD48 00000000
~~~~~~~~~~~~~~~~~ ++++++++++++++++
--//0x39 = 57,表示chunk size+1.
--//注意看下划线以及+++相关内容,注意intel系列CPU的大小头问题.
--//前后都出现0AC5DD48 00000000.
--//02B8B4B8 000007FF 颠倒过来就是 000007FF 02B8B4B8,减去0x10就是000007FF 02B8B4A8与Chunk 7ff02b8b4a8一致.
--//0197DF78 000007FF 颠倒过来就是 000007FF 0197DF78,减去0x10就是000007FF 0197DF68与Chunk 7ff0197df68一致.
--//037A8378 000007FF 颠倒过来就是 000007FF 037A8378,减去0x10就是000007FF 037A8368与Chunk 7FF037A8368一致.
--//前面表示上一个,后面表示下一个,这样看就形成1个环.
--//这样很清晰展示free list 一个bucket桶里面的free chunk地址如何链接在一起的.
--//可以猜测存在一个单独free list区,包含地址0x0AC5DD48.
SYS@test> oradebug peek 0x0AC5DD48 32
[00AC5DD48, 00AC5DD68) = 037A8378 000007FF 0197DF78 000007FF 00000040 00000000 013BB068 000007FF
~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
--//大小头颠倒, 000007FF037A8378 , 000007FF0197DF78,减去0x10对应前面转储看到的尾部,头部.
--//下面确定一个bucket在free list区占用多少空间,实际上从前面的输出可以猜测占用24字节,因为00000040 00000000不可能是地址.
--//似乎表示某种长度,0x40 = 64,仅仅是猜测.
--//013BB068 000007FF 颠倒过来就是 000007FF 013BB068,减去0x10就是 000007FF 013BB058,仔细看前面跟踪的输出
Bucket 4 size=64
Chunk 7ff013bb058 sz= 64 free " "
--//正好是bucket 4的内容,可以确定一个bucket在free list区占24字节.
--//看跟踪文件下一个bucket 4,仅仅一个chunk.
Bucket 4 size=64
Chunk 7ff013bb058 sz= 64 free " "
SYS@test> oradebug peek 0x7ff013bb058 32
[7FF013BB058, 7FF013BB078) = 00000041 C0B38F00 013B7858 000007FF 0AC5DD60 00000000 0AC5DD60 00000000
~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~
--// 0x41 = 65 =chunk size+1,第4-7字节都是C0B38F00.第8-11,12-15字节内容我不知道表示怎么?无法猜测...
SYS@test> oradebug peek 0x0AC5DD60 24
[00AC5DD60, 00AC5DD78) = 013BB068 000007FF 013BB068 000007FF 00000048 00000000
--//000007FF 013BB068 - 0x10 = 0x7ff013bb058,后面的00000048 00000000猜测不出来.视乎增大1个bucket,增加8.
--//每个bucket占用 0AC5DD60-0AC5DD48 = 0x18 = 24.
--//0x48 = 72,哦似乎明白了,这个值表示这个free list bucket桶的最大chunk size是72-1=71.可以在后面单独验证.
SYS@test> @ fchaz 0x0AC5DD60
no rows selected
--//奇怪没有输出!!
--//看看这个地址0x0AC5DD60在那里:
SYS@test> oradebug ipc
IPC information written to the trace file
SYS@test> @ t
TRACEFILE
-----------------------------------------------------------------
D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_7324.trc
*** 2024-05-30T20:44:35.830155+08:00 (CDB$ROOT(1))
Processing Oradebug command 'ipc'
Dump of Windows skgm context
areaflags 000017ff
realmflags 00001b01
maxtotalrealmsize 936d0000
VMpagesize 00001000
VMallocgranularity 00010000
minappaddress 0000000000010000
maxappaddress 000007FFFFFEFFFF
stacklimit 0000000023981000
magic acc01ade
Handle: 0000000001260060 `sga_test'
Dump of Windows realm handle `sga_test', flags = 00000000
Area #0 `Fixed Size' containing Subareas 0-0
Total size 0000000000882ba0 Minimum Subarea size 00000000
Area Subarea Start Addr
0 0 000000000AB10000
Subarea size
08925184
[
Base = 000000000AB10000 Prot = RW Size = 8925184 State = COM
]
--//AB10000 = 179372032
--//179372032+8925184 = 188297216
--//188297216 = 0xb393000
--//0x0AC5DD60 在 0xAB10000 - 0xb393000之间.也就是free list表在fixed 区.
Area #1 `Variable Size' containing Subareas 2-2
Total size 000000002f000000 Minimum Subarea size 00400000
Area Subarea Start Addr
1 2 000007FF00000000
Subarea size
788529152
[
Base = 000007FF00000000 Prot = RW Size = 788529152 State = COM
]
Area #2 `Redo Buffers' containing Subareas 1-1
Total size 000000000077d000 Minimum Subarea size 00001000
Area Subarea Start Addr
2 1 000000000C090000
Subarea size
07852032
[
Base = 000000000C090000 Prot = RW Size = 7852032 State = COM
]
--//看看第一个Bucket 0.
FREE LISTS:
Bucket 0 size=32
Chunk 7ff0b000088 sz= 0 kghdsx
~~~~~~~~~~~~~
SYS@test> oradebug peek 0x7ff0b000088 32
[7FF0B000088, 7FF0B0000A8) = 00000001 C0B38F00 00000000 00000000 0AC5DD00 00000000 0AC5DD00 00000000
--//00000001 = 1 ,仅仅1个字节,也就是这个chunk size=0,不过注意记录的sz确实等于0,看下划线内容.
--//这样后面的信息如何保存,也许这个chunk非常特殊,kghdsx表示什么,我感觉这个chunk非常特殊,似乎表示开始.
SYS@test> oradebug peek 0x0AC5DD00 24
[00AC5DD00, 00AC5DD18) = 0B000098 000007FF 0B000098 000007FF 00000028 00000000
--//0AC5DD60-0AC5DD00 = 0x60 = 60 = 96
--//96/4 = 24,反向验证free list每个bucket占用24字节.
--//0x28 = 40,验证我前面的判断表示这个bucket最大的chunk size不能大于40.
--//仅仅启动到mount,free list都是null的.看看 Bucket 30,反向验证是否正确.
--//注意一个细节,整个测试最好不要执行任何sql语句,尽量减少使用chunk的情况.
Bucket 30 size=272
Chunk 7ff013bd300 sz= 272 free " "
--//30 = 0x1e,24=0x18
--//0AC5DD00 + 0x18 * 0x1e = 0xac5dfd0
SYS@test> oradebug peek 0xac5dfd0 24
[00AC5DFD0, 00AC5DFF0) = 013BD310 000007FF 013BD310 000007FF 00000118
~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~
--//000007FF013BD310 - 0x10 = 0x7FF013BD300,正好对上.
--//0x118 = 280,0x48 = 72 ,0x28 = 40
--//280-72 = 208 , 208/8 = 26
--//280-40 = 240, 240/8 = 30
--//确实第16-19字节每个bucket增加8.
--//ac5dfd0-0x18 = 0xac5dfb8,bucket 29
SYS@test> oradebug peek 0xac5dfb8 24
[00AC5DFB8, 00AC5DFD0) = 0AC5DFB8 00000000 0AC5DFB8 00000000 00000110 00000000
--//ac5dfd0+0x18 = 0xac5dfe8,,bucket 31
SYS@test> oradebug peek 0xac5dfe8 24
[00AC5DFE8, 00AC5E000) = 0AC5DFE8 00000000 0AC5DFE8 00000000 00000120 00000000
--//确实第16-19字节基本可以确定表示这个bucket的最大chunk size是该数值-1.
3.启动到open,再次转储,步骤略:
Bucket 251 size=12376
Chunk 7ff00188010 sz= 16384 free " "
Chunk 7feff84ac88 sz= 13560 free " "
Chunk 7ff003a1000 sz= 16384 free " "
Bucket 252 size=16408
--//可以发现 Bucket 251链接的是chunk size>= 12376 and <=16408-1 的chunk.
--//这样最小chunk size 是 32字节.bucket 0 chunk size >=32 <=40-1 的chunk.
Bucket 0 size=32
Chunk 7ff0b000088 sz= 0 kghdsx
--//sz=0 应该具有某种特殊意义.我不知道.
4.验证第16-19字节是否表示这个bucket的最大chunk size是该数值-1.
--//重新启动到mount状态.再次转储,步骤略:
--//单独保存free list部分为文件a.txt
$ grep Bucket a.txt | cut -d= -f2 | awk 'NR==1 {a=$1} NR>1 {print $1-a;a=$1}'| uniq -c
179 8
10 16
50 48
1 72
1 8
1 16
1 4096
1 536
3 8
1 608
1 8
1 2976
1 8
1 4032
1 16384
1 32768
--//Sum = 254
--//简单解析执行过程,避免以后忘记.
--//grep Bucket a.txt | cut -d= -f2 ,过滤含有Bucket行,再使用cut以=作为分隔符号,取第2个字段就是size=后面的值
D:\>grep Bucket a.txt | head -4
Bucket 0 size=32
Bucket 1 size=40
Bucket 2 size=48
Bucket 3 size=56
D:\>grep Bucket a.txt | cut -d= -f2| head -4
32
40
48
56
--//awk "NR==1 {a=$1} NR>1 {print $1-a;a=$1}" 就是做上下相减操作.
--//uniq -c -c 表示prefix lines by the number of occurrences
--//看前面的输出,开始以为我计算错误,仔细检查发现没有问题.
--//可以看出oracle每个bucket增加的大小并非线性增加,中间出现仅仅增加8字节的情况.说明oracle一直在调整算法,避免内存浪费.或
--//者讲避免ora-04031错误.
d:\tmp> oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
// DBMS_SHARED_POOL package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// initialization parameters SHARED_POOL_RESERVED_SIZE and
// SHARED_POOL_SIZE.
// If the large pool is out of memory, increase the initialization
// parameter LARGE_POOL_SIZE.
// If the error is issued from an Oracle Streams or XStream process,
// increase the initialization parameter STREAMS_POOL_SIZE or increase
// the capture or apply parameter MAX_SGA_SIZE.
--//179+10+50+1 = 240,贴出其中一段:
Bucket 238 size=3976
Bucket 239 size=4024
Bucket 240 size=4096
Bucket 241 size=4104 --//出现上下相减等于8的情况.
Bucket 242 size=4120
Bucket 243 size=8216
Bucket 244 size=8752
Bucket 245 size=8760
Bucket 246 size=8768
Bucket 247 size=8776 --//出现上下相减等于8的情况.
Bucket 248 size=9384
Bucket 249 size=9392
Bucket 250 size=12368
Bucket 251 size=12376 --//出现上下相减等于8的情况.
Bucket 252 size=16408
Bucket 253 size=32792
Bucket 254 size=65560
--//上下相减 48,72 8,16....
--//可以想象在具体应用中一定许多chunk集中在size=4024 4096 4104 4120 附近.不然oracle不会做这样的改进.
--//换一个方式验证:
--//free list区 bucket 0在 00000000 0AC5DD00 位置.
SYS@test> oradebug peek 0x0AC5DD00 24
[00AC5DD00, 00AC5DD18) = 0B000098 000007FF 0B000098 000007FF 00000028 00000000
--//0AC5DD00 +0x10 地址保存chunk size .
SYS@test> oradebug peek 0x0AC5DD10 8
[00AC4DD10, 00AC4DD18) = 00000028 00000000
--//继续昨天的测试,今天启动到mount后,发现free list开始地址出现变动.
SYS@test> oradebug peek 0x7ff0ac00088 32
[7FF0AC00088, 7FF0AC000A8) = 00000001 C0B38F00 00000000 00000000 0AC4DD00 00000000 0AC4DD00 00000000
--//开始位置出现在0x0AC4DD00
SYS@test> oradebug peek 0x0AC4DD10 8
[00AC4DD10, 00AC4DD18) = 00000028 00000000
--//0AC4DD10 = 180673808
$ seq 0 1 254 | tr -d '\r' | xargs -IQ echo "obase=16;180673808+Q*24"| bc | tr -d '\r'| awk '{print "oradebug peek 0x" toupper($1),4}'
--//输出略.使用tr -d '\r'主要原因是cygwin seq,bc的输出多了\r字符.
$ seq 0 1 254 | tr -d '\r' | xargs -IQ echo "obase=16;180673808+Q*24"| bc | tr -d '\r'| awk '{print "oradebug peek 0x" toupper($1),4}' > b.txt
SYS@test> oradebug setmypid
Statement processed.
SYS@test> @ b.txt
[00AC4DD10, 00AC4DD14) = 00000028
[00AC4DD28, 00AC4DD2C) = 00000030
[00AC4DD40, 00AC4DD44) = 00000038
[00AC4DD58, 00AC4DD5C) = 00000040
...
[00AC4F360, 00AC4F364) = 00000FB8
[00AC4F378, 00AC4F37C) = 00001000
[00AC4F390, 00AC4F394) = 00001008
[00AC4F3A8, 00AC4F3AC) = 00001018
[00AC4F3C0, 00AC4F3C4) = 00002018
[00AC4F3D8, 00AC4F3DC) = 00002230
[00AC4F3F0, 00AC4F3F4) = 00002238
[00AC4F408, 00AC4F40C) = 00002240
[00AC4F420, 00AC4F424) = 00002248
[00AC4F438, 00AC4F43C) = 000024A8
[00AC4F450, 00AC4F454) = 000024B0
[00AC4F468, 00AC4F46C) = 00003050
[00AC4F480, 00AC4F484) = 00003058
[00AC4F498, 00AC4F49C) = 00004018
[00AC4F4B0, 00AC4F4B4) = 00008018
[00AC4F4C8, 00AC4F4CC) = 00010018
[00AC4F4E0, 00AC4F4E4) = 00000000
--//最后1个00000000.
SYS@test> @ t
TRACEFILE
-----------------------------------------------------------------
D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_900.trc
$ grep "^\[" test_ora_900.trc | cut -d= -f2 | sed -n -e '1,$s/^ //p' | awk '{print strtonum("0x"$0)}' | awk 'NR==1 {a=$1} NR>1 {print $1-a;a=$1}'| uniq -c
178 8
10 16
50 48
1 72
1 8
1 16
1 4096
1 536
3 8
1 608
1 8
1 2976
1 8
1 4032
1 16384
1 32768
1 -65560
--//Sum = 254
--//出现178 8是正常的,因为这次计算使用最大值.
--//简单说明: grep "^\[" test_ora_900.trc | cut -d= -f2 | sed -n -e '1,$s/^ //p' 过滤开头[的行,取分隔符=的第2字段,删除
--//开头的空格.
--//awk '{print strtonum("0x"$0)}' 转换为10进制数字.
--//awk 'NR==1 {a=$1} NR>1 {print $1-a;a=$1}' 上下相减
--//uniq -c 压缩输出.
Bucket 239 size=4024
Bucket 240 size=4096
Bucket 241 size=4104 --//出现上下相减等于8的情况.
Bucket 242 size=4120
$ seq 100000 | xargs -IQ echo "select Q from dual;" | sqlplus / as sysdba > /dev/null
SYS@test> select count(*) ,ksmchsiz from x$ksmsp where ksmchsiz between 4000 and 4120 group by ksmchsiz order by 2;
COUNT(*) KSMCHSIZ
---------- ----------
11 4000
63 4008
1 4024
3 4032
2 4040
2 4048
2 4056
39 4072
14 4080
1 4088
5957 4096
1 4104
4 4112
145 4120
14 rows selected.
--//我的测试环境语句太少,不过还是可以看出KSMCHSIZ=4120有许多.某种意义看出oracle故意这样设置.
5.总结:
--//前面已经展示free list如何链接的.仅仅相差0x10字节
--//free list在fixed区,并且每个bucket占用24字节,开始0-7字节表示free list地址的尾部,8-15表示free list地址的头部,16-23字节
--//表示该buckect free list 链接的chunk 最大chunk大小 -1,我开始猜测16-23字节相关内容时浪费一点点时间.
--//12c free list chunk size大小做了一些改进,每个bucket增加的大小并非线性增加,中间出现仅仅增加8字节的情况.说明oracle一直
--//在调整算法,避免内存浪费.或者讲避免ora-04031错误.在具体应用中一定许多chunk集中在size=4024 4096 4104 4120 附近.不然
--//oracle不会做这样的改进.补充在11g下也测试发现从11g就开始这样设计.
--//测试在mount下进行,不过不应该影响测试结论.
--//windows下测试太麻烦了,浪费大量时间,cygwin不够灵活,遇到输出\r字符的问题.
--//写的很乱,主要记录我当时的思考以及分析过程,整个分析过程夹杂了我许多猜测,术语也使用不对,不知道如何表达.