[20240604]简单探究RESERVED FREE LISTS chunk size的分布.txt

[20240604]简单探究RESERVED FREE LISTS chunk size的分布.txt --//前几天探究探究free list,无意中发现12c版本free list chunk size的发生了变化.单独另外写一篇blog. --//我开始分析以为脚本执行有问题,仔细查看12c版本free list chunk size分布发生了变化. --//我找了以前的11g下的转储,发生实际上11g已经出现这样的变化,只不过大家没有注意罢了. --//今天看看RESERVED FREE LISTS chunk size的分布情况. 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 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. 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_7940.trc 2.查看转储文件: d:\tmp> grep  -E "RESERVED FREE LISTS| Reserved bucket " D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_7940.trc RESERVED FREE LISTS:  Reserved bucket 0 size=40  Reserved bucket 1 size=4400  Reserved bucket 2 size=8216  Reserved bucket 3 size=8752  Reserved bucket 4 size=8760  Reserved bucket 5 size=8768  Reserved bucket 6 size=8776  Reserved bucket 7 size=9384  Reserved bucket 8 size=9392  Reserved bucket 9 size=12368  Reserved bucket 10 size=12376  Reserved bucket 11 size=16408  Reserved bucket 12 size=32792  Reserved bucket 13 size=65560  Reserved bucket 14 size=1990664 RESERVED FREE LISTS FOR SIZES 64KB to 512KB:  Reserved bucket 0 size=40  Reserved bucket 1 size=65536  Reserved bucket 2 size=2147483647 RESERVED FREE LISTS FOR SIZES 512KB to 976KB:  Reserved bucket 0 size=40  Reserved bucket 1 size=524288  Reserved bucket 2 size=2147483647 RESERVED FREE LISTS FOR SIZES OVER 976KB:  Reserved bucket 0 size=40  Reserved bucket 1 size=999424  Reserved bucket 2 size=2147483647 RESERVED FREE LISTS:  Reserved bucket 0 size=40  Reserved bucket 1 size=4400  Reserved bucket 2 size=8216  Reserved bucket 3 size=8752  Reserved bucket 4 size=8760  Reserved bucket 5 size=8768  Reserved bucket 6 size=8776  Reserved bucket 7 size=9384  Reserved bucket 8 size=9392  Reserved bucket 9 size=12368  Reserved bucket 10 size=12376  Reserved bucket 11 size=16408  Reserved bucket 12 size=32792  Reserved bucket 13 size=65560  Reserved bucket 14 size=1990664 --//12c输出很奇怪,前后出现相同的内容. --//我的感觉是分成4段: RESERVED FREE LISTS:                             <64KB RESERVED FREE LISTS FOR SIZES 64KB to 512KB:     >=64KB  <512KB RESERVED FREE LISTS FOR SIZES 512KB to 976KB:    >=512KB <~976KB RESERVED FREE LISTS FOR SIZES OVER 976KB:        >=976KB RESERVED FREE LISTS:  Reserved bucket 0 size=40  Reserved bucket 1 size=4400 --//RESERVED FREE LISTS非常特殊,一般大于一定大小chunk在有可能在保留池分配,受参数_shared_pool_reserved_min_alloc控制,缺省 --//4400. SYS@test> @ hide _shared_pool_reserved_min_alloc NAME                            DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD ------------------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- --------- _shared_pool_reserved_min_alloc minimum allocation size in bytes for reserved area of shared pool  TRUE          4400          4400         FALSE FALSE --//一般即使需要使用这么大的chunk,oracle先在普通共享池的Free list中分配,不行才到保留池分配. --//设置 Reserved bucket 1 size=4400,来源这里.分隔剩下如果小于4400,放入 Reserved bucket 0 size=40中. --//尝试修改看看: SYS@test> alter system set "_shared_pool_reserved_min_alloc"=4500 scope=spfile; System altered. --//重启数据库略. 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_4108.trc SYS@test> host grep  -E "RESERVED FREE LISTS| Reserved bucket " &TRC RESERVED FREE LISTS:  Reserved bucket 0 size=40  Reserved bucket 1 size=4500  Reserved bucket 2 size=8216  Reserved bucket 3 size=8752  Reserved bucket 4 size=8760  Reserved bucket 5 size=8768  Reserved bucket 6 size=8776  Reserved bucket 7 size=9384  Reserved bucket 8 size=9392  Reserved bucket 9 size=12368  Reserved bucket 10 size=12376  Reserved bucket 11 size=16408  Reserved bucket 12 size=32792  Reserved bucket 13 size=65560  Reserved bucket 14 size=1990664 RESERVED FREE LISTS FOR SIZES 64KB to 512KB:  Reserved bucket 0 size=40  Reserved bucket 1 size=65536  Reserved bucket 2 size=2147483647 RESERVED FREE LISTS FOR SIZES 512KB to 976KB:  Reserved bucket 0 size=40  Reserved bucket 1 size=524288  Reserved bucket 2 size=2147483647 RESERVED FREE LISTS FOR SIZES OVER 976KB:  Reserved bucket 0 size=40  Reserved bucket 1 size=999424  Reserved bucket 2 size=2147483647 RESERVED FREE LISTS:  Reserved bucket 0 size=40  Reserved bucket 1 size=4500  Reserved bucket 2 size=8216  Reserved bucket 3 size=8752  Reserved bucket 4 size=8760  Reserved bucket 5 size=8768  Reserved bucket 6 size=8776  Reserved bucket 7 size=9384  Reserved bucket 8 size=9392  Reserved bucket 9 size=12368  Reserved bucket 10 size=12376  Reserved bucket 11 size=16408  Reserved bucket 12 size=32792  Reserved bucket 13 size=65560  Reserved bucket 14 size=1990664 --//可以发现改变隐含参数,导致 Reserved bucket 1 size=4500,但是Reserved bucket 2 size=8216没有变化. --//还原: SYS@test> alter system reset "_shared_pool_reserved_min_alloc"; System altered. --//重启数据库略.