診斷shared pool常用命令

----------查詢各個子池的記憶體分配情況
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 KSMCHCLS CLASS,
COUNT (KSMCHCLS) NUM,
         SUM (KSMCHSIZ) SIZ,
         TO_CHAR ( ( (SUM (KSMCHSIZ) / COUNT (KSMCHCLS) / 1024)), '999,999.00')
         || 'k'
            "AVG SIZE"
    FROM X$KSMSP
GROUP BY KSMCHCLS;

---------檢視各個子池更加詳細的記憶體剩餘情況 
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') order by 1,2;