oracle对sga统计信息不对,关于oracle sga设置的总结，很经典--转

关于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