RE: FYI - 10gR2 automatic memory management bugs

From: Schultz, Charles <sac_at_uillinois.edu>
Date: Thu, 4 May 2006 12:17:39 -0500
Message-ID: <565F609E6D736D439837F1A1A797F34171D4C5@ADMINMAIL1.ui.uillinois.edu>

Stinking proprietary information.... =)

Here is what I have been told by an Oracle employee: 4507532: DEFERRED requests (grow or shrink) were too slow. This is fixed in 10.2.0.2 I believe, but we do not have a one-off patch. 4920199: grows shared pool even when incoming requests are for literal sol statements. Also affects the shared pool advice (from ADDM) which recommends that you increase the sga_target because sql cursors are not being shared.

I do not have any official information for 4466399 (SYSTEM WIDE HANG ON MMON WAITING FOR 'SGA ALLOCATION FORCING COMPONENT GROWTH'), other than our symptoms matched the bug. Our symptoms included "excessive" (my word) shared pool growth and activity. At one point, we saw the shared pool jump up 1.7gb in 15 seconds. Last night, we grew from 2gb to 7gb at which point we started getting ora-4031 errors and everything went down the crapper.

From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Thursday, May 04, 2006 11:58 AM
To: Schultz, Charles
Cc: oracle-l
Subject: Re: FYI - 10gR2 automatic memory management bugs

Uh, not ASSM, AMM, or whatever the Automatic Memory Mgt is called.

On 5/4/06, Jared Still <jkstill_at_gmail.com> wrote:

	Interesting, as none of those bug numbers appear on MetaLink. 
	(Advanced search, bug id)
	
	Can you provide more detail as to what these bugs involve?
	
	I've run into a couple issues on 10.2.0.1 that are ASSM related.
	
	Jared
	
	
	
	
	On 5/4/06, Schultz, Charles <sac_at_uillinois.edu> wrote: 

		In hopes that nobody else suffers critical slowdowns in

their production database in the middle of the day or night, I am sharing what we have recently learned about some of the automatic memory management "features" (aka, bugs).

Apparently, the algorithms that decide when and how fast to grow the shared pool are a bit less than ideal. I do not know all the details yet, but we have stumbled upon the following bugs in 10.2.0.1:

		4466399 
		4472338 (duplicate of the above 4466399) 
		4507532 (turned out to be a bad diagnosis, but the bug
is real) 
		4920199 

		It is my understanding that only 4507532 is fixed in

10.2.0.2 - too bad we did not hit that bug. =) 4920199 is currently being worked on, and I was told that 4466399 was only available in v11. And since there is an ingenious work-around, no one-off patch is planned. What is this work-around? Simple, turn off the automatic tuning features by setting sga_target = 0.

		-- 
	Jared Still
	Certifiable Oracle DBA and Part Time Perl Evangelist

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 04 2006 - 12:17:39 CDT