Oracle9i性能调整学习指南笔记（三）

第五章 调整缓冲区高速缓冲存储器

===== 了解数据库缓冲区高速缓冲存储器

buffer cache存储的数据块可能属于以下类型：
表
索引
聚簇
LOB段
LOB索引
回退段
临时段

db buffer cache使用以下机制的一种组合管理：
LRU列表
User Server Process
Database Writer Background Process (DBWn)


--LRU
*LRU维护的缓冲区具有的状态：
Free    -->缓冲区当前未在使用
Pinned    -->缓冲区当前由一个ServerProcess使用着
Clean    -->刚从Pinned状态释放并因其中数据还未改变而被标记为可立即重用(如果该缓冲区内容 被再次请求)
Dirty    -->缓冲区未使用，但含有没有被DBWN写到磁盘的已提交数据

管理DIRTY的机制：
dirty list
write list

*用户服务器进程
   用户服务器进程未在BUFFER CACHE中找到所需数据时，它将从磁盘上读取数据。
   在读取前必须找到一个自由缓冲区，用来保存找到的数据。

当搜索一个自由缓冲区来保存要读的块时，用戶的ServerProcess利用LRU和dirty list:
   搜索LRU上free块时，ServerProcess將LRU找到的dirty块转移到dirty list;
   当dirty list达到设置的值时，DBW0就把dirty块写入磁盘；
   当ServerProcess沒有找到free块时也会将dirty块写入磁盘(此情況直接从LRU写入磁盘)；

*DBW0
DBWN触发事件：
1.当dirty list达到指定阈值长度时-->DBW0从dirty list把脏块写盘；
2.当LRU列表被搜索太长时间而没有找到free块-->DBW0直接从LRU列表把脏块写盘；
3.当3秒过去时-->DBW0从LRU列表把脏块转移到dirty list上；
4.在发生checkpoint-->DBW0把LRU列表中脏块转移至dirty list上，然後把它們写盘；
5.当数据库shutdown时-->除非使用abort选项，否则DBW0总是在数据库关闭时把所有脏块写盘；
6.在表空间热备时-->DBW0从LRU列表把该表空间的脏块转移至dirty list,然後把它們写盘；
7.在表空间临时脱机时-->DBW0从LRU列表把该表空间的脏块转移至dirty list,然後把它們写盘；
8.在删除段时-->删除一个表或索引导致DBW0先把该daunting的脏块写盘；


=====测量Database Buffer Cache性能
命中率
free buffer inspected 等待
buffer busy wait事件
free buffer wait事件

--v$sysstat含有自实例启动以来总体系统性能统计数据
(V$SYSSTAT计算buffer cache用到的4个统计数据)
Physical Reads物理读
   表示从磁盘读到Buffer Cache中的数据块个数(即表、索引、回退段)
Physical Reads direct(直接物理读)
   表示由于数据块直接从磁盘上被读取而绕过Buffer Cache的读次数。
   直接物理读是使用parallel query之类某些功能故意安排的。
   计算Buffer Cache命中率时，直接物理读要从物理读中被减去。
Physical Reads direct(LOB,直接物理读)
   表示由于数据块与一个大对象(LOB)数据类型相关联而绕过Buffer Cache的次数。
   计算命中率需減去此读。
Session Logical Reads(会话逻辑读)
   表示从buffer cache获得数据块的次数。
   

使用v$sysstat获取命中率：
--计算公式：1 - ((physical reads - physical reads direct - physical reads direct (lob)) / session logical reads)
SQL> select 1-((physical.value-direct.value-lobs.value)/logical.value) "buffer cache hit ratio"
from v$sysstat physical,
     v$sysstat direct,
     v$sysstat lobs,
     v$sysstat logical
where physical.name='physical reads'
  and direct.name='physical reads direct'
  and lobs.name='physical reads direct (lob)'
  and logical.name='session logical reads';

buffer cache hit ratio
----------------------
        .998967208
   

====非命中率性能测试
使用非命中率作为调整的起点比计算命中率并盲目增加buffer cache大小更有效。
测量非命中率的3个统计数据：
--free buffer inspected(已检查的自由缓冲区)
    用户Server Prcoess在找到一个free块之前已检查过的buffer cache数量。
    相关的统计数据是dirty buffer inspected，它表示用户进程在试图查找一个free块期间找到的脏缓冲区总个数。
--free buffer waits(自由缓冲区等待次数)
    Server Prcoess在Free Buffer Inspected活动期间所经历的等待次数。
    当该Server Prcoess必須等待DBW0将一个脏块写入磁盘，该等待就出现一次。

--buffer busy waits(缓冲区繁忙等待次数)
    Server Prcoess等待一个free块变得可用的次数。
    当请求一个已在内存中的块时，但这个块正被另一个进程使用着，此等待就发生一次。
    该等待可因回退段缓冲区以及数据与索引缓冲区而出现。
 
    NOTE:上述任一统计数据很高或不断增长都表明用户ServerProcess正花费太多时间搜索并访问Database Buffer Cache中的Free块。

v$sysstat含有free buffer inspected統計数据
v$system_event含有free buffer wait和buffer busy wait统计数据
--查詢非命中率统计数据，statspack也有相关信息(?:free buffer waits查不到)
SQL> select name,value
from v$sysstat
where name in ('free buffer inspected')
union
select event,total_waits
from v$system_event
where event in ('free buffer waits','buffer busy waits');

==============改善Buffer Cache性能
使之更大
使用多个缓冲区池
把表缓存在内存中
绕过Buffer Cache
适当的使用索引

--使之更大(可动态调整)
  加大Buffer Cache將降低Free Buffer inspected，Buffer busy waits和Free Buffer waits等待次数。
  调整Buffer Cache池大小时，需要注意的3条规则：
     1.指定的大小必須是区组大小的一个倍数；
     2.Buffer Cache、shared pool、redo log Buffer的总大小不能超过sga_max_size
     3.db_cache_size(9i)不能设置成零

配置db_cache_advice
db_cache_advice=ON

分析Buffer Cache advisory
SQL> select name,size_for_estimate,estd_physical_reads    
     from v$db_cache_advice
     where block_size='8192'
     and advice_status='ON';

--使用多个缓冲区池
keep pool
recycle pool
default pool

使用v$bh和dba_objects寻找高速缓存候选者
SQL> select obj.owner,
       obj.object_name,
       obj.object_type,
       count(distinct bh.block#) "Num Buffers"
     from dba_objects obj,v$bh bh
     where obj.object_id=bh.objd
       and owner != 'SYS'
       and owner != 'SYSTEM'
       group by obj.owner,
                obj.object_name,
                obj.object_type
       order by 4;

使用v$cache和dba_users寻找高速缓存候选者 (速度慢啊)
  -->使用v$cache需要先运行创建脚本@$ORACLE_HOME/rdbms/admin/catparr.sql
SQL> select username "owner",
       name "seg.name",
       kind "seg.type",
       count(distinct block#) "num.buffers"
     from v$cache,dba_users
     where v$cache.owner#=dba_users.user_id
    AND username != 'SYS'
    AND username != 'SYSTEM'
     group by name,username,kind
     having count(distinct block#) > 10
     order by 4 desc;

NOTE:oracle建议，在keep pool中缓存总长度小于default pool大小的10%且经常访问的段。
     oracle建议，在recycle pool中缓冲缓冲总长度时default pool大小的两倍以上的段。
 





--动态创建缓冲区
alter system set db_cache_size=300M;
alter system set db_keep_cache_size=150M;
alter system set db_recycle_cache_size=50M;

--給缓冲区分配段
alter table apps.employee storage (buffer_pool keep);
alter table apps.division storage (buffer_pool recycle);

--查看已经分配给非default buffer pool的段
SQL> select owner,segment_type,segment_name,buffer_pool
     from dba_segments
     where buffer_pool != 'DEFAULT';

====监视多缓冲区池的性能：
使用v$buffer_pool、v$buffer_pool_statistics

-- 检查每个Buffer pool的大小：
SQL> select name,block_size,current_size from v$buffer_pool;

--计算各Buffer pool命中率：
SQL> select name "buffer pool",
     1-(physical_reads/(db_block_gets+consistent_gets)) "buffer pool hit ratio"
     from v$buffer_pool_statistics
     order by name;

--显示哪些表已经得到缓存：
SQL> select owner,table_name
     from dba_tables
     where ltrim(cache)='Y';

--将现有表改成高速缓存区表：
SQL> ALTER TABLE employee CACHE;

==正确使用索引
  在引用了另一表中主键的表的外键列上建立索引。这不仅帮助改进多表连接的效力，而且还最大限度地介绍了两个表之间的Buffer Cache密集型的FTS(全表扫描)分类与合并连接。