ITPub博客

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

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

原创 Oracle 作者:lfree 时间:2021-01-27 11:52:48 0 删除 编辑

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

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

注册时间:2008-01-03

  • 博文量
    2855
  • 访问量
    6643923