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

来源：这里教程网时间：2026-03-03 16:24:02 作者：

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

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推