#############################################################################
Buffer Cache
edit by sky on 20111110 参考:beatony及performance guid
#############################################################################
************************************************MSMM —————9i**********************************************
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^MSMM相关知识^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
启用手动SGA管理:设置MEMORY_TARGET及SGA_TARGET初始化参数均为0 ------AMM到MSMM
buffer cache的设置:在创建表空间的时候指定了非标准数据块，则必须配置非标准数据大小的缓存(DB_nK_CACHE_SIZE)去容纳这些表空间
通过初始化参数DB_BLOCK_SIZE指定标准块大小——2K到32K,在指定标准块大小后DB自动给予DB_CACHE_SIZE默认值，不需要设置，而DB_nK_CACHE_SIZE默认值为0，设置nk大小后需要设置DB_CACHE_SIZE
可以设置多大的非标准块，取决于操作系统的限制
非标准块只能使用DEFAULT pool，而标准块大小能使用于多个缓冲池如DEFAULT buffer pool、KEEP buffer pool、RECYCLE buffer pool
非标准块大小是在创建表空间时指定，可以查看:select dbms_metadata.get_ddl('TABLESPACE','tablespace_name') from dual;
!!!!!!!!由此能推出只有使用标准块大小的对象才能被放入到keep或recycle pool这句是错误的，nk_cache只是为了让oracle在缓存中分配空间时以非标准块大小的缓存读入数据块，所有的块大小的对象都可以被放置到keep或recycle中
Result Cache从shared pool中分配内存，因此当增加RESULT_CACHE_MAX_SIZE时需要考虑相应增加共享池的大小，
通常情况下不需要指定此缓存，oracle会根据当前SGA总共的可用内存和SGA管理方式自动给予默认值，
可通过参数RESULT_CACHE_MAX_SIZE查看当前值，此值四舍五入到最接近32K的倍数，通过SELECT dbms_result_cache.status() FROM dual;确认是否启用
如果RESULT_CACHE_MAX_SIZE为0，表示结果最大缓存被禁用，可以通过动态设置此值为一个非0值启用，或从文本初始化参数中移除并重启DB
几个额外的参数控制SGA对内存的使用:
LOCK_SGA=true锁整个SGA到物理内存，阻止换页、只适用于手动SGA管理
SHARED_MEMORY_ADDRESS和HI_SHARED_MEMORY_ADDRESS指定SGA的起始地址，对于64位平台，最后一个参数指定了64位地址的高32位，两者很少被使用，
USE_INDIRECT_DATA_BUFFERS用在32位操作系统上为了扩展buffer cache使用超过4G的内存，此参数仅用来手动管理的SGA，其它平台将不支持
几个相关SGA和PGA大小的动态性能视图:
V$MEMORY_CURRENT_RESIZE_OPS ------显示当前自动或手动改变内存大小操作的信息
V$MEMORY_DYNAMIC_COMPONENTS ------显示当前所有动态调优组件的大小及总共SGA和实例PGA的大小
V$MEMORY_RESIZE_OPS ------显示过去800秒之内完成内存大小调整的操作，包括自动和手动模式，但不包括当前正在进行调整的操作
V$MEMORY_TARGET_ADVICE ------显示MEMORY_TARGET值调整的建议
V$SGA_CURRENT_RESIZE_OPS -----显示当前正在进行的SGA调整大小的操作
V$SGA_RESIZE_OPS -----显示过去800秒之内完成SGA大小调整的操作，不包括当前正在进行的操作
V$SGA_DYNAMIC_COMPONENTS -----显示自启动以来SGA中有关动态调优组件的信息
V$SGA_DYNAMIC_FREE_MEMORY -----显示SGA中为动态调优组件的可用内存量
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
buffer cache:
1.介绍
------------------------------------------------------------------------------------------------------------------------
buffer cache大小查看:
select component,current_size,min_size from v$sga_dynamic_components; --------9i
select name,bytes/1024/1024 from v$sgainfo where name='Buffer Cache Size';------10g
show parameter cache_size -----查看各种缓冲区
select name,current_size,buffers,block_size FROM v$buffer_pool;----查看各种缓冲区
Buffer cache是SGA的一部分，其内容是由用户进程从数据文件读取出来的数据块，并且所有的用户共享这些数据块。
通常，服务器进程为提高I/O性能，会一次性读多个数据块。对于Buffer cache中的脏数据则由DBWn进程写入到数据文件。
同样为提高性能，DBWn进程也会一次写多个数据块。
Buffer cache会拥有一个数据块的多个副本，当前块的最新副本仅有一份，而该数据块老的或旧的副本可能有多份，用于块的读一致性。
Buffer cache采用LRU算法来淘汰掉过时的数据块。
Buffer cache中存在检查点队列以及LRU链表。
与Buffer cache打交道的后台进程为DBWn，与之对应的数据文件通常位于system表空间，sysaux表空间，undo表空间，datafile等
Buffer cache中块的四种状态:
pinned:意味着多个会话在相同的时段写同一个数据块，其他的会话等待访问块
clean:优先要淘汰掉的数据块,即不是pinned状态,也不会被再次使用的块.该块可能和磁盘上的块处于同步状态,也可能是一个读一致性块
free/unused:即Buffer cache中的块处于空闲状态或未使用状态，通常是由于实例刚刚启动
dirty：已发生变化的数据块，且没有进程再使用该块，则在aged out之前需要立即由DBWn写入到数据文件
服务器进程将数据块从数据文件填充到Buffer cache，当Buffer cache中不再需要使用到数据块的副本时，而DBWn进程则将脏数据写入到数据文件，用于将数据块由 pinned状态变为free状态
Buffer cache的几个相关参数:
DB_BLOCK_SIZE:参数决定了数据库主块的块大小，该块的大小通常被系统表(system,sysaux)空间和主要的Buffer cache(recycle,keep,default buffer cache)所使用
db_block_checksum:该参数设置为true，则一个指定的校验码被同时写入到数据块，用于防止磁盘，I/O系统损坏导致数据的丢失
热块，次热快，以及冷块存放到不同的buffer pool中。实际上这几个不同的buffer pool除了分配的大小不同之外，所采用的算法都是LRU算法，因此对块的缓存以及淘汰(aged out)算法实质一样
任意一个不同的buffer pool都将根据访问方式的不同而只缓存读取到的数据块，即如果是全表扫描，则缓存所有块，如果是索引快速扫描，则缓存索引的所有叶节点块
dafault pool:
普通对象的缓冲池，那些没有在keep pool也没有在recycle pool的对象将缓冲到这里
keep pool:
对于经常访问的小表将其常驻内存，即放置到keep pool
其作用是保证这部分经常访问的数据能够常驻内存而不被替换出内存，从而提高访问这些数据的速度
这个池最好能够保持99%的命中率，也就是说要保证这个池的大小能够缓存放于这个池的大部分对象
recycle pool:
对于不经常访问的大segment，就可以考虑将其放置到recycle pool，以尽快将其淘汰出去
nk buffer caches:
主要适用于不同平台传输表空间，或根据业务需要来使用非标准表空间之外的表空间
buffer的解释:
如磁盘上数据文件的最小I/O单元叫block一样，buffer cache的最小单元（或者说结构）叫buffer
每个buffer跟x$bh中每条记录存在一 一对应关系
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
包括:default buffer cache、keep buffer cache、recycle buffer cache、nk buffer caches
以上各缓冲区分别对应动态参数为:db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size
排序和并行读操作不使用buffer cache，直接从磁盘读入到PGA及内存
为了有效使用buffer cache，需要调整频繁执行的sql语句及发生许多缓冲区获取的sql语句
使用V$DB_CACHE_ADVICE和缓冲区命中率查看缓冲区大小的建议
V$DB_CACHE_ADVICE可以被动态启用，需要动态设置DB_CACHE_ADVICE为on，这个视图被启用将会额外增加CPU
使用基于采样来获得cache建议的统计信息，因为采样将会大大减少CPU和内存的消耗，但如果一开始buffer pool就很小的话，采样将不能被用于buffer pool
在使用buffer cache时需要在实际的工作量负载下测试才有意义
物理读不表示一定是从磁盘获取，也有可能从文件系统缓存中获取
对于RAC环境不同的实例可以根据应用的不同有不同的缓冲池分配方法
在AMM和ASMM下oracle使用隐含参数__db_cache_size确定了默认池的大小，无法手动修改
default buffer cache =db_cache_size-db_keep_cache_size-db_recycle_cache_size-db_nk_cache_size
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
2.客户端服务器进程从buffer cache获取数据的过程:
a.服务器进程使用一个哈希函数来检查所需的数据块是否已经位于Buffer cache
如果在Buffer cache中找到所需的数据块，则该块根据使用的频率放置到LRU队列中特定的位置
此时的读数据块为逻辑读，且不在需要执行后续步骤。如果不在Buffer cache中，则转到下一步
b.服务器进程搜索LRU列表中是否存在可用的空闲空间存放新的数据块
在搜索LRU列表同时时，已经被修改的脏数据将被服务器进程放置到检查点队列
c.检查点队列长度超出预设大小的阙值或服务器进程搜索空闲块操作预设的次数(由隐藏参数_db_block_max_scan_pct所指定的值，
表示已经扫描的buffer header数量占整个LRU链表上的buffer header的总数量，在i中该限定值为40%)，
则服务器进程通过DBWn将脏数据从Buffer cache写出到数据文件
d.当可用的空闲块被找到后，服务器进程从数据文件读入块到Database Buffer cache并放置到LRU队列中。
如果所得到的块不是一致性读块，则服务器进程从undo segment中重构一致性块
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
3.Buffer Cache与DBWR的关系(DBWR触发条件)
脏缓冲列表达到指定的阙值大小
搜索LRU空闲队列达到预设的阙值次数
发生检查点事件
数据库关闭时
表空间实现热备份时
表空间离线
在段被删除时
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
4.buffer cache相关的参数和视图:
参数:
db_cache_size
db_cache_advice
db_keep_cache_size
db_recycle_cache_size
db_file_multiblock_read_count
statspace report
视图:
v$buffer_pool_statistics
v$buffer_pool
v$db_cache_advice
v$sysstat
v$sesstat
v$system_event
v$session_wait
v$bh
v$cache
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
5.buffer cache各组件大小关系说明-------没有一个参数控制buffer cache的大小，只是各组件大小决定buffer cache总大小
sys@ORCL>select*fromv$version;
BANNER
----------------------------------------------------------------
OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Prod
sys@ORCL>selectname,bytes/1024/1024fromv$sgainfowherename='Buffer Cache Size';
NAME BYTES/1024/1024
------------------------- ---------------
Buffer CacheSize 192
sys@ORCL>selectname,current_size,buffers,block_size FROMv$buffer_pool;
NAME CURRENT_SIZE BUFFERS BLOCK_SIZE
--------------- ------------ ---------- ----------
DEFAULT 192 23952 8192
sys@ORCL>altersystemsetdb_recycle_cache_size=16m;
sys@ORCL>altersystemsetdb_keep_cache_size=16m;
sys@ORCL>selectname,current_size,buffers,block_size FROMv$buffer_pool;
NAME CURRENT_SIZE BUFFERS BLOCK_SIZE
--------------- ------------ ---------- ----------
KEEP 16 1996 8192
RECYCLE 16 1996 8192
DEFAULT 160 19960 8192
sys@ORCL>altersystemsetdb_16k_cache_size=4m;
sys@ORCL>selectname,current_size,buffers,block_size FROMv$buffer_pool;
NAME CURRENT_SIZE BUFFERS BLOCK_SIZE
--------------- ------------ ---------- ----------
KEEP 16 1996 8192
RECYCLE 16 1996 8192
DEFAULT 160 19960 8192
DEFAULT 4 252 16384
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
6.buffer cache的命中率:
命中率=1-(物理读的次数-总的请求次数)
SELECTROUND(1-((physical.value-direct.value-lobs.value)/logical.value),3)*100||'%'
"Buffer Cache Hit Ratio"
FROM v$sysstat physical,
v$sysstat direct,
v$sysstat lobs,
v$sysstat logical
WHERE physical.name='physical reads'
ANDdirect.name='physical reads direct'
ANDlobs.name='physical reads direct (lob)'
ANDlogical.name='session logical reads'; --------第一种命中率的计算方法
/*physical reads:*/
------从Oracle级别来理解，从磁盘读数据块的次数，一次可以读多块,由参数db_file_multiblock_read_count来控制。此种读方式使用了db cache
形象示意：db_file==>db_cache==>pga
/*physical reads direct:*/
------有些数据块不会先从硬盘读入内存再从内存读入PGA再传给用户，而是绕过SGA直接从硬盘读入PGA,
比如并行查询以及从临时表空间读取数据,这部分数据块由于不缓存使得hit ratio不会被提高。其在计算hit ratio时应当被扣除
形象示意：db_file ==>pga
通常，disksort/hash,expdirect=Y,都会有physical reads direct
scott@ORCL> select name from v$statname where statistic# in (54,55,56);
NAME
----------------------------------------------------------------
physical reads
physical reads cache --使用buffer cache
physical reads direct --不使用buffer cache
/*physical reads direct(lob)
--------对于大值对象，如LOB数据类型以及LOB段，Oracle可以绕过buffer cache而直接使用PGA，其原理等同于physical reads direct
使用physical reads direct，physical reads direct(lob)的优点：
对于一个大操作，需要请求大量数据块，假设又使用并行执行，且执行次数就那么一次，这个时候就适合使用direct方式，
如果还是走buffer cache则需要把buffer cache里已缓存的数据库都清空
注意physical write/direct同理
/*session logical reads:
发出的总的请求次数，此处是指从database buffer cache中请求块。对于一致性读，则这些缓冲包含来自回滚段的数据
SELECTNAME,
physical_reads,
db_block_gets,
consistent_gets,
ROUND((1-(physical_reads/(db_block_gets+consistent_gets)))*100)||'%'ratio
FROM V$BUFFER_POOL_STATISTICS
WHERE NAME='DEFAULT'; ------第二种命中率的计算方法通常用于10g、11g
SELECT(1-(SUM(decode(NAME,'physical reads',VALUE,0))/
(SUM(DECODE(NAME,'db block gets',VALUE,0))+
SUM(DECODE(NAME,'consistent gets',VALUE,0)))))*100 "Hit Ratio"
FROM v$sysstat; ------第三种命中率的计算方法
/*consistent gets from cache
-------在回滚段Buffer中的数据构造一致性读数据块的总次数。其产生原因是由于其他会话对当前数据块进行操作，如update操作，
但是由于我们的查询是在这些修改之前调用的，所以需要使用回滚段中的数据块的前映像进行查询，来保证数据的一致性。
这样就产生了一致性读。
/*db block gets:
------在操作中提取的块数目，而不是在一致性读的情况下而产生的块数。
当前块(current,相对于cosistent读而言，current总是最新的块)，从buffer cache中请求的次数。当前块意思就是在操作
中正好提取的块数目，而不是在一致性读的情况下而产生的块数。通常的情况下，一个查询提取的块是在查询开始的那个时
间点上存在的数据块，当前块是在这个时刻存在的数据块，而不是在这个时间点之前或者之后的数据块数目。
/*physical reads cache:
-------从磁盘读入到buffer cache中的总次数.
产生的主要原因是：在数据库高速缓存中不存在这些块,全表扫描,磁盘排序等
-------此方法与前面命中率计算的方法不一样，更简单直观
影响命中率的因素:
全表扫描(小标尚可，对于大表而言I/O性能更差。而且全表扫描总是被置于LRU的最尾端，随时被aged out)
不同数据定义和应用程序设计影响命中率
大表的随机访问(非顺序)
不均衡的cache hit
命中率的调整:
a.对相同的大表和索引的重复扫描容易造成命中率很高的假象。定期检查频繁使用且返回结果集很大的SQL语句，确保这些SQL语
句使用了最优的执行计划。
b.避免返回查询相同的数据，尽可能将获得的结果集缓存的客户端程序或中间件。
c.大表的全表扫描问题，直接将其放置到LRU的尾端，容易aged out(小表通常指全表扫描时占用buffer cache 20%或拥有个数据块)。
d.对较大OLTP系统而言，表中的很多行仅仅被访问次或很少的次数，基于此，这些块不易长时间占住buffer cache。
e.对于并行查询或排序等，持续增加buffer cache的大小并无实际意义，优化效果并明显。
查询不同的buffer pool的命中率:
SELECTNAME,
block_size,
physical_reads,
db_block_gets,
consistent_gets,
(1-(physical_reads/(DECODE(db_block_gets,0,1,db_block_gets)+
DECODE(consistent_gets,0,1,consistent_gets))))*100 "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS
ORDER BYNAME;
低ratio并不能表明增加cache size可以提高性能。高ratio有时反而会让你误认为cache size已经足够大而满足要求了。
比如：重复的扫描一些大表或索引。然后大表的全表扫描往往都是物理读，会人为的降低hit ratio。
因此在不同高峰时段，多次采集数据非常有必要(或使用StatsPack)
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
7.大段随机访问buffer cache的问题及优化:
问题:由于对随机访问的大段导致buffer cache过于快速的老化(由于存在LRU算法使得大段占用了大量的cache空间，使得其它缓存对象被挤出cache)
优化方法:
a.如果访问的对象是一个索引，找出索引是否是有选择性的的。如果没有，调整SQL语句的使用更具选择性的索引
b.如果sql语句已优化，可以移动相应的大段到RECYCLE cache(再生缓冲池比默认缓冲池小，但能更快的重用buffer)
c.将小的热段放在keep池中，确保keep池不为大段所使用,keep buffer pool能够最小化cache的丢失
确定那个段在占用了大量的缓冲区的两种方法:
1.查看每个段在查询时刻驻留在cache中的块的数量(需要大量的排序空间):
COLUMN OBJECT_NAME FORMAT A40
COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999
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(*);
2.为指定的对象在指定的时间确认其占用cache的百分比:-----这种查询仅适合简单的段对象，对于分区对象必须分单个分区计算
SELECT DATA_OBJECT_ID, OBJECT_TYPE
FROM DBA_OBJECTS
WHERE OBJECT_NAME = UPPER('segment_name');
SELECT COUNT(*) BUFFERS
FROM V$BH
WHERE BJD = data_object_id_value;
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS)
FROM V$BUFFER_POOL
GROUP BY NAME, BLOCK_SIZE
HAVING SUM(BUFFERS) > 0;
% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
8.将指定段对象加入某个指定的buffer cache:
为一个对象指定特定的缓冲池：create或alter表、cluster、索引的sql语句中STORAGE子句的BUFFER_POOL关键字指定
如果为一个分区表或索引指定了特定的缓冲池，那么这个分区表或索引中所包含义的对象将继承并使用分区表或索引所指定的缓冲池
当通过alter语句改变一个对象的缓冲池，那么在alter语句之前(这个段已经被修改的数据块)将依然保留在此前的缓冲池中，只有最近加载的块和老化的块被放到新的缓冲池中
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
9.keep pool使用与优化:
DB_KEEP_CACHE_SIZE
经常访问的一些固定不变的小表应该放到keep pool中
一般情况是对象的大小应当为少于default buffer pool大小的10%
kepp pool分配的大小取决于kepp pool存放对象的大小
计算keep pool中段所占块的大小，可以查询V$BH或如果收集了段的统计信息可以查询DBA_TABLES.BLOCKS及DBA_TABLES.EMPTY_BLOCKS
计算所有将要放入到keep buffer cache对象的总块数得到一个近似值，然后将稍微大于该近似值的尺寸指定给keep buffer pool
对于放入keep buffer pool中的表数据，如果对象块多于buffer pool数量，则以buffer pool数量为准进行缓存，冷块将被新块置换
将段放入keep pool的操作：
exec dbms_stats.gather_table_stats('USER_NAME','TABLENAME');
select table_name,blocks,empty_blocks from dba_tables where wner='USER_NAME' and table_name='TABLE_NAME';
alter system set db_keep_cache_size=16m scope=both; --注意，该参数值的大小应根据实际情况设置,相应的大小会自动从default pool中减去
alter table TABLE_NAME storage(buffer_pool keep);
select component,current_size from v$sga_dynamic_components where component='KEEP buffer cache'; show parameter keep ----查看keep池大小
select p.name,a.cnum_repl "total buffers",a.anum_repl "free buffers" from x$kcbwds a, v$buffer_pool p where a.set_id=p.LO_SETID and p.name='KEEP'; -----查看keep池剩余大小
select segment_name，segment_type from dba_segments where BUFFER_POOL = 'KEEP'; -------查看keep池的对象
select table_name,cache,blocks from dba_tables where wner='ROBINSON' and buffer_pool='KEEP'; -------查看段对象是否已经被缓存到keep(cache为yes)，以及多少块被缓存
alter system flush buffer_cache !!!!!!!!!慎用-------只有在业务空闲期间才用，一般情况下不使用
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
10.RECYCLE Pool的使用与优化:
对应用随机访问，不经常利用的一些大对象适合放在RECYCLE Pool,表明这些对象是不需要被缓存
一旦事务被提交则这些块将从recycle buffer pool中被清除
对象的大小应当为多于default buffer pool大小的2倍
recycle buffer pool需要具有一个事务所需要的全部块
将段放入recycle pool:
exec dbms_stats.gather_table_stats('USER_NAME','TABLE_NAME');
select blocks,empty_blocks from dba_tables where table_name='TABLE_NAME' and wner='USER_NAME';
alter system set db_recycle_cache_size=16m scope=both; ------注意，该参数值的大小应根据实际情况设置
alter table TABLE_NAME storage(buffer_pool recycle);
SELECT owner#
,NAME
,COUNT(*)blocks
FROMv$cache
GROUPBYowner#,NAME;
SELECT s.username -------跟踪recycle buffer pool的I/O情况
,io.block_gets
,io.consistent_gets
,io.physical_reads
FROMv$sess_ioio,v$session s
WHEREio.sid=s.sid;
select table_name from dba_tables where buffer_pool='keep'; ----查看哪些表被放在缓存区 但并不意味着该表已经被缓存
alter table table_name nocache; -----已经加入缓存(如keep池等)的对象移出缓存
select table_name,cache,buffer_pool from user_TABLES; ----查询当前用户下表是否在缓存中
select segment_name,segment_type,buffer_pool from user_segments;-----查询当前用户下段是否在缓存中
alter table t2 modify lob(c2) (storage (buffer_pool keep) cache); ------LOB类型的segment的cache方法
alter table test modify lob(address) (storage (buffer_pool keep) nocache); -----LOB类型的segment取消缓存的方法
select column_name,segment_name from user_lobs; -------查询该用户下所有表内的大字段情况
-------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------
11.buffer cache的优化:
优化目标:尽可能在Buffer cache中找到数据，降低等待可用空闲块的时间
调整方法:
降低SQL命令对数据块的请求，如避免使用select * from语句
增加缓冲池的大小
不同访问方式使用不同的缓冲池(buffer pools)
缓存常用的表到内存
并行读或排序操作不使用cache,直接从磁盘读入到PGA及内存
数据收集:wait events、cache hit ration、v$db_cache_advice view
通过相应视图和等待事件获取相应数据，用相应指标去衡量采用那种方法
相应指标:
SELECTNAME,VALUE
FROM v$sysstat
WHERE NAMEIN('session logical reads',
'physical reads',
'physical reads direct',
'physical reads direct (lob) ',
'consistent gets',
'db block gets',
'free buffer inspected',
'free buffer requested',
'dirty buffers inspected',
'pinned buffers inspected');
/*Session Logical Reads ------所有的逻辑读的数据块的数量
/*Free Buffer Inspected ------为寻找空闲buffer之前所检查块的总数量，即跳过块的数量。
如果该值接近脏数据块的数量，则表明空闲块很少，该值应尽可能小于脏块的数量
/*Free Buffer Waits -----当session在LRU list上没有寻找到空闲可用数据块或者搜寻可用的内存数据块被暂停的时候，该发生该事件，
此为等待DBWn将脏块写入到数据文件的等待数。除此之外，会话在做一致性读时，需要构造数据块在某个时刻的前映像（image），
此时需要申请内存来存放这些新构造的数据块，如果内存中无法找到这样的内存块，也会发生这个等待事件
/*Buffer Busy Waits ------用户服务器进程已找到所需的数据块，但该块正被其它进程使用或多个进程同时要修改该块，此时需要等待的时间
当一个会话需要读取一个数据块，但这个数据块正在被另一个会话读取到内存中时，此时同样发生Buffer Busy Waits事件
SELECT event,total_waits
FROM v$system_event
WHERE event IN ('free buffer waits','buffer busy waits');
SELECTNAME
,parameter1
,parameter2
,parameter3
FROM v$event_name
WHERE NAME='buffer busy waits';
产生Buffer busy waits的几种情形:
发生在数据块data block处理方法:
a.尽可能缩小SQL语句的查询字段，查询范围，如不使用select *查询，将like子句改为直接赋值等
b.检查索引的合理性。如使用了sequence生成的索引，其索引键通常位于相同的块，因此可以使用反向索引避免此问题
c.使用自动段空间管理或增加空闲列表，以避免多个进程同时插入相同的块
d.查询视图v$session_wait来获得热点块的文件ID，块ID，通过这些信息来获得对象ID，进一步对该对象进行调整
发生在基于UNDO Header的竞争:
如果未使用自动撤销段管理模式，则需要增加更多的回滚段
发生在UNDO BLOCK:
基于UNDO段块的竞争，如果未使用自动撤销段管理模式，则需为回滚段分配更大的尺寸
产生Free Buffer waits的几种情形:
是由于DBWn进程来不及将数据写入到数据文件，导致需要等待被释放的空间的原因
a.I/O系统速度过于缓慢
确保数据库文件是否分布在不同的磁盘上，或增加更高性能的磁盘
b.资源等待造成I/O系统过慢，如latch等待
确保数据库文件是否分布在不同的磁盘上，或增加更高性能的磁盘
c.Buffer cache太小，导致DBWn来不及将脏数据写入到数据文件
需要增大buffer cache的尺寸
d.Buffer cache太大，而单一的DBWn进程需要多次才能将数据写入到文件
减少buffer cache的尺寸，或增加更多的DBWn进程
增加buffer cache:
一些等待事件已经被优化
不良的SQL语句已经被优化
操作系统级别无不良的内存页面置换
上次增加的buffer cache有效
基于上面的情形，且命中率很低，此时可以增加buffer cache
增加步骤:
设置db_cache_advice为on
检查动态性能视图v$db_cache_advice(需要考虑增加后不影响操作系统级别过多的内存页面置换)
动态增加db_cache_size的值(生产数据库不建议关闭系统而使用动态调整altersystemsetdb_cache_size=nM;)
SELECT NAME,current_size,buffers FROM v$buffer_pool; ------查看当前buffer cache的大小
减少buffer cache:
在命中率很高的情形下，查询视图v$db_cache_advice，来权衡适度降低buffer cache size是否会使得系统I/O显剧增加，如不是，且
降低buffer cache size不会影响性能的情况下，则可以适度降低buffer cache size的大小
使用alter system set db_cache_size来调整
buffer cache大小的评估(使用advisor):
确保STATISTICS_LEVEL参数为TYPICAL或者ALL
OFF：禁用buffer cache advisor特性，且不为advisor分配内存
READY：不收集数据,但是收集数据的内存已经预先分配好了.通过把参数值从off设置为ready,然后再设置为on，以避免出现错误
ALTER SYSTEM SET db_cache_advice=ON|READY|OFF;
SELECT size_for_estimate "Cache Size (MB)",
size_factor,
buffers_for_estimate "Buffers",
estd_physical_read_factor,
estd_physical_reads,
estd_physical_read_time -------此参数在i中不可用
FROM v$db_cache_advice
WHERE NAME='DEFAULT'
AND block_size=(
SELECT VALUE
FROM v$parameter
WHERE NAME='db_block_size'
ANDadvice_status='ON'); -----default pool大小的评估
SELECT SIZE_FOR_ESTIMATE, BUFFERS_FOR_ESTIMATE, ESTD_PHYSICAL_READ_FACTOR,
ESTD_PHYSICAL_READS
FROM V$DB_CACHE_ADVICE
WHERE NAME = 'KEEP' --------keep pool大小的评估
相关数据收集:
视图v$bh(基于视图x$bh)显示当前位于SGA中所有块的详细信息。决定哪个段位于哪个缓冲区，所占住的块的个数等
a.查询buffer cache中不同对象占住块的个数(可以根据查询将不经常访问的大对象置于到recycle pool)
SELECT o.owner,object_name,object_type,COUNT(1)buffers --这个查询获得到经常访问的对象，可以将其放到recycle pool中
FROM SYS.x$bh,dba_objects o
WHERE (tch=1OR(tch=0ANDlru_flag<8))
AND bj=o.object_id
AND o.owner NOT IN('SYSTEM','SYS')
GROUP BY o.owner,object_name,object_type
ORDER BY buffers;
SELECT o.owner,object_name,object_type,COUNT(1)buffers --这个查询获得到经常访问的对象，可以将其放到keep pool中
FROM SYS.x$bh,dba_objects o
WHERE tch>10
AND lru_flag=8
AND bj=o.object_id
AND o.owner NOT IN('SYSTEM','SYS')
GROUP BY o.owner,object_name,object_type
ORDER BY buffers;
b.查询单个对象占住buffer cache中块的总个数
SELECT COUNT(*)
FROM v$bh
WHERE bjd=(
SELECT data_object_id
FROM Dba_Objects
WHERE object_name=UPPER('big_table')
AND wner='SCOTT'
AND status!='free');
c.获得所有不同的buffer pool当前分配块的总个数
SELECT NAME
,block_size
,SUM(buffers)
FROM v$buffer_pool
GROUP BY NAME,block_size
HAVING SUM(buffers)>0;
d.获得单个对象占用buffer cache的比率
SELECT round(obj_cnt/totalcache_cnt*100,3)
FROM
(SELECT COUNT(*)ASobj_cnt
FROM v$bh
WHERE bjd=(
SELECT data_object_id
FROM Dba_Objects
WHERE object_name=UPPER('big_table')
AND wner='SCOTT'))a,
(SELECT NAME
,block_size
,SUM(buffers) AS totalcache_cnt
,COUNT(*)
FROM v$buffer_pool
WHERE NAME='DEFAULT'
GROUP BY NAME,block_size
HAVING SUM(buffers)>0)b;
-------------------------------------------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16807927/viewspace-711586/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16807927/viewspace-711586/
2025
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
