|
Dynamic SGA and automatic memory management
|
Hi, everyone. After the DataGuard topic, I would like to introduce the upgraded features in Oracle 10g, dynamic SGA and automatic memory management. This dynamic SGA, as a new feature since Oracle 9i, allows changing the SGA configuration without stopping the instance.
Expansion and shrinkage
# Default buffer cache, shared pool, large pool and process private memory can be changed dynamically.
Expansion: up to SGA_MAX_SIZE
Shrinkage: down to the minimum size defined by Oracle (the limit suggested according to the O/S)
It can be altered by ALTER SYSTEM SET statement.
# The expansion and the shrinkage of buffer pool and SGA pool are executed by Oracle internal management.
If the component takes memory, the internal automatic tuning will transmit memory from other components.
Note: The buffer pool will be shrinked and the shared pool will be expanded.
Some points that look different in Oracle 10g
# Java pool and streams pool can be changed dynamically.
Note: The SGA component Streams pool is available since Oracle 10g.
# The memory managament method has changed very much.
Initial parameters are simplified, SGA_TARGET is added, and we do not have to specify SGA memory parameters anymore.
SGA configuration can be determined without specifying SHARED_POOL_SIZE, LARGE_POOL_SIZE,
JAVA_POOL_SIZE,DB_CACHE_SIZE, etc.
They can still be defined seperately, but automatic management will not be executed then.
# Be carefule that STATISTICS_LEVEL parameter must be either TYPICAL or ALL.
# The SGA configuration memory parameters are organized and can be checked in the newly added
V$SGAINFO dynamic view. Granule size and other information are included in this view.
# Changes in SGA size are reflected synchronously in Oracle 9i but with some delay in Oracle 10g.
Therefore, a new flag value is defined in the dynamic view.
ExjValues added or deleted in V$SGA_RESIZE_OPS
OPER_TYPE: Operation type
STATIC (ADDED)
INITIALIZING (ADDED)
DISABLED (ADDED)
SHRINK_CANCEL (ADDED)
OPER_MODE:Operation mode
DISABLED (ADDED)
IMMEDIATE (ADDED)
AUTO (DELETED)
STATUS:Job-end status
INACTIVE (ADDED)
PENDING (ADDED)
COMPLETE (ADDED)
CANCELLED (ADDED)
NORMAL (DELETED)
CANCEL (DELETED)
|
Let's expand and shrink the memory dynamically. We will expand the shared pool, and the default buffer pool will shrink or expand accordingly. In Oracle 10g, SGA_TARGET is also an important value as the trigger of expansion or shrinkage.
Environment
Linux RAC10g1 2.4.9-e.40smp
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
SGA is defined as 176MB.
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_max_size big integer 176M
sga_target big integer 176M
|
We can see the size of SGA by referring to the newly added dynamic view. One granule is 4MB in this environment.
SQL> select * from V$SGAINFO;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 778016 No
Redo Buffers 524288 No
Buffer Cache Size 117440512 Yes
Shared Pool Size 54525952 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No ->Pgranule
Maximum SGA Size 184549376 No
Startup overhead in Shared Pool 33554432 No
Free SGA Memory Available 0
11 rows are selected.
|
Expansion of shared pool
We will expand the shared pool from 52MB to 56MB.
NAME TYPE VALUE
------------------------------------ ----------- -----------
__shared_pool_size big integer 52M
streams_pool_size big integer 0
__db_cache_size big integer 112M
SQL> ALTER SYSTEM SET shared_pool_size ='56M' SCOPE=MEMORY SID='ora101';
System is changed.
|
The time this process takes depends on the specified size. Let's confirm that the size is expanded.
NAME TYPE VALUE
------------------------------------ ----------- -----------
__shared_pool_size big integer 56M
shared_pool_size big integer 56M
__db_cache_size big integer 108M
|
The expansion size of shared pool is equal to the shrinkage size of default buffer cache.
Let's check the dynamic view that is related with SGA.
It is shown that
db_cache_size SHRINKS to 113246208 (108MB) and
shared_pool_size GROWS to 58720256 (56MB).
Note: FINAL_SIZE shows the final size.
SQL> select COMPONENT,OPER_TYPE,OPER_MODE,PARAMETER,
INITIAL_SIZE,TARGET_SIZE,FINAL_SIZE,STATUS,
to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS')as START_TIME,
to_char(END_TIME, 'YYYY/MM/DD HH24:MI:SS')as END_TIME
from V$SGA_RESIZE_OPS ;
COMPONENT OPER_TYPE OPER_MODE PARAMETER
------------------------ ------------- --------- ---------------
DEFAULT buffer cache SHRINK MANUAL db_cache_size
shared pool GROW MANUAL shared_pool_size
INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS
------------ ----------- ---------- ---------
117440512 113246208 113246208 COMPLETE
^^^(108MB)
54525952 58720256 58720256 COMPLETE
^^(56MB)
START_TIME END_TIME
------------------- -------------------
2004/07/11 15:32:13 2004/07/11 15:32:13
2004/07/11 15:32:13 2004/07/11 15:32:13
|
Although it seems easy this time, sometimes it takes long for ALTER statement to get response after we execute the same process several times. Be carefule when you want to apply it to the real system.
Next time we will talk about the optimal timing for resizing.
Hideaki Oishi
|
|
