ITPub博客

首页 > 数据库 > Oracle > [20210126]探究oracle内存分配.txt

[20210126]探究oracle内存分配.txt

原创 Oracle 作者:lfree 时间:2021-01-26 10:32:08 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2855
  • 访问量
    6643913