C/C++教程

oracle 调优参数,oracle性能优化参数调整

本文主要是介绍oracle 调优参数,oracle性能优化参数调整,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

oracle 调优参数,oracle性能优化参数调整

黑你就是没商量于 2021-04-04 07:25:16 发布192 收藏 文章标签: oracle 调优参数  

1.SHARE_POOL_SIZE

数据字典的命中率计算:

select ((1 - (Sum(GetMisses) / (Sum(Gets) + Sum(GetMisses)))) * 100) "Hit Rate"

from V$RowCache

where Gets + GetMisses <> 0;

数据字典的命中率低于95%可考虑提升SHARE_POOL_SIZE

LIBRARY CACHE(reload)的命中率计算:

select Sum(Pins) "Hits",Sum(Reloads) "Misses",((Sum(Reloads) / Sum(Pins)) * 100)"Reload %"

from V$LibraryCache;

LIBRARY CACHE的命中率计算:

select Sum(Pins) "Hits",Sum(Reloads) "Misses",Sum(Pins) / (Sum(Pins) + Sum(Reloads)) "Hit Ratio"

from V$LibraryCache;

LIBRARY CACHE(reload)的命中率高于1%或LIBRARY CACHE命中率低于95%可考虑增大SHARE_POOL_SIZE

2.db_cache_size

data cache hit ratio计算:

select sum(decode(name,'physical reads',value,0)) phys,

sum(decode(name,'db block gets',value,0)) gets,sum(decode(name,'consistent gets', value,0)) con_gets,

(1 - (sum(decode(name,'physical reads',value,0)) /

(sum(decode(name,'db block gets',value,0)) +

sum(decode(name,'consistent gets',value,0))))) * 100 hitratio

from v$sysstat;

一般可让命中率保持95%以上

可通过查看V$DB_CHCHE_ADVICE视图,确定要不要调整:

NAME       SIZE_FOR_ESTIMATE SIZE_FACTOR  ESTD_PHYSICAL_READ_FACTOR

--------------------    -----------------      -----------      -------------------------

DEFAULT         4        .1667 1        .8136

DEFAULT         8        .3333         1.0169

DEFAULT        12         .5          1.0085

DEFAULT        16         .6667           1

DEFAULT       20                                .8333            1

DEFAULT                            24                                   1                                                1

由上可看出ESTD_PHYSICAL_READ_FACTOR达到1时SIZE_FOR_ESTIMATE 为16,而当前的值为(SIZE_FACTOR)列24,因此可考虑适当减少DB_CACHE_SIZE.

这篇关于oracle 调优参数,oracle性能优化参数调整的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!