Re: Partitionining perfortmance degraded drastically after upgrading the database from Oracle 102.0.4 to Oracle 11.2.0.3
From: Hans Forbrich <fuzzy.graybeard_at_gmail.com>
Date: Sun, 11 Jan 2015 08:54:54 -0700
Message-ID: <54B29CCE.80301_at_gmail.com>
On 09/01/2015 5:21 PM, Ls Cheng wrote:
> I always have a doubt, why people set SGA_TARGET smaller than
> SGA_MAX_SIZE. What is the objective of doing so?
On systems that support Dynamic [reallocatoon of] Shared Memory, which have several instances with varying work loads, the sum of SGA_TARGET plus the sum of PGA_AGGREGATE_TARGET should be less than physical memory, but the sum of SGA_MAX_SIZE plus the sum of PGA_AGGREGATE_TARGET can exceed the physical memory, preallocating the fixed SGA portion for each instance based on the maximums, permitting SGA_TARGETs to be adjusted without restarts.
Date: Sun, 11 Jan 2015 08:54:54 -0700
Message-ID: <54B29CCE.80301_at_gmail.com>
On 09/01/2015 5:21 PM, Ls Cheng wrote:
> I always have a doubt, why people set SGA_TARGET smaller than
> SGA_MAX_SIZE. What is the objective of doing so?
On systems that support Dynamic [reallocatoon of] Shared Memory, which have several instances with varying work loads, the sum of SGA_TARGET plus the sum of PGA_AGGREGATE_TARGET should be less than physical memory, but the sum of SGA_MAX_SIZE plus the sum of PGA_AGGREGATE_TARGET can exceed the physical memory, preallocating the fixed SGA portion for each instance based on the maximums, permitting SGA_TARGETs to be adjusted without restarts.
Very limited use case, but important for avoiding outages for such a simple reconfiguration. Potentially significant for Multitenant architecture.
/Hans
-- http://www.freelists.org/webpage/oracle-lReceived on Sun Jan 11 2015 - 16:54:54 CET