共享池碎片化分析脚本

共享池逐渐碎片化是正常现象，oracle有自动合并内存的机制来解决碎片化，如果这个机制解决不了问题，那么考虑业务少的时候刷新共享池（alter system flush shared_pool;）或重启实例。
SQL> set line 200
SQL> col sga_heap format a15
SQL> col size format a10
SQL> select KSMCHIDX "SubPool", 'sga heap('||KSMCHIDX||',0)'sga_heap,ksmchcom ChunkComment,
  2  decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
  3  4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
  4  '8-9k', 9,'9-10k','> 10K') "size",
  5  count(*),ksmchcls Status, sum(ksmchsiz) Bytes
  6  from x$ksmsp
  7  where KSMCHCOM = 'free memory'
  8  group by ksmchidx, ksmchcls,
  9  'sga heap('||KSMCHIDX||',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
 10  1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
 11  '6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');

   SubPool SGA_HEAP        CHUNKCOMMENT     size         COUNT(*) STATUS        BYTES
---------- --------------- ---------------- ---------- ---------- -------- ----------
         1 sga heap(1,0)   free memory      7-8k                7 free          48472
         1 sga heap(1,0)   free memory      3-4K               69 free         210672
         1 sga heap(1,0)   free memory      > 10K               7 R-free      3308616
         1 sga heap(1,0)   free memory      > 10K              78 free        7297704
         1 sga heap(1,0)   free memory      4-5K              172 free         687552
         1 sga heap(1,0)   free memory      8-9k               46 free         376800
         1 sga heap(1,0)   free memory      6-7k               12 free          73184
         1 sga heap(1,0)   free memory      5-6k               13 free          64696
         1 sga heap(1,0)   free memory      2-3K              158 free         300944
         1 sga heap(1,0)   free memory      5-6k                1 R-free         5056
         1 sga heap(1,0)   free memory      4-5K                4 R-free        15520

   SubPool SGA_HEAP        CHUNKCOMMENT     size         COUNT(*) STATUS        BYTES
---------- --------------- ---------------- ---------- ---------- -------- ----------
         1 sga heap(1,0)   free memory      0-1K             1090 free         133008
         1 sga heap(1,0)   free memory      9-10k               2 free          17688
         1 sga heap(1,0)   free memory      1-2K              115 free         128720
         1 sga heap(1,0)   free memory      6-7k                9 R-free        52984

15 rows selected.
SQL> set line 100
SQL> select ksppstvl from x$ksppsv where indx=( select indx from x$ksppi where   upper(ksppinm) = '_SHARED_POOL_RESERVED_MIN_ALLOC');

KSPPSTVL
----------------------------------------------------------------------------------------------------
4400

SQL> SELECT free_space, avg_free_size,used_space, avg_used_size, request_failures, last_failure_size FROM v$shared_pool_reserved; 

FREE_SPACE AVG_FREE_SIZE USED_SPACE AVG_USED_SIZE REQUEST_FAILURES LAST_FAILURE_SIZE
---------- ------------- ---------- ------------- ---------------- -----------------
    932760    22208.5714   28436712    677064.571                7             16416
如果：REQUEST_FAILURES > 0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC 
或者  REQUEST_FAILURES 等于0 并且 LAST_FAILURE_SIZE < SHARED_POOL_RESERVED_MIN_ALLOC
这时候就需要去调整了