[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.

--//重啟資料庫略.