Re: Strange error when resizing MEMORY_TARGET

From: De DBA <dedba_at_tpg.com.au>
Date: Wed, 16 Jan 2013 09:05:31 +1000
Message-ID: <50F5E0BB.70106_at_tpg.com.au>

Ah, I should have added that I did check V$memory_resize_ops and v$memory_dynamic_components and the total memory usage at the time that I could see was only around 6GB, well under the target of 7G that was in force.

I understand that Oracle means target, not limit, which is why I did not expect any immediate resizing when changing the target. Perhaps I misunderstand the meaning of "target"? In my understanding it means that Oracle should attempt to bring memory usage up/down if possible. What I gather from the answers though, is that Oracle attempts to force memory down into the new target and refuses to set that target if it momentarily fails to do that. That would indicate that Oracle treats the "target" as a hard limit, imho.

I noticed a similar target=limit behaviour with memory_target and memory_max_target. We did at one point require more than 7G, and although memory_max_target was 20G and used memory (SGA + PGA) at 7G, Oracle raised ORA-4031 instead of growing memory beyond 7G (which is why we want to resize to 16G in the first place).

Cheers,
Tony

On 16/01/13 2:35 AM, Mark W. Farnham wrote:
> Good answer. I would only add that it is easy to miss that Oracle really
> means "target" not "limit" which has probably led to your confusion.
>
> After you find the lowest setting it will swallow, you *might* want to
> increase your target to that if what you're after is more generous start-up
> values and less dynamic allocation. If you're already at 20 and you need
> more, that is going to require a bounce.
>
> If your memory is currently oscillating by just a few granules between
> shared_pool and buffer_cache, you *might* want to add a little room and set
> both to the ceilings of their respective oscillations and turn off automatic
> resizing as much as you can when you eventually get the chance.
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Stefano Cislaghi
> Sent: Tuesday, January 15, 2013 2:21 AM
> To: dedba_at_tpg.com.au
> Cc: Oracle Discussion List
> Subject: Re: Strange error when resizing MEMORY_TARGET
>
> On 15 January 2013 08:15, De DBA<dedba_at_tpg.com.au> wrote:
>> ORA-00846
>
>
> Hi Tony,
> this is a known behaviour. Check your v$memory_resize_ops .
> You should see that the sum of sga and pga is greater than 16gb. This is the
> reason of the message. Oracle needs in fact to decrease memory.
> For more info, if you have MoS access, check also note: 452512.1 and
> 139783.1
>
> Ste
>
> --
> http://www.stefanocislaghi.eu
>
> The SQLServerAgent service depends on the MSSQLServer service, which has
> failed due to the following error: The operation completed successfully.
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Wed Jan 16 2013 - 00:05:31 CET