中，size_for_estimate表示预计的数据库高速缓冲区的大小（即：db_cache_size的值）。ESTD_PHYSICAL_READS表示预计的物理读。
当增加数据库高速缓冲区的的时候，物理读（ESTD_PHYSICAL_READS）越少，说明命中率越高。
1.优化缓冲区大小、提高服务器的命中率
db_cache_size big integer
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
---Oracle9i数据 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
160 19850 2.1053
240 29775 1.5819
320 39700 1.4262
400 49625 1.3543
480 59550 1.278
560 69475 1.1893
640 79400 1.1325
720 89325 1.0762
800 99250 1
880 .7067
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
960 .3991
1040 .2305
1120 .1927
1200 .1506
1280 .1501
1360 .1501
1440 .1501
1520 .1501
1600 .1501
SIZE_FOR_ESTIMATE M 为单位：
当SIZE_FOR_ESTIMATE=80M 的时候 ESTD_PHYSICAL_READS=
当SIZE_FOR_ESTIMATE=1120M 的时候 ESTD_PHYSICAL_READS=
当SIZE_FOR_ESTIMATE=1280M 的时候 ESTD_PHYSICAL_READS=
之后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
SQL>alter system set SGA_MAX_SIZE=3500M scope=spfile;
系统已更改。
重新启动与关闭解决
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers 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 1 1528
SIZE_FOR_ESTIMATE BUFFERS_FOR_ESTIMATE ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS
----------------- -------------------- ------------------------- -------------------
960 1 1528
1040 1 1528
1120 1 1528
1200 1 1528
1280 1 1528
1360 1 1528
1440 1 1528
1520 1 1528
1600 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
----------------------
.
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area bytes
Fixed Size bytes
Variable Size bytes
Database Buffers bytes
Redo Buffers 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
----------------------
.
-----查询推荐的值
show parameter db_block_size
8192
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
----------------------
.
说明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
DEFAULT 256
DEFAULT 384
DEFAULT 512
DEFAULT 640
DEFAULT 768
DEFAULT 896
DEFAULT 1024
DEFAULT 1152
DEFAULT 1280
--此时开始保持在一个稳定的读取值
alter system set db_cache_size=1196M
DEFAULT 1296
NAME SIZE_FOR_ESTIMATE ESTD_PHYSICAL_READS
-------------------- ----------------- -------------------
DEFAULT 1408
DEFAULT 1536
DEFAULT 1664
DEFAULT 1792
DEFAULT 1920
DEFAULT 2048
DEFAULT 2176
DEFAULT 2304
DEFAULT 2432
DEFAULT 2560
--修改
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';
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容，请联系我们，一经查实，本站将立刻删除。
如需转载请保留出处：https://51itzy.com/kjqy/37465.html