您好,登錄后才能下訂單哦!
本篇文章為大家展示了Oracle調(diào)優(yōu)中常用表KEEP到內(nèi)存中的示例分析,內(nèi)容簡明扼要并且容易理解,絕對能使你眼前一亮,通過這篇文章的詳細介紹希望你能有所收獲。
數(shù)據(jù)遷移后性能受到影響,需要將老數(shù)據(jù)庫中keep到內(nèi)存中的表在新庫中keep到內(nèi)存中,使用如下方法。
新庫設(shè)置db_keep_cache_size為適當(dāng)值,這個值的大小不能小于需要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表總大?。?br/>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表到內(nèi)存中:
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')
上述三個腳本輸出結(jié)果放在plsql中執(zhí)行即可。
附錄:與cache到內(nèi)存相關(guān)的命令
--表緩存
alter table ..... storage(buffer_pool keep);
--查看哪些表被放在緩存區(qū) 但并不意味著該表已經(jīng)被緩存
select table_name from dba_tables where buffer_pool='keep';
--查詢到該表是否已經(jīng)被緩存
select table_name,cache,buffer_pool from user_TABLES where cache like '%Y';
--已經(jīng)加入到KEEP區(qū)的表想要移出緩存,使用
alter table table_name nocache;
--查詢當(dāng)前用戶下表的情況
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);
--查詢該用戶下所有表內(nèi)的大字段情況
select column_name,segment_name from user_lobs;
--取消表緩存
alter table XXX storage(buffer_pool default);
ps:
查看keep空間的剩余大?。?br/>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';
上述內(nèi)容就是Oracle調(diào)優(yōu)中常用表KEEP到內(nèi)存中的示例分析,你們學(xué)到知識或技能了嗎?如果還想學(xué)到更多技能或者豐富自己的知識儲備,歡迎關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。