您现在的位置是:主页 > 数据库技术 > 数据库技术

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

IDCBT2021-12-28服务器技术人已围观

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

标签:

很赞哦! ()

本栏推荐