Oracle BUFFER CACHE研究

最新推荐文章于 2023-02-21 19:47:58 发布

leishifei

最新推荐文章于 2023-02-21 19:47:58 发布

阅读量921

点赞数

分类专栏： Oracle 基础知识 Oracle 性能调优文章标签： cache buffer oracle statistics session 性能优化

本文链接：https://blog.csdn.net/leishifei/article/details/6534169

版权

Oracle 基础知识同时被 2 个专栏收录

143 篇文章 0 订阅

订阅专栏

Oracle 性能调优

48 篇文章 0 订阅

订阅专栏

Oracle BUFFER CACHE研究

BUFFER CACHE之一：调整buffer cache大小

Buffer Cache是存放真正数据的缓冲区，shared Pool里面存放的是sql指令(LC中一次编译，多次运行，加快处理性能，cache hit ratio要高)，而buffer cache里面存放真正的查询结果。

Buffer Cache：由彼此独立的三个子cache（subcaches，也叫主buffer cache：keep，recycle，default）组成支持多种数据块的多缓冲池。注意system表空间只能用主数据块。

Step1: 查看各个组件size（查看buffer cache Method No.1）.

SQL> show parameter size

NAME TYPE VALUE

------------------------------------ ----------- ------------

bitmap_merge_area_size integer 1048576

create_bitmap_area_size integer 8388608

db_16k_cache_size big integer 0

db_2k_cache_size big integer 0

db_32k_cache_size big integer 0

db_4k_cache_size big integer 0

db_8k_cache_size big integer 0

db_block_size integer 8192

db_cache_size big integer 0

db_keep_cache_size big integer 0

db_recovery_file_dest_size big integer 2G

NAME TYPE VALUE

------------------------------------ ----------- -----------

db_recycle_cache_size big integer 0

global_context_pool_size string

hash_area_size integer 131072

java_max_sessionspace_size integer 0

java_pool_size big integer 0

large_pool_size big integer 0

max_dump_file_size string UNLIMITED

object_cache_max_size_percent integer 10

object_cache_optimal_size integer 102400

olap_page_pool_size big integer 0

parallel_execution_message_size integer 2148

NAME TYPE VALUE

------------------------------------ ----------- ------------

sga_max_size big integer 160M

shared_pool_reserved_size big integer 2936012

shared_pool_size big integer 56M

sort_area_retained_size integer 0

sort_area_size integer 65536

streams_pool_size big integer 0

workarea_size_policy string AUTO

发现db_cache_size的值还是0，这个与shared_pool_size的情况也类似,10g文档描述：

If SGA_TARGET is set: If the parameter is not specified, then the default is 0 (internally determined by the Oracle Database). If the parameter is specified, then the user-specified value indicates a minimum value for the memory pool.

If SGA_TARGET is not set, then the default is either 48 MB or 4MB * number of CPUs * granule size, whichever is greater.

这样只有找到参数文件查看buffer cache的大小。

Step2: 动态指定db_cache_size的大小.

SQL> alter system set db_cache_size=92M scope=both;

System altered.

SQL> commit;

Commit complete.

1.DB_CACHE_SIZE指定的是基于主块大小(primary block size)的default缓冲池(buffer pool)的大小

2.该参数至少是4M*CPU个数*grunule大小。

采集统计数据用来预测不同cache size下的性能，用视图v$DB_CACHE_ADVICE查看。

Method No. 2：

SQL> select component,current_size,user_specified_size,granule_size

2 from v$sga_dynamic_components;

COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE GRANULE_SIZE

------------------------------ ------------ ------------------- ------------

shared pool 58720256 58720256 4194304

large pool 4194304 0 4194304

java pool 4194304 0 4194304

streams pool 0 0 4194304

DEFAULT buffer cache 96468992 96468992 4194304

KEEP buffer cache 0 0 4194304

RECYCLE buffer cache 0 0 4194304

DEFAULT 2K buffer cache 0 0 4194304

DEFAULT 4K buffer cache 0 0 4194304

DEFAULT 8K buffer cache 0 0 4194304

DEFAULT 16K buffer cache 0 0 4194304

COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE GRANULE_SIZE

------------------------------ ------------ ------------------- ------------

DEFAULT 32K buffer cache 0 0 4194304

ASM Buffer Cache 0 96468992 4194304

13 rows selected.

Step3: 查看是否启用动态buffer cache advisory参数.

SQL> show parameter advice

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_cache_advice string ON

SQL> show parameter statistics

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

statistics_level string TYPICAL

timed_os_statistics integer 0

timed_statistics boolean TRUE

1.如果statistics_level的值是typical或all，则db_cache_size为on

2.三个值：on、off、ready；

ready是advisory关闭，但是系统为其分配了内存，off->ready->on,正常开启顺序；

ready->off/off->on,报错ORA-4031(inability to allocate from the shared pool)

只有ready->on->off来关闭

Step4: 查看v$db_cache_advice视图收集的buffer cache advisory信息.

SQL>select name,size_for_estimate,estd_physical_read_factor,estd_physical_reads

2 from v$db_cache_advice;

NAME SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

-------------------- ----------------- ------------------------- -------------------

DEFAULT 8 1.6735 11840

DEFAULT 16 1.4867 10518

DEFAULT 24 1.3121 9283

DEFAULT 32 1.1869 8397

DEFAULT 40 1.1047 7816

DEFAULT 48 1.0329 7307

DEFAULT 56 1 7075

DEFAULT 64 1 7075

DEFAULT 72 1 7075

DEFAULT 80 1 7075

DEFAULT 88 1 7075

NAME SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

-------------------- ----------------- ------------------------- -------------------

DEFAULT 92 1 7075

DEFAULT 96 1 7075

DEFAULT 104 1 7075

DEFAULT 112 1 7075

DEFAULT 120 1 7075

DEFAULT 128 1 7075

DEFAULT 136 1 7075

DEFAULT 144 1 7075

DEFAULT 152 1 7075

DEFAULT 160 1 7075

21 rows selected.

NOTE: 在factor或reads变化不大的情况下，无需增加buffer cache因为不会带来significant benefit,根据视图，我的db_cache_size调到32M就够用了。

ESTD_PHYSICAL_READ_FACTOR:Physical read factor for this cache size, which is the ratio of the number of estimated physical reads to the number of reads in the real cache. If there are no physical reads in the real cache, the value of this column is null.

因为我没有非标准块所以直接查询无妨，标准的查询语句应为：

select size_for_estimate,buffers_for_estimate,estd_physical_read_fact,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之二：管理buffer cache

一、Server process和Buffer Cache：

SP是如何请求读取数据块的

1.SP使用hash函数检查所请求的数据块是否在buffer cache里：

a．如果相应的buffer被找到，则该buffer被移到LRU列表的MRU（most recently used）端，这个叫做逻辑读（logical read），因为实际没有I/O产生；

b．如果相应的buffer未被找到，则SP必须从数据文件里读取所需数据块。

2.在执行b之前，SP会去扫描LRU列表寻找空闲的buffer块（free buffer），与此同时会将已经被SP修改的buffer块放到write list（或checkpoint quene）上，以便于在检查点发生时这些dirty buffer里面的数据能够被拷回磁盘。

3. 如果checkpoint quene超过了它的阀值（size threshold），或者如果SP经历了一次查找阀值（search threshold）还未找到空闲buffer块，SP就会通知DBWn将dirty buffer里面的数据flush掉（写到磁盘）；

4. 直到一个free buffer块被找到，SP就会将数据文件的数据块读取到该free buffer中，如果该数据块不是读一致性的（not read sonsistent），SP就会从前镜像信息（past image information）中构建一个读一致性拷贝。

At any given time, the buffer cache may hold multiple copies of a single database block.

Only one current copy of the block exists, but to satisfy queries server processes may need to construct read-consistent copies from past image information(a CR block).

二、调优目的与工具

目的：减少物理I/O操作，提高buffer cache的命中率，减轻DB workload。

调优指标及工具：等待事件、直接命中率、缓冲池大小、查找空闲buffer的艰难度。

等待事件：v$system_event, v$session_event, v$session_wait.

主要的几个wait events：Buffer Busy Waits, Free Buffer Inspected, Free Buffers Waits.

SQL> SELECT event, total_waits

2 FROM v$system_event

3 WHERE event in

4 ('free buffer waits',

5 'buffer busy waits');

EVENT TOTAL_WAITS

---------------------- -----------

free buffer waits 337

buffer busy waits 3466

Buffer cache尺寸：v$db_cache_advice

(db_cache_size, db_keep_cache_size,db_recycle_cache_size)

Cache hit ratio : v$sysstat (含该计算CHR的参数), v$sesstat, Statspack.

Hit Ratio = 1 – [(physical reads – physical reads direct - physical reads direct (lob) )] / session logical reads

SQL> select name,value from v$sysstat

2 where name in ('session logical reads', #逻辑读请求次数

3 'physical reads', #从磁盘读取的数据块个数

4 'physical reads direct', #不需要Cache，直接读的个数

5 'physical reads direct (lob)'); #直接读大的二进制对象的次数

NAME VALUE

---------------------------------------------------------------- ----------

session logical reads 171695

physical reads 7242

physical reads direct 24

physical reads direct (lob) 0

SQL> SELECT 1 - (phy.value - lob.value - dir.value)

2 / ses.value "CACHE HIT RATIO"

3 FROM v$sysstat ses, v$sysstat lob,

4 v$sysstat dir, v$sysstat phy

5 WHERE ses.name = 'session logical reads'

6 AND dir.name = 'physical reads direct'

7 AND lob.name = 'physical reads direct (lob)'

8 AND phy.name = 'physical reads';

CACHE HIT RATIO

---------------

.890831495

Free Buffer Inspected

You should consider increasing the buffer cache size if there are high or increasing values for the Free Buffer Inspected system statistic. This statistic is the number of buffers skipped to find a free buffer. Buffers are skipped because they are dirty or pinned.

SQL> select name,value from v$sysstat

2 where name = 'free buffer inspected';

NAME VALUE

---------------------------------------------------------------- ----------

free buffer inspected 0

其他视图：v$buffer_pool_statistics（各个缓冲池信息）, v$buffer_pool（描述多缓冲池）, v$bh (描述buffer cache里面的块)

SQL> select name,physical_reads,db_block_gets,

2 consistent_gets from v$buffer_pool_statistics;

NAME PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS

-------------------- -------------- ------------- ---------------

DEFAULT 7220 40604 132453

调优方法：

1.sql语句：是否调优了？降低其对数据块的请求，即降低workload,杜绝无条件扫描大记录表，

例：select *语句；

2.索引：是否有创建的必要来减少不必要的块阅读？

3.v$db_cache_advice：是有有必要修改buffer cache的大小？

4.多缓冲池技术（multiple buffer pool）：对不同类型的分流

5.常查的小表被固定到缓冲池中：cached

Technical Note

You need to consider the impact of operating system caching. For example, the Oracle server may show a high rate of physical I/O that does not appear at the operating system level. This could mean that Oracle blocks, aged out of the buffer cache, are kept in the operating system cache and can be accessed very quickly. However, as a general rule it is best to bypass the operating system cache, because:

* More memory may be required to maintain duplicate blocks in memory (one block in the operating system cache and one in the database buffer cache).

* There is the CPU overhead of copying blocks from the operating system cache to the database buffer cache.

BUFFER CACHE之三：主要的等待事件

原因：资源紧张，等待其释放。

原因的原因：1. lgwr和DBWn进程写太慢；2. Buffer和latch不可用

原因的原因的原因：全表扫描、library cache latches数太多等。

视图：V$SYSTEM_EVENT (instance-level waits), V$SESSION_EVENT (session-level waits)

注意：等待与共享池和缓冲池中的高命中率（high hit ratios）无关，换句话说，高命中率也会有长时间的等待。

* Buffer Busy Waits

This wait indicates that there are some buffers in the buffer cache that multiple

processes are attempting to access concurrently. To determine the wait statistics for each class of buffer, query the v$waitstat view. Common buffer classes that have buffer busy waits include:

缓冲池中的buffer块被多个进程并发访问，用v$waitstat视图查看各类buffer的等待统计数据。

- Data Block

If the contention is on tables or indexes (not the segment header):

Check for SQL statements using unselective indexes.

Check for right-hand-indexes (that is, indexes that are inserted at the same

point by many processes; for example, those which use sequence number

generators for the key values).

Consider using automatic segment-space management or increasing free lists to

avoid multiple processes attempting to insert into the same block.

- Data Block (continued)

The v$session_wait view will provide the file and block numbers (in the

P* columns) for those blocks that have the most frequent block waits. These

blocks can then be mapped to which object they belong.

- Undo Header

Displays contention on rollback segment header: If you are not using automatic

undo management, then add more rollback segments.

- Undo Block

Displays contention on rollback segment block: If you are not using automatic

undo management, consider making rollback segment sizes larger.

* Free Buffer Inspected

This is a measure of how many buffers on the LRU list are inspected by a process

looking for a free buffer (writing a new block) before triggering DBWn to flush the dirty buffers to disk.

* Free Buffer Waits

This wait event indicates that a server process was unable to find a free buffer and has requested the database writer to make free buffers by writing out dirty buffers. A dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWn has written the blocks to disk.

To resolve the contention, DBWn has to make buffers available faster for

overwriting. To achieve this, examine ways of speeding up the write process. This

event is also an indication that the buffer cache is too small. Examine the hit ratios for the buffer cache to determine if the cache should be resized.

If this event occurs frequently, examine the session waits for DBWn to determine

whether there is anything delaying DBWn.

Causes

DBWn may not be keeping up with writing dirty buffers in the following situations:

- The I/O system is slow.

Solution: Check that the files are equally distributed across all devices. If that produces no effect get faster disks or place offending files onto faster disks.

- The I/O is waiting for resources, such as latches.

Solution: Check that the files are equally distributed across all devices. If that produces no effect get faster disks or place offending files onto faster disks.

- The buffer cache is so small that DBWn spends most of its time cleaning out

buffers for server processes.

Solution: Increase the buffer cache size.

- The buffer cache is so large that one DBWn process cannot free enough buffers

in the cache to satisfy requests.

Solution: Decrease the buffer cache size or initialize more database writer

processes.

BUFFER CACHE之四：Cache Hit Ratio和缓冲池的问题

全表扫描，应用程序设计，随意查询大表，查询对象过于集中。

一、命中率不是buffer cache性能优化的重中之重：

1. 设计糟糕的数据库可能有99%的命中率

2. 只是性能优化的一部分