os:windows 2003 32bit

db:oracle rac 10.2.0.1

最近在给某用户单位分析oracle巡检报告，查看alert_orcl日志的时候，发现有报错如下：

Fri Jul 23 12:41:38 2010

Errors in file

d:oracleproduct10.2.0adminorclbdumporcl1_smon_2080

ORA-04030: 在尝试分配 8389132 字节

(pga heap,redo read buffer) 时进程内存不足

pga_aggregate_target大小为512M：

SQL> show parameter pga_aggregate_target

NAME TYPE VALUE
------------------------------- ----------- --------
pga_aggregate_target big integer 91M

查看分析报告中V$PGA_TARGET_ADVICE视图信息：

Instance Name	PGA Target for Estimate	Estimated Extra Bytes R/W	Estimated PGA Cache Hit %	ESTD_OVERALLOC_COUNT
orcl1	67,108,864	338,482,451,456	89	44,585
	134,217,728	301,259,850,752	90	40,163
	268,435,456	823,532,544	100	0
	402,653,184	823,532,544	100	0
	536,870,912	215,786,496	100	0
	644,244,480	215,786,496	100	0
	751,619,072	215,786,496	100	0
	858,992,640	215,786,496	100	0
	966,367,232	215,786,496	100	0
	1,073,741,824	215,786,496	100	0
	1,610,612,736	215,786,496	100	0
	2,147,483,648	215,786,496	100	0
	3,221,225,472	215,786,496	100	0
	4,294,967,296	215,786,496	100

根据信息显示，PGA内存为256M的时候命中率已经为100%了，当前PGA为

512M，已经造成资源浪费，甚至造成PGA内存过度分配引发ora-04030错误。

修改PGA大小：

SQL> alter system set pga_aggregate_target=256m scope=spfile;

系统已更改。

[@more@]

ITPub博客

关于PGA分配问题引起的ORA-04030