Oracle内存参数配置及版本问题

Oracle的内存配置与Oracle性能息息相关。从总体上讲，可以分为两大块：共享部分（主要是SGA）和进程独享部分（主要是PGA）。在 32 位操作系统下的Oracle版本，不时有项目反馈关于内存的错误（如ORA-04030、04031错误）都是十分令人头疼的问题。查阅资料了解到，ORA-04030的问题一般是PGA过度分配造成的（对应的操作是sort/hash_join）。在Oracle中pga_aggregate_target指定了所有session总共使用的最大PGA上限。经测试验证，32位Oracle版本使用的物理内存保持在 1.6G以下为佳(SGA+PGA)，超过 1.7G左右系统开始不稳定，推荐的内存配置为：SGA=1200M，PGA=360M；

调整内存参数的命令示例如下：

alter system set sga_max_size=1200M scope=spfile;
alter system set sga_target=1200M scope=spfile;
alter system set pga_aggregate_target=360M scope=spfile;

附，调整sharepool、buffer cache

-- 设置内存自动调整
alter system set memory_max_target=48G scope=spfile;
alter system set memory_target=48G scope=spfile;
alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;

-- 设置buffer cache和shared pool的值（在自动内存管理的情况下，这个值会作为最小值），解决shared pool过大和SGA Resizing引发的性能问题
alter system set sga_max_size=36G scope=spfile;
alter system set sga_target=36G scope=both;
alter system set pga_aggregate_target=12G scope=spfile;
alter system set db_cache_size=25G scope=both;
alter system set shared_pool_size=5G scope=both;
alter system set "_memory_broker_stat_interval"=900 scope=both;  -- 设置resize的频率不低于15分钟

-- 保留的共享池空间与游标共享 ？？
alter system set shared_pool_reserved_size=500M scope=both;  --缺省值为shared_pool_size的5%，可以改为10~20%
alter system set cursor_sharing='SIMILAR'; -- 共享游标(EXACT/SIMILAR/FORCE)，缺省为EXACT

-- 设置日志缓冲等 ？？ 
alter system set log_buffer=134217728 scope=spfile; --128M ??? 
alter system set db_cache_size=25G scope=both sid='*'; --RAC 
alter system set lock_sga=true scope=spfile; -- 锁定内存

Oracle支持自动和手工混合的内存参数管理方式，如果同时设置了Memory_target、SGA_target、db_cache_size，Oracle的处理方式是每个部件不会低于指定的内存大小，但可以根据需要调整到超过指定值的内存。这种自动和传统手工管理相结合的方式，更能适应复杂多变的系统需求。

完全使用SGA自动管理有一个缺陷就是，如果应用系统绑定变量做得不好，或者由于BUG，child cursor过多，导致shared pool会变得很大，甚至超过10G，严重的比buffer cache还大，另一方面，在buffer cache和shared pool之间频繁地调整大小，可能会导致严重的解析问题和其他性能问题。针对这个问题，通常有2种解决办法：一种就是关闭SGA自动管理，即将SGA_TARGET设置为0，以9i的方式来设置shared_pool_size,db_cache_size这些参数，来手动管理SGA；第二种就是sga_target仍然大于0，即自动管理SGA，但是通过设置shared_pool_size，db_cache_size等参数限制这些内存组件的最小大小，而只留给系统极少的自动调整空间。

建议使用的Oracle版本：10.2.0.5、11.2.0.3/4；对于64位版本，建议先把20%的内存留给操作系统，剩余80%分配给Oracle（其中SGA=物理内存*80%*80%，PGA=物理内存*80%*20%，db_cache_size=SGA*80%，shared_pool_size=SGA*15%）。

曾经在多个项目上发现过奇怪的现象，一个较复杂的SQL，直接执行或查看执行计划，操作系统中可以看到CPU立刻飙到99%，而且即使等待很长时间（比如2分钟，对于一个各表数据量小于10K的查询，哪怕都走全表扫描也应该执行完的，2分钟实在是太久了），CPU也不会降下来，SQL命令也无法正常结束，只能强制终止该会话或Oracle进程。该SQL访问的所有表的数据量都不是很大（小于10K），更新统计信息等都没有效果。我分别在Windows和Linux平台下的测试环境验证过，问题都能够重现，当然如果将SQL脚本简化也能解决，但没有明显的规律、规则，感觉应该是Oracle的bug，最后都是通过升级到最新版本解决的。

如分页SQL脚本（MV_118_CTLIST_03为视图）：

SELECT MV_118_CTLIST_03."CTLIST_Name"
    , MV_118_CTLIST_03."CTLIST_Depart_LSBMZD_BMMC"
    , MV_118_CTLIST_03."CTLIST_Value"
    , MV_118_CTLIST_03."CTLIST_Handler_LSZGZD_ZGXM"
    , QRY_WORKITEM.STARTEDDATE
    , QRY_WORKITEM.COMPLETEDDATE
    , QRY_WORKITEM.PROCESSINSTANCEID
    , QRY_WORKITEM.ACTIVITYDEFINITIONID
    , QRY_WORKITEM.PROCESSDEFINITIONID
    , QRY_WORKITEM.ActivityInstanceId
    , QRY_WORKITEM.WORKITEMID
    , QRY_WORKITEM.WORKTYPE
FROM QRY_WORKITEM 
    JOIN MV_118_CTLIST_03 ON ROOTPROCINSTID = MV_118_CTLIST_03."CTLIST_SPID"
    JOIN (SELECT PK
          FROM (SELECT PK, rownum rowNumber
                FROM (SELECT WORKITEMID AS PK
                      FROM QRY_WORKITEM
                          JOIN MV_118_CTLIST_03 ON ROOTPROCINSTID = MV_118_CTLIST_03."CTLIST_SPID"
                      WHERE QRY_WORKITEM.Participant = '5b181b7c-8ea8-45a5-b35d-a90aed0725dc' 
                        AND QRY_WORKITEM.State = '2' 
                        AND QRY_WORKITEM.BIZPROCID = '0fad699e-a787-4fb6-bbff-8d3382f6d37f'
                      ORDER BY STARTEDDATE)
                WHERE rownum <= 20)
          WHERE rowNumber >= 1) tblPK ON workitemid = tblPK.PK
WHERE QRY_WORKITEM.Participant = '5b181b7c-8ea8-45a5-b35d-a90aed0725dc' 
    AND QRY_WORKITEM.State = '2' 
    AND QRY_WORKITEM.BIZPROCID = '0fad699e-a787-4fb6-bbff-8d3382f6d37f' 
ORDER BY STARTEDDATE