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

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

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

编辑推荐：

返回列表

[20210126]探究oracle内存分配4.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：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推