Oracle BUFFER CACHE研究
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来关闭
|
评论暂时关闭