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 sizeNAME TYPE VALUE------------------------------------ ----------- ------------bitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608db_16k_cache_size big integer 0db_2k_cache_size big integer 0db_32k_cache_size big integer 0db_4k_cache_size big integer 0db_8k_cache_size big integer 0db_block_size integer 8192db_cache_size big integer 0db_keep_cache_size big integer 0db_recovery_file_dest_size big integer 2GNAME TYPE VALUE------------------------------------ ----------- -----------db_recycle_cache_size big integer 0global_context_pool_size stringhash_area_size integer 131072java_max_sessionspace_size integer 0java_pool_size big integer 0large_pool_size big integer 0max_dump_file_size string UNLIMITEDobject_cache_max_size_percent integer 10object_cache_optimal_size integer 102400olap_page_pool_size big integer 0parallel_execution_message_size integer 2148NAME TYPE VALUE------------------------------------ ----------- ------------sga_max_size big integer 160Mshared_pool_reserved_size big integer 2936012shared_pool_size big integer 56Msort_area_retained_size integer 0sort_area_size integer 65536streams_pool_size big integer 0workarea_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_size2 from v$sga_dynamic_components;COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE GRANULE_SIZE------------------------------ ------------ ------------------- ------------shared pool 58720256 58720256 4194304large pool 4194304 0 4194304java pool 4194304 0 4194304streams pool 0 0 4194304DEFAULT buffer cache 96468992 96468992 4194304KEEP buffer cache 0 0 4194304RECYCLE buffer cache 0 0 4194304DEFAULT 2K buffer cache 0 0 4194304DEFAULT 4K buffer cache 0 0 4194304DEFAULT 8K buffer cache 0 0 4194304DEFAULT 16K buffer cache 0 0 4194304COMPONENT CURRENT_SIZE USER_SPECIFIED_SIZE GRANULE_SIZE------------------------------ ------------ ------------------- ------------DEFAULT 32K buffer cache 0 0 4194304ASM Buffer Cache 0 96468992 419430413 rows selected.Step3: 查看是否启用动态buffer cache advisory参数.SQL> show parameter adviceNAME TYPE VALUE------------------------------------ ----------- ------------------------------db_cache_advice string ONSQL> show parameter statisticsNAME TYPE VALUE------------------------------------ ----------- ------------------------------statistics_level string TYPICALtimed_os_statistics integer 0timed_statistics boolean TRUE1.如果statistics_level的值是typical或all，则db_cache_size为on2.三个值：on、off、ready；ready是advisory关闭，但是系统为其分配了内存，off->ready->on,正常开启顺序；ready->off/off->on,报错ORA-4031（inability to allocate from the shared pool）只有ready->on->off来关闭

Oracle高效分页存储过程Oracle 10g 自动共享内存管理相关资讯 Oracle教程

Oracle中纯数字的varchar2类型和（07/29/2015 07:20:43）
Oracle教程：Oracle中查看DBLink密（07/29/2015 07:16:55）
[Oracle] SQL*Loader 详细使用教程（08/11/2013 21:30:36）

Oracle教程：Oracle中kill死锁进程（07/29/2015 07:18:28）
Oracle教程：ORA-25153 临时表空间（07/29/2015 07:13:37）
Oracle教程之管理安全和资源（04/08/2013 11:39:32）

本文评论查看全部评论（0）