Oracle调优中常用表KEEP到内存中的示例分析

本篇文章为大家展示了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-um_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;

上述内容就是Oracle调优中常用表KEEP到内存中的示例分析，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注行业资讯频道。