buffer cache 的学习
来源:互联网 发布:淘宝网助理软件 编辑:程序博客网 时间:2024/05/21 08:40
A server process cannot find clean buffers for reading new blocks into the database buffer cache.
The database must advance the checkpoint, which is the position in the redo thread from which instance recovery must begin.
Tablespaces are changed to read-only status or taken offline.
SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads FROM V$DB_CACHE_ADVICE WHERE name = 'DEFAULT' AND block_size = (SELECT value FROM V$PARAMETER WHERE name = 'db_block_size') AND advice_status = 'ON';
根据执行上面语句,我们可以看到当buffer cache 为272M时,而不是304M时,物理读会增加1.74倍或者是74%,所以不建议设置buffer cache为212M。
当buffer cache 增加到334M时,物理读会减少7%,所以当SGA_MAX设置允许的情况下,建议增加buffer cache的大小。
Estd Phys Estd Phys Cache Size (MB) Buffers Read Factor Reads---------------- ------------ ----------- ------------ 30 3,802 18.70 192,317,943 10% of Current Size 60 7,604 12.83 131,949,536 91 11,406 7.38 75,865,861 121 15,208 4.97 51,111,658 152 19,010 3.64 37,460,786 182 22,812 2.50 25,668,196 212 26,614 1.74 17,850,847 243 30,416 1.33 13,720,149 273 34,218 1.13 11,583,180 304 38,020 1.00 10,282,475 Current Size 334 41,822 .93 9,515,878 364 45,624 .87 8,909,026 395 49,426 .83 8,495,039 424 53,228 .79 8,116,496 456 57,030 .76 7,824,764 486 60,832 .74 7,563,180 517 64,634 .71 7,311,729 547 68,436 .69 7,104,280 577 72,238 .67 6,895,122 608 76,040 .66 6,739,731 200% of Current Size
计算buffer cache 的命中率
1.计算buffer cache 命中率所需要的一些资料如下
Statistic Description
consistent gets from cache
Number of times a consistent read was requested for a block from the buffer cache.
db block gets from cache
Number of times a CURRENT block was requested from the buffer cache.
physical reads cache
Total number of data blocks read from disk into buffer cache.
Example 7-1 Calculating the Buffer Cache Hit Ratio
SELECT NAME, VALUE FROM V$SYSSTATWHERE NAME IN ('db block gets from cache', 'consistent gets from cache', 'physical reads cache');Using the values in the output of the query, calculate the hit ratio for the buffer cache with the following formula:
1 - (('physical reads cache') / ('consistent gets from cache' + 'db block gets from cache'))
计算命中率的sql 如下
select db_block_gets,consistent_gets,physical_read,1 - (physical_read / (db_block_gets + consistent_gets)) from ( SELECT max(decode(name, 'db block gets from cache', value, null)) as db_block_gets, max(decode(name, 'consistent gets from cache', value, null)) as consistent_gets, max(decode(name, 'physical reads cache', value, null)) as physical_read FROM V$SYSSTAT WHERE NAME IN ('db block gets from cache','consistent gets from cache','physical reads cache') );
The following query counts the number of blocks for all segments that reside in the buffer cache at that point in time. Depending on buffer cache size, this might require a lot of sort space.
COLUMN OBJECT_NAME FORMAT A40COLUMN NUMBER_OF_BLOCKS FORMAT 999,999,999,999SELECT 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(*);Method 2
Use the following steps to determine the percentage of the cache used by an individual object at a given point in time:
Find the Oracle Database internal object number of the segment by entering the following query:
SELECT DATA_OBJECT_ID, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME = UPPER('segment_name');
Because two objects can have the same name (if they are different types of objects), use the
OBJECT_TYPE
column to identify the object of interest.Find the number of buffers in the buffer cache for
SEGMENT_NAME
:SELECT COUNT(*) BUFFERS FROM V$BH WHERE OBJD = data_object_id_value;
where
data_object_id_value
is from step 1.Find the number of buffers in the instance:
SELECT NAME, BLOCK_SIZE, SUM(BUFFERS) FROM V$BUFFER_POOL GROUP BY NAME, BLOCK_SIZE HAVING SUM(BUFFERS) 0;
Calculate the ratio of buffers to total buffers to obtain the percentage of the cache currently used by
SEGMENT_NAME
:% cache used by segment_name = [buffers(Step2)/total buffers(Step3)]
ID
NUMBER
缓冲池标识号(从1到8,1-6对应于DB_nK_CACHE_SIZE,DB_CACHE_SIZE与系统标准块尺寸的序号相关,如DB_BLOCK_SIZE为8K,则DB_CACHE_SIZE的标识号为3(2,4,8…)。7是DB_KEEP_CACHE_SIZE,8是DB_RECYCLE_CACHE_SIZE)NAME
VARCHAR2(20)
缓冲池名称BLOCK_SIZE
NUMBER
该池中的缓冲区的块大小(以字节为单位)。可能的值:标准块大小,2的幂非标准的块大小, 2048
, 4096
, 8192
,16384
, 32768
.ADVICE_STATUS
VARCHAR2(3)
建议器状态:ON表示建议器在运行,OFF表示建议器已经关闭。当建议器关闭了,视图中的数据是上一次打开所统计得出的SIZE_FOR_ESTIMATE
NUMBER
预测性能数据的Cache大小(M为单位)SIZE_FACTOR
NUMBER
预测的Cache大小因子(即与当前大小的比例)BUFFERS_FOR_ESTIMATE
NUMBER
预测性能数据的Cache大小(缓冲块数)ESTD_PHYSICAL_READ_FACTOR
NUMBER
这一缓冲大小时,物理读因子,它是如果缓冲大小为SIZE_FOR_ESTIMATE时,建议器预测物理读数与当前实际物理读数的比率值。如果当前物理读数为0,这个值为空。ESTD_PHYSICAL_READS
NUMBER
如果缓冲大小为SIZE_FOR_ESTIMATE时,建议器预测物理读数。ESTD_PHYSICAL_READ_TIME
NUMBER
当缓冲池的大小为size_for_estimate时需要的物理读的时间。ESTD_PCT_OF_DB_TIME_FOR_READS
NUMBER
当缓冲池为size_for_estimate是物理读所消耗时间的百分比ESTD_CLUSTER_READS
NUMBER
为读取的数据块的总数。只对RAC有效ESTD_CLUSTER_READ_TIME
NUMBER
读取花费的总时间。只对rac有效- buffer cache 的学习
- linux的buffer cache
- buffer cache和page cache的区别?Buffer,Cache区别
- Buffer cache学习(二)
- Buffer cache学习(三)
- Latch: cache buffer chains等待事件的学习
- 学习笔记——Buffer和Cache的区别
- buffer cache内buffer的各种状态
- buffer cache和page cache的区别
- linux 的page cache 和buffer cache
- page cache 和 buffer cache的区别
- Buffer cache和page cache的区别
- Buffer cache和page cache的区别
- page cache和buffer cache的关系
- Buffer和Cache的区别
- cache 和 buffer的区别
- cache 和 buffer的区别
- buffer 与cache 的区别
- (*)hibernate映射文件配置
- Oracle设计表、批处理方式快速导入到数据库示例
- 链表排序
- mongodb查询的语法
- border的使用
- buffer cache 的学习
- Linux怎么进行Yum软件仓库配置
- Imperfect C++ 编译期约束 must_have_base
- 常用代码
- C# 匿名方法和拉姆达表达式
- ORA-00257
- 今后学习的方向
- 条件变量演示
- WEB网站架构设计