[20170406]查询占用内存情况.txt

[20170406]查询占用内存情况.txt

--//昨天看了链接,http://www.dbaqhs.com/archives/35,发现脚本不对,显示KEEP buffer cache.
--//google找到如下脚本:

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
/

MIN(BASEADDR)    MAX(BASEADDR)      GRANULES         MB  GRANFLAGS COMPONENT                        GRANSTATE
---------------- ---------------- ---------- ---------- ---------- -------------------------------- ----------------
0000000060C00000 000000007A000000        102        408          4 DEFAULT buffer cache             ALLOC
000000007A400000 000000007A400000          1          4          4 java pool                        ALLOC
000000007A800000 000000007B000000          3         12          4 large pool                       ALLOC
000000007B400000 0000000085C00000         43        172          4 shared pool                      ALLOC

--//注上面的MAX(BASEADDR)不是某种类型的边界,而是最大的BASEADDR ,在加上gransize才是边界,
--//看COMPONENT='java pool',那行就很容易理解.

--//gransize=4*1024*1024=4194304
--//4194304 = 0x400000
--//0x7A400000+0x400000= 0x7A800000.

SYS@book> select GRANTYPE,COMPONENT from x$kmgsct;
  GRANTYPE COMPONENT
---------- --------------------------------
         1 shared pool
         2 large pool
         3 java pool
         4 streams pool
         5 SGA Target
         7 DEFAULT buffer cache
         8 KEEP buffer cache
         9 RECYCLE buffer cache
        10 DEFAULT 2K buffer cache
        11 DEFAULT 4K buffer cache
        12 DEFAULT 8K buffer cache
        13 DEFAULT 16K buffer cache
        14 DEFAULT 32K buffer cache
        15 Shared IO Pool
        16 PGA Target
        17 ASM Buffer Cache
16 rows selected.

--//按照上面的例子很容易自己写一个:
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
/

--//另外注意这些内存不一定是连续的,如果你使用自动内存管理就会出现.