Oracle Wait Interface检查瓶颈原因
v$session_wait
v$session_event
v$system_event
需要增加cache size的情况
1.任何事件等待.
2.SQL语句的优化
3.操作系统换页严重
4.比较低的命中率
开启DB_CACHE_ADVICE设为on.
根据v$DB_CACHE_ADVICE决定是否需要调整.
调整DB_CACHE_SIZE.
如果命中率很高,检查v$db_cache_advice查看
是否可以减少db_cache_size大小.
如果在不同的表空间的块大小不等,可以设置多个块大小.
需要设置以下动态参数,可以动态调整:
DB_CACHE_SIZE:
DB_KEEP_CACHE_SIZE:
DB_RECYCLE_CACHE_SIZE:
尽量减少随机访问大表的large segs;
第一种方法:
select o.object_name,count(*) number_of_blocks
from dba_objects o,v$bh bh
where o.data_object_id=bh.objd
and o.owner!='SYS'
group by o.object_name
order by count(*);
方法二:
1. select data_object_id,object_type
from dba_objects
where object_name =upper('segment_name');
2. select count(*) buffers
from v$bh
where objd=data_object_id_value;
3. select name ,block_size ,sum(buffers)
from v$buffer_pool
group by name,block_size
having sum(buffers)>0;
4.
% cache userd by segment_name =[buffers(step2)/total buffers(step3)]
keeP buffer pool 指导基线
execute dbms_stats.gather_table_stats('HR','DEPTMENTS');
select table_name ,blocks
from dba_tables
where owner='HR'
and table_name='DEPTMENTS';
RECYCLE BUFFER POOL:用于批处理情况下.
select owner#,name ,count(*) blocks
from v$cache
group by owner#, name;
查看会话的io命令中情况
select s.username ,io.block_gets,io.consistent_gets,
io.physical_reads
from v$sess_io io,v$session s
where io.sid=s.sid;
所有buffer pool中的命中率
select name, 1-(physical_reads/(db_block_gets+consistent_gets)) "HIT_RATIO"
from v$buffer_pool_statistics
where db_block_gets+consistent_gets>0;
keep pool:重复访问的大小为小于default的10%,
recycle pool:只能在事物中使用.segment大小为默认buffer pool的两倍.
查看每一个buffer pool中block的多少
select id,name ,block_size,buffers from v$buffer_pool;
缓存常用表
常见的全表扫描的block将放在LRU的尾部,那么这些block将很块
被出局.CBO经常使用使用这个表但是很小,解决这种情况就需要使用
缓存表,这是block将放在LRU头部.小表全部扫描其实是最快的.
缓存表:
创建表WITH Cache,
create table phone
(
emp_id number,
phoneNum number
)tablespace tbs_data
storage(
initial 50k
next 50k
pctincrease 0
)cache;
默认表创建使用nocache,除非指定使用cache.
SQL查询WITH Cache,
select /*+CACHE */ last_name,first_name from employee;
ALter修改表WITH Cache.
alter table employee cache;
缓存表建议不要放在buffer cache中,一般放在keep pool中.
ASSM
管理空闲空间通过数据库segment的自动管理.
跟踪segment空间使用bitmaps而不是free lists.
提供空间利用率.
创建表空间时候指定.
OEM支持.
create tablespace bit_seg_ts
datafile ''
size 1M
extent management local
segment space management auto;
create table bit_seg_table
(id number)
tablespace bit_seg_ts;
Oracle 性能调优学习笔记(五)-- buffer Cache调优C
最新推荐文章于 2024-05-20 15:19:16 发布