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来关闭