05 内存oracle内存分配与调整.docx

1、05 内存oracle内存分配与调整ORACLE内存分配与调整一、前言对于oracle的内存的管理，截止到9iR2，都是相当重要的环节，管理不善，将可能给数据库带来严重的性能问题。下面我们将一步一步就内存管理的各个方面进行探讨。二、概述oracle的内存可以按照共享和私有的角度分为系统全局区和进程全局区，也就是SGA和PGA(process global area or private global area)。对于SGA区域内的内存来说，是共享的全局的，在UNIX上，必须为oracle设置共享内存段（可以是一个或者多个），因为oracle在UNIX上是多进程；而在WINDOWS上oracle

2、是单进程（多个线程），所以不用设置共享内存段。PGA是属于进程（线程）私有的区域。在oracle使用共享服务器模式下（MTS）,PGA中的一部分，也就是UGA会被放入共享内存large_pool_size中。对于SGA部分，我们通过sqlplus中查询可以看到：SQL select * from v$sga;NAME VALUE- -Fixed Size 454032Variable Size 109051904Database Buffers 385875968Redo Buffers 667648Fixed Sizeoracle 的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的

3、值，里面存储了SGA各部分组件的信息，可以看作引导建立SGA的区域。Variable Size包含了shared_pool_size、java_pool_size、large_pool_size等内存设置和用于管理数据缓冲区等内存结构的hash table、块头信息(比如x$bh消耗内存)等Database Buffers指数据缓冲区，在8i中包含default pool、buffer_pool_keep、buffer_pool_recycle三部分内存。在9i中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cac

4、he_size。这里要注意在8i中三部分内存总和为db_block_buffers*db_block_size。Redo Buffers指日志缓冲区，log_buffer。在这里要额外说明一点的是，对于v$parameter、v$sgastat、v$sga查询值可能不一样。v$parameter里面的值，是指用户在初始化参数文件里面设置的值，v$sgastat 是oracle实际分配的日志缓冲区大小（因为缓冲区的分配值实际上是离散的，也不是以block为最小单位进行分配的），v$sga里面查询的值，是在oracle分配了日志缓冲区后，为了保护日志缓冲区，设置了一些保护页，通常我们会发现保护页大

5、小大约是11k(不同环境可能不一样)。参考如下内容SQL select substr(name,1,10) name,substr(value,1,10) value 2 from v$parameter where name = log_buffer;NAME VALUE- -log_buffer 524288SQL select * from v$sgastat ;POOL NAME BYTES- -fixed_sga 454032buffer_cache 385875968log_buffer 656384SQL select * from v$sga;NAME VALUE- -Fixe

6、d Size 454032Variable Size 109051904Database Buffers 385875968Redo Buffers 667648关于各部分内存的作用，参考oracle体系结构，在此不再叙述。1、SGA的大小那么我们现在来考察内存参数的设置。实际上，对于特定的环境，总是存在着不同的最优设置的，没有任何一种普遍适用的最优方案。但为什么在这里我们还要来谈设置这个话题呢，那仅仅是出于一个目的，避免过度的犯错误。事实上，在任何一个生产系统正式投入使用之前，我们不拥有任何系统运行信息让我们去调整，这样就只有两种可能，一是根据文档推荐设置，另外一种就是根据经验设置。相对来说

7、，根据经验的设置比根据文档的设置要可靠一些。尤其是那些24*7的系统，我们更要减少错误的发生。那么我们尝试去了解不同的系统不同的应用的具体设置情况，从而提供一个参照信息给大家。为了得出一个参照设置，我们就必须假定一个参照环境。以下所有设置我们基于这样一个假定，那就是硬件服务器上只考虑存在操作系统和数据库，在这个单一的环境中，我们来考虑内存的设置。在设置参数之前呢，我们首先要问自己几个问题一：物理内存多大二：操作系统估计需要使用多少内存三：数据库是使用文件系统还是裸设备四：有多少并发连接五：应用是OLTP类型还是OLAP类型根据这几个问题的答案，我们可以粗略地为系统估计一下内存设置。那我们现在逐

8、个问题地讨论，首先：物理内存多大是最容易回答的一个问题：用命令systeminfo查看然后：操作系统估计使用多少内存呢？从经验上看，不会太多，通常应该在200M以内（不包含大量进程PCB）。接下来：我们要探讨一个重要的问题，那就是关于文件系统和裸设备的问题，这往往容易被我们所忽略。操作系统对于文件系统，使用了大量的buffer来缓存操作系统块。这样当数据库获取数据块的时候，虽然SGA中没有命中，但却实际上可能是从操作系统的文件缓存中获取的。而假如数据库和操作系统支持异步IO，则实际上当数据库写进程DBWR写磁盘时，操作系统在文件缓存中标记该块为延迟写，等到真正地写入磁盘之后，操作系统才通知DB

9、WR写磁盘完成。对于这部分文件缓存，所需要的内存可能比较大，作为保守的估计，我们应该考虑在 0.20.3 倍内存大小。但是如果我们使用的是裸设备，则不考虑这部分缓存的问题。这样的情况下SGA就有调大的机会。关于数据库有多少并发连接，这实际上关系到PGA的大小（MTS下还有large_pool_size）。事实上这个问题应该说还跟OLTP类型或者OLAP类型相关。对于OLTP类型oracle倾向于可使用MTS,对于OLAP类型使用独立模式，同时OLAP还可能涉及到大量的排序操作的查询，这些都影响到我们内存的使用。那么所有的问题综合起来，实际上主要反映在UGA的大小上。UGA主要包含以下部分内存设

10、置SQL show parameters area_sizeNAME TYPE VALUE- - -bitmap_merge_area_size integer 1048576create_bitmap_area_size integer 8388608hash_area_size integer 131072sort_area_size integer 65536在这部分内存中我们最关注的通常是sort_area_size，这是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。由于磁盘排序效率和内存排序效率相差好几个数量级，所以这个参

11、数的设置很重要。这四个参数都是针对会话进行设置的，是单个会话使用的内存的大小，而不是整个数据库使用的。偶尔会看见有人误解了这个参数以为是整个数据库使用的大小，这是极其严重的错误。假如设置了MTS，则UGA被分配在large_pool_size，也就是说放在了共享内存里面，不同进程（线程）之间可以共享这部分内存。在这个基础上，我们假设数据库存在并发执行server process为100个，根据上面我们4个参数在oracle8.1.7下的默认值，我们来计算独立模式下PGA的大致大小。由于会话并不会经常使用create_bitmap_area_size、bitmap_merge_area_size

12、，所以我们通常不对四个参数求和。在考虑到除这四个参数外会话所保存的变量、堆栈等信息，我们估计为2M，则100个进程最大可能使用200M的PGA。现在，根据上面这些假定，我们来看SGA实际能达到多少内存。在1G的内存的服务器上，我们能分配给SGA的内存大约为400500M。若是2G的内存，大约可以分到1G的内存给SGA，8G的内存可以分到5G的内存给SGA。当然我们这里是以默认的排序部分内存sort_area_size=64k进行衡量的，假如我们需要调大该参数和hash_area_size等参数，然后我们应该根据并发的进程的数量，来衡量考虑这个问题。事实上，通常我们更习惯通过直观的公式化来表达这

13、样的问题：OS使用内存+SGA+并发执行进程数*(sort_area_size+hash_area_size+2M) show parameters sga_max_sizeNAME TYPE VALUE- - -sga_max_size unknown 193752940SQL alter system set db_cache_size = 30000000;System altered.SQL alter system set shared_pool_size = 20480000;System altered.3、Lock_sga = true 的问题由于几乎所有的操作系统都支持虚拟内

14、存，所以即使我们使用的内存小于物理内存，也不能避免操作系统将SGA换到虚拟内存（SWAP）。所以我们可以尝试使得SGA锁定在物理内存中不被换到虚拟内存中，这样减少页面的换入和换出，从而提高性能。但在这里遗憾的是，windows是无法避免这种情况的。下面我们来参考在不同的几个系统下怎么实现lock_sga ：AIX 5L（AIX 4.3.3以上）logon aix as rootcd /usr/samples/kernel./vmtune (信息如下) v_pingshm已经是1./vmtune -S 1然后oracle用户修改initSID.ora 中 lock_sga = true 重新启动

15、数据库HP UNIXRoot身份登陆Create the file /etc/privgroup: vi /etc/privgroupAdd line dba MLOCK to fileAs root, run the command /etc/setprivgrp -f /etc/privgroup:$/etc/setprivgrp -f /etc/privgrouporacle用户修改initSID.ora中lock_sga=true 重新启动数据库SOLARIS (solaris2.6以上)8i版本以上数据库默认使用隐藏参数 use_ism = true ，自动锁定SGA于内存中,不用设

16、置lock_sga, 如果设置 lock_sga =true 使用非 root 用户启动数据库将返回错误。WINDOWS不能设置lock_sga=true,可以通过设置pre_page_sga=true,使得数据库启动的时候就把所有内存页装载，这样可能起到一定的作用。4、关于内存参数的调整关于参数调整，是oracle的复杂性的一个具体体现。通常来讲，我们更倾向于让客户做 statspack报告，然后告诉我们os监控的状况，在这些的信息的基础上，再向客户索取具体的详细信息以诊断问题的所在。系统的调整：现在我们通常采用从等待事件入手的方法。因为一个系统感觉到慢，必然是在某个环节上出现等待，那么我们

17、从等待最多的事件入手逐步诊断并解决问题。内存的调整：相对来说简单一些，我们首先可以针对数据缓冲区的大小来看。首先观察命中率数据缓冲区命中率SQL select value from v$sysstat where name =physical reads; VALUE- 14764SQL select value from v$sysstat where name =physical reads direct; VALUE- 50SQL select value from v$sysstat where name =physical reads direct (lob); VALUE- 0SQ

18、L select value from v$sysstat where name =consistent gets; VALUE-167763SQL select value from v$sysstat where name = db block gets; VALUE- 14305这里命中率的计算应该是令 x = physical reads direct + physical reads direct (lob)命中率 =100 - ( physical reads - x) / (consistent gets + db block gets - x)*100通常如果发现命中率低于90

19、%,则应该调整应用可可以考虑是否增大数据缓冲区。共享池的命中率SQL select sum(pinhits)/sum(pins)*100 hit radio from v$librarycache; hit radio- 99.809291假如共享池的命中率低于95%,就要考虑调整应用（通常是没使用bind var）或者增加内存。关于排序部分SQL select name,value from v$sysstat where name like %sort%;NAME VALUE- -sorts (memory) 67935sorts (disk) 1sorts (rows) 7070假如我们发现sorts (disk)/ (sorts (memory)+ sorts (disk)的比例过高，则通常意味着sort_area_size部分内存较小，可考虑调整相应的参数。关于log_buffer SQL select name,value from v$sysstat 2 where name in(redo entries,redo buffer allocation retries);NAME VALUE- -