关于oracle sga设置的总结，很经典--转

上一篇 / 下一篇  2009-07-10 11:59:07 / 个人分类：oracle

查看( 2485 ) / 评论( 3 ) / 评分( 15 / 0 )

关于oracle sga设置的总结，很经典--转 说明：
本总结不针对特例，仅对服务器只存在OS + ORACLE 为例，如果存在其他应用请酌情考虑。写这个也是因为近来这种重复性的问题发生的太多所导致的。

首先不要迷信STS、OCP SG以及某些专家给出的任何建议、内存百分比的说法。基本掌握的原则是：

l          data buffer 通常可以尽可能的大

l          shared_pool_size 要适度

l          log_buffer 通常大到几百K到 1M就差不多了

设置之前，首先要明确2个问题
1：除去OS和一些其他开销，能给ORACLE使用的内存有多大？
2：Oracle是64 bit 还是 32 bit ？32bit 通常 SGA有 1.7G 的限制(某些OS有特定处理或者WINDOWS上有特定设定可以支持到2G以上甚至达到3.7G，本人无这方面经验)。

下面是我的Windows2000下的Oracle :

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

SQL>

windows上存在32bit的限制，如AIX、HP-UX 等有明确的64 bit OS 和ORACLE的版本，32 bit Oracle可以装在64 bit OS 上，64 bit Oracle不能装在32 bit OS上。

不管Oracle是32 bit 还是 64 bit 的，假定应用存在没有很好的使用bind var 的情况，也不能设置 shared_pool_size 过大，通常应该控制在100M--200M，除非是 ORACLE ERP 一类的使用了很多存储过程函数、包 ，这样的很大的系统，可以考虑增大shared_pool_size，但是如果超过500M可能是危险的，达到1G几乎就会造成CPU的严重负担，系统甚至瘫痪。所以shared_pool_size 如果超过200M还命中率不高，那么应该从应用上找原因而不是一味的增加内存，shared_pool_size 过大主要增加了管理负担和latch 的开销。

log_buffer ：128K -- 1M 之间通常问题不大，不应该太大。

large_pool_size ：如果不设置MTS，通常在 RMAN 、OPQ 会使用到，但是在10M --50M 应该差不多了。假如设置 MTS，则由于 UGA 放到large_pool_size 的缘故，这个时候依据 session最大数量和 sort_ares_size 等参数设置，必须增大large_pool_size 的设置，可以考虑为 session * (sort_area_size + 2M)。这里要提醒一点，不是必须使用MTS，我们都不主张使用MTS，尤其同时在线用户数小于500的情况下。

java_pool_size : 若不使用java，给30M通常就够了。

data buffer ：在做了前面的设置后，凡可以提供给Oracle的内存，都应该给data buffer = (db_block_size * db_block_buffers) 在9i 中可以是 db_cache_size。

还有2个重要参数我们需要注意：

sort_area_size
hash_area_size
这两个参数在非MTS下都是属于PGA ，不属于SGA。它是为每个session单独分配的，在我们的服务器上除了OS + SGA，一定要考虑这两部分。

(****) : OS 使用内存+ SGA + session*(sort_area_size + hash_area_size + 2M) < 总物理RAM 为好 。

这样归结过来，假定Oracle是 32 bit ,服务器RAM大于2G ，注意你的PGA的情况，则建议：

shared_pool_size + data buffer +large_pool_size + java_pool_size < 1.6G

再具体化，注意满足上面(****) 的原则的基础上可以参考如下设置：
如果512M RAM
建议 shared_pool_size = 50M, data buffer = 200M ；

如果1G RAM
shared_pool_size = 100M , data buffer = 500M ；

如果2G RAM
shared_pool_size = 150M ,data buffer = 1.2G。

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

举例来说：
假定64 bit ORACLE

内存4G
shared_pool_size = 200M , data buffer = 2.5G

内存8G
shared_pool_size = 200M , data buffer = 5G

内存 12G
shared_pool_size = 300M , data buffer = 8G

   
 
Top

4 楼shuen（ShiningStone）回复于 2005-09-04 18:04:32 得分 0 我的系统是WIN2000   SERVER+   ORACLE   9I       RAM     2G  
  其中有些参数配置：  
   
  db_cache_size               =                   25165824  
  shared_pool_size   =   50331648  
   
  这些都是默认配置，没改动多  
  以前系统问题不大，可能是处理的数据不大  
  但现在有些表数据达到了几十万行  
  多个用户运行SQL查询就很慢，但系统状态显示服务器的CUP和内存的占用率都不高  
  是不是配置存在很大的问题？？？  
  谢谢
Top

5 楼shuen（ShiningStone）回复于 2005-09-04 18:08:17 得分 0 db_cache_size   的大小修改不了，怎么回事？
Top

6 楼dnmyg2003（毛毛）回复于 2005-09-06 15:59:52 得分 50在设置新系统得SGA大小时，我们不是使用oracle建议值，而是使用下列经验法则：  
  服务器物理内存×0.55=要分配给所有SGA的内存总量（TSGA）  
  TSGA/服务器上的oracle实例个数=每个实例的总SGA大小（TSGAI）  
  使用TSGAI值，然后就可以计算出每个实例的SGA大小：  
  TSGAI×0.45=已分配给shared   pool的总内存  
  TSGAI×0.45=已分配给database   buffer   cache的总内存  
  TSGAI×0.10=已分配给redo   log   buffer的总内存  
  在大多数情况中，为redo   log   buffer保留SGA空间的10%将是不必要的。但是，在调整统计数据可使用之后，大家随时可以从这个区域中取走内存，并把它们分配给shared   pool、database   buffer   cache和large   pool。  
  考虑事项  
  这么计算的目的是为了充分利用现有内存，同时又仍给操作系统、oracle后台进程、用户服务器进程以及运行在该服务器上的其他任何非oracle进程保留充足的内存。操作系统所需要的内存量在Windows   NT系统（一般需要较多内存）到UNIX型系统（一般需要较少内存）之间变化很大，因此大家可能需要相应地调整自己的计算。这条经验法则在总内存小于1GB的服务器上非常适用。在具有1GB以上内存量的服务器上，大家应该把TSGA计算中使用的55%初始缩小率改成一个较大的值，比如60%~75%   。    

##################################
数据块缓存区(data   block   buffer   cache)是SGA中的一个高速缓存区域，用来存储从数据库中读取数据段的数据块(如表、索引和簇)。数据块缓存区的大小由数据库服务器init.ora文件中的DB_BLOCK_BUFFERS参数决定(用数据库块的个数表示)。在调整和管理数据库时，调整数据块缓存区的大小是一个重要的部分。因为数据块缓存区的大小固定，并且其大小通常小于数据库段所使用的空间，所以它不能一次装载下内存中所有的数据库段。通常，数据块缓存区只是数据库大小的1   %～2   %，Oracle使用最近最少使用(   LRU，least   recently   used)算法来管理可用空间。当存储区需要自由空间时，最近最少使用块将被移出，新数据块将在存储区代替它的位置。通过这种方法，将最频繁使用的数据保存在存储区中。然而，如果SGA的大小不足以容纳所有最常使用的数据，那么，不同的对象将争用数据块缓存区中的空间。当多个应用程序共享同一个SGA时，很有可能发生这种情况。此时，每个应用的最近使用段都将与其他应用的最近使用段争夺SGA中的空间。其结果是，对数据块缓存区的数据请求将出现较低的命中率，导致系统性能下降。  
  数据库对象的信息存储在数据字典表中，这些信息包括用户帐号数据、数据文件名、段名、盘区位置、表说明和权限，当数据库需要这些信息(如检查用户查询一个表的授权)时，将读取数据字典表并且将返回的数据存储在字典缓存区的SGA中。数据字典缓存区通过最近最少使用(LRU)   算法来管理。字典缓存区的大小由数据库内部管理。字典缓存区是SQL共享池的一部分，共享池的大小由数据库文件init.ora中的SHARED_POOL_SIZE参数来设置。如果字典缓存区太小，数据库就不得不反复查询数据字典表以访问数据库所需的信息，这些查询称为循环调用(recuesive   call)，这时的查询速度相对字典缓存区独立完成查询时要低。  
   
  SHARED_POOL_SIZE由前者组成，至于大小要看你数据库的用途和业务及配置。  
   
 
Top

2 楼joachern（程序人生）回复于 2003-07-23 09:01:28 得分 0 自己up!!
Top

3 楼maohaisheng（虫族：无名小虫）回复于 2003-07-23 13:22:31 得分 10这两个参数没有绝对的关系，一楼已经说得很清楚了
Top

4 楼luckysxn（坚持到底）回复于 2003-07-23 15:28:00 得分 30ORACLE     8.0.X     版本      
     
  SGA=((db_block_buffers     *     block     size)+(shared_pool_size+large_pool_size+log_buffers)+1MB      
     
  ORACLE     8.1.X     版本      
     
  SGA=((db_block_buffers     *     block     size)+(shared_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB      
     
  理论上SGA可占OS系统物理内存的1/2——1/3，我们可以根据需求调整      
     
  我推荐SGA=0.45*(OS     RAM)      
     
  假设服务器运行ORACLE     8.1.X     版本,     OS系统内存为2G     MEM,     db_block_size     是8192     bytes,          
  除了运行ORACLE数据库外,     没有其它的应用程序或服务器软件.      
     
  这样SGA合计约为921M     (     0.45*2048M     ),          
     
  设shared_pool_size     300M     (300*1024*1024     bytes)      
     
  设database     buffer     cache     570M     (72960*8192     bytes)      
     
    initorasid.ora文件里具体各参数如下:      
     
  shared_pool_size     =     314572800      
  #     300     M      
     
  db_block_buffers     =     72960      
  #     570     M      
     
  log_buffer     =     524288      
  #     512k     (128K*CPU个数)      
     
  large_pool_size     =     31457280      
  #     30     M      
     
  java_pool_size     =     20971520      
  #     20     M      
     
  sort_area_size     =     524288      
  #     512k     (65k--2M)      
     
  sort_area_retained_size     =     524288      
  #     MTS     时     sort_area_retained_size     =     sort_area_size      
     
  SUN     Solaris里/etc/system文件里的几个参数同样跟内存分配有关      
     
  ORACLE安装时缺省的设置:     建议修改的设置:          
  set     shmsys:shminfo_shmmax=4294967295          
  set     shmsys:shminfo_shmmin=1          
  set     shmsys:shminfo_shmmni=100          
  set     shmsys:shminfo_shmseg=15          
  set     semsys:seminfo_semmns=200          
  set     semsys:seminfo_semmni=70          
  set     ulimit=3000000              
  set     semsys:seminfo_semmni=315      
  set     semsys:seminfo_semmsl=300      
  set     semsys:seminfo_semmns=630      
  set     semsys:seminfo_semopm=315      
  set     semsys:seminfo_semvmx=32767      
  set     shmsys:shminfo_shmmax=4294967295      
  set     shmsys:shminfo_shmmni=315      
  set     shmsys:shminfo_shmseg=10      
  set     shmsys:shminfo_shmmin=1          
     
  其中这些参数的含义      
     
  shmmax     -     共享内存段，建议设大点，     达到最大SGA      
  shmmin     -     最小的共享内存段.      
  shmmni     -     共享内存标志符的数量.      
  shmseg     -     一个进程可分配的最大内存段数.      
  shmall     -     最大可允许的内存数，比SGA还要大.      
  semmns     -     信号灯，跟ORACLE的PROCESS数有关.      
  semmsl     -     一个信号灯中最大的信号灯数.      
     
     
  ---------------------------------------------------------------      
     
  分析你的init.ora      
  shared     pool         31,457,280         #30M      
  data     buffers     4326*8192=     35,438,592     #35M      
  large     pool         614,400                 #600K      
  #多线程      
  mts_dispatchers     =     "(protocol=TCP)(tick=15)(pool=true)"      
  ---------------------      
  这样，如果你认为你的语句已经优化了，如果你的内存也不是太大的话。      
  对于     shared     pool和data     buffers的大小已经够了。      
  但是，你又用了多线程，又只把large     pool配置了600k      
  你要么先把多线程去掉，要么把large     pool加大（30M)试试（多线程用到了large     pool）。      
  最后，把你的init文件中的一些参数还调整一下      
  db_file_multiblock_read_count     =     8         #可以加大到16     or     32试试      
  把      
  oracle_trace_collection_name     =     ""      
  system_plan      
  这注释掉      
 
Top

5 楼woodpan（木头）回复于 2003-07-23 19:00:39 得分 10shared_pool_size   以字节为单位，指定共享池的大小。共享池包含如:   共享游标、存储的过程、控制结构和并行执行消息缓冲区等对象。较大的值能改善多用户系统的性能。这个值不需要太大，一般不超过150M  
  db_block_buffers   缓冲区高速缓存中   Oracle   块的数量。database_buffers=db_block_buffers×db_block_size.这个值越大越好，只要整个SGA不超过系统物理内存限制，给最大值  

 

##############################
数据高速缓存区命中率
　　--计算公式：1-(physical reads / (db block gets + consistent gets))
　　--命中率应大于0.90最好
　　select name,value
　　from v$sysstat
　　where name in ('physical reads','db block gets','consistent gets')
　　/
　　--共享区库缓存区命中率
　　--计算公式：SUM(pins - reloads) / SUM(pins)
　　--命中率应大于0.99
　　select sum(pins-reloads)/sum(pins)
　　from v$librarycache
　　/
　　--共享区字典缓存区命中率
　　--计算公式：SUM(gets - getmisses - usage -fixed) / SUM(gets)
　　--命中率应大于0.85
　　select sum(gets-getmisses-usage-fixed)/sum(gets)
　　from v$rowcache
　　/
　　--检测回滚段的争用
　　--SUM(waits)值应小于SUM(gets)值的1%
　　select sum(gets),sum(waits),sum(waits)/sum(gets)
　　from v$rollstat
　　/
　　--检测回滚段收缩次数
　　select name,shrinks
　　from v$rollstat, v$rollname
　　where v$rollstat.usn = v$rollname.usn
　　/
　　--关于SGA的调优
　　(****) : OS 使用内存+ SGA + session*(sort_area_size + hash_area_size + 2M) < 总物理RAM 为好
　　log_buffer : 128K ---- 1M 之间通常问题不大，不应该太大
　　large_pool_size :如果不设置MTS，通常在 RMAN 、OPQ 会使用到，但是在10M --- 50M 应该差不多了。
　　java_pool_size : 若不使用java，给30M通常就够了
　　data buffer ,在做了前面的设置后，凡可以提供给oracle的内存，都应该给data buffer = (db_block_size * db_block_buffers)
　　不能设置 shared_pool_size 过大，通常应该控制在200M--300M
　　再具体化，注意满足上面(****) 的原则的基础上可以参考如下设置
　　如果512M RAM
　　建议 shared_pool_size = 50M, data buffer = 200M
　　如果1G RAM
　　shared_pool_size = 100M , data buffer = 500M
　　如果2G
　　shared_pool_size = 150M ,data buffer = 1.2G
　　物理内存再大已经跟参数没有关系了
　　假定64 bit ORACLE
　　内存4G
　　shared_pool_size = 200M , data buffer = 2.5G
　　内存8G
　　shared_pool_size = 300M , data buffer = 5G
　　内存 12G
　　shared_pool_size = 300M-----800M , data buffer = 8G