ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于oracle sga设置的总结,很经典--转

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

原创 Linux操作系统 作者:chinaguy 时间:2009-07-10 11:59:07 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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/787018/viewspace-608817/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-21

  • 博文量
    30
  • 访问量
    137157