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;
??????????? ???