[20210126]探究oracle記憶體分配4.txt
[20210126]探究oracle記憶體分配4.txt
--//昨天簡單做了oracle記憶體分配的探究.我提到log_buffer基本不用設定.基本就是show sga看到的
--// (Fixed Size+Redo Buffers)/ GRANULE_SIZE取整N,然後設定分配N*GRANULE_SIZE大小的共享記憶體段內,至少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> show sga
Total System Global Area 643084288 bytes
Fixed Size 2255872 bytes
Variable Size 205521920 bytes
Database Buffers 427819008 bytes
Redo Buffers 7487488 bytes
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
--(Fixed Size+Redo Buffers)/ GRANULE_SIZE
--// (2255872+7487488)/4/1024/1024 = 2.322998046875,實際佔用3*GRANULE_SIZE=12M.
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 .....
$ cat /proc/$(ps -ef | grep smo[n]| awk '{print $2}')/maps | grep SYSV
60000000-60c00000 rw-s 00000000 00:0b 30736386 /SYSV00000000 (deleted)
60c00000-86800000 rw-s 00000000 00:0b 30769155 /SYSV00000000 (deleted)
86800000-86a00000 rw-s 00000000 00:0b 30801924 /SYSVe8a8ec10 (deleted)
$ ipcs -m
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x00000000 30736386 oracle 640 12582912 25
0x00000000 30769155 oracle 640 633339904 25
0xe8a8ec10 30801924 oracle 640 2097152 25
--//你如何知道Fixed Size在段60000000-60c00000呢?
2.看看資料庫scn在哪裡?
SYS@book> oradebug setmypid
Statement processed.
SYS@book> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [06001AE70, 06001AEA0) = 197DF079 00000003 00000000 00000000 000102D6 00000000 00000000 00000000 00000000 00000000 6001AB50 00000000
--//可以發現在60000000-60c00000段內.
SYS@book> select * from V$FIXED_TABLE where name like 'X$%MEM%';
NAME OBJECT_ID TYPE TABLE_NUM
-------------- ---------- ------ ---------
X$KSMMEM 4294951147 TABLE 71
X$KGLMEM 4294951885 TABLE 86
X$KGLJMEM 4294951997 TABLE 87
X$KSMSGMEM 4294952475 TABLE 88
X$KMGSBSMEMADV 4294952877 TABLE 226
X$QESRCMEM 4294952788 TABLE 601
6 rows selected.
SYS@book> set verify off
SYS@book> @ tpt/fv2 kcsgscn_
Display Fixed SGA Variables matching kcsgscn_
ADDR INDX SGAVARNAME KSMMVAL_DEC KSMMMVAL DATATYPE KSMFSADR KSMFSSIZ
---------------- ---------- ---------- ----------- ---------------- ------------------------- ---------------- ----------
0000000009B159E0 3260 kcsgscn_ 13312586897 00000003197DF491 kcslf 000000006001AE70 48
SYS@book> select * from X$KSMMEM where addr=hextoraw('000000006001AE70');
ADDR INDX INST_ID KSMMMVAL
---------------- ---------- ---------- ----------------
000000006001AE70 13774 1 00000003197DF535
--//你可以查詢x$ksmfsv獲得對應名字. Tanel Poder的工具包裡面fv開頭的檔案指令碼都可以使用.
--//addr地址落在60000000-60c00000.
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 00000000604D0400 5449 0 0 3735928559 00 0 0 0 6992 3579904
1 0000000060591000 00000000608FAE00 0000000060599600 66 0 0 3735928559 00 0 0 0 6992 3579904
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 00 0 0 0 249 132096
10 0000000081F31000 00 00 0 0 0 3735928559 00 0 0 0 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.
--//轉儲0000000060000000-0x0000000060227000的內容
--//0x60227000-0x60000000 = 2256896,比上面看到的Fixed Size=2255872大.
--//2256896-2255872 = 4132
--//不貼出這部分內容了.
--//0x00000000608FAE00+0x200 = 1620029440 = 0x608fb000
--//60c00000-608fb000 = 3166208
3.探究未知的區域:
--//0x00000000608fb000-0x0000000060c00000 就是我前面提到未知的區域.
SELECT X$KSMMEM.*
FROM X$KSMMEM
WHERE addr BETWEEN HEXTORAW ('00000000608fb000')
AND HEXTORAW ('0000000060c00000');
ADDR INDX INST_ID KSMMMVAL
---------------- ---------- ---------- ----------------
00000000608FB000 1177088 1 0000000060227000
00000000608FB008 1177089 1 0000000060227200
00000000608FB010 1177090 1 0000000060227400
00000000608FB018 1177091 1 0000000060227600
....
--//每個相差0x200就是512位元組,就是一個redo size的大小.第一條記錄記錄就是imu指令碼輸出的FIRST_BUF_KCRFA.以後每次遞增
--//0x200=512,這樣這個區域是可以算出來的.
--//TOTBUFS#=6992 , 看imu指令碼的輸出.
SELECT X$KSMMEM.*
FROM X$KSMMEM
WHERE addr BETWEEN HEXTORAW ('00000000608fb000')
AND HEXTORAW ('0000000060c00000')
AND ksmmmval <> '00'
AND ksmmmval IN (HEXTORAW ('0000000060227000')
,HEXTORAW ('0000000060590E00')
,HEXTORAW ('0000000060591000')
,HEXTORAW ('00000000608FAE00'));
ADDR INDX INST_ID KSMMMVAL
---------------- ---------- ---------- ----------------
00000000608FB000 1177088 1 0000000060227000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000060908A78 1184079 1 0000000060590E00
0000000060908A80 1184080 1 0000000060227000
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
00000000609164F8 1191071 1 0000000060590E00
0000000060916500 1191072 1 0000000060591000
0000000060923F78 1198063 1 00000000608FAE00
0000000060923F80 1198064 1 0000000060591000
00000000609319F8 1205055 1 00000000608FAE00
--//奇怪每個怎麼出現2次.
1184080-1177088= 6992
1191072-1184080= 6992
1198064-1191072= 6992
--//正好等於6992.不會是巧合吧,為什麼出現2次呢.我不知道.
--//6992*8*4 = 223744
SELECT X$KSMMEM.*
FROM X$KSMMEM
WHERE addr BETWEEN HEXTORAW ('0000000060931A00')
AND HEXTORAW ('0000000060c00000');
ADDR INDX INST_ID KSMMMVAL
---------------- ---------- ---------- ----------------
0000000060931A00 1205056 1 0000000000000002
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000060931A08 1205057 1 0000000300000000
0000000060931A10 1205058 1 0000000100000000
0000000060931A18 1205059 1 0000000000000004
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000060931A20 1205060 1 0000000500000002
0000000060931A28 1205061 1 0000000300000000
0000000060931A30 1205062 1 0000000000000006
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
0000000060931A38 1205063 1 0000000700000004
0000000060931A40 1205064 1 0000000500000000
0000000060931A48 1205065 1 0000000000000008
0000000060931A50 1205066 1 0000000900000006
0000000060931A58 1205067 1 0000000700000000
0000000060931A60 1205068 1 000000000000000A
0000000060931A68 1205069 1 0000000B00000008
...
select * from
(SELECT rownum id ,X$KSMMEM.*
FROM X$KSMMEM
WHERE addr BETWEEN HEXTORAW ('0000000060931A00')
AND HEXTORAW ('0000000060c00000'))
where mod(id,3)=1;
--//結果我不貼出了.
4.最後貼出指令碼:
--//Tanel Poder的工具包裡面fv開頭的檔案指令碼可以可以在他的站點找到下載.
$ 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 ;
$ cat tpt/fv2.sql
column fv_ksmfsnam heading SGAVARNAME for a50 wrap
column fv_ksmfstyp heading DATATYPE for a25 wrap
column fv_ksmmval_dec heading KSMMVAL_DEC for 99999999999999999999
prompt Display Fixed SGA Variables matching &1
select /*+ ORDERED USE_NL(m) */
f.addr
, f.indx
, f.ksmfsnam fv_ksmfsnam
, to_number(m.ksmmmval, 'XXXXXXXXXXXXXXXX') fv_ksmmval_dec
, m.ksmmmval
, f.ksmfstyp fv_ksmfstyp
, f.ksmfsadr
, f.ksmfssiz
from
x$ksmfsv f, x$ksmmem m
where
f.ksmfsadr = m.addr
and (lower(ksmfsnam) like lower('&1') or lower(ksmfstyp) like lower('&1'))
order by
ksmfsnam
/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2753605/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210126]探究oracle記憶體分配.txtOracle記憶體
- [20210126]探究oracle記憶體分配3.txtOracle記憶體
- Oracle記憶體分配與調整Oracle記憶體
- Oracle的記憶體分配和使用Oracle記憶體
- Oracle記憶體分配與使用(zt)Oracle記憶體
- oracle9i 的記憶體分配Oracle記憶體
- Oracle記憶體分配經驗法則Oracle記憶體
- Pytorch訓練時視訊記憶體分配過程探究PyTorch記憶體
- JavaScript記憶體分配JavaScript記憶體
- JVM記憶體分配JVM記憶體
- java記憶體分配Java記憶體
- 垃圾收集器與記憶體分配策略_記憶體分配策略記憶體
- oracle 記憶體分配和調優 總結Oracle記憶體
- _ksmg_granule_size oracle記憶體分配粒度Oracle記憶體
- Oracle的記憶體的分配、回收[final]Oracle記憶體
- JVM 記憶體模型 記憶體分配，JVM鎖JVM記憶體模型
- 探索iOS記憶體分配iOS記憶體
- Java 記憶體分配策略Java記憶體
- java jvm 記憶體分配JavaJVM記憶體
- 動態記憶體分配記憶體
- [C++]記憶體分配C++記憶體
- oracle資料庫記憶體分配（sga和pga）Oracle資料庫記憶體
- 【SGA】【PGA】普適的Oracle記憶體分配策略Oracle記憶體
- 探究 iOS 記憶體問題iOS記憶體
- 記憶體的分配與釋放，記憶體洩漏記憶體
- C語言-記憶體管理之一[記憶體分配]C語言記憶體
- java基礎-記憶體分配Java記憶體
- C語言-記憶體分配C語言記憶體
- java-方法記憶體分配Java記憶體
- go記憶體分配器Go記憶體
- Java 堆疊記憶體分配Java記憶體
- 記憶體分配策略學習記憶體
- 記憶體分配的確定記憶體
- weblogic的記憶體分配Web記憶體
- 記憶體分配方式 (轉)記憶體
- 簡單理解動態記憶體分配和靜態記憶體分配的區別記憶體
- java陣列記憶體的探究Java陣列記憶體
- hadoop 記憶體分配規則Hadoop記憶體