Oracle調優-常用表KEEP到記憶體中

資料遷移後效能受到影響，需要將老資料庫中keep到記憶體中的表在新庫中keep到記憶體中，使用如下方法。

新庫設定db_keep_cache_size為適當值，這個值的大小不能小於需要keep的表的大小。

檢視老庫中需要keep的表資訊：
select s.owner,
       s.segment_name,
       s.partition_name,
       s.bytes / 1024 / 1024 as "size(m)"
  from dba_segments s
 where owner = 'XXX'
   and segment_name in
       (select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')
 order by 4 desc


查詢老庫中需要keep表總大小：
select sum(s.bytes / 1024 / 1024 / 1024) as "total keep size(G)"
  from dba_segments s
 where segment_name in
       (select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')


生成keep指令碼：   
select 'alter table XXX.'||s.segment_name||' storage(buffer_pool keep);' as "指令碼"
  from dba_segments s
 where owner = 'XXX'
   and segment_name in
       (select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')
 
keep表到記憶體中：
select 'alter table XXX.'||s.segment_name||' cache;' as "指令碼2"
  from dba_segments s
 where owner = 'XXX'
   and segment_name in
       (select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')
  
上兩個指令碼整合：
select 'alter table XXX.'||s.segment_name||' storage(buffer_pool keep) cache;' as "指令碼"
  from dba_segments s
 where owner = 'XXX'
   and segment_name in
       (select table_name from dba_tables where owner = 'XXX' and buffer_pool='KEEP')

上述三個指令碼輸出結果放在plsql中執行即可。

附錄：與cache到記憶體相關的命令
--表快取 
alter table ..... storage(buffer_pool keep);  
--檢視哪些表被放在快取區 但並不意味著該表已經被快取 
select table_name from dba_tables where buffer_pool='keep';
--查詢到該表是否已經被快取 
select table_name,cache,buffer_pool from user_TABLES where cache like '%Y';
--已經加入到KEEP區的表想要移出快取，使用 
alter table table_name nocache;  
--查詢當前使用者下表的情況 
select table_name,cache,buffer_pool from user_TABLES;  
--對於普通LOB型別的segment的cache方法 
alter table t2 modify lob(c2) (storage (buffer_pool keep) cache);  
--取消快取 
alter table test modify lob(address) (storage (buffer_pool keep) nocache);  
--查詢段 
select segment_name,segment_type,buffer_pool from user_segments;  
--對基於CLOB型別的物件的cache方法   
alter table lob1 modify lob(c1.xmldata) (storage (buffer_pool keep) cache);   
--查詢該使用者下所有表內的大欄位情況 
select column_name,segment_name from user_lobs;   
--取消表快取
alter table XXX storage(buffer_pool default);



ps：
檢視keep空間的剩餘大小：
select p.name,a.cnum_repl "total buffers",a.anum_repl "free buffers" 
      from x$kcbwds a, v$buffer_pool p
      where a.set_id=p.LO_SETID and p.name='KEEP'; 


檢視keep空間的大小：
select component,current_size from v$sga_dynamic_components
    where component='KEEP buffer cache';