Oracle -----SHARED_POOL---- 常用查询

show parameter shared

---检查整体命中率(library cache)
select sum(pinhits) get ,
       sum(pins)-sum(pinhits) miss,
       sum(pinhits) / sum(pins)
  from v$librarycache;

-- 查看library cache 命中率（分类）

   select t.NAMESPACE,t.GETHITRATIO*100
    from v$librarycache t;

     select sum(pins) "hits",
            sum(reloads) "misses",
            sum(pins) / (sum(pins) + sum(reloads)) "Hits Ratio"
       from v$librarycache;
    
---检查shered pool  free  space
SELECT * 
  FROM V$SGASTAT
WHERE NAME = 'free memory'
   AND POOL = 'shared pool';

---检查row cache(数据字典缓冲区)命中率
---当执行一个dml或ddl都会造成对数据字典的递归修改
     column updates format 999,999,999
     SELECT parameter
                  , sum(gets)
                  , sum(getmisses)
                 , 100*sum(gets - getmisses) / sum(gets)  pct_succ_gets
                  , sum(modifications)                     updates
             FROM V$ROWCACHE
              WHERE gets > 0
             GROUP BY parameter;

  SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE"
    FROM V$ROWCACHE;

---查看Shared pool latch(多池技术)
  
     /*col parameter for a20
     col session for a20*/
       select a.ksppinm "Parameter",
             b.ksppstvl "Session Value",
             c.ksppstvl "Instance Value"
             from sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c
             where a.indx = b.indx and a.indx = c.indx
             and a.ksppinm = '_kghdsidx_count';
          
---查看shared pool建议
   column c1     heading 'Pool |Size(M)'
   column c2     heading 'Size|Factor'
   column c3     heading 'Est|LC(M)  '
   column c4     heading 'Est LC|Mem. Obj.'
   column c5     heading 'Est|Time|Saved|(sec)'
   column c6     heading 'Est|Parse|Saved|Factor'
   column c7     heading 'Est|Object Hits'   format 999,999,999
   SELECT shared_pool_size_for_estimate c1,
          shared_pool_size_factor c2,
          estd_lc_size c3,
          estd_lc_memory_objects c4,
          estd_lc_time_saved c5,
          estd_lc_time_saved_factor c6,
          to_char(estd_lc_memory_object_hits, 99999999999) c7
     FROM V$SHARED_POOL_ADVICE;     

--查看shared pool中 各种类型的chunk的大小数量    
  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 sid, seq#, event, p1, p1raw, p2, p2raw, p3, p3raw, state
       from v$session_wait
     where event like 'library%';   
       
--row cache命中率
SELECT 'Dictionary Cache Hit Ratio ' "Ratio",
       ROUND((1 - (SUM(GETMISSES) / SUM(GETS))) * 100, 2) || '%' "Percentage"
  FROM V$ROWCACHE;

---library cache中详细比率信息
     SELECT 'Library Lock Requests' "Ratio",
            ROUND(AVG(gethitratio) * 100, 2) || '%' "Percentage"
       FROM V$LIBRARYCACHE
     UNION all
     SELECT 'Library Pin Requests' "Ratio",
            ROUND(AVG(pinhitratio) * 100, 2) || '%' "Percentage"
       FROM V$LIBRARYCACHE
     UNION all
     SELECT 'Library I/O Reloads' "Ratio",
            ROUND((SUM(reloads) / SUM(pins)) * 100, 2) || '%' "Percentage"
       FROM V$LIBRARYCACHE ;
    
  
--查看library cache 内存分配情况(对哪类对象)
     SELECT lc_namespace               "Library",
       LC_INUSE_MEMORY_OBJECTS    "Objects",
       LC_INUSE_MEMORY_SIZE       "Objects Mb",
       LC_FREEABLE_MEMORY_OBJECTS "Freeable Objects",
       LC_FREEABLE_MEMORY_SIZE    "Freeable Mb"
  FROM v$library_cache_memory;    

---查看使用shard_pool保留池情况
     SELECT request_misses, request_failures, free_space
     FROM v$shared_pool_reserved;
  
  
  
---查看cache中所有pool,命中情况
     COL pool FORMAT a10;
     SELECT a.name "Pool", a.physical_reads, a.db_block_gets
     , a.consistent_gets
     ,(SELECT ROUND((1-(physical_reads / (db_block_gets + consistent_gets)))*100)
     FROM v$buffer_pool_statistics
     WHERE db_block_gets+consistent_gets != 0
     AND name = a.name) "Ratio"
     FROM v$buffer_pool_statistics a;
  
  
     相关命令
     --- alter table xx cache
     ---ALTER TABLE(INDEX) xx STORAGE(BUFFER_POOL KEEP);
     ---取消cache或keep(keep pool)
     ---ALTER TABLE XX NOCACHE;
     ---SELECT 'ALTER INDEX '||index_name||' STORAGE(BUFFER_POOL DEFAULT);'
     ---FROM USER_INDEXES WHERE BUFFER_POOL!='DEFAULT';