As and when Oracle release new version of database, DBA gets new bunch of features.
Before Oracle 9i, DBA has to maintain the whole bunch of memory parameters like DB_BLOCK_BUFFERS, SHARED_POOL_SIZE, set of PGA parameters *_AREA_SIZE, LOG_BUFFER and so on. Real complexity before 9i is assigning correct values to *_AREA_SIZE
(PGA) parameters as these values is not for whole instance; it is for EACH oracle user process(Oracle user process count can grow upto maximum of PROCESSES settings).
In 9i, Oracle introducted a new parameters called PGA_AGGREGATE_TARGET and WORKAREA_SIZE_POLICY which helps users to assign a single value for the whole instance's PGA and Oracle instance itself will do self mangement for PGA memory. In earlier version, DBA has to assign value for _AREA_SIZE parameters which assign equal size for all sessions.
Oracle 10g has gone to next level from 9i wherein it automates the memory management of whole SGA. Instead of DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE,
LARGE_POOL_SIZE and
LOG_BUFFER parameter; user has to manage just SGA_TARGET and SGA_MAX_SIZE(see diagram).
Oracle 11g has gone to the next level from 10g of autmatic memory management where in the whole instance can be controlled by initialization parameter MEMORY_TARGET and a maximum memory size initialization parameter MEMORY_MAX_TARGET. Oracle Database then tunes to the target memory size, redistributing memory as needed between the system global area (SGA) and the instance program global area (instance PGA).
Oracle doc says
MEMORY_TARGET specifies the Oracle system-wide usable memory. The database tunes memory to the MEMORY_TARGET value, reducing or enlarging the SGA and PGA as needed.
MEMORY_MAX_TARGET specifies the maximum value to which a DBA can set the MEMORY_TARGET initialization parameter.
Here is how internally the memory management parameters control the memory structure if MEMORY_TARGET is set to a non-zero value:
- If SGA_TARGET and PGA_AGGREGATE_TARGET are not set, it will distribute the total server memory in a fixed ratio as 60% and 40% and assign it to SGA_TARGET and PGA_AGGREGATE_TARGET respectively.
- If SGA_TARGET and PGA_AGGREGATE_TARGET are set, they will be considered the minimum values for the sizes of SGA and the PGA respectively (But sum of SGA_TARGET and PGA_AGGREGATE_TARGET should be less than or equal to MEMORY_TARGET).
- If SGA_TARGET is set and PGA_AGGREGATE_TARGET is not set, we will still auto-tune both parameters. PGA_AGGREGATE_TARGET will be initialized to a value of (MEMORY_TARGET-SGA_TARGET).
- If PGA_AGGREGATE_TARGET is set and SGA_TARGET is not set, we will still auto-tune both parameters. SGA_TARGET will be initialized to a value of min(MEMORY_TARGET-PGA_AGGREGATE_TARGET, SGA_MAX_SIZE (if set by the user)).
Note: MEMORY_TARGET can be dynamically increased until MEMORY_MAX_TARGET without bouncing the instance. Regarding reducing the size of MEMORY_TARGET, it is just a request to Oracle server which will be honoured in the course of time.
Monitoring Automatic Memory Management in Oracle 11g using views V$MEMORY_DYNAMIC_COMPONENTS (current size of all memory components) and V$MEMORY_RESIZE_OPS (circular history list of the last 800 SGA resize requests).
C:\Documents and Settings\tchettia>sqlplus
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jul 13 11:43:05 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@ORCL> set pagesize 50
sys@ORCL> col COMPONENT format a30
sys@ORCL> select COMPONENT,CURRENT_SIZE from V$MEMORY_DYNAMIC_COMPONENTS;
COMPONENT CURRENT_SIZE
------------------------------ ------------
shared pool 192937984
large pool 4194304
java pool 12582912
streams pool 0
SGA Target 536870912
DEFAULT buffer cache 318767104
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
DEFAULT 32K buffer cache 0
Shared IO Pool 0
PGA Target 318767104
ASM Buffer Cache 0
16 rows selected.
sys@ORCL> col PARAMETER format a30
sys@ORCL> select PARAMETER,OPER_TYPE,INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS from V$MEM
ORY_RESIZE_OPS;
PARAMETER OPER_TYPE INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS
------------------------------ ------------- ------------ ----------- ---------- ---------
shared_pool_size STATIC 0 192937984 192937984 COMPLETE
db_cache_size INITIALIZING 318767104 318767104 318767104 COMPLETE
java_pool_size STATIC 0 12582912 12582912 COMPLETE
streams_pool_size STATIC 0 0 0 COMPLETE
sga_target STATIC 0 536870912 536870912 COMPLETE
db_cache_size STATIC 0 318767104 318767104 COMPLETE
db_keep_cache_size STATIC 0 0 0 COMPLETE
db_recycle_cache_size STATIC 0 0 0 COMPLETE
db_2k_cache_size STATIC 0 0 0 COMPLETE
db_4k_cache_size STATIC 0 0 0 COMPLETE
db_8k_cache_size STATIC 0 0 0 COMPLETE
db_16k_cache_size STATIC 0 0 0 COMPLETE
db_32k_cache_size STATIC 0 0 0 COMPLETE
pga_aggregate_target STATIC 0 318767104 318767104 COMPLETE
db_cache_size STATIC 0 0 0 COMPLETE
large_pool_size STATIC 0 4194304 4194304 COMPLETE
16 rows selected.
4 comments:
Is there any possibility in AAM (Oracle 11g) SGA_MAX_TARGET to be ignored.
I think no, but from your description it is a little bit confusing.
This is a little bit more detailed description of this mechanism:
http://www.datasoftech.com/library/DSI_11g_AMM.pdf
You post:
If SGA_TARGET and PGA_AGGREGATE_TARGET are not set, it will distribute the total server memory in a fixed ratio as 60% and 40% and assign it to SGA_TARGET and PGA_AGGREGATE_TARGET respectively.
But in document is posted:
If both SGA_TARGET as well as PGA_AGGREGATE_TARGET are not set then
SGA_TARGET will be initialized to
Min [ (60 * MEMORY_TARGET)/100 , SGA_MAX_SIZE (if set) ]
PGA_AGGREGATE_TARGET will be initialized to
Max [(40 *MEMORY_TARGET)/100 ,MEMORY_TARGET – SGA_MAX_SIZE (if Set) ]
Where is the truth???
Sorry for the miss type:
When write SGA_MAX_TARGET I mean SGA_MAX_SIZE.
Tere is no SGA_MAX_TARGET parameter of course.
By server memory I mean Oracle server memory (MEMORY_TARGET).
if SGA_TARGET and PGA_AGGREGATE_TARGET are not set, then
SGA_TARGET will be (60 * MEMORY_TARGET)/100 ==> 60% of total memory
and PGA_AGGREGATE_TARGET will be (40 *MEMORY_TARGET)/100 ==> 40% of total server memory.
Hope this clarifies your doubt
Hi Thiru,
In my production database, 11g R1. Memory_Target is set to 0 and all other memory parameter value is set. So guess it will not use memory target feature , please confirm and suggest. Our db use informatica queries so if we use automatic tuning feature it may benefit. Waiting for your suggeion.
Kavita
Post a Comment