Oracle Tuning的一些总结

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各个组成部分的情况：SQLselect*fromv$sga;NAMEVALUE------------------------------FixedSize104936VariableSize823164928DatabaseBuffers1073741824RedoBuffers172032或者SQLshowsgaTotalSystemGlobalArea1897183720bytesFixedSize104936bytesVariableSize823164928bytesDatabaseBuffers1073741824bytesRedoBuffers172032bytesFixedSizeoracle的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了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(不同环境可能不一样)。参考如下内容SQLselectsubstr(name,1,10)name,substr(value,1,10)value2fromv$parameterwherename='log_buffer';NAMEVALUE----------------------------------------log_buffer163840SQLselect*fromv$sgastatwherepoolisnull;POOLNAMEBYTES-----------------------------------------------fixed_sga104936db_block_buffers1073741824log_buffer163840SQLselect*fromv$sga;NAMEVALUE------------------------------FixedSize104936VariableSize823164928DatabaseBuffers1073741824RedoBuffers172032172032–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主要包含以下部分内存设置SQLshowparametersarea_sizeNAMETYPEVALUE---------------------------------------------------bitmap_merge_area_sizeinteger1048576create_bitmap_area_sizeinteger8388608hash_area_sizeinteger131072sort_area_sizeinteger65536SQL在这部分内存中我们最关注的通常是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里面不存在已