[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,0×48 = 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字符的问题.

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