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. 【NOTE】 不受ASMM（Automatic Shared Memory
Management）影响，可以手动调整的组件：Log buffer，其他的buffer caches（如，keep，recycle和其他的非缺省块的cache），固定SGA和其他内部分配。 相关的初始化参数：DB_KEEP_CACHE_SIZE, DB_RECYCLE_CACHE_SIZE, DB_nK_CACHE_SIZE, and LOG_BUFFER 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';
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/24463783/viewspace-671411/ ，如需转载，请注明出处，否则将追究法律责任。
