|
Dynamic SGA and automatic memory management
|
Hello. We would like to continue the topic "Dynamic SGA and automatic memory management."
Environment
Windows XP Pro + SP1
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
Memory expansion and shrinkage of SGA components
In Oracle 10g, it is not required anymore to set up the sizes of all SGA components, which include shared pool, Java pool, large pool and default buffer cache.
By specifying only the total size of SGA with the initial parameter sga_target, the size of each component will be modified automatically.
(Even if we do not set up the initial parameter, the size of each component will still be modified automatically according to the system load.
In fact, the initial parameter assigns the minimum size of each component.
The shared pool, Java pool, large pool and default buffer cache work as source of memory; therefore, whenever the system detects insufficiency of space after modifying the components automatically, the expansion of the space will be executed automatically.
Furthermore, the memory space will not move among the components such as shared pool, Java pool and large pool.
Let's start the examination. In order to make the system recognize the insufficiency of shared pool size is insufficient, we executed a lot of SQL statements to decrease the library cache hit rate.
(After creating a table, we execute a lot of SQL statements in PL/SQL without using any bind variable. This time we execute 3000 rows of insert statements.)
BEGIN
INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*100));
INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*101));
INSERT INTO TBL1(t1,t2,t3,t4) VALUES (i,(i*100),(i*100),(i*102));
（details unwritten....）
END;
/
|
Confirm the expansion or shrinkage of the components.
SQL>
select to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS') as "ProcessTime",
COMPONENT as "Component",
OPER_TYPE as "Operation",
(FINAL_SIZE-INITIAL_SIZE)/1024/1024 as "Difference(MB)",
FINAL_SIZE/1024/1024 as "Size(MB)"
from V$SGA_RESIZE_OPS;
ProcessTime Component Operation Difference(MB) Size(MB)
------------------- -------------------- ------------ ------------- ----------
2004/08/10 23:00:04 DEFAULT buffer cache SHRINK -4 76
2004/08/10 23:00:04 shared pool GROW 4 40
2004/08/10 23:23:03 DEFAULT buffer cache SHRINK -4 72
2004/08/10 23:23:03 shared pool GROW 4 44
|
It looks like the memory space allocation to the shared pool is reflected and the space is always allocated from buffer cache.
The size increases or decreases by the unit of 4KB (1 granule).
When the space is allocated from the default buffer cache
If the default buffer cache shrinks to its lower limit, what will happen?
Confirm the expansion or shrinkage of the components.
SQL>
select to_char(START_TIME,'YYYY/MM/DD HH24:MI:SS') as "ProcessTime",
COMPONENT as "Component",
OPER_TYPE as "Operation",
(FINAL_SIZE-INITIAL_SIZE)/1024/1024 as "Difference(MB)",
FINAL_SIZE/1024/1024 as "Size(MB)"
from V$SGA_RESIZE_OPS;
ProcessTime Component Operation Difference(MB) Size(MB)
------------------- -------------------- ------------ ------------- ----------
2004/08/10 23:00:04 DEFAULT buffer cache SHRINK -4 76
2004/08/10 23:00:04 shared pool GROW 4 40
2004/08/10 23:23:03 DEFAULT buffer cache SHRINK -4 72
2004/08/10 23:23:03 shared pool GROW 4 44
|
It looks like the memory space allocation to the shared pool is reflected here and the space is always allocated from buffer cache.
The size increases or decreases by the unit of ４KB (1 granule).
When the space is allocated from the default buffer cache
If the default buffer cache shrinks to its lower limit, what will happen?
Current parameters (Specified by the initial parameter)
NAME TYPE VALUE
------------------------------------ ----------- -------
sga_max_size big integer 140M
sga_target big integer 128M
db_cache_size big integer 68M ←The lower limit
Current value of buffer pool
SQL> select name,bytes/1024/1024 as "Byte(MB)"
from v$SGASTAT where name = 'buffer_cache';
NAME Byte(MB)
-------------------------- ----------
buffer_cache 80
|
We executed a lot of SQL statements to decrease the library cache hit rate. This is the result of shrinkage.
ProcessTime Component Operation Difference(MB) Size(MB)
------------------- -------------------- ---------- -------------- ----------
2004/08/11 16:22:42 DEFAULT buffer cache SHRINK -4 76
2004/08/11 16:22:42 shared pool GROW 4 40
2004/08/11 16:22:49 DEFAULT buffer cache SHRINK -4 72
2004/08/11 16:22:49 shared pool GROW 4 44
2004/08/11 16:24:14 DEFAULT buffer cache SHRINK -4 68 *
2004/08/11 16:24:14 shared pool GROW 4 48
|
RELOADS of the library cache increased 6 times.
NAMESPACE GETS GETHIT PINS PINHIT RELOADS
-------------- ------ ------ ------ ------ -------
SQL AREA 18471 0.50 74567 0.61 6091
|
The size of buffer cache shrinked to 68MB.
SQL> select name,bytes/1024/1024 as "Byte(MB)"
2 from v$SGASTAT where name = 'buffer_cache';
NAME Byte(MB)
-------------------------- ----------
buffer_cache 68
|
Let's alter it dynamically. We will increase the shared pool.
SQL> alter system set shared_pool_size=49M;
alter system set shared_pool_size=49M
*
Errors occurred at row 1.
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool
|
It does not work. It seems like the lower limit is taken as the stopper. The key point is the upper limit of sga_target.
SQL> select trunc(sum(bytes)/1024/1024) from v$SGASTAT ;
TRUNC(SUM(BYTES)/1024/1024)
---------------------------
128
|
Increase sga_target to sga_max_size.
SQL> alter system set sga_target=140M;
System changed.
NAME TYPE VALUE
------------------------------------ ----------- ----------
sga_max_size big integer 140M
sga_target big integer 140M
|
Expand the shared pool again.
SQL> alter system set shared_pool_size=49M;
System changed.
|
It worked well this time. Judging from the result of expansion, the size of default buffer cache has been expanded.
Due to the automatic modification, the lower limit of default buffer cache is always the upper limit of sga_target (maximum sga_max_size).
ProcessTime Component Operation Difference(MB) Size(MB)
------------------- -------------------- ---------- -------------- ----------
2004/08/11 16:22:42 DEFAULT buffer cache SHRINK -4 76
2004/08/11 16:22:42 shared pool GROW 4 40
2004/08/11 16:22:49 DEFAULT buffer cache SHRINK -4 72
2004/08/11 16:22:49 shared pool GROW 4 44
2004/08/11 16:24:14 DEFAULT buffer cache SHRINK -4 68
2004/08/11 16:24:14 shared pool GROW 4 48
2004/08/11 16:26:10 DEFAULT buffer cache SHRINK -4 76 *
2004/08/11 16:26:10 shared pool GROW 4 52
|
Moreover, even if the size of default buffer cache is insufficient, the system does not shrink the shared pool or other components to expand the default buffer cache.
We guess that
# when the size of buffer cache is insufficient, the status becomes standby and errors do not occur.
# insufficiency of shared pool or other components is a more serious problem than buffer cache standby.
Allocating space from the shared pool or other components might deteriorate the situation.
# when the size of shared pool, large pool and Java pool is insufficient,
ORA-4031(unable to allocate string bytes of shared memory) and
ORA-29554(Unhanded Java out of memory condition) occur and user processing will fail.
Of course it is better to test all these guesses to make sure. That is all for today.
Hideaki Oishi
|
|
