Reducing sga_target in 10gR2 without getting ORA-00827
- From: Michael Ray <topshot.rhit@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 16 Jun 2006 10:30:34 -0400
To attempt to mitigate ORA-04030 crashes we've been experiencing, Oracle
has suggested lowering sga_target. Unfortunately, the log/trace files
have provided no clues at all as to what is causing this crash every 3-5
weeks. The server has 4GB of physical RAM, but is not using the /3GB
switch so Oracle can only access 2GB. Currently sga_target is set at
1.2GB. The server runs only this 1 instance of Oracle so Windows has
plenty of mem.
select * from v$sga_dynamic_free_memory gives 0 because sga_target=sga_max_size so Oracle has no memory to grow if needed. It shouldn't need to since 1.2GB should be way more mem than it needs for SGA.
Based on note 295626.1 I don't think I can reduce sga_target appreciably without getting ORA-00827 (could not shrink sga_target to specified value). Currently the sum of the min sizes
(select component, current_size/1024/1024 "CURRENT_SIZE", min_size/1024/1024 "MIN_SIZE", user_specified_size/1024/1024 "USER_SPECIFIED_SIZE" from v$sga_dynamic_components)
is 1188 and sga_target is 1200.
Is there any way I can reduce sga_target to 1000 dynamically? Will I just have to keep doing it in small steps of 12 for example?
Sincerely, Michael Ray Topshot Systems LLC -- http://www.freelists.org/webpage/oracle-l
Other related posts:
- » Reducing sga_target in 10gR2 without getting ORA-00827
- » RE: Reducing sga_target in 10gR2 without getting ORA-00827
- » Re: Reducing sga_target in 10gR2 without getting ORA-00827