|
Dynamic SGA and automatic memory management
|
Hi, everyone. This week we want to continue the same topic, Dynamic SGA and automatic memory management. We examined the expansion of the shared pool last week. Next, let's check how the shrinkage changes the SGA component.
<Review of the previous test>
Environment
Linux RAC10g1 2.4.9-e.40smp
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
Expansion of shared pool
The shared pool is expanded 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.
|
NAME TYPE VALUE
------------------------------------ ----------- -----------
__shared_pool_size big integer 56M
shared_pool_size big integer 56M
__db_cache_size big integer 108M
|
shared_pool_size GROWS to 58720256 (56MB).
db_cache_size SHRINKS to 113246208 (108MB) and
<Next test - shrinkage>
In Oracle 9i, when memory size is changed dynamically, the size changes will be reflected synchronously in MANUAL mode. However, in Oracle 10g, such changes are reflected with some delay in INTERNAL mode. SGA_TARGET is an important value as the trigger of expansion or shrinkage.
Shrinkgae of the shared pool
The shared pool was once expanded to 56MB, and we now want to shrink it to 52MB.
SQL> ALTER SYSTEM SET shared_pool_size ='52M' SCOPE=MEMORY SID='ora101';
System is changed.
|
Even if the shared pool parameter seems to be back, the buffer cache does not change. When will resizing start?
NAME TYPE VALUE
------------------------------------ ----------- -----------
__shared_pool_size big integer 56M
shared_pool_size big integer 52M
__db_cache_size big integer 108M@<HERE!>
db_cache_size big integer 0
|
In the dynamic view, it is not recognized as SGA blank space.
SQL> select * from V$SGA_DYNAMIC_FREE_MEMORY;
CURRENT_SIZE
------------
0
|
Nothing is different in V$SGA_RESIZE_OPS. We don't really feel like waiting anymore....
Push the button.....
By changing SGA_TARGET, which is newly added in Orcle 10g, recounting will be executed due to automatic memory management. Since it is 176MB, as large as SGA_MAX_SIZE, let's change it to 168MB. Be carefule it can not exceed SGA_MAX_SIZE.
NAME TYPE VALUE
------------------------------------ ----------- -----------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 176M
sga_target big integer 176M
SQL> ALTER SYSTEM SET sga_target ='168M' SCOPE=MEMORY SID='ora101';
System is changed.
NAME TYPE VALUE
------------------------------------ ----------- -----------
sga_max_size big integer 176M
sga_target big integer 168M
|
176MB-168MB=8MB blank space is recognized.
SQL> select * from V$SGA_DYNAMIC_FREE_MEMORY;
CURRENT_SIZE
------------
8388608
|
The result of resizing is shown in V$SGA_RESIZE_OPS. Default buffer cache is adjusted to 100MB according to SGA_TARGET. Moreover, DEFERRED mode, which is not available in Oracle 9i, is displayed now.
COMPONENT OPER_TYPE OPER_MODE PARAMETER
------------------------ ------------- --------- ------------
DEFAULT buffer cache SHRINK DEFERRED db_cache_size
^^^^^^^^
INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS
------------ ----------- ---------- ---------
113246208 104857600 104857600 COMPLETE
^^^^^^^^^
START_TIME END_TIME
------------------- -------------------
2004/07/11 15:35:23 2004/07/11 15:35:23
NAME TYPE VALUE
------------------------------------ ----------- -----------
__db_cache_size big integer 100M
db_cache_size big integer 0
|
After a while...
NAME TYPE VALUE
------------------------------------ ----------- -----------
__db_cache_size big integer 96M
db_cache_size big integer 0
|
The buffer cache is shrinked.
In V$SGA_RESIZE_OPS, IMMEDIATE is shown, Java pool is expanded, and the default buffer pool is shrinked from 100MB to 96MB.
COMPONENT OPER_TYPE OPER_MODE PARAMETER
------------------------ ------------- --------- --------------
java pool GROW IMMEDIATE java_pool_size
DEFAULT buffer cache SHRINK IMMEDIATE db_cache_size
^^^^^^^^^
INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS
------------ ----------- ---------- ---------
4194304 8388608 8388608 COMPLETE
104857600 100663296 100663296 COMPLETE
^^^^^^^^^
START_TIME END_TIME
------------------- -------------------
2004/07/11 16:00:17 2004/07/11 16:00:17
2004/07/11 16:00:17 2004/07/11 16:00:17
|
As we have shown above, once SGA_TARGET parameter is changed, the optimal size will be determined to fit the workload according to internal tuning algorithm. SGA component size will be changed dynamically.
Next time we will make a further discussion about dynamic SGA and automatic memory management.
Hideaki Oishi
|
|
