Oracle 性能调整的一些总结.docx

Oracle 性能调整的一些总结.docx

《Oracle 性能调整的一些总结.docx》由会员分享，可在线阅读，更多相关《Oracle 性能调整的一些总结.docx（49页珍藏版）》请在冰点文库上搜索。

Oracle性能调整的一些总结

OracleTuning的一些总结

       关于Oracle的性能调整，一般包括两个方面，一是指Oracle数据库本身的调整，比如SGA、PGA的优化设置，二是连接Oracle的应用程序以及SQL语句的优化。

做好这两个方面的优化，就可以使一套完整的Oracle应用系统处于良好的运行状态。

       本文主要是把一些OracleTuning的文章作了一个简单的总结，力求以实际可操作为目的，配合讲解部分理论知识，使大部分具有一般Oracle知识的使用者能够对OracleTuning有所了解，并且能够根据实际情况对某些参数进行调整。

关于更加详细的知识，请参见本文结束部分所提及的推荐书籍，同时由于该话题内容太多且复杂，本文必定有失之偏颇甚至错误的地方，请不吝赐教，并共同进步。

1.SGA的设置

       在OracleTuning中，对SGA的设置是关键。

SGA，是指SharedGlobalArea,或者是SystemGlobalArea,称为共享全局区或者系统全局区，结构如下图所示。

       对于SGA区域内的内存来说，是共享的、全局的，在UNIX上，必须为oracle设置共享内存段（可以是一个或者多个），因为oracle在UNIX上是多进程；而在WINDOWS上oracle是单进程（多个线程），所以不用设置共享内存段。

1.1SGA的各个组成部分

下面用sqlplus查询举例看一下SGA各个组成部分的情况：

SQL>select*fromv$sga;

NAME                     VALUE

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

FixedSize                  104936

VariableSize             823164928

DatabaseBuffers         1073741824

RedoBuffers                172032

或者

SQL>showsga

TotalSystemGlobalArea  1897183720bytes

FixedSize                  104936bytes

VariableSize             823164928bytes

DatabaseBuffers         1073741824bytes

RedoBuffers                172032bytes

FixedSize

       oracle的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了SGA各部分组件的信息，可以看作引导建立SGA的区域。

VariableSize

       包含了shared_pool_size、java_pool_size、large_pool_size等内存设置

DatabaseBuffers

       指数据缓冲区，在8i中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle三部分内存。

在9i中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。

RedoBuffers

      指日志缓冲区，log_buffer。

在这里要额外说明一点的是，对于v$parameter、v$sgastat、v$sga查询值可能不一样。

v$parameter里面的值，是指用户在初始化参数文件里面设置的值，v$sgastat是oracle实际分配的日志缓冲区大小（因为缓冲区的分配值实际上是离散的，也不是以block为最小单位进行分配的），v$sga里面查询的值，是在oracle分配了日志缓冲区后，为了保护日志缓冲区，设置了一些保护页，通常我们会发现保护页大小是8k（不同环境可能不一样）。

参考如下内容

SQL>selectsubstr（name,1,10）name,substr（value,1,10）value

      2fromv$parameterwhere;

NAME                VALUE

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

log_buffer             163840

SQL>select*fromv$sgastatwherepoolisnull;

POOL       NAME                      BYTES

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

           fixed_sga                     104936

           db_block_buffers           1073741824

           log_buffer                    163840

SQL>select*fromv$sga;

NAME                     VALUE

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

FixedSize                  104936

VariableSize              823164928

DatabaseBuffers          1073741824

RedoBuffers                172032

172032–163840=8192

（以上试验数据是在HPB.11.11+Oracle8.1.7.4环境下得到的）

1.2SGA的大小设置

      在对SGA的结构进行简单分析以后，下面是关于如何根据系统的情况正确设置SGA大小的问题。

SGA是一块内存区域，占用的是系统物理内存，因此对于一个Oracle应用系统来说，SGA决不是越大越好，这就需要寻找一个系统优化的平衡点。

1.2.1设置参数前的准备

在设置SGA的内存参数之前，我们首先要问自己几个问题

一：

物理内存多大

二：

操作系统估计需要使用多少内存

三：

数据库是使用文件系统还是裸设备

四：

有多少并发连接

五：

应用是OLTP类型还是OLAP类型

根据这几个问题的答案，我们可以粗略地为系统估计一下内存设置。

那我们现在来逐个问题地讨论，首先物理内存多大是最容易回答的一个问题，然后操作系统估计使用多少内存呢？

从经验上看，不会太多，通常应该在200M以内（不包含大量进程PCB）。

接下来我们要探讨一个重要的问题，那就是关于文件系统和裸设备的问题，这往往容易被我们所忽略。

操作系统对于文件系统，使用了大量的buffer来缓存操作系统块。

这样当数据库获取数据块的时候，虽然SGA中没有命中，但却实际上可能是从操作系统的文件缓存中获取的。

而假如数据库和操作系统支持异步IO，则实际上当数据库写进程DBWR写磁盘时，操作系统在文件缓存中标记该块为延迟写，等到真正地写入磁盘之后，操作系统才通知DBWR写磁盘完成。

对于这部分文件缓存，所需要的内存可能比较大，作为保守的估计，我们应该考虑在0.2——0.3倍内存大小。

但是如果我们使用的是裸设备，则不考虑这部分缓存的问题。

这样的情况下SGA就有调大的机会。

关于数据库有多少并发连接，这实际上关系到PGA的大小（MTS下还有large_pool_size）。

事实上这个问题应该说还跟OLTP类型或者OLAP类型相关。

对于OLTP类型oracle倾向于可使用MTS,对于OLAP类型使用独立模式，同时OLAP还可能涉及到大量的排序操作的查询，这些都影响到我们内存的使用。

那么所有的问题综合起来，实际上主要反映在UGA的大小上。

UGA主要包含以下部分内存设置

SQL>showparametersarea_size

NAME                                TYPE   VALUE

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

bitmap_merge_area_size                  integer   1048576

create_bitmap_area_size                  integer   8388608

hash_area_size                          integer    131072

sort_area_size                           integer    65536

SQL>

在这部分内存中我们最关注的通常是sort_area_size，这是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。

由于磁盘排序效率和内存排序效率相差好几个数量级，所以这个参数的设置很重要。

当出现大量排序时的磁盘I/O操作时，可以考虑增加sort_area_size的值。

sort_area_size是Oracle用于一次排序所需的最大内存数，在排序结束但是结果列返回之前，Oracle会释放sort_area_size大小的内存，但是会保留sort_area_retained_size大小的内存，知道最后一行结果列返回以后，才释放所有的内存。

会导致排序的语句有SELECTDISTINCT,MINUS,INTERSECT,UNION和min（）、max（）、count（）操作；而不会导致排序的语句有UPDATE,带BETWEEN子句的SELECT等等。

这四个参数都是针对会话进行设置的，是单个会话使用的内存的大小，而不是整个数据库使用的。

偶尔会看见有人误解了这个参数以为是整个数据库使用的大小，这是极其严重的错误。

假如设置了MTS，则UGA被分配在large_pool_size，也就是说放在了共享内存里面，不同进程（线程）之间可以共享这部分内存。

在这个基础上，我们假设数据库存在并发执行serverprocess为100个，根据上面我们4个参数在oracle8.1.7下的默认值，我们来计算独立模式下PGA的大致大小。

由于会话并不会经常使用create_bitmap_area_size、bitmap_merge_area_size，所以我们通常不对四个参数求和。

在考虑到除这四个参数外会话所保存的变量、堆栈等信息，我们估计为2M，则200个进程最大可能使用200M的PGA。

1.2.2一个经验公式

      现在，根据上面这些假定，我们来看SGA实际能达到多少内存。

在1G的内存的服务器上，我们能分配给SGA的内存大约为400—500M。

若是2G的内存，大约可以分到1G的内存给SGA，8G的内存可以分到5G的内存给SGA。

当然我们这里是以默认的排序部分内存sort_area_size=64k进行衡量的，假如我们需要调大该参数和hash_area_size等参数，然后我们应该根据并发的进程的数量，来衡量考虑这个问题。

事实上，通常我们更习惯通过直观的公式化来表达这样的问题：

OS使用内存+SGA+并发执行进程数*（sort_area_size+hash_ara_size+2M）<0.7*总内存

（公式是死的，系统是活的，实际应用的调整不必框公式，这不过是一个参考建议）

在我们的实际应用中，假如采用的是裸设备，我们可适当的增大SGA（如果需要的话）。

由于目前几乎所有的操作系统都使用虚拟缓存，所以实际上如果就算SGA设置的比较大也不会导致错误，而是可能出现频繁的内存页的换入与换出（pagein/out）。

在操作系统一级如果观察到这个现象，那么我们就需要调整内存的设置。

1.2.3各个参数的设置

那么SGA中的各个参数具体应该按照什么样的原则来设置呢，下面进行讨论：

log_buffer

对于日志缓冲区的大小设置，通常我觉得没有过多的建议，因为参考LGWR写的触发条件之后，我们会发现通常超过3M意义不是很大。

作为一个正式系统，可能考虑先设置这部分为log_buffer=1—3M大小，然后针对具体情况再调整。

large_pool_size

对于大缓冲池的设置，假如不使用MTS，建议在20—30M足够了。

这部分主要用来保存并行查询时候的一些信息，还有就是RMAN在备份的时候可能会使用到。

如果设置了MTS，则由于UGA部分要移入这里，则需要具体根据session最大数量和sort_ares_size等相关会话内存参数的设置来综合考虑这部分大小的设置，一般可以考虑为session*（sort_area_size+2M）。

这里要提醒一点，不是必须使用MTS，我们都不主张使用MTS，尤其同时在线用户数小于500的情况下。

。

java_pool_size

假如数据库没有使用JAVA，我们通常认为保留10—20M大小足够了。

事实上可以更少，甚至最少只需要32k，但具体跟安装数据库的时候的组件相关（比如httpserver）。

shared_pool_size

这是迄今为止最具有争议的一部分内存设置。

按照很多文档的描述，这部分内容应该几乎和数据缓冲区差不多大小。

但实际上情况却不是这样的。

首先我们要考究一个问题，那就是这部分内存的作用，它是为了缓存已经被解析过的SQL，而使其能被重用，不再解析。

这样做的原因是因为，对于一个新的SQL（shared_pool里面不存在已经解析的可用的相同的SQL），数据库将执行硬解析，这是一个很消耗资源的过程。

而若已经存在，则进行的仅仅是软分析（在共享池中寻找相同SQL），这样消耗的资源大大减少。

所以我们期望能多共享一些SQL，并且如果该参数设置不够大，经常会出现ora-04031错误，表示为了解析新的SQL，没有可用的足够大的连续空闲空间，这样自然我们期望该参数能大一些。

但是该参数的增大，却也有负面的影响，因为需要维护共享的结构，内存的增大也会使得SQL的老化的代价更高，带来大量的管理的开销，所有这些可能会导致CPU的严重问题。

在一个充分使用绑定变量的比较大的系统中，shared_pool_size的开销通常应该维持在300M以内。

除非系统使用了大量的存储过程、函数、包，比如oracleerp这样的应用，可能会达到500M甚至更高。

于是我们假定一个1G内存的系统，可能考虑设置该参数为100M，2G的系统考虑设置为150M,8G的系统可以考虑设置为200—300M。

对于一个没有充分使用或者没有使用绑定变量系统，这可能给我们带来一个严重的问题。

所谓没有使用bindvar的SQL，我们称为LiteralSQL。

也就是比如这样的两句SQL我们认为是不同的SQL,需要进行2次硬解析：

select*fromEMPwhere>select*fromEMPwhere>假如把’TOM’和’JERRY’换做变量V，那就是使用了bindvar，我们可以认为是同样的SQL从而能很好地共享。

共享SQL本来就是shared_pool_size这部分内存存在的本意，oracle的目的也在于此，而我们不使用bindvar就是违背了oracle的初衷，这样将给我们的系统带来严重的问题。

当然，如果通过在操作系统监控，没有发现严重的cpu问题，我们如果发现该共享池命中率不高可以适当的增加shred_pool_size。

但是通常我们不主张这部分内存超过800M（特殊情况下可以更大）。

事实上，可能的话我们甚至要想办法避免软分析，这在不同的程序语言中实现方式有差异。

我们也可能通过设置session_cached_cursors参数来获得帮助（这将增大PGA）

关于使用绑定变量的话题，在下面的应用优化中继续讨论。

Databuffer

现在我们来谈数据缓冲区，在确定了SGA的大小并分配完了前面部分的内存后，其余的，都分配给这部分内存。

通常，在允许的情况下，我们都尝试使得这部分内存更大。

这部分内存的作用主要是缓存DBBLOCK，减少甚至避免从磁盘上获取数据，在8i中通常是由db_block_buffers*db_block_size来决定大小的。

如果我们设置了buffer_pool_keep和buffer_pool_recycle，则应该加上后面这两部分内存的大小。

可以看出，设置SGA时基本上应该掌握的原则是：

databuffer一般可以尽可能的大

shared_pool_size应该适度

logbuffer在1MB以内就可以了

假定oracle是32bit,服务器RAM大于2G，注意你的PGA的情况，,则建议

shared_pool_size+databuffer+large_pool_size+java_pool_size<1.6G

如果1GRAM

shared_pool_size=100M,databuffer=500M

如果2GRAM

shared_pool_size=150M,databuffer=1.2G

物理内存再大已经跟参数没有关系了

假定64bitORACLE

内存4G

shared_pool_size=200M,databuffer=2.5G

内存8G

shared_pool_size=300M,databuffer=5G

内存12G

shared_pool_size=300M-----800M,databuffer=8G

1.332bit与64bit对SGA的影响

为什么在上面SGA大小设置的经验规则中要分32bitOracle和64bitOracle呢，是因为这关系到SGA大小的上限问题。

在32bit的数据库下，通常oracle只能使用不超过1.7G的内存，即使我们拥有12G的内存，但是我们却只能使用1.7G，这是一个莫大的遗憾。

假如我们安装64bit的数据库,我们就可以使用很大的内存，几乎不可能达到上限。

但是64bit的数据库必须安装在64bit的操作系统上，可惜目前windows上只能安装32bit的数据库，我们通过下面的方式可以查看数据库是32bit还是64bit：

SQL>select*fromv$version;

BANNER

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

Oracle8iEnterpriseEditionRelease8.1.7.0.0-Production

PL/SQLRelease8.1.7.0.0-Production

CORE8.1.7.0.0Production

TNSfor32-bitWindows:

Version8.1.7.0.0-Production

NLSRTLVersion3.4.1.0.0–Production

在UNIX平台下的显示有所不同，明显可以看出是64bitOracle，比如在HP-UX平台上：

SQL>select*fromv$version;

BANNER

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

Oracle8iEnterpriseEditionRelease8.1.7.4.0-64bitProduction

PL/SQLRelease8.1.7.4.0-Production

CORE   8.1.7.0.0      Production

TNSforHPUX:

Version8.1.7.4.0-Production

NLSRTLVersion3.4.1.0.0–Production

32bit的oracle无论跑在32bit或者64bit的平台都有SGA的限制的，而对于32bit的平台只能跑32bit的oracle，但是在特定的操作系统下，可能提供了一定的手段，使得我们可以使用超过1.7G的内存，达到2G以上甚至更多。

由于我们现在一般都使用64bitOracle，因此关于如何在32bit平台上扩展SGA大小的问题不再赘述。

1.49i中相关参数的变化

oracle的版本的更新，总是伴随着参数的变化，并且越来越趋向于使得参数的设置更简单，因为复杂的参数设置使得DBA们经常焦头烂额。

关于内存这部分的变化，我们可以考察下面的参数。

事实上在9i中数据库本身可以给出一组适合当前运行系统的SGA相关部分的参数调整值（参考V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），关于PGA也有相关视图V$PGA_TARGET_ADVICE等。

Databuffer

9i中保留了8i中的参数，如设置了新的参数，则忽略旧的参数。

9i中用db_cache_size来取代db_block_buffers，用db_keep_cache_size取代buffer_pool_keep,用db_recycle_cache_size取代buffer_pool_recycle；这里要注意9i中设置的是实际的缓存大小而不再是块的数量。

另外9i新增加了db_nk_cache_size，这是为了支持在同一个数据库中使用不同的块大小而设置的。

对于不同的表空间，可以定义不同的数据块的大小，而缓冲区的定义则依靠该参数的支持。

其中n可以为2、4、6、8、16等不同的值。

在这里顺便提及的一个参数就是db_block_lru_latches，该参数在9i中已经成为了保留参数，不推荐手工设置。

PGA

在9i里面这部分也有了很大的变化。

在独立模式下，9i已经不再主张使用原来的UGA相关的参数设置，而代之以新的参数。

假如workarea_size_policy=AUTO（缺省），则所有的会话的UGA共用一大块内存，该内存由pga_aggregate_target设置。

在我们根据前面介绍的方法评估了所有进程可能使用的最大PGA内存之后，我们可以通过在初始化参数中设置这个参数，从而不再关心其他”*_area_size”参数。

SGA_MAX_SIZE

在9i中若设置了SGA_MAX_SIZE，则在总和小于等于这个值内，可以动态的调整数据缓冲区和共享池的大小

SQL>showparameterssga_max_size

NAME      TYPE            VALUE

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