分类： Oracle
2010-07-05 17:39:57
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 statisti
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';
