数据迁移后性能受到影响，需要将老数据库中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';
转载于:https://www.cnblogs.com/Clark-cloud-database/p/7813675.html
