[20240601]简单探究free list chunk size的分布.txt
--//前几天探究探究free list,无意中发现12c版本free list chunk size的发生了变化.单独另外写一篇blog.
--//我开始分析以为脚本执行有问题,仔细查看12c版本free list chunk size分布发生了变化.
--//我找了以前的11g下的转储,发生实际上11g已经出现这样的变化,只不过大家没有注意罢了.
1.环境:
SYS@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
SYS@book> alter session set events 'immediate trace name heapdump level 2';
Session altered.
$ grep "^ Bucket " /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_4217.trc
Bucket 0 size=32
Bucket 1 size=40
Bucket 2 size=48
Bucket 3 size=56
Bucket 4 size=64
Bucket 5 size=72
Bucket 6 size=80
Bucket 7 size=88
Bucket 8 size=96
Bucket 9 size=104
Bucket 10 size=112
Bucket 11 size=120
Bucket 12 size=128
Bucket 13 size=136
Bucket 14 size=144
Bucket 15 size=152
Bucket 16 size=160
Bucket 17 size=168
Bucket 18 size=176
Bucket 19 size=184
Bucket 20 size=192
Bucket 21 size=200
Bucket 22 size=208
Bucket 23 size=216
Bucket 24 size=224
Bucket 25 size=232
Bucket 26 size=240
Bucket 27 size=248
Bucket 28 size=256
Bucket 29 size=264
Bucket 30 size=272
Bucket 31 size=280
Bucket 32 size=288
Bucket 33 size=296
Bucket 34 size=304
Bucket 35 size=312
Bucket 36 size=320
Bucket 37 size=328
Bucket 38 size=336
Bucket 39 size=344
Bucket 40 size=352
Bucket 41 size=360
Bucket 42 size=368
Bucket 43 size=376
Bucket 44 size=384
Bucket 45 size=392
Bucket 46 size=400
Bucket 47 size=408
Bucket 48 size=416
Bucket 49 size=424
Bucket 50 size=432
Bucket 51 size=440
Bucket 52 size=448
Bucket 53 size=456
Bucket 54 size=464
Bucket 55 size=472
Bucket 56 size=480
Bucket 57 size=488
Bucket 58 size=496
Bucket 59 size=504
Bucket 60 size=512
Bucket 61 size=520
Bucket 62 size=528
Bucket 63 size=536
Bucket 64 size=544
Bucket 65 size=552
Bucket 66 size=560
Bucket 67 size=568
Bucket 68 size=576
Bucket 69 size=584
Bucket 70 size=592
Bucket 71 size=600
Bucket 72 size=608
Bucket 73 size=616
Bucket 74 size=624
Bucket 75 size=632
Bucket 76 size=640
Bucket 77 size=648
Bucket 78 size=656
Bucket 79 size=664
Bucket 80 size=672
Bucket 81 size=680
Bucket 82 size=688
Bucket 83 size=696
Bucket 84 size=704
Bucket 85 size=712
Bucket 86 size=720
Bucket 87 size=728
Bucket 88 size=736
Bucket 89 size=744
Bucket 90 size=752
Bucket 91 size=760
Bucket 92 size=768
Bucket 93 size=776
Bucket 94 size=784
Bucket 95 size=792
Bucket 96 size=800
Bucket 97 size=808
Bucket 98 size=816
Bucket 99 size=824
Bucket 100 size=832
Bucket 101 size=840
Bucket 102 size=848
Bucket 103 size=856
Bucket 104 size=864
Bucket 105 size=872
Bucket 106 size=880
Bucket 107 size=888
Bucket 108 size=896
Bucket 109 size=904
Bucket 110 size=912
Bucket 111 size=920
Bucket 112 size=928
Bucket 113 size=936
Bucket 114 size=944
Bucket 115 size=952
Bucket 116 size=960
Bucket 117 size=968
Bucket 118 size=976
Bucket 119 size=984
Bucket 120 size=992
Bucket 121 size=1000
Bucket 122 size=1008
Bucket 123 size=1016
Bucket 124 size=1024
Bucket 125 size=1032
Bucket 126 size=1040
Bucket 127 size=1048
Bucket 128 size=1056
Bucket 129 size=1064
Bucket 130 size=1072
Bucket 131 size=1080
Bucket 132 size=1088
Bucket 133 size=1096
Bucket 134 size=1104
Bucket 135 size=1112
Bucket 136 size=1120
Bucket 137 size=1128
Bucket 138 size=1136
Bucket 139 size=1144
Bucket 140 size=1152
Bucket 141 size=1160
Bucket 142 size=1168
Bucket 143 size=1176
Bucket 144 size=1184
Bucket 145 size=1192
Bucket 146 size=1200
Bucket 147 size=1208
Bucket 148 size=1216
Bucket 149 size=1224
Bucket 150 size=1232
Bucket 151 size=1240
Bucket 152 size=1248
Bucket 153 size=1256
Bucket 154 size=1264
Bucket 155 size=1272
Bucket 156 size=1280
Bucket 157 size=1288
Bucket 158 size=1296
Bucket 159 size=1304
Bucket 160 size=1312
Bucket 161 size=1320
Bucket 162 size=1328
Bucket 163 size=1336
Bucket 164 size=1344
Bucket 165 size=1352
Bucket 166 size=1360
Bucket 167 size=1368
Bucket 168 size=1376
Bucket 169 size=1384
Bucket 170 size=1392
Bucket 171 size=1400
Bucket 172 size=1408
Bucket 173 size=1416
Bucket 174 size=1424
Bucket 175 size=1432
Bucket 176 size=1440
Bucket 177 size=1448
Bucket 178 size=1456
Bucket 179 size=1464
Bucket 180 size=1480
Bucket 181 size=1496
Bucket 182 size=1512
Bucket 183 size=1528
Bucket 184 size=1544
Bucket 185 size=1560
Bucket 186 size=1576
Bucket 187 size=1592
Bucket 188 size=1608
Bucket 189 size=1624
Bucket 190 size=1672
Bucket 191 size=1720
Bucket 192 size=1768
Bucket 193 size=1816
Bucket 194 size=1864
Bucket 195 size=1912
Bucket 196 size=1960
Bucket 197 size=2008
Bucket 198 size=2056
Bucket 199 size=2104
Bucket 200 size=2152
Bucket 201 size=2200
Bucket 202 size=2248
Bucket 203 size=2296
Bucket 204 size=2344
Bucket 205 size=2392
Bucket 206 size=2440
Bucket 207 size=2488
Bucket 208 size=2536
Bucket 209 size=2584
Bucket 210 size=2632
Bucket 211 size=2680
Bucket 212 size=2728
Bucket 213 size=2776
Bucket 214 size=2824
Bucket 215 size=2872
Bucket 216 size=2920
Bucket 217 size=2968
Bucket 218 size=3016
Bucket 219 size=3064
Bucket 220 size=3112
Bucket 221 size=3160
Bucket 222 size=3208
Bucket 223 size=3256
Bucket 224 size=3304
Bucket 225 size=3352
Bucket 226 size=3400
Bucket 227 size=3448
Bucket 228 size=3496
Bucket 229 size=3544
Bucket 230 size=3592
Bucket 231 size=3640
Bucket 232 size=3688
Bucket 233 size=3736
Bucket 234 size=3784
Bucket 235 size=3832
Bucket 236 size=3880
Bucket 237 size=3928
Bucket 238 size=3976
Bucket 239 size=4024
Bucket 240 size=4096
Bucket 241 size=4104
Bucket 242 size=4120
Bucket 243 size=8216
Bucket 244 size=8696
Bucket 245 size=8704
Bucket 246 size=8712
Bucket 247 size=8720
Bucket 248 size=9368
Bucket 249 size=9376
Bucket 250 size=12352
Bucket 251 size=12360
Bucket 252 size=16408
Bucket 253 size=32792
Bucket 254 size=65560
--//另保存为b1.txt文件.
$ grep Bucket b1.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 480
3 8
1 648
1 8
1 2976
1 8
1 4048
1 16384
1 32768
--//Sum = 254
--//12c的情况:
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
SYS@test> alter session set events 'immediate trace name heapdump level 2';
Session altered.
$ grep Bucket a.txt
Bucket 0 size=32
Bucket 1 size=40
Bucket 2 size=48
Bucket 3 size=56
Bucket 4 size=64
Bucket 5 size=72
Bucket 6 size=80
Bucket 7 size=88
Bucket 8 size=96
Bucket 9 size=104
Bucket 10 size=112
Bucket 11 size=120
Bucket 12 size=128
Bucket 13 size=136
Bucket 14 size=144
Bucket 15 size=152
Bucket 16 size=160
Bucket 17 size=168
Bucket 18 size=176
Bucket 19 size=184
Bucket 20 size=192
Bucket 21 size=200
Bucket 22 size=208
Bucket 23 size=216
Bucket 24 size=224
Bucket 25 size=232
Bucket 26 size=240
Bucket 27 size=248
Bucket 28 size=256
Bucket 29 size=264
Bucket 30 size=272
Bucket 31 size=280
Bucket 32 size=288
Bucket 33 size=296
Bucket 34 size=304
Bucket 35 size=312
Bucket 36 size=320
Bucket 37 size=328
Bucket 38 size=336
Bucket 39 size=344
Bucket 40 size=352
Bucket 41 size=360
Bucket 42 size=368
Bucket 43 size=376
Bucket 44 size=384
Bucket 45 size=392
Bucket 46 size=400
Bucket 47 size=408
Bucket 48 size=416
Bucket 49 size=424
Bucket 50 size=432
Bucket 51 size=440
Bucket 52 size=448
Bucket 53 size=456
Bucket 54 size=464
Bucket 55 size=472
Bucket 56 size=480
Bucket 57 size=488
Bucket 58 size=496
Bucket 59 size=504
Bucket 60 size=512
Bucket 61 size=520
Bucket 62 size=528
Bucket 63 size=536
Bucket 64 size=544
Bucket 65 size=552
Bucket 66 size=560
Bucket 67 size=568
Bucket 68 size=576
Bucket 69 size=584
Bucket 70 size=592
Bucket 71 size=600
Bucket 72 size=608
Bucket 73 size=616
Bucket 74 size=624
Bucket 75 size=632
Bucket 76 size=640
Bucket 77 size=648
Bucket 78 size=656
Bucket 79 size=664
Bucket 80 size=672
Bucket 81 size=680
Bucket 82 size=688
Bucket 83 size=696
Bucket 84 size=704
Bucket 85 size=712
Bucket 86 size=720
Bucket 87 size=728
Bucket 88 size=736
Bucket 89 size=744
Bucket 90 size=752
Bucket 91 size=760
Bucket 92 size=768
Bucket 93 size=776
Bucket 94 size=784
Bucket 95 size=792
Bucket 96 size=800
Bucket 97 size=808
Bucket 98 size=816
Bucket 99 size=824
Bucket 100 size=832
Bucket 101 size=840
Bucket 102 size=848
Bucket 103 size=856
Bucket 104 size=864
Bucket 105 size=872
Bucket 106 size=880
Bucket 107 size=888
Bucket 108 size=896
Bucket 109 size=904
Bucket 110 size=912
Bucket 111 size=920
Bucket 112 size=928
Bucket 113 size=936
Bucket 114 size=944
Bucket 115 size=952
Bucket 116 size=960
Bucket 117 size=968
Bucket 118 size=976
Bucket 119 size=984
Bucket 120 size=992
Bucket 121 size=1000
Bucket 122 size=1008
Bucket 123 size=1016
Bucket 124 size=1024
Bucket 125 size=1032
Bucket 126 size=1040
Bucket 127 size=1048
Bucket 128 size=1056
Bucket 129 size=1064
Bucket 130 size=1072
Bucket 131 size=1080
Bucket 132 size=1088
Bucket 133 size=1096
Bucket 134 size=1104
Bucket 135 size=1112
Bucket 136 size=1120
Bucket 137 size=1128
Bucket 138 size=1136
Bucket 139 size=1144
Bucket 140 size=1152
Bucket 141 size=1160
Bucket 142 size=1168
Bucket 143 size=1176
Bucket 144 size=1184
Bucket 145 size=1192
Bucket 146 size=1200
Bucket 147 size=1208
Bucket 148 size=1216
Bucket 149 size=1224
Bucket 150 size=1232
Bucket 151 size=1240
Bucket 152 size=1248
Bucket 153 size=1256
Bucket 154 size=1264
Bucket 155 size=1272
Bucket 156 size=1280
Bucket 157 size=1288
Bucket 158 size=1296
Bucket 159 size=1304
Bucket 160 size=1312
Bucket 161 size=1320
Bucket 162 size=1328
Bucket 163 size=1336
Bucket 164 size=1344
Bucket 165 size=1352
Bucket 166 size=1360
Bucket 167 size=1368
Bucket 168 size=1376
Bucket 169 size=1384
Bucket 170 size=1392
Bucket 171 size=1400
Bucket 172 size=1408
Bucket 173 size=1416
Bucket 174 size=1424
Bucket 175 size=1432
Bucket 176 size=1440
Bucket 177 size=1448
Bucket 178 size=1456
Bucket 179 size=1464
Bucket 180 size=1480
Bucket 181 size=1496
Bucket 182 size=1512
Bucket 183 size=1528
Bucket 184 size=1544
Bucket 185 size=1560
Bucket 186 size=1576
Bucket 187 size=1592
Bucket 188 size=1608
Bucket 189 size=1624
Bucket 190 size=1672
Bucket 191 size=1720
Bucket 192 size=1768
Bucket 193 size=1816
Bucket 194 size=1864
Bucket 195 size=1912
Bucket 196 size=1960
Bucket 197 size=2008
Bucket 198 size=2056
Bucket 199 size=2104
Bucket 200 size=2152
Bucket 201 size=2200
Bucket 202 size=2248
Bucket 203 size=2296
Bucket 204 size=2344
Bucket 205 size=2392
Bucket 206 size=2440
Bucket 207 size=2488
Bucket 208 size=2536
Bucket 209 size=2584
Bucket 210 size=2632
Bucket 211 size=2680
Bucket 212 size=2728
Bucket 213 size=2776
Bucket 214 size=2824
Bucket 215 size=2872
Bucket 216 size=2920
Bucket 217 size=2968
Bucket 218 size=3016
Bucket 219 size=3064
Bucket 220 size=3112
Bucket 221 size=3160
Bucket 222 size=3208
Bucket 223 size=3256
Bucket 224 size=3304
Bucket 225 size=3352
Bucket 226 size=3400
Bucket 227 size=3448
Bucket 228 size=3496
Bucket 229 size=3544
Bucket 230 size=3592
Bucket 231 size=3640
Bucket 232 size=3688
Bucket 233 size=3736
Bucket 234 size=3784
Bucket 235 size=3832
Bucket 236 size=3880
Bucket 237 size=3928
Bucket 238 size=3976
Bucket 239 size=4024
Bucket 240 size=4096
Bucket 241 size=4104
Bucket 242 size=4120
Bucket 243 size=8216
Bucket 244 size=8752
Bucket 245 size=8760
Bucket 246 size=8768
Bucket 247 size=8776
Bucket 248 size=9384
Bucket 249 size=9392
Bucket 250 size=12368
Bucket 251 size=12376
Bucket 252 size=16408
Bucket 253 size=32792
Bucket 254 size=65560
$ 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
--//对比两者的不同.
$ diff -Nur <(grep Bucket b1.txt | cut -d= -f2 | awk 'NR==1 {a=$1} NR>1 {print $1-a;a=$1}'| uniq -c) \
> <(grep Bucket a.txt | cut -d= -f2 | awk 'NR==1 {a=$1} NR>1 {print $1-a;a=$1}'| uniq -c) \
>
--- /dev/fd/63 2024-06-01 21:35:00.000000000 +0800
+++ /dev/fd/62 2024-06-01 21:35:00.000000000 +0800
@@ -5,12 +5,12 @@
1 8
1 16
1 4096
- 1 480
+ 1 536
3 8
- 1 648
+ 1 608
1 8
1 2976
1 8
- 1 4048
+ 1 4032
1 16384
1 32768
--//可以发现两者在一些细节上还是有所不同.
--//11g
Bucket 242 size=4120
Bucket 243 size=8216
Bucket 244 size=8696
Bucket 245 size=8704
--//12c
Bucket 242 size=4120
Bucket 243 size=8216
Bucket 244 size=8752
Bucket 245 size=8760
--//大家可以对比 9i的情况.链接https://www.eygle.com/archives/2004/10/shared_pool-1.html
--//说明oracle一直在调整free list chunk size的大小分布,目的还是减少ora-04031错误.