Oracle 性能调优学习笔记(五)-- buffer Cache调优C

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;