X$KSMSP

最新推荐文章于 2021-04-08 02:02:45 发布

gengwei80

最新推荐文章于 2021-04-08 02:02:45 发布

阅读量643

点赞数

分类专栏： Oracle 文章标签： class sql

本文链接：https://blog.csdn.net/gengwei80/article/details/7675534

版权

Oracle 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

X$KSMSP: kernal Storage Memory Sga Hea P

SQL> desc x$ksmsp;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
ADDR                                               RAW(4)
INDX                                               NUMBER
INST_ID                                            NUMBER
KSMCHIDX                                           NUMBER
KSMCHDUR                                           NUMBER
KSMCHCOM                                           VARCHAR2(16)
KSMCHPTR                                           RAW(4)
KSMCHSIZ                                           NUMBER
KSMCHCLS                                           VARCHAR2(8)
KSMCHTYP                                           NUMBER
KSMCHPAR                                           RAW(4)

每一行代表shared pool 中的一个chunk

KSMCHSIZ ksmsp chunk size 表示每个chunk的大小 16---3981312

KSMCHCLS ksmsp chunk class 表示类型，主要有4类:

free ： free chunks 表示可以自由分配

recr ： recreatable chunks 可以被重新创建的chunks

freeable ：可以被释放的chunks

perm ：永久包含的对象

查看一下子池使用及内存情况

SELECT 'shared pool (' || NVL (DECODE (TO_CHAR (ksmdsidx), '0', '0 - Unused', ksmdsidx),'Total') || '):' subpool, SUM (ksmsslen) BYTES, ROUND (SUM (ksmsslen)/ 1048576, 2) mb FROM x$ksmss WHERE ksmsslen > 0
GROUP BY ROLLUP (ksmdsidx) ORDER BY subpool ASC
/

查询一下各个子池的剩余内存

SELECT     ksmchidx "SubPool", 'sga heap(' || ksmchidx  || ',0)' sga_heap,
              ksmchcom chunkcomment,
              DECODE (    ROUND (ksmchsiz / 1000),
                         0, '0-1K', 1, '1-2K',2,  '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',
                          6, '6-7k', 7, '7-8k',8,  '8-9k',9, '9-10k', '> 10K'
                           ) "size",
           COUNT (*), ksmchcls status, SUM (ksmchsiz)  BYTES
        FROM x$ksmsp WHERE ksmchcom = 'free memory'
    GROUP BY ksmchidx, ksmchcls, 'sga heap(' || ksmchidx ||  ',0)',ksmchcom, ksmchcls,
          DECODE (    ROUND (ksmchsiz / 1000),
                         0, '0-1K', 1, '1-2K',2,  '2-3K',3, '3-4K',4, '4-5K', 5, '5-6k',
                         6, '6-7k', 7, '7-8k',8,  '8-9k',9, '9-10k', '> 10K' );