|
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
Review
We have confirmed a dynamic SGA action. When the size of shared pool, Java pool or large pool is insufficient, the default buffer cache will shrink automatically;on the other hand, the shared pool, Java pool and large pool will expand.
Moreover, memory space will not be adjusted among these three components. (shared pool, large pool, java pool)
After the sizes of components are modified automatically, will the space return back to the default buffer cache? (1)shared pool-< default buffer cache
(2) Java pool-< default buffer cache (3) large pool-< default buffer cache.
Test 1
(1)Clear the shared pool. (alter system flush shared_pool)
(2)Confirm that the library cache hit rate is good.
(3)Decrease the buffer cache hit rate (In this examination, we lower it from 98% to 83%.)
select round(100
* ( ( max(decode(name,'db block gets',value))
+ max(decode(name,'consistent gets',value))
- max(decode(name,'physical reads',value)))
/ ( max(decode(name,'db block gets',value))
+ max(decode(name,'consistent gets',value)))),2) HIT_RATIO
from v$sysstat
/
HIT_RATIO
----------
83.01
|
The sizes of SGA components have not changed at all.
TIME COMPONENT OPER_TYPE GAP Final Size STATUS
------------------- -------------------- --------- --- ---------- --------
2004/08/23 15:03:44 DEFAULT buffer cache SHRINK -4 76 COMPLETE
2004/08/23 15:03:44 shared pool GROW 4 40 COMPLETE
2004/08/23 15:03:58 DEFAULT buffer cache SHRINK -4 72 COMPLETE
2004/08/23 15:03:58 shared pool GROW 4 44 COMPLETE
|
The manual does not mention about the logic of the expansion of default buffer cache. In this case, is it possible to alter it manually, then?
Test 2
Let's expand the default buffer cache manually.
shared pool (from 44MB to 40MB) * shrinked
default buffer cache (from 72MB to 76MB) *expanded
sga_target (from 128MB to 140MB: sga_max_size)
TIME COMPONENT OPER_TYPE GAP Final Size STATUS
------------------- -------------------- --------- --- ---------- --------
2004/08/23 15:03:58 DEFAULT buffer cache SHRINK -4 72 COMPLETE
2004/08/23 15:03:58 shared pool GROW 4 44 COMPLETE
SQL> alter system set sga_target=140M;
System changed.
SQL> alter system set shared_pool_size=40M;
System changed.
SQL> alter system set db_cache_size=76M;
System changed.
|
The sizes of SGA components have not change at all. It seems that default buffer cache cannot even be executed manually. Moreover, we also failed to shrink the shared pool manually.
(In this case, should we take specifying "expansion" of shared pool as the trigger of recalculation?)
* In Oracle 9.2 , it is possible to shrink the shared pool manually.
We expand the shared pool since sga_target seems to have some more margin.
SQL> alter system set shared_pool_size=48M;
System changed.
TIME COMPONENT OPER_TYPE GAP Final Size STATUS
------------------- -------------------- --------- --- ---------- --------
2004/08/23 15:09:03 DEFAULT buffer cache SHRINK -4 80 COMPLETE
2004/08/23 15:09:03 shared pool GROW 4 48 COMPLETE
|
The shared pool has been expanded. The remaining space in the sga_target range is allocated to default buffer cache and recalculated. (from 72MB to 80MB) As a result, default buffer cache is also expanded.
Results
Although there is some ramaining space in the shared pool, Java pool and large pool and the space of default buffer cache is insufficient, size change does not occur in dynamic SGA. Therefore, when the application enters standby mode due to insufficient space of buffer cache, we cannot expect dynamic SGA to solve this problem.
If you really want to increase the size of default buffer cache, it is possible to increase sga_target manually and modify the space of shared pool, Java pool and larege pool (when sga_max_size>=sga_target).
(The remaining space will be allocated to default buffer cache and the difference may be more than the current default buffer cache.)
It might be difficult to determine automatically that there is remaining space in the shared pool, Java pooland large pool, but it is surprising that it is also impossible to shrink the components manually.
There is still another case.
When sga_target<=total size of SGA components, and an expansion error occurrs after we execute alter system set db_cache_size="size to be expanded" (it is not surprising since there is no remaining space in SGA), the following information is recorded in V$SGA_RESIZE_OPS dynamic view.
TIME COMPONENT OPER_TYPE GAP Final Size STATUS
------------------- -------------------- --------- --- ---------- ------
2004/08/23 15:06:13 shared pool SHRINK 0 44 ERROR
2004/08/23 15:06:13 DEFAULT buffer cache GROW 0 72 ERROR
|
It can be observed that we have tried to shrink the shared pool and expand the default buffer cache(GROW). It means that such a kind of logic does exist. That is all for today.
Hideaki Oishi
|
|
