[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.
--//重启数据库略.
--//前几天探究探究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.
--//重启数据库略.
