转---Oracle10G的Sga_max_size和sga_target应该如何设置啊！
转载地址：http://space.itpub.net/14769796/viewspace-631265
1调整原因
我们的客户反应现在我们公司的软件使用起来比较漫。目前他们已经用快要两年了。根据用户反应的情况，公司派我到现场做数据库调优。我在现场走访了几个部门，也向操作人员了解了一些情况。我初步分析认定为是数据库性能存在问题。
认定的理由是在用户操作的时候，不是每次操作都漫，也有快的时候，根据这样特点我下定结论是数据库问题。在操作漫的时候肯定是使用的人多（也可说是并发多），所以数据库资源有些紧张，所以使用时就漫了。
2数据库分析
我也在网上找了一些资料，对数据库的性能指标进行了评估，结果几项指标都没达到要求。（也不知道我分析的对不对，如有不对的地方，还请大侠多多指教，小弟我刚出道，以前是搞开发的。）
2.1性能指标
性能指标
参考办法
查询语句
原值
2010-4-2
8:01:00
2010-4-2
9:56:00
v$librarycache
v$librarycache中reloads/pins的比率应该小于1％，如果大于1％，应该增加参数shared_pool_size的值。
Select sum(pins) “executions”,sum(reloads) “cache misses”,sum(reloads)/sum(pins) from v$librarycache;
0.028362
0.011563686
0.026409799
监视共享池的性能
SQL AREA部分的gethitratio、pinhitratio要> 90%。
select namespace,gethitratio,pinhitratio,reloads,invalidations
from v$librarycache where namespace in (‘SQL AREA’,’TABLE/PROCEDURE’,’BODY’,’TRIGGER’);
0.666566
0.539428
0.655988245
重新装载率Reload Ratio要< 1%。
select SUM(reloads)/SUM(pins)“Reload Ratio”from V$librarycache;
2.83662
0.011831132
0.0264695
调整缓冲区高速缓存(Buffer Cache)的性能
“Buffer Cache Hit Ratio”的值要> 90%。
select 1-((physical.value–direct.value–lobs.value)/logical.value)“Buffer Cache Hit Ratio”from V$SYSSTAT physical, V$SYSSTAT direct,V$SYSSTAT lobs, V$SYSSTAT logical where physical.name =‘physical reads’
And direct.name =‘physical reads direct’and
lobs.name =‘physical reads direct (lob)’
And logical.name =‘session logical reads’;
0.793315
0.786405701
0.698056181
2.2现在的Sgainfo信息
Fixed SGA Size
No
1251172
Redo Buffers
No
7135232
Buffer Cache Size
Yes
1317011456
Shared Pool Size
Yes
125829120
Large Pool Size
Yes
8388608
Java Pool Size
Yes
8388608
Streams Pool Size
Yes
0
Granule Size
No
8388608
Maximum SGA Size
No
1468006400
Startup overhead in Shared Pool
No
58720256
Free SGA Memory Available
0
正是因为根据上面的分析结果，所以就更肯定了是数据库问题。我就把目光放在了Sga_max_size和sga_target它俩上，通过调整它俩来加大共享池（shared_pool_size）。
3应用环境描述
服务器：
型号/类型：IBM SYSTEM 3650 CPU：Intel® Xean® E5430 2.66(4颗单核)
内存：4G 硬盘：146G*7（使用磁盘阵列）
网卡：NetXtreme II GigE(2块) 是否RIAD：是，RIAD5
操作系统：
名称：Windows 32位 版本：2003 SP1
数据库：
名称：Oracle 版本：E10.2.0.1
所属行业：
行业名称：医院办公系统（HIS）
系统架构：C/S
客户端数：150台，实际应用120台左右（通过会话看的，也不知道还能怎么看）
4参数修改
通过对以上内容的分析和在网上找了一些参考资料，决定将sga_max_size和sga_target调整到2200M，但是操作结果失败了，告诉我内存不够，数据库启不来了，还有备份机，要不然我就惨了（我事先没做备份，当时老突突了，本来在操作前都想到要备份了，结果还是没备，后怕。）。
4.1备份
这步是我后加在的，必须得做。呵呵~~可别让自己后怕了。
把这个文件考那都行“D:\oracle\product\10.2.0\db_1\dbs\SPFILEORCL.ORA”，要是改完之后启不了数据库了那就在把文件考回去。当然还有其它方法。
根据初化参数文件spfile创建pfile文件，此文件可以进行编辑，修改初始化参数。
开始——》运行——》cmd
>sqlplus / as sysdba
SQL>create pfile='c:\orcl.ora' from spfile;
根据修改后的pfile文件创建spfile文件。
SQL>create spfile from pfile='c:\orcl.ora';
4.2修改参数
开始——》运行——》cmd
>sqlplus / as sysdba;
SQL>ALTER system SET sga_max_size=2200mscope=spfile;
SQL>ALTER system SET sga_target=2000mscope=spfile;
4.3关闭数据库
SQL>shutdown immediate;
4.4开启数据库
SQL>startup;
就在这一步出现问题了，报内存不够用，当时很不能理解，明明是4G内存，我才设置到2.2G，怎么能不够呢！因为比较着急，所以就用备份机上的文件“SPFILEORCL.ORA”给恢复了，数据库启来了。就那样用了。优化失败。（也不知道这样用有没有问题，如果有问题还请大家给指点指点，说明一下存在问题的原因和解决办法，小弟多谢了。）
面对失败我非常不甘心，回去后在自己的笔记本上好个试（机器配置就不详说了，2.53双核，2G内存，XP sp3，Oracle Database10gEnterprise Edition Release10.2.0.1.0，32-bit），测试结果是，我的机子最多可以设置到1700M，当设置到1800M的时候，就启不了数据库了。本以为我就能设置到1700M呢！在第二天我又测试了一遍，又设置到了1800M，这次我没有直接用startup启动数据库，而是在“服务”中把数据库服务重新启动了一下，呵呵~~数库也启动了。试过1900M，是启不来的（我这只是测试，设置到这么大是不合理的，我看的资料说大小等于物理内存-1.5么！也不知道这样是不是最合理的，有机会一定得试试。呵呵~~）。
通过这个测试，让我突然间想起了，前一天晚上对服务器的设置，我当时也是在命令行中直接用startup启动的数据库，如果我在服务中将数据库服务重启一下，会不会也可以启来呢？？？？？有这个可能性。我在想，是不是即使我关闭了数据库，服务是不是也会点用内存（通过下面的测试，没看出有关系来），并没有释放内存呢！正是因为这样，所以物理内存可用的并不多了，而我设置的值却大于了可用内存，这一点我没有验证，只是推测。（写完了，我就试试，哈哈……）
测试情况
1、先把我本机的数据库设置的值恢复到合理的值。(登录SQLPLUS我就不说了)
SQL>ALTER system SET sga_max_size=600mscope=spfile;
SQL>ALTER system SET sga_target=600mscope=spfile;
2、关闭数据库，把服务也重启一下，让一切恢复到原始状态。
SQL>shutdown immediate;
3、记录内存现在占用的情况。
系统共用：1.2G；ORACLE：222876K
4、修改数据库的那俩个参数。
SQL>ALTER system SET sga_max_size=1600mscope=spfile;
SQL>ALTER system SET sga_target=1600mscope=spfile;
5、关闭数据库。
SQL>shutdown immediate;
6、记录现在内存使用情况。（看现在这种情况，现用内存+我设置的，已经大于物理内存了。）
系统共用：597M；ORACLE：76916K
7、启动数据库。
SQL>startup;
8、结果。并记录现在内存使用情况。
可以启动。
系统共用：2.18G；ORACLE：246384K
9、分析内存的使用情况。
现在使用的内存已经大于我实际内存(2G，只能看到1.93G)了。怪怪怪……这是怎么回事啊！阿，知道了，哈哈……是不是把虚拟内存也算上了。我的虚拟内存是2046~4092（相当于2G~4G）。
10、增大SGA到1700，重复第四步，到第8步。
可以启动。
系统共用：2.28G；ORACLE：248568K
11、增大SGA到1800，重复第四步，到第8步。
报错，数据库无法启动。
ORA-27102: out of memory
12、在服务中把数据库服务停止。记录内存使用情况。
系统共用：560M；
13、在服务中启动数据库服务。
启动成功能。
系统共用：2.35G；ORACLE：278292K
14、结论。
关于最大能加到多大我就不试了，反证加到1900肯定是不行的。最后的结论是，没弄明白到底是什么原理，到底是和我的内存大小有关，还是和这个值设置的上限有关，呜呜呜~~~~~那位高人知道，那就说说吧！很想知道为什么，还会继续研究的，很有意思。哈哈~~
如果是和我的内存大小有关，那么我觉得我的那台服务器设置到2200是有戏的。如果是那个值（还是别那个值了，就是sga_max_size的值）有上限……
我也把我的分析和用户那个管事的说了，但是由于要放假了，所以我就不能验证了。正好这也是给我个机会，有充足的时间，我可以多问问了，呵呵~~
4.5查看修改后的参数
SQL>select * from v$sga;
SQL>show parameter sga_max_size;
SQL>show parameter sga_target;
4.6分析修改后的性能指标情况
根据第2节的性能指标，检查现在的数据库。
请前辈们多多指教，看看我那台服务器的配置，参数sga_max_size和sga_target设置为多大更合理（现在是1400M）。另外，有没有什么更好办法可以测试出服务器性能的、有没有其它参数需要修改的或者是有现成的非常实用的文档，请不要保留，发出来让俺也学学。多谢。（网上也看了很多，但是没找到我相要的，可能是我太笨了，呜呜~~）
其余一些资料（来源-网络）：
1) 32bit和64bit限制
在32位的操作系统中，Oracle最大可用内存为1.75g，也就是说SGA+PGA<=1.75g，超过这一限制的内存将不会被Oracle用到。
32位的Oracle可以装到64位的操作系统上，64位的Oracle SGA不可以装到32位的操作系统上。
3) 各组件设置：
JAVA_POOL_SIZE：如果没用到数据库端java的系统，30MB足够。
LOG_BUFFER：默认为MAX(512KB,128KB*CPU个数)。一般系统1MB足够，运行大型事务的系统可以设为2MB，让1/3满写入日志文件时可以继续写入缓冲，再大也没有意义。
SHARED_POOL_SIZE：过大过小都会严重影响系统性能，1GB内存可以设为100MB，2GB内存可设为150MB，4GB内存可设为 300MB。共享池命中过低首先要调整的是应用程序而不是扩大共享池。使用绑定变量可以减少共享池需求、提高命中率，减少共享池管理负担和LATCH竞争。
LARGE_POOL_SIZE：使用专用服务模式可设为30MB，除非必要，不然不建议使用共享服务器模式。
DB_CACHE_SIZE：除去上述内存外其他可用内存都分配给该区域。
总结
32位Oracle SGA：
1G内存：SHARED_POOL_SIZE=100MB，DB_CACHE_SIZE=0.5GB；
2G内存：SHARED_POOL_SIZE=150MB，DB_CACHE_SIZE=1.25GB；
64位Oracle
4G内存：SHARED_POOL_SIZE=200MB，DB_CACHE_SIZE=2.5GB；
8G内存：SHARED_POOL_SIZE=400MB，DB_CACHE_SIZE=5GB；
12G内存：SHARED_POOL_SIZE=500MB，DB_CACHE_SIZE=8GB
再次强调，以上只是避免因SGA设置不当引起问题的大体设置，需要根据具体的系统负载和业务逻辑结合Stackpack等工具细调。
一、oracle 10g中的SGA_MAX_SIZE与SGA_TARGET参数
sga_target是自动管理内存时使用的，这样你就不用设置DB_CACHE_SIZE，SHARED_POOL_SIZE等参数，他们会根据需要自动的进行调整的。
SGA_MAX_SIZE是从oracle9i以来一直存在﹐是不可动态修改的。
SGA_TARGET是oracle10g中用于实现自动SGA内存管理而新增加的。
SGA_MAX_SIZE指的是可动态分配的最大值﹐而SGA_TARGET是当前已分配的最大sga。
SGA_MAX_SIZE是不可以动态修改的﹔而SGA_TARGET是可动态修改﹐直到SGA_MAX_SIZE的值（当然这是在实例启动前已设定SGA_MAX_SIZE>SGA_TARGET的情况) 如果在实例启动时﹐SGA_MAX_SIZE < SGA_TARGET 或SGA_MAX_SIZE没设定﹐则启动后SGA_MAX_SIZE的值会等于SGA_TARGET的值。
二、在Oracle10g中使用SGA_TARGET参数自动管理SGA
在Oracle10g中,不必再如从前一样用下列各个参数分别指定SGA的每个部分的大小.也就是说不需要首先评估SGA各组件的大小,并且在
init<SID>.ora初始参数文件中分组件指定.(当然某些组件如果不不使用是可以不指定的.比如java_pool_size).
shared_pool_size
java_pool_size
large_pool_size
db_cache_size
在10g中可用一个新参数SGA_TARGET来设定实列所需的SGA最大值,并替代以上参数.
假设,将SGA_TARGET设置为 152M,则表明SGA最大为152M. 所有SGA组件如shared pool, buffer cache,large pool, java pool都需从其中分配. Oracle会自动为各组件指定初始值,并在运行过程中动态地调整各组件的大小.毋须人工干预.
这样,只要设置了SGA_TARGET, 就毋须显式地定义shared pool,buffer pool,large pool和java pool的大小.
例如:定义SGA_TARGET=152M(block size为8k).
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter sga_target
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_target big integer 152M
Oracle会自动将内存分配给各组件:
SQL> show sga
Total System Global Area 159383552 bytes
Fixed Size 1218268 bytes
Variable Size 67111204 bytes
Database Buffers 88080384 bytes
Redo Buffers 2973696 bytes
使用sga_target参数有几点需要注意:
1. 当使用sga_target时, 若未设置sga_max_size,或设置了小于sga_target,则sga_max_size将等于sga_target.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 152M
sga_target big integer 152M
2. 当sga_target是可动态调整的,但其值不能大于sga_max_size.
SQL> alter system set sga_target=160M; alter system set sga_target=160M * ERROR at line 1: ORA-02097: parameter cannot be
modified because specified value is invalid ORA-00823: Specified value of sga_target greater than sga_max_size
