调试oracle缓存命中率来增加数据库的效率

下面两篇文章全部是转帖~

原地址：http://www.im80hou.com/html/oracle/2009/0722/824.html

理论：

内存与硬盘的速度差异，从内存中读取数据要比从硬盘中读取数据快10000倍

众所周知，从内存中读取数据要比从硬盘中读取数据快10000倍。这主要是内存与硬盘的速度差异所造成的。为此在Oracle数据库中提出一个数据缓存的概念。简单的说，就是数据库会把用户前几次查询的数据放置在数据缓存中。下次假如其它用户也需要用到类似的数据，则直接中数据缓存中读取数据，而不是从硬盘中读取数据，以提高数据库的性能。这个数据库缓存命中率就是来评价缓存应用效率的一个指数，它是指那些不用从硬盘上无力操作的数据块比例。

　　一、 提高数据缓存命中率对数据库性能的影响。

　　在Oracle数据库中，有很多因素为影响到数据库的缓存命中率。不过根据笔者的经验，笔者认为主要影响缓存命中率的有三个因素，分别为连接、索引与数据缓存的大小。表与表之间的连接或者索引设置的不当的话，都会降低数据缓存的命中率。不过表与表之间的连接、索引等等对数据库性能的影响，是一个比较综合的影响，为此笔者不在这里做过多的阐述。以后若有计划的话，笔者会把他们分开来详细讲述。笔者今天要谈的是，假如调整数据缓存的大小来提高这个数据库缓存命中率。

　　在Oracl初始化参数文件中，有一个参数叫做DB_CACHE_SIZE。顾名思义，这个参数主要是用来设置数据库缓存大小的。也许有人会讲，把这个参数设置越大越好，如此就可以提高数据缓存命中率。这个说法是不科学的，至少是不全面的。假如把这个参数设置的太大，则系统在切换后就很可能会莫名其妙的罢工;而若这个参数设置的太小的花，则管理员无论如何优化数据库系统，其性能都会十分的有限，由于数据库没有足够的数据缓存来处理相关的操作。所以从这个角度讲，根据实际情况为数据库设置合适的缓存，是对数据库进行其它优化操作的前提。

　　假如不考虑其它因素(如索引、连接)的影响，则一般情况下，假如这个参数设置的比较低的话，那么最近使用得数据就会被最近一次查询的数据所覆盖掉。如此的话，假如另外一个查询需要重新调用这些被覆盖的数据，则就必须重新从服务器的硬盘中读取相关的数据。此时就会极大的降低数据库查询的效率。

　　显然，假如数据库缓存命中率降低，会影响数据库的性能，特别是查询作业的性能。那么这个影响的程度到底有多大的?是否值得数据库管理员花大力气去调整这个参数呢?笔者个人认为是值得的，而且大部分数据库管理专家跟笔者有相同的见解。如一个数据库的前辈，美国的Richard，笔者所崇拜的偶像，其就明确提出，一个好的数据库系统，数据库缓存命中率应该要提到95%以上。而且他还明确的提出，由于系统从硬盘与内存中读取数据在性能上存在巨大的差异，为此当数据库性能不佳时，假如将数据库缓存的命中率从90%提高到95%时，数据库性能会得到成倍的改善。甚至其改善的效果可以达到300%。这是以给很令人惊喜的数据。

　　不过这里需要注意的是，缓存命中率也并不是越高越好。真理多走一步，就可能成为了谬误。有时候，异常高的命中率通常也有可能是由于差的索引与连接所造成的。所以说，此时命中率高，反而其性能会下降。为此这个命中率参数，往往还需要跟一些其它的性能指标综合起来使用，以判断是否需要在提高缓存命中率上花功夫。

　　二、 如何提高数据库缓存命中率?

　　在Oracle数据库中，最简单的提高数据库缓存命中率的方法就是调整数据库缓存的大小。通常情况下，数据库系统会根据服务器内存的大小，自动设置这个数据库缓存。但是在一些复杂的情况下，这个默认的数据缓存大小往往不能够满足企业的需要。如在一台数据库服务器中可能部署有多个应用服务器，此时Oracle数据库系统自己并不能够发现这种情况。为此这个默认设置并不能够保证数据库服务器的最佳性能。另外，有些企业中数据库系统可能主要用来进行数据查询，其查询操作占整个数据库操作的90%以上。此时也需要调整数据缓存的大小，以提高数据库的性能。为此数据库管理员通常需要根据企业的实际应用，来调整这个数据缓存的大小。

　　在Oracle数据库中，可以通过DB_CACHE_SIZE这个参数来更改数据库默认的数据缓存大小。不过需要注意的是，这个参数并不是设置的越大越好。假如数据缓存设置的比较大，则可能会跟其它的应用程序冲突，从而导致两败俱伤的结果。为此，数据库管理员需要根据相关的参数来调整这个数据缓存的大小。在下面第三部分的内容中，笔者将会给大家介绍如何利用Oracle数据库提供的工具来调整这个数据缓存的大小。不过在谈这个方法之前，笔者先要强调一个问题，即影响数据缓存命中率的因素有很多。除了这个数据缓存大小为，还有索引、表连接等等。有时候可能索引或者表连接不合理，反而会造成很高的索引命中率。为此假如由于数据缓存命中率不高或者异常的高，在调整数据缓存之前最好先对索引或者表连接进行优化。对他们进行调整之后，再来判断是否需要调整数据缓存。

　　另外需要注意的是，数据缓存大小的调整受到服务器内存大小的限制。为此需要根据内存的多少、以及有多少应用服务在共享这个内存等因素来调整数据缓存的大小。
     
    三、 如何判断是否需要调整数据缓存大小?

　　从以上的分析中可以看出，数据缓存大小并不是越大越好。需要根据数据库的实际运行情况来进行判断。在Oracle数据库中，就提供了一张动态视图。通过查询这张视图可以帮助管理员判断当前的缓存是否符合需要。如 下图所示。其中第一个字段就表示从硬盘中读取数据的情况。如高要提高数据缓存的命中率，那么就需要减少从硬盘中读取数据的次数。一般来说，最好把这个次数控制在1以下。另外在这个视图中，还有一个字段叫做size_factor。有时候这个字段对于数据库管理员判断是否需要调整数据缓存非常有帮助。

 

　　根据这个视图管理员就可以判断是否需要调整数据缓存的大小，即将第一个字段的值控制在1以内所需要的数据缓存。不过需要注意的是，这个视图中的数据只是Oracle系统根据当前的系统状况而预计的一个结果。有时候跟实际的情况会有一定的差异。为此在调整好数据缓存之后，数据库管理员仍然需要进行跟踪。如连续7天运行这个视图，看看调整了数据缓存之后，是否起到了预计的效果，数据缓存命中率是否得到了提高。

　　一般比较理想的情况就是要将数据库的缓存命中率保持在95%以上。假如低于95%，则最好采取相应的措施，如提高数据缓存大小等手段来提高数据缓存的命中率。不过也不要心太狠，想将这个命中率保持在100%。大家都知道，从50%提高到95%，可能不怎么吃力。但是要想从97%提高到99%，可能需要花费更多的精力。虽然前者提高了45个百分点，而后者只提高了2个百分点。但是还是前者的调整简单的多。所以，一般情况下，这个缓存命中率只要能够达到97%左右即可。达到这个百分比之后，就不用再费心去调整数据缓存，或者采取其他措施来提高这个缓存的命中率。由于再想提高的话，会很难。

　　其次需要注意的是，假如对数据库系统进行了比较大的调整，如改变了表的连接、调整了索引或者对数据库系统进行了升级。那么在调整前后最好能够运行一下这个视图。然后将前后两个结果进行对比。后续管理员能够从中得到一些比较宝贵的提示。如将内存从6个G升级到8个G，则可以从前后数据中分析出，内存升级对于数据缓存命中率的影响。这将为以后的工作积累经验。

 

 

对于上面理论的具体操作如下：

原地址：http://blog.csdn.net/RadonMar/archive/2009/07/15/4349028.aspx

 

转贴部分内容，氡马自己总结了部分内容

【查看Oracle建议的缓冲区设置】

Oracle自身提供了一个缓冲区建议值的视图v$db_cache_advice，要查看Oracle对缓冲区的建议值使用如下SQL语句：

select block_size, size_for_estimate, size_factor, estd_physical_read_factor, estd_physical_reads from v$db_cache_advice;

例如得到如下结果：

SIZE_FOR_ESTIMATE SIZE_FACTOR ESTD_PHYSICAL_READ_FACTOR ESTD_PHYSICAL_READS

20 0.0893 6.1454 270840 
40 0.1786 3.0632 135003 
60 0.2679 2.4908 109775 
80 0.3571 2.1411 94362 
100 0.4464 1.9619 86464 
120 0.5357 1.8235 80366 
140 0.625 1.6081 70873 
160 0.7143 1.5038 66275 
180 0.8036 1.3957 61510 
200 0.8929 1.1541 50861 
220 0.9821 1.0089 44465 
224 1 1 44072 
240 1.0714 0.8524 37568 
260 1.1607 0.8184 36068 
280 1.25 0.7965 35103 
300 1.3393 0.793 34948 
320 1.4286 0.7908 34853 
340 1.5179 0.7905 34841 
360 1.6071 0.7905 34841 
380 1.6964 0.7905 34841 
400 1.7857 0.7873 34698 

解释一下v$db_cache_advice的作用，Oracle利用这个视图对当前数据库db_cache_size大小提出一建议，提出了20个db_cache_size大小及相关的物理读的估计值，估计的db_cache_size大小的范围从当前的10%到200%，以10%为一个增加单位。 
下面解释几个列的含义 
size_for_estimate：估计的cache size大小 
size_factor：   估计的cache size大小与当前大小的比值 
estd_physical_reads：在估计的cache size大小情况下，会产生的物理读数量 
estd_physical_read_factor：估计的物理读数量与当前物理读数量的比值。 

从上面的数据中可以看到，当前db_cache_size为224M，其物理读为44072，增加cache size大小会减少物理读，当cache size到340M时，物理读的减少不再明显，因此db_cache_size大小为340M在目前情况下是一个合适的值。 
由于这里用的是自动共享内存管理方式，应该增加sga_target，这样db_cache_size大小会相应增加，要增加sga_target要先增加sga_max_size。

【修改数据库参数方法】

在Oralce用户下，使用sqlplus "/as sysdba"登录，注意引号。

在提示符下输入show parameter则可以看到所有参数，如果要看一个具体参数，如db_cache_size则使用showparameter db_cache_size

如果要修改一个参数的值则使用：

ALTER SYSTEM SET sga_max_size=600M scope=spfile;

【与缓冲区有关数据库参数】

sga_max_size，限定SGA的大小

sga_target，设定SGA的实际大小

db_cache_size，设定Oracle内存缓冲区的大小