[20210126]探究oracle記憶體分配.txt
[20210126]探究oracle記憶體分配.txt
--//昨天別人問的一個問題,為什麼生產系統的log_buffer這麼大.自己做一些簡單的探究也許不對.
--//生產環境:
> set numw 12
> show sga
Total System Global Area 80972824576 bytes
Fixed Size 2261968 bytes
Variable Size 17448307760 bytes
Database Buffers 63350767616 bytes
Redo Buffers 171487232 bytes
--//171487232/1024/1024 = 163.54296875M
SYS@192.168.99.105:1521/dbcn> select component,current_size,granule_size from v$sga_dynamic_components where current_size != 0;
COMPONENT CURRENT_SIZE GRANULE_SIZE
-------------------- ------------ ------------
shared pool 13421772800 268435456
large pool 1879048192 268435456
java pool 1610612736 268435456
streams pool 536870912 268435456
DEFAULT buffer cache 63350767616 268435456
--//實際上與GRANULE_SIZE相關,N*GRANULE_SIZE- Fixed Size 剩下的就是Redo Buffers.
--//268435456-2261968 = 266173488 與Redo Buffers= 171487232 還是存在很大差異.在測試環境探究看看.
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
2.探究:
SYS@book> show sga
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
--//注:我採用手工分配記憶體,基本各個buffer是連續的.
SYS@book> select component,current_size,granule_size from v$sga_dynamic_components where current_size != 0;
COMPONENT CURRENT_SIZE GRANULE_SIZE
-------------------------------- ------------ ------------
shared pool 180355072 4194304
large pool 12582912 4194304
java pool 12582912 4194304
DEFAULT buffer cache 427819008 4194304
--//GRANULE_SIZE = 4M.
SYS@book> @ memalloc
MIN(BASEADDR) MAX(BASEADDR) GRANULES MB GRANFLAGS COMPONENT GRANSTATE
---------------- ---------------- ---------- ---------- ---------- -------------------------------- ----------------
0000000060C00000 000000007A000000 102 408 4 DEFAULT buffer cache ALLOC
000000007A400000 000000007AC00000 3 12 4 java pool ALLOC
000000007B000000 000000007B800000 3 12 4 large pool ALLOC
000000007BC00000 0000000086400000 43 172 4 shared pool ALLOC
press enter .....
--//注意我僅僅貼出顯示上部分，這樣查詢是有問題的，但是我是全手工分配各個快取池的。也就是記憶體區域是連續的，不存在問題。
--//如果是動態分配，第2部分顯示會出現一些交錯。
--//看看public和private redo的使用空間以及分配情況:
SYS@book> @ imu
INDX FIRST_BUF_KCRFA LAST_BUF_KCRFA NXTBUFADR NXTBUF# B/buf STATE STRAND# STRADR STRIDX STRSPC TXN TOTBUFS# STRSZ
---------- ---------------- ---------------- ---------------- ---------- ---------- ---------- ---------- ---------------- ---------- ---------- ---------- ---------- ----------
0 0000000060227000 0000000060590E00 000000006022DC00 53 0 0 3735928559 00 0 0 0 6992 3579904
1 0000000060591000 00000000608FAE00 0000000060593A00 20 0 0 3735928559 00 0 0 0 6992 3579904
--// 以上2個是公有redo。3579904 * 2 = 7159808, redo buffers=7487488,相差 7487488-7159808 = 327680, 327680/1024 = 320K.
--// 0x60590E00-0x60227000 = 3579392, 3579904-3579392 = 512 .如果有日誌寫入，NXTBUFADR每次會變化。
--// 0000000060590E00是最後一個log buffer的開頭加上512就是此log buffer的大小.
--// 0x0000000060591000-0x0000000060590E00-0x200 = 0
2 0000000081E27000 00 00 0 0 0 3735928559 0000000081E27054 3735928559 126464 0 249 132096
3 0000000081E49000 00 00 0 0 0 3735928559 0000000081E49054 3735928559 126464 1 249 132096
4 0000000081E6A000 00 00 0 0 0 3735928559 0000000081E6A054 3735928559 126464 2 249 132096
5 0000000081E8B000 00 00 0 0 0 3735928559 0000000081E8B054 3735928559 126464 3 249 132096
6 0000000081EAC000 00 00 0 0 0 3735928559 0000000081EAC054 3735928559 126464 4 249 132096
7 0000000081ECE000 00 00 0 0 0 3735928559 0000000081ECE054 3735928559 126464 5 249 132096
8 0000000081EEF000 00 00 0 0 0 3735928559 0000000081EEF054 3735928559 126464 6 249 132096
9 0000000081F10000 00 00 0 0 0 3735928559 0000000081F10054 3735928559 126464 7 249 132096
10 0000000081F31000 00 00 0 0 0 3735928559 0000000081F31054 3735928559 126464 8 249 132096
11 0000000081F53000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
12 0000000081F74000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
13 0000000081F95000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
14 0000000081FB6000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
15 0000000081FD8000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
16 0000000081835000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
17 0000000081856000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
18 0000000081877000 00 00 0 0 0 3735928559 00 0 0 0 249 132096
19 rows selected.
SYS@book> @ fcha 0000000081E27000
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000081C34000 1 1 permanent memor 3949936 perm 0 00
SYS@book> @ fcha 0000000081E49000
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000081C34000 1 1 permanent memor 3949936 perm 0 00
SYS@book> @ fcha 0000000081877000
LOC KSMCHPTR KSMCHIDX KSMCHDUR KSMCHCOM KSMCHSIZ KSMCHCLS KSMCHTYP KSMCHPAR
--- ---------------- ---------- ---------- ---------------- ---------- -------- ---------- ----------------
SGA 0000000081834000 1 1 permanent memor 3919464 perm 0 00
--//應該在0x000000007BC00000 - 0x0000000086400000的shared pool區域。
--//而私有redo在共享池區域之中。
3.看看各個共享記憶體段:
$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 30212098 oracle 640 12582912 27
0x00000000 30244867 oracle 640 633339904 27
0xe8a8ec10 30277636 oracle 640 2097152 27
--//注:我不知道ipcs如何看各個記憶體段的首地址.我測試環境僅僅1個例項並且僅僅資料庫使用共享記憶體段.
$ ps -ef | grep smo[n]
oracle 9407 1 0 Jan07 ? 00:01:39 ora_smon_book
$ cat /proc/9407/maps | grep SYSV
60000000-60c00000 rw-s 00000000 00:0b 30212098 /SYSV00000000 (deleted)
~~~~~~~~~~~~~~~~~~~~
60c00000-86800000 rw-s 00000000 00:0b 30244867 /SYSV00000000 (deleted)
86800000-86a00000 rw-s 00000000 00:0b 30277636 /SYSVe8a8ec10 (deleted)
--//可以看到3個共享記憶體段.一般不會設定核心引數太小.
--//60000000-60c00000 = -12582912
--//60c00000-86800000 = -633339904
--//86800000-86a00000 = -2097152
--//大小都可以與上面的ipcs顯示對上.
--//從記憶體段地址60000000-60c00000看,就是log_buufer使用的空間.Fixed Size也在整個段內.
--//ipcs顯示的第2部分對應的就是memalloc指令碼顯示的部分.另外說明如果核心引數設定不合理,可能ipcs這裡會顯示很多行.
--//順便看看IMU的記憶體分配以及空間使用.
SELECT ktifpno
,ktifpxcb tx_addr
,ktifpupb undo_start
,ktifpupc undo_cur
, TO_NUMBER (ktifpupc, 'XXXXXXXXXXXXXXXX')
- TO_NUMBER (ktifpupb, 'XXXXXXXXXXXXXXXX')
undo_usage
,ktifprpb redo_start
,ktifprpc redo_cur
, TO_NUMBER (ktifprpc, 'XXXXXXXXXXXXXXXX')
- TO_NUMBER (ktifprpb, 'XXXXXXXXXXXXXXXX')
redo_usage
,ktifptxflg
FROM x$ktifp;
SYS@book> @ imuy
KTIFPNO TX_ADDR UNDO_START UNDO_CUR UNDO_USAGE REDO_START REDO_CUR REDO_USAGE KTIFPTXFLG
---------- ---------------- ---------------- ---------------- ---------- ---------------- ---------------- ---------- ----------
0 00 0000000081B12C00 0000000081B12C00 0 00 00 0 7
1 00 0000000081B23E00 0000000081B23E00 0 00 00 0 7
2 00 0000000081B35200 0000000081B35200 0 00 00 0 7
3 00 0000000081B46400 0000000081B46400 0 00 00 0 7
4 00 0000000081B57800 0000000081B57800 0 00 00 0 7
5 00 0000000081B68C00 0000000081B68C00 0 00 00 0 7
6 00 0000000081B79E00 0000000081B79E00 0 00 00 0 7
7 00 0000000081B8B200 0000000081B8B200 0 00 00 0 7
8 00 0000000081B9C400 0000000081B9C400 0 00 00 0 7
9 00 0000000081BAD800 0000000081BAD800 0 00 00 0 0
10 00 0000000081BBEC00 0000000081BBEC00 0 00 00 0 0
11 00 0000000081BCFE00 0000000081BCFE00 0 00 00 0 0
12 00 0000000081BE1200 0000000081BE1200 0 00 00 0 0
13 00 0000000081435600 0000000081435600 0 00 00 0 0
14 00 0000000081446A00 0000000081446A00 0 00 00 0 0
15 00 0000000081457C00 0000000081457C00 0 00 00 0 0
16 00 0000000081469000 0000000081469000 0 00 00 0 0
17 00 000000008147A200 000000008147A200 0 00 00 0 0
18 00 000000008148B600 000000008148B600 0 00 00 0 0
19 00 000000008149CA00 000000008149CA00 0 00 00 0 0
20 00 00000000814ADC00 00000000814ADC00 0 00 00 0 0
21 00 00000000814BF000 00000000814BF000 0 00 00 0 0
22 00 00000000814D0200 00000000814D0200 0 00 00 0 0
23 00 00000000814E1600 00000000814E1600 0 00 00 0 0
24 00 00000000814F2800 00000000814F2800 0 00 00 0 0
25 00 0000000081503C00 0000000081503C00 0 00 00 0 0
26 00 0000000081515000 0000000081515000 0 00 00 0 0
27 00 0000000081526200 0000000081526200 0 00 00 0 0
28 00 0000000081537600 0000000081537600 0 00 00 0 0
29 00 0000000081548800 0000000081548800 0 00 00 0 0
30 00 0000000081559C00 0000000081559C00 0 00 00 0 0
31 00 000000008156B000 000000008156B000 0 00 00 0 0
32 00 000000008157C200 000000008157C200 0 00 00 0 0
33 00 000000008158D600 000000008158D600 0 00 00 0 0
34 00 000000008159E800 000000008159E800 0 00 00 0 0
35 00 00000000815AFC00 00000000815AFC00 0 00 00 0 0
36 rows selected.
--//數量36 ,與引數transactions有關,一般transactions/10.我的測試環境transactions=369.
--//可以確定這部分割槽域位於shared pool.
4.這樣就可以知道為什麼log_buffer的設定:
Fixed Size 2261968 bytes
Redo Buffers 7487488 bytes
--//(2261968+7487488)/4/1024/1024 = 2.324451446533203125
$ cat /proc/9407/maps | grep SYSV
60000000-60c00000 rw-s 00000000 00:0b 30212098 /SYSV00000000 (deleted)
~~~~~~~~~~~~~~~~~~~~
60c00000-86800000 rw-s 00000000 00:0b 30244867 /SYSV00000000 (deleted)
86800000-86a00000 rw-s 00000000 00:0b 30277636 /SYSVe8a8ec10 (deleted)
--//這就是為什麼第1個段佔用12M.不知道里面的0.68X4M的記憶體用來做什麼.那位知道.
5.附上指令碼:
$ cat memalloc.sql
col component format a32
select min(BASEADDR), max(BASEADDR), count(1) Granules, sum(a.gransize)/1048576 MB, a.GRANFLAGS, component, a.GRANSTATE
from x$ksmge a, x$kmgsct b
where a.grantype = b.grantype (+)
group by a.GRANFLAGS, component, a.GRANSTATE
order by 1,2;
pause press enter .....
select a.BASEADDR, a.gransize, a.GRANFLAGS, b.component, a.GRANSTATE
from x$ksmge a, x$kmgsct b
where a.grantype = b.grantype (+)
order by 1,2;
$ cat imu.sql
SELECT INDX
,FIRST_BUF_KCRFA
,last_buf_kcrfa
,PNEXT_BUF_KCRFA_CLN nxtbufadr
,NEXT_BUF_NUM_KCRFA_CLN nxtbuf#
,BYTES_IN_BUF_KCRFA_CLN "B/buf"
,PVT_STRAND_STATE_KCRFA_CLN state
,STRAND_NUM_ORDINAL_KCRFA_CLN strand#
,PTR_KCRF_PVT_STRAND stradr
,INDEX_KCRF_PVT_STRAND stridx
,SPACE_KCRF_PVT_STRAND strspc
,TXN_KCRF_PVT_STRAND txn
,TOTAL_BUFS_KCRFA totbufs#
,STRAND_SIZE_KCRFA strsz
FROM X$KCRFSTRAND ;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2752917/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210126]探究oracle記憶體分配3.txtOracle記憶體
- [20210126]探究oracle記憶體分配4.txtOracle記憶體
- [20191114]linux記憶體分配的討論.txtLinux記憶體
- Pytorch訓練時視訊記憶體分配過程探究PyTorch記憶體
- 垃圾收集器與記憶體分配策略_記憶體分配策略記憶體
- 動態記憶體分配記憶體
- [20190202]使用smem查詢oracle記憶體使用.txtOracle記憶體
- 記憶體的分配與釋放，記憶體洩漏記憶體
- java-方法記憶體分配Java記憶體
- go記憶體分配器Go記憶體
- java基礎-記憶體分配Java記憶體
- hadoop 記憶體分配規則Hadoop記憶體
- C語言-記憶體分配C語言記憶體
- 記憶體分配策略學習記憶體
- 深度理解glibc記憶體分配記憶體
- linux記憶體管理（一）實體記憶體的組織和記憶體分配Linux記憶體
- [20191115]oracle例項佔用記憶體計算.txtOracle記憶體
- 探究 iOS 記憶體問題iOS記憶體
- [20220321]探究oracle sequence.txtOracle
- 【Java】 記憶體分配全面淺析Java記憶體
- JVM GC 與 記憶體分配策略JVMGC記憶體
- C++動態記憶體分配C++記憶體
- 記憶體分配問題處理記憶體
- mimalloc記憶體分配程式碼分析記憶體
- C語言的記憶體分配C語言記憶體
- C中的記憶體分配模型記憶體模型
- Python如何管理記憶體？記憶體分配機制是什麼？Python記憶體
- jvm：記憶體模型、記憶體分配及GC垃圾回收機制JVM記憶體模型GC
- java陣列記憶體的探究Java陣列記憶體
- [20220322]探究oracle sequence 2.txtOracle
- MySQL記憶體管理，記憶體分配器和作業系統MySql記憶體作業系統
- 圖解Go語言記憶體分配圖解Go記憶體
- Android O 8.0 以上 bitmap記憶體分配Android記憶體
- v8記憶體分配淺談記憶體
- curl 中減少記憶體分配操作記憶體
- JVM 之 記憶體分配與回收策略JVM記憶體
- 深入理解golang：記憶體分配原理Golang記憶體
- Netty 中的記憶體分配淺析Netty記憶體