buffer cache 的加深理解

刚才网上看了一些资料,又加深了一点印象
 select name, value
   from v$sysstat
  where name in
        ('session logical reads', 'physical reads', 'physical reads direct',
         'physical reads direct (lob)', 'consistent gets', 'db block gets',
         'free buffer inspected');
/*
   1):session logical reads :所有的逻辑读的数据块的数量。注意：其中包括先从硬盘读数据块到内存里。在从内存里读取数据块
   2):consistent gets : 在一致性(consistents read)读模式下读取的内存里的数据块数量。包含从rollback segment里读取的数块数量，
以及从data block buffer里读取的数据块的数量。主要是通过select产生的，update/delete也会产生少量的此类数据块。注意：如果oracle
运行的时间过长，由于oracle的buf导致consistents gets大大超过了实际的数量。因此建议使用'nowork-consistent read gets','cleanouts only-consistent read gets',
'rollbacks only-consistent read gets','cleanouts and rollbacks-consistent read gets'之和来代替consistent gets得值
   3):db block gets：在当前(current）模式下读取的内存里的数据块的数量。不是读取过去某个时点的数据块，而必须是当前最新的数据块，
主要通过update/delete/insert来产生的。因为ddl需要当前最新的数据块才能对之前进行改变。在字典管理表空间下。一些获得当前可用扩展
空间的select语句也会产生此类数据块，因为必须得到当前最新的空间使用信息才能扩展。逻辑上，session logical reads=consistent gets + db block gets.
  4):physical reads :从硬盘力度取得数据块的数量。注意：这个数量大于实际从硬盘里读取的数量。因为这部分block也包括从操作系统缓存里读取的数据块的数量
  5):physical reads direct:有些数据块不会从硬盘读入内存在从内存读入pga在传给用户。而绕过sga直接从硬盘读取数据读入pga,比如并行查询以及从临时表空间
读取数据。这部分数据块由于不缓存使得hit ratio不会被提高。
  6):physical reads direct(lob):与physical reads direct一样
  7):free buffer inspected:这个指标是为了找到可用的数据块而跳过数据块的数量。这些被条过的数据块就是脏的或者被锁定的数据块。明显，这个值如果持续增长或者很高，
就需要增加buffer cache的大小了
*/
--1 Hit Ratio   1-(physical reads - physical reads direct - physical reads direct(lob))/session logical reads
--2 Miss Ration (physical reads - physical reads direct - physical reads direct(lob))/session logical reads

/*
  一般的oltp系统，Hit Ratio应高高于90%，如果低于90%，就应该相应的增加buffer cache的大小。但是在调整Hit Ratio时，需要注意几点：
  1):如果增加buffer cache对Hit Ratio效果不是很明显，就不要盲目的区增加buffer cache的大小。因为有部分排序操作或者并行读，会绕过buffer cache的
  2):近两不要产生因增加很多的buffer cache而只能提高少量的Hit Ratio。
*/

 select 1-(b.value-c.value-d.value)/a.value as "Hit Ratio" from
  (select name, value from v$sysstat where name = 'session logical reads') a,
  (select name, value from v$sysstat where name = 'physical reads') b,
  (select name, value from v$sysstat where name = 'physical reads direct') c,
  (select name, value from v$sysstat where name = 'physical reads direct (lob)') d
  
 

select name, block_size, physical_reads, db_block_gets, consistent_gets
  from v$buffer_pool_statistics;

select count(*) from v$latch_children where name like 'cache buffers chains';

select x.ksppinm,y.ksppstvl,x.ksppdesc  from x$ksppi x,x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '\_%'escape'\'
and ksppinm like '%_db_block_hash_buckets%';

select /*+rule*/
 s.sql_text
  from x$bh a,
       dba_extents b,
       (select *
          from (select addr
                  from v$latch_children
                 where name = 'cache buffers chains'
                 order by sleeps desc)
         where rownum < 11) c,
       v$sqltext s
 where a.hladdr = c.addr
   and a.dbarfil = b.relative_fno
   and a.dbablk between b.block_id and b.block_id + b.blocks
   and s.SQL_TEXT like '%' || b.segment_name || '%'
   and b.segment_type = 'TABLE'
 order by s.HASH_VALUE, s.ADDRESS, s.PIECE;
 

select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name
  from x$bh a, dba_objects b
 where (a.obj = b.object_id and a.obj = b.data_object_id)
   and a.hladdr in
       (select p1raw
          from (select sid, p1raw, p2, p3, seconds_in_wait, wait_time
                  from v$session_wait sw
                 where sw.event like 'latch free'
                 order by p1, p1raw));

buffer busy waits等待
  当一个session在读取或修改buffer cache里的内存数据块时，首先必须获得cache buffer chains latch,
获得以后，到hash chain上边里直到找到与要得buffer header后，这是，该session 必须在该buffer header
上以share或者exclusive模式（具体那个模式由该session的操作决定）获得一个buffer lock或者一个buffer pin,
一旦buffer header被pin住，session就将释放cache buffers chains latch，然后可以在该buffer上进行操作。
如果无法获得buffer pin，那么该session就会等待buffer busy wait等待事件，该等待事件不会出现在session
的私有pga里。

  buffer busy waits等待时间不能像latch free等待那样可以相对比较容易的进行事后跟踪。对于该等待事件，
oracle提供了v$waitstat视图，v$waitstat里的记录都是buffer busy waits等待事件发生时进行更新的。
也就说，该视图体现的都是buffer busy waits等待事件的统计数据，但这只能给你提供一个大概的buffer busy waits的分布。
如果像具体的诊断该等待事件，只能当发生等待时，到v$session_wait里去找原因，从而才能找到解决办法，
处理buffer busy wait等待时间时，首先找到等待该数据块的类别以及对应的segment.

select 'Segment Headers' class,
       a.segment_name,
       a.segment_type,
       a.partition_name
  from dba_segments a, v$session_wait b
 where a.header_file = b.P1
   and a.header_block = b.P2
   and a.extents = 'buffer busy waits'
union
select 'Freelist Groups' class,
       a.segment_name,
       a.segment_type,
       a.partition_name
  from dba_segments a, v$session_wait b
 where b.p2 between a.header_block + 1 and
       (a.header_block + a.freelist_groups)
   and a.header_file = b.P1
   and a.freelist_groups > 1
   and b.EVENT = 'buffer busy waits'
union
select a.segment_type || 'block' class,
       a.segment_name,
       a.segment_type,
       a.partition_name
  from dba_extents a, v$session_wait b
 where b.p2 between a.block_id and a.block_id + a.blocks - 1
   and a.file_id = b.p1
   and b.EVENT = 'buffer busy waits'
   and not exists (select 1
          from dba_segments
         where header_file = b.p1
           and header_block = b.p2);

1)如果数据块类型为data block,如果版本为10g以前，则可以同时参照p3列得值来共同诊断，
如果p3为130意味着同时又很多session在访问同一个data block，而且该data block ,而且该
data block 没有在内村里，而必须从磁盘上获取。有三种方法可以降低该事件出现的频率：
   a、降低并发性。这个确实比较难
   b、找出并优化含有这些segment的sql语句，以降低物理读和逻辑读。
   c、增加freelist和freelist groups
如果没有足够的freelist，当同时对同一个进行insert时，这就很容易引起buffer busy waits等待，
如果正在等待buffer busy waits的session正在进行insert操作，那么需要检查一下那个表由多少freelist了，
当然，由于freelists的不足主要会导致对于segment header的buffer busy waits等待。
   如果p3为220意味着有多个session同时修改在一个block(该block已经被堵如内存了)里的不同的行，
这种情况通常出现在高dml并发的环境里。有三种方法可以降低该事件出现的频率：
   a、降低并发性（比较难）
   b、通过增加pctfree减少block里含有的行数。
   c、将该对象移动到拥有较小block尺寸的表空间里(9i或以上版本）
2）如果数据块类行为data segment header（表或索引的segment header,不是undo segment header）上
发生buffer busy waits等待事件，通常表明数据库里有些表或索引的段头具有频繁的活动。
   进程访问segment header主要有两种原因：一是获得或者修改process freelists信息，二是扩展HWM。
有三种方法可以降低该事件出现的频率：
   a、增加征用对象的freelists和freelists groups的数量。
   b、确定pctfree和pctused之间的间隔不要太小
   c、确定next extent的尺寸不要太小
   d、9i以后，使用ASSM特性来管理block
3）如果数据块类型为 undo segment headers的征用等待，表明数据库中的rollback segment太少，
或者他们的extent size太小，导致对于同一个segment header的大量更新。如果使用9i以后的auto undo
management，则不用处理，因为oracle会根据需要自动创建新的undo segments，如果是9i之前，则可以
创建新的private rollback segments,并把他们online，或者通过降低transactions_per_rollback_segment
参数来减轻该等待。

   在一个数据块被读入buffer cache之前，oracle进程必须为该数据块获得一个对应的可用的内存数据块。
当session在lru list上无法发现一个可用的内存数据块或者搜寻可用的数据块被暂停的时候。该session
就必须等待free buffer waits事件。

  oralce跟踪每次对于可用的内存快的请求次数（记录在v$sysstat里的free buffer requested），也跟踪
每次请求可用的内存数据块失败的次数（记录在v$sysstat里的free buffer waits的total_waits）。而v$sysstat
里的free buffer inspected则说明oracle为了可用的内存数据块所跳过的数据块的个数。如果buffer cache很空，
有很多空块的话，则该值为0，如果free buffer inspected相对free buffer requested来说很高，则说明oracle
进程需要扫描更多的lru链表上的数据块可以找到可用的数据块。

select * from v$sysstat where name in ('free buffer requested','free buffer inspected');

STATISTIC# NAME                                CLASS      VALUE    STAT_ID
---------- ------------------------------ ---------- ---------- ----------
        93 free buffer requested                   8    2716801 3411924934
        97 free buffer inspected                   8    2125875  941676439

如果一个sssion花费了很多的时间等待free buffer waits等待事件的话，通常可能有以下原因：
  1）低效率的sql语句：对于那些引起很大的逻辑度的sql语句（v$sql里的disk_reads)，那些sql语句可能进行了
全表扫描，索引全扫描、或者通过了不正确的索引扫描表等。调整这些sql语句以降低逻辑读。
  2) dbwr进程不够多，也可以通过增加dbwr checkpoints的个数来降低free buffer waits。9i下，可以通过减小
fast_start_mttr_target参数来缩短mttr，从而增加dbwr进程启动的次数，然而这也有可能引起进程等待
write complete waits事件。
  3）i/o子系统太慢
  4）延迟的块清除（block clearouts）：通常发生的情形是，向数据库倒入一个很大的表。然后再运行系统时，
发现有进程在等待buffer busy waits，这是因为第一个访问标的进程将进行一个延迟的块清除，而这会导致
buffer busy waits等待时间。解决办法是在导入表完毕以后，执行一个全表扫描，而不通常是select count(*) from 表。
这样后边的进程再次访问的时候就不会产生buffer busy waits的等待事件了。
  5）buffer cache太小：遇到free buffer waits事件，就要去增加buffer cache的大小。