Oracle10G的Sga_max_size和sga_target应该如何设置啊！
1调整原因
我们的客户反应现在我们公司的软件使用起来比较漫。目前他们已经用快要两年了。根据用户反应的情况，公司派我到现场做数据库调优。我在现场走访了几个部门，也向操作人员了解了一些情况。我初步分析认定为是数据库性能存在问题。
认定的理由是在用户操作的时候，不是每次操作都漫，也有快的时候，根据这样特点我下定结论是数据库问题。在操作漫的时候肯定是使用的人多（也可说是并发多），所以数据库资源有些紧张，所以使用时就漫了。
2数据库分析
我也在网上找了一些资料，对数据库的性能指标进行了评估，结果几项指标都没达到要求。（也不知道我分析的对不对，如有不对的地方，还请大侠多多指教，小弟我刚出道，以前是搞开发的。）
2.1性能指标
性能指标 |
参考办法 |
查询语句 |
原值 |
2010-4-2 |
2010-4-2 |
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 |
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’ |
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节的性能指标，检查现在的数据库。