ORACLE内存优化.doc

l 前言对于oracle的内存的管理，截止到9iR2，都是相当重要的环节，管理不善，将可能给数据库带来严重的性能问题。下面我们将一步一步就内存管理的各个方面进行探讨。l 概述oracle的内存可以按照共享和私有的角度分为系统全局区和进程全局区，也就是SGA和PGA(process global area or private global area)。对于SGA区域内的内存来说，是共享的全局的，在UNIX上，必须为oracle设置共享内存段（可以是一个或者多个），因为oracle在UNIX上是多进程；而在WINDOWS上oracle是单进程（多个线程），所以不用设置共享内存段。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 的不同平台和不同版本下可能不一样，但对于确定环境是一个固定的值，里面存储了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_cache_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分配了日志缓冲区后，为了保护日志缓冲区，设置了一些保护页，通常我们会发现保护页大小大约是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- -Fixed Size 454032Variable Size 109051904Database Buffers 385875968Redo Buffers 667648关于各部分内存的作用，参考oracle体系结构，在此不再叙述。l SGA的大小那么我们现在来考察内存参数的设置。实际上，对于特定的环境，总是存在着不同的最优设置的，没有任何一种普遍适用的最优方案。但为什么在这里我们还要来谈设置这个话题呢，那仅仅是出于一个目的，避免过度的犯错误。事实上，在任何一个生产系统正式投入使用之前，我们不拥有任何系统运行信息让我们去调整，这样就只有两种可能，一是根据文档推荐设置，另外一种就是根据经验设置。相对来说，根据经验的设置比根据文档的设置要可靠一些。尤其是那些24*7的系统，我们更要减少错误的发生。那么我们尝试去了解不同的系统不同的应用的具体设置情况，从而提供一个参照信息给大家。为了得出一个参照设置，我们就必须假定一个参照环境。以下所有设置我们基于这样一个假定，那就是硬件服务器上只考虑存在操作系统和数据库，在这个单一的环境中，我们来考虑内存的设置。在设置参数之前呢，我们首先要问自己几个问题一：物理内存多大二：操作系统估计需要使用多少内存三：数据库是使用文件系统还是裸设备四：有多少并发连接五：应用是OLTP类型还是OLAP类型根据这几个问题的答案，我们可以粗略地为系统估计一下内存设置。那我们现在来逐个问题地讨论，首先物理内存多大是最容易回答的一个问题，然后操作系统估计使用多少内存呢？从经验上看，不会太多，通常应该在200M以内（不包含大量进程PCB）。接下来我们要探讨一个重要的问题，那就是关于文件系统和裸设备的问题，这往往容易被我们所忽略。操作系统对于文件系统，使用了大量的buffer来缓存操作系统块。这样当数据库获取数据块的时候，虽然SGA中没有命中，但却实际上可能是从操作系统的文件缓存中获取的。而假如数据库和操作系统支持异步IO，则实际上当数据库写进程DBWR写磁盘时，操作系统在文件缓存中标记该块为延迟写，等到真正地写入磁盘之后，操作系统才通知DBWR写磁盘完成。对于这部分文件缓存，所需要的内存可能比较大，作为保守的估计，我们应该考虑在 0.20.3 倍内存大小。但是如果我们使用的是裸设备，则不考虑这部分缓存的问题。这样的情况下SGA就有调大的机会。关于数据库有多少并发连接，这实际上关系到PGA的大小（MTS下还有large_pool_size）。事实上这个问题应该说还跟OLTP类型或者OLAP类型相关。对于OLTP类型oracle倾向于可使用MTS,对于OLAP类型使用独立模式，同时OLAP还可能涉及到大量的排序操作的查询，这些都影响到我们内存的使用。那么所有的问题综合起来，实际上主要反映在UGA的大小上。UGA主要包含以下部分内存设置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 65536SQL在这部分内存中我们最关注的通常是sort_area_size，这是当查询需要排序的时候，数据库会话将使用这部分内存进行排序，当内存大小不足的时候，使用临时表空间进行磁盘排序。由于磁盘排序效率和内存排序效率相差好几个数量级，所以这个参数的设置很重要。这四个参数都是针对会话进行设置的，是单个会话使用的内存的大小，而不是整个数据库使用的。偶尔会看见有人误解了这个参数以为是整个数据库使用的大小，这是极其严重的错误。假如设置了MTS，则UGA被分配在large_pool_size，也就是说放在了共享内存里面，不同进程（线程）之间可以共享这部分内存。在这个基础上，我们假设数据库存在并发执行server process为100个，根据上面我们4个参数在oracle8.1.7下的默认值，我们来计算独立模式下PGA的大致大小。由于会话并不会经常使用create_bitmap_area_size、bitmap_merge_area_size，所以我们通常不对四个参数求和。在考虑到除这四个参数外会话所保存的变量、堆栈等信息，我们估计为2M，则100个进程最大可能使用200M的PGA。现在，根据上面这些假定，我们来看SGA实际能达到多少内存。在1G的内存的服务器上，我们能分配给SGA的内存大约为400500M。若是2G的内存，大约可以分到1G的内存给SGA，8G的内存可以分到5G的内存给SGA。当然我们这里是以默认的排序部分内存sort_area_size=64k进行衡量的，假如我们需要调大该参数和hash_area_size等参数，然后我们应该根据并发的进程的数量，来衡量考虑这个问题。事实上，通常我们更习惯通过直观的公式化来表达这样的问题：OS使用内存+SGA+并发执行进程数*(sort_area_size+hash_ara_size+2M) show parameters sga_max_sizeNAME TYPE VALUE- - -sga_max_size unknown 193752940SQLSQL alter system set db_cache_size = 30000000;System altered.SQL alter system set shared_pool_size = 20480000;System altered.l Lock_sga = true 的问题由于几乎所有的操作系统都支持虚拟内存，所以即使我们使用的内存小于物理内存，也不能避免操作系统将SGA换到虚拟内存（SWAP）。所以我们可以尝试使得SGA锁定在物理内存中不被换到虚拟内存中，这样减少页面的换入和换出，从而提高性能。但在这里遗憾的是，windows是无法避免这种情况的。下面我们来参考在不同的几个系统下怎么实现lock_sgaAIX 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 重新启动数据库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于内存中,不用设置lock_sga, 如果设置 lock_sga =true 使用非 root 用户启动数据库将返回错误。WINDOWS不能设置lock_sga=true,可以通过设置pre_page_sga=true,使得数据库启动的时候就把所有内存页装载，这样可能起到一定的作用。l 关于内存参数的调整关于参数调整，是oracle的复杂性的一个具体体现。通常来讲，我们更倾向于让客户做statspack报告，然后告诉我们os监控的状况，在这些的信息的基础上，再向客户索取具体的详细信息以诊断问题的所在。系统的调整，现在我们通常采用从等待事件入手的方法。因为一个系统感觉到慢，必然是在某个环节上出现等待，那么我们从等待最多的事件入手逐步诊断并解决问题。对于内存的调整，相对来说简单一些，我们首先可以针对数据缓冲区的大小来看。首先观察命中率数据缓冲区命中率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- 0SQL 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%,则应该调整应用可可以考虑是否增大数据缓冲区共享池的命中率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) 7070SQL假如我们发现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- -redo entries 2325719redo buffer allocation retries 10假如 redo buffer allocation retries/ redo entries 的比例超过1%我们就可以考虑增大log_buffer通常来说，内存的调整的焦点就集中在这几个方面，更多更详细的内容，建议从statspack入手来一步一步调整。最后关于内存的调整，再强调这一点，一定要结合操作系统来衡量，任何理论都必须要实践来检验。在操作系统中观察 page in/out 状况，发现问题严重，应该考虑调小SGA。l 32bit 和 64bit 的问题对于oracle来说，存在着32bit与64bit的问题。这个问题影响到的主要是SGA的大小。在32bit的数据库下，通常oracle只能使用不超过1.7G的内存，即使我们拥有12G的内存，但是我们却只能使用1.7G，这是一个莫大的遗憾。假如我们安装64bit的数据库,我们就可以使用很大的内存，几乎不可能达到上限。但是64bit的数据库必须安装在64bit的操作系统上，可惜目前windows上只能安装32bit的数据库，我们通过下面的方式可以查看数据库是32bit还是64bit：SQL select * from v$version;BANNER-Oracle8i Enterprise Edition Release .0 - ProductionPL/SQL Release .0 - ProductionCORE .0 ProductionTNS for 32-bit Windows: Version .0 - ProductionNLSRTL Version .0 - Production 但是在特定的操作系统下，可能提供了一定的手段，使得我们可以使用超过1.7G的内存，达到2G以上甚至更多。 Oracle 10g的内存调整报告SGA-(初始化参数sga_target) | |_shared pool(初始化参数shared_pool_size)chunk链表结构构成Bucket | | | |_library cache | | | |_dictionary cache | | | |_reserved size (初始化参数shared_pool_reserved_size) | | |_DEFAULT buffer cache(初始化参数db_cache_size)LRU链表结构 | |_KEEP buffer cache (初始化参数db_keep_cache_size) | |_RECYCLE buffer cache (初始化参数db_recycle_cache_size) | | |_large pool(初始化参数large_pool_size) | |_java pool(初始化参数java_pool_size) | |_streams pool(初始化参数streams_pool_size) | |_log buffer (初始化参数log_buffer)PGA-(初始化参数pga_aggregate_target) | |_ sort_area_size | |_ bitmap_merge_area_size | |_ hash_area_size | |_ open_cursors | |_ oracle堆栈和TNS堆栈PRE_PAGE_SGAoracle实例启动时，会只载入各个内存区最小的大小。而其他SGA内存只作为虚拟内存分配，只有当进程touch到相应的页时，才会置换到物理内存中。但我们也许希望实例一启动后，所有SGA都分配到物理内存。这时就可以通过设置PRE_PAGE_SGA参数来达到目的了。这个参数的默认值为FALSE，即不将全部SGA置入物理内存中。当设置为TRUE时，实例启动会将全部SGA置入物理内存中。它可以使实例启动达到它的最大性能状态，但是，启动时间也会更长（因为为了使所有SGA都置入物理内存中，oracle进程需要touch所有的SGA页）。但是，要记住一点：PRE_PAGA_SGA只是在启动时将物理内存分配给SGA，但并不能保证系统在以后的运行过程不会将SGA中的某些页置换到虚拟内存中，也就是说，尽管设置了这个参数，还是可能出现Page In/Out。如果需要保障SGA不被换出，就需要由另外一个参数LOCK_SGA来控制了。LOCK_SGA上面提到，为了保证SGA都被锁定在物理内存中，而不必页入/页出，可以通过参数LOCK_SGA来控制。这个参数默认值为FALSE，当指定为TRUE时，可以将全部SGA都锁定在物理内存中。当然，有些系统不支持内存锁定，这个参数也就无效了。use_indirect_data_buffers这个参数使32位平台使用扩展缓冲缓存基址，以支持支持4GB多物理内存。设置此参数，可以使SGA突破在32位系统中的2G最大限制。64位平台中，这个参数被忽略。SGA中，设置sga_target的值，并把statistics_level设置为typical或者all以启动ASMM，启动自动SGA管理后，Oracle可以自动为我们调整以下内存池的大小:shared poolbuffer cachelarge pooljava poolstreams pool需要手动调整的参数log bufferdb_nk_cache_sizedb_keep_cache_sizedb_recycle_cache_size下面是初始化参数中定义的各个内存的值：SQL SELECT NAME,VALUE 2 FROM v$parameter 3 WHERE NAME IN(sga_max_size, 4 db_cache_size, 5 shared_pool_size, 6 shared_pool_reserved_size, 7 large_pool_size, 8 java_pool_size, 9 db_block_size, 10 db_block_buffers, 11 log_buffer, 12 sort_area_size, 13 sort_area_retained_size, 14 hash_area_size, 15 sessions, 16 open_cursors, 17 streams_pool_size, 18 sga_target, 19 pga_aggregate_target) 20 ORDER BY NAME; NAME VALUE- -db_block_buffers 0db_block_size 32768db_cache_size 0hash_area_size 131072java_pool_size 0large_pool_size 0log_buffer 14238720open_cursors 500pga_aggregate_target 1995440128sessions 445sga_max_size 4294967296sga_target 4294967296shared_pool_reserved_size 38587596shared_pool_size 0sort_area_retained_size 0sort_area_size 65536streams_pool_size 0 17 rows selected通过以上语句可以看到SGA的大小是4294967296/1024/1024=4096MB这4096MB是如何分配的呢，通过以下语句可以看到:SQL select t.COMPONENT,t.CURRENT_SIZE/1024/1024 CURRENT_SIZE (MB)from v$sga_dynamic_components t;COMPONENT CURRENT_SIZE (MB)- -shared pool 800large pool 16java pool 16streams pool 0DEFAULT buffer cache 3248KEEP buffer cache 0RECYCLE buffer cache 0DEFAULT 2K buffer cache 0DEFAULT 4K buffer cache 0DEFAULT 8K buffer cache 0DEFAULT 16K buffer cache 0DEFAULT 32K buffer cache 0ASM Buffer Cache 013 rows selectedSGA实际大小= DB_CACHE_SIZE + DB_KEEP_CACHE_SIZE + DB_RECYCLE_CACHE_SIZE + DB_nk_CACHE_SIZE + SHARED_POOL_SIZE + LARGE_POOL_SIZE + JAVA_POOL_SIZE + STREAMS_POOL_SIZE（10g中的新内存池）+ LOG_BUFFERS+11K(Redo Log Buffer的保护页) + 1MB + 16M(SGA内部内存消耗，适合于9i及之前版本)4096MB=3248+0+0+0+800+16+16+0+(14238720/1024)+11)/1024+1=4094.6MB一般来说，设置1GB以上的shared pool不会给性能带来明显的提高，相反，这将给Oracle管理shared pool以及监控shared pool的过程中带来较多的麻烦。我们可以在系统上线时，设置shared pool为SGA的10%，但是不要超过1GB，或者利用9i以后引入的顾问来帮助我们判断shared pool的设置是否合理。只要将初始化参数statistics_level设置为typical或者all就启动对shared pool的建议，basic为关闭该功能。SQL select t.SHARED_POOL_SIZE_FOR_ESTIMATE SP, 2 t.ESTD_LC_SIZE EL, 3 t.ESTD_LC_MEMORY_OBJECTS ELM, 4 t.ESTD_LC_TIME_SAVED ELT, 5 t.ESTD_LC_TIME_SAVED_FACTOR ELTS %, 6 t.ESTD_LC_MEMORY_OBJECT_HITS ELMO 7 from v$shared_pool_advice t 8 ; SP EL ELM ELT ELTS % ELMO- - - - - - 320 88 5074 33933340 0.9864 1103895941 400 164 9091 34082404 0.9908 1103917266 480 243 12488 34212579 0.9946 1103936243 560 321 16246 34298181 0.997 1103950027 640 400 20958 34349935 0.9985 1103959377 720 479 25599 34381446 0.9995 1103965662 800 558 29274 34399956 1 1103969741 880 637 31830 34410211 1.0003 1103972287 960 716 36387 34415861 1.0005 1103973889 1040 771 38954 34419129 1.0006 1103974939 1120 810 41453 34421119 1.0006 1103975631 1200 889 45092 34422524 1.0007 1103976097 1280 969 48656 34423731 1.0007 1103976439 1360 1018 51779 34424784 1.0007 1103976714 1440 1057 54744 34425580 1.0007 1103976944 1520 1096 57024 34426021 1.0008 1103977142 1600 1175 63265 34426180 1.0008 1103977322通过这两个语句可以看出，Oracle建议我们shared pool的值为800MB，而且实际分配的值也是800MB。ESTD_LC_SIZE:估计库高速缓存的使用数量（以兆字节为单位）ESTD_LC_MEMORY_OBJECTS:估算共享池中库缓存的内存对象数ESTD_LC_TIME_SAVED:估算将可以