Re: Reducing sga_target in 10gR2 without getting ORA-00827

Schultz, Charles wrote:
> We had significant problems with sga_target, enough that we finally
> turned it off. Apparently, in 10.2.0.1 there are some issues with the
> algorithm that allocates/deallocates memory which can cause degradation.
>
> Our Support Analyst(s) referenced the following bugs:
> 4466399/4472338 (dup) (SR 5386579.993): SYSTEM WIDE HANG ON MMON WAITING
> FOR 'SGA ALLOCATION FORCING COMPONENT GROWTH':
> 4507532 (SR 5256243.992): SGA_TARGET DOESN'T WORK AS EXPECTED
>
> Not sure if this is relevant for your situation, but wanted to offer it
> up just in case.

That's certainly not comforting is it. We are still 10.2.0.1 now because I didn't see anything in the patches so far that dealt with SGA issues.

You may have a good idea of going manual, because this instance doesn't vary much in transaction type or level. Frankly, I wonder if I've stumbled onto some mem leak with the combination of parameters I've got (nothing exotic) except I set _KGL_LARGE_HEAP_WARNING_THRESHOLD to get rid of most of the gazillions of notifications in the alert log. "Memory Notification: Library Cache Object loaded into SGA Heap size 2251K exceeds notification threshold (2048K)" I set it to 16MB I think and I've harldly seen any since. In 10.2.0.2 Oracle has set it to 50MB.

Their note (330239.1) on the subject has this interesting statement though: "The meaning is that the process is just spending a lot of time in finding free memory extents during an allocate as the memory may be heavily fragmented. Real memory messages should be ORA-403x when a real memory allocation problem occurs."
I clearly have a real memory problem (4030) every few weeks so why is my mem so fragmented? Going manual is sounding better all the time.

Is manual SGA sizing in 10g done the same way it is in 8i? I have no experience with 9i. Maybe I should sit down and figure out what all my values should be. Now where were all those books...?

-- 

Sincerely,
Michael Ray
Topshot Systems LLC
--
http://www.freelists.org/webpage/oracle-l