[20240529]简单探究FREE LISTS列表.txt

[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字符的问题.

--//写的很乱,主要记录我当时的思考以及分析过程,整个分析过程夹杂了我许多猜测,术语也使用不对,不知道如何表达.

热门相关:隐婚101天:唐少宠妻成瘾   千夫斩   来自地狱的男人   秦吏   呆萌配腹黑:绝宠小冤家