`

db_cache_buffer修改策略(已测试)

 
阅读更多
1.优化缓冲区大小、提高服务器的命中率
db_cache_size                        big integer 838860800
2.查看缓冲区命中率是否需要调优.

select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
     "Buffer Cache Hit Ratio"
  from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
  where physical.name = 'physical reads'
  and direct.name='physical reads direct'
  and lobs.name='physical reads direct (lob)'
  and logical.name='session logical reads';
当命中率>90%说明命中率很高了

3。获取推荐的值
select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from v$db_cache_advice 
where block_size='8192' and advice_status='ON';

set linesize 1000
---20100619Oracle9i数据 Solaris 9i操作系统
SQL> set linesize 1000
SQL> select size_for_estimate, buffers_for_estimate ,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READS from v$db_cache_advice 
    where block_size='8192' and advice_status='ON';

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
               80                 9925                   28.9757          1436311200
              160                19850                    2.1053           104360120
              240                29775                    1.5819            78413087
              320                39700                    1.4262            70693980
              400                49625                    1.3543            67131735
              480                59550                     1.278            63349434
              560                69475                    1.1893            58954568
              640                79400                    1.1325            56135206
              720                89325                    1.0762            53347837
              800                99250                         1            49569438
              880               109175                     .7067            35030953

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
              960               119100                     .3991            19784701
             1040               129025                     .2305            11423374
             1120               138950                     .1927             9552903
             1200               148875                     .1506             7466278
             1280               158800                     .1501             7438186
             1360               168725                     .1501             7438186
             1440               178650                     .1501             7438186
             1520               188575                     .1501             7438186
             1600               198500                     .1501             7438186
SIZE_FOR_ESTIMATE  M 为单位:
当SIZE_FOR_ESTIMATE=80M 的时候 ESTD_PHYSICAL_READS=1436311200
当SIZE_FOR_ESTIMATE=1120M 的时候 ESTD_PHYSICAL_READS=9552903
当SIZE_FOR_ESTIMATE=1280M 的时候 ESTD_PHYSICAL_READS=7438186
之后ESTD_PHYSICAL_READS固定了
所以应该过大db_cache_size=1120M的值使得
4.修改发现DB_cache_size太大了。过大SGA区域解决
alter system set db_cache_size=1120M
--sga设置太小了导致
SQL>  alter system set db_cache_size=1120M;
alter system set db_cache_size=1120M
*
ERROR 位于第 1 行:
ORA-02097: 无法修改参数,因为指定的值无效
ORA-00384: 没有足够的内存来增加高速缓存的大小
之前的值
sga_max_size                         big integer 1494715120
SQL>alter system set SGA_MAX_SIZE=3500M scope=spfile;
系统已更改。

重新启动与关闭解决
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 3675756336 bytes
Fixed Size                   735024 bytes
Variable Size            2835349504 bytes
Database Buffers          838860800 bytes
Redo Buffers                 811008 bytes
数据库装载完毕。
数据库已经打开。
查看命中率\当前只有80的满足要求
SQL>  select size_for_estimate, buffers_for_estimate ,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READS from v$db_cache_advice 
  2      where block_size='8192' and advice_status='ON';

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
               80                 9925                         1                1528
              160                19850                         1                1528
              240                29775                         1                1528
              320                39700                         1                1528
              400                49625                         1                1528
              480                59550                         1                1528
              560                69475                         1                1528
              640                79400                         1                1528
              720                89325                         1                1528
              800                99250                         1                1528
              880               109175                         1                1528

SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
              960               119100                         1                1528
             1040               129025                         1                1528
             1120               138950                         1                1528
             1200               148875                         1                1528
             1280               158800                         1                1528
             1360               168725                         1                1528
             1440               178650                         1                1528
             1520               188575                         1                1528
             1600               198500                         1                1528
命中率降低了?        
    SQL> select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
  2       "Buffer Cache Hit Ratio"
  3    from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
  4    where physical.name = 'physical reads'
  5    and direct.name='physical reads direct'
  6    and lobs.name='physical reads direct (lob)'
  7    and logical.name='session logical reads';

Buffer Cache Hit Ratio
----------------------
            .906673167

继续缩小SGA大小
SQL>alter system set SGA_MAX_SIZE=3000M scope=spfile;

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 3155661888 bytes
Fixed Size                   734272 bytes
Variable Size            2315255808 bytes
Database Buffers          838860800 bytes
Redo Buffers                 811008 bytes
数据库装载完毕。
数据库已经打开。
select size_for_estimate, buffers_for_estimate ,ESTD_PHYSICAL_READ_factor,ESTD_PHYSICAL_READS from v$db_cache_advice 
    where block_size='8192' and advice_status='ON';


继续测试:等待30分钟之后测试
    SQL> alter system set db_cache_size=1120M;
   
    select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
     "Buffer Cache Hit Ratio"
  from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
  where physical.name = 'physical reads'
  and direct.name='physical reads direct'
  and lobs.name='physical reads direct (lob)'
  and logical.name='session logical reads';
  ---命中率逐渐的提高了
  ---半个小时之后查询命中率是98%
  SQL> select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
  2       "Buffer Cache Hit Ratio"
  3    from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
  4    where physical.name = 'physical reads'
  5    and direct.name='physical reads direct'
  6    and lobs.name='physical reads direct (lob)'
  7    and logical.name='session logical reads';

Buffer Cache Hit Ratio
----------------------
            .980309028
-----查询推荐的值
show parameter db_block_size
8192

select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from
v$db_cache_advice  where block_size='8192' and advice_status='ON';

NAME                 SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
-------------------- ----------------- -------------------
DEFAULT                            112                9581
DEFAULT                            224                9514
DEFAULT                            336                9514
DEFAULT                            448                9514
DEFAULT                            560                9514
DEFAULT                            672                9514
DEFAULT                            784                9514
DEFAULT                            896                9514
DEFAULT                           1008                9514
DEFAULT                           1120                9514
DEFAULT                           1232                9514

NAME                 SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
-------------------- ----------------- -------------------
DEFAULT                           1344                9514
DEFAULT                           1456                9514
DEFAULT                           1568                9514
DEFAULT                           1680                9514
DEFAULT                           1792                9514
DEFAULT                           1904                9514
DEFAULT                           2016                9514
DEFAULT                           2128                9514
DEFAULT                           2240                9514
显示只要112M大小的空间就可以稳定降低ESTD_PHYSICAL_READS
alter system set db_cache_size=112M
NAME                 SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
-------------------- ----------------- -------------------
DEFAULT                             16                   0
DEFAULT                             32                   0
DEFAULT                             48                   0
DEFAULT                             64                   0
DEFAULT                             80                   0
DEFAULT                             96                   0
DEFAULT                            112                   0
DEFAULT                            128                   0
DEFAULT                            144                   0
DEFAULT                            160                   0
DEFAULT                            176                   0

NAME                 SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
-------------------- ----------------- -------------------
DEFAULT                            192                   0
DEFAULT                            208                   0
DEFAULT                            224                   0
DEFAULT                            240                   0
DEFAULT                            256                   0
DEFAULT                            272                   0
DEFAULT                            288                   0
DEFAULT                            304                   0
DEFAULT                            320                   0
---查看命中率
  select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
     "Buffer Cache Hit Ratio"
  from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
  where physical.name = 'physical reads'
  and direct.name='physical reads direct'
  and lobs.name='physical reads direct (lob)'
  and logical.name='session logical reads';

Buffer Cache Hit Ratio
----------------------
            .982795739
说明Oracle更换峰值的情况决定db_cache_size的大小。
所以设置最大峰值满足的情况。
alter system set db_cache_size=1120M;满足峰值的时候最大值
select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from
v$db_cache_advice  where block_size='8192' and advice_status='ON';


oracle10g Solaris 10 sparc系统调整

--查
  select name,size_for_estimate,v$db_cache_advice.ESTD_PHYSICAL_READS from
v$db_cache_advice  where block_size='8192' and advice_status='ON';

-------------------- ----------------- -------------------
DEFAULT                            128             1206647
DEFAULT                            256             1067789
DEFAULT                            384              987752
DEFAULT                            512              965164
DEFAULT                            640              913815
DEFAULT                            768              893485
DEFAULT                            896              867321
DEFAULT                           1024              808884
DEFAULT                           1152              599909
DEFAULT                           1280              344618
--此时开始保持在一个稳定的读取值
alter system set db_cache_size=1196M

DEFAULT                           1296              321763

NAME                 SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
-------------------- ----------------- -------------------
DEFAULT                           1408              285030
DEFAULT                           1536              280506
DEFAULT                           1664              280325
DEFAULT                           1792              279816
DEFAULT                           1920              266871
DEFAULT                           2048              235838
DEFAULT                           2176              231298
DEFAULT                           2304              228483
DEFAULT                           2432              224971
DEFAULT                           2560              219467
--修改
alter system set db_cache_size=1196M
--查看命中率
  select 1 - ((physical.value - direct.value - lobs.value) / logical.value)
     "Buffer Cache Hit Ratio"
  from v$sysstat physical,v$sysstat direct,v$sysstat lobs,v$sysstat logical
  where physical.name = 'physical reads'
  and direct.name='physical reads direct'
  and lobs.name='physical reads direct (lob)'
  and logical.name='session logical reads';


在oracle9I+windows 2003中实际应用环境中测试,效果不错。很明显。感谢原作者。嘿嘿。
分享到:
评论

相关推荐

    oracle性能调优之buffer cache

    Buffer Cache 的大小可以通过设置 db_block_size、db_cache_size、db_recycle_cache_size 和 db_keep_cache_size 等参数来控制。同时,也可以通过设置 SGA_TARGET,实现自动管理。 Buffer Cache 的优化 为了提高 ...

    深入Buffer Cache 原理

    ### 深入Buffer Cache 原理 #### Buffer Cache 概述 Buffer Cache作为System Global Area (SGA) 的一部分,在Oracle数据库中扮演着极其重要的角色。它的主要任务是缓存数据块以减少磁盘I/O操作,提高数据访问速度...

    Oracle 中 Buffer Cache 的研究.pdf

    Buffer Cache的工作过程涉及到LRU(Least Recently Used)列表,这是一个常用的缓存替换策略,用于管理Buffer Cache中的缓存块。当Buffer Cache空间不足时,LRU列表会根据最近最少使用的缓存块进行淘汰,以腾出空间...

    Oracle基础.doc

    - SGA包括共享池(Share Pool)、数据缓冲区(DB Buffer Cache)、重做日志缓冲区(Redo Log Buffer)、大缓冲池(Large Pool)、Java池(Java Pool)和Stream Buffer(10g引入)等。 - 其中,数据缓冲区又细分为...

    oracle性能调优

    而在Oracle 9i及更高版本中,可以修改启动参数文件后创建服务器参数文件,或者直接使用SQL命令如`ALTER SYSTEM SET db_cache_size=200M SCOPE=SPFILE;`进行修改。 总之,Oracle性能调优特别是内存参数调优是一项...

    Statspack工具

    这一节展示了数据库缓存的大小,包括数据库缓冲区缓存(DB_CACHE_SIZE)、保持缓存(DB_KEEP_CACHE_SIZE)、回收缓存(DB_RECYCLE_CACHE_SIZE)等,这些参数对数据库性能有直接影响。例如,增大DB_CACHE_SIZE可以...

    数据高速缓存区命中率

    本文档将深入探讨数据高速缓存区(Buffer Cache)的管理与优化策略,以提升其命中率。 ### Buffer Cache原理 Buffer Cache在数据库系统中扮演着存储最近访问过的数据块的角色,以便于未来的请求能够快速地从内存中...

    Oracle内存参数调优技术详解

    在Oracle 9i及之前版本,这个区域通常用Db_cache_size表示。调整原则是:SGA中该区域应占可用内存的40%左右。 - 共享池(shared_pool_size):存放数据字典、SQL缓存和PL/SQL语法分析结果。增大共享池可以提高查询...

    Oracle数据库内存优化的讨论与配置

    - `db_buffer_cache_min`、`db_buffer_cache_max`:设定数据缓冲区的最小和最大限制,防止过度消耗内存资源。 - `db_keep_cache_size`、`db_recycle_cache_size`:分别用于保留常用数据块和回收不常用数据块,优化...

    Oracle buffer cache

    如果该 Latch 竞争激烈,可以通过增大 Buffer Cache、修改参数_db_block_lru_latches、使用多缓冲池技术等方法来解决。 Oracle Buffer Cache 是 Oracle 数据库中的一种重要机制,用于提高数据库的性能。通过理解 ...

    Oracle 10g 内存 调整报告

    - **db_cache_size**: 设置Buffer Cache的初始大小。 - **db_keep_cache_size**: 设置Keep Buffer Cache的初始大小。 - **db_recycle_cache_size**: 设置Recycle Buffer Cache的初始大小。 - **large_pool_size**: ...

    Oracle 11g体系-全面详解

    - **NoDefault Pool**: 包括db_nk_cache_size、db_keep_cache_size和db_recycle_cache_size等参数,用于指定不同类型的缓存空间。 - `db_nk_cache_size`: 指定非标准块大小内存空间,例如2k、4k、16k、32k。 - `...

    数据库参数设置技术手册.doc

    3. **DB_BLOCK_BUFFERS**:此参数设定数据库缓冲区缓存(Buffer Cache)的大小,直接影响数据读取速度。 4. **DB_BLOCK_SIZE**:定义数据库块的大小,影响数据存储和读取的效率,应根据应用需求进行合理设置。 5. ...

    oracle培训资料

    本文主要关注Oracle9i数据库的性能调优,特别是关于Buffer Cache、Shared Pool、Log Buffer以及Sort Area的调整策略。 Buffer Cache是Oracle数据库中一个重要的组件,它负责缓存数据文件中的数据块,以减少磁盘I/O...

    Oracle 数据缓冲区调优精选

    1. V$DB_CACHE_ADVICE:提供关于Buffer Cache的优化建议,包括增加Buffer Cache大小、改变高速缓存策略等。 2. V$BUFFER_POOL_STATISTICS:显示Buffer Cache的统计信息,如命中率、缓冲区获取和释放次数等。 3. AWR...

    ORACLE-SGA.docx

    - `SHARED_POOL_SIZE`、`JAVA_POOL_SIZE`、`LARGE_POOL_SIZE`、`STREAMS_POOL_SIZE`、`DB_CACHE_SIZE`和`LOG_BUFFER`:分别控制相应内存池的大小。 Oracle数据块的大小(粒度,Granule)是一个固定的内存单位,通常...

    mysql 5.5 配置文档

    15. `query_cache_size`, `query_cache_limit` 和 `query_cache_min_res_unit`: 查询缓存的总大小、单个查询结果的最大大小以及最小分配单元,用于缓存已解析的查询结果。 16. `default-storage-engine`: 默认的...

    oracle系统参数调优.doc

    本文档主要关注Oracle 9i及之前的版本,这些版本中的一些核心参数如`shared_pool_size`, `db_block_buffers`, `log_buffer`等对性能有显著影响。 1.1 Oracle 9i之前的内存参数调优: - **shared_pool_size**:用于...

Global site tag (gtag.js) - Google Analytics