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博客 ” ,链接:http://blog.itpub.net/7417660/viewspace-1037056/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7417660/viewspace-1037056/