[20210126]探究oracle記憶體分配.txt

lfree發表於2021-01-26

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章