Oracle 10g introduced a new feature known as ‘Automatic Shared Memory Management (ASMM)’. As its name suggests this feature allows an Oracle instance to automatically adjust some of its internal memory sizes:
pga_aggregate_target <- 11g only if using memory_target
The setting of sga_target (10g or 11g) or memory_target (11g) to a non-zero value provides a total amount of memory that Oracle can adjust between the above components as it sees fit. When using this feature if you have any of the above parameter set to non zero values then they act as minimums e.g. if you have
then your buffer cache should not fall below 6G, it can go above 6G but Oracle is effectively now only managing 4G of memory automatically. When initially introduced this feature had some bugs (not surprisingly given the complexity of the underlying memory structures ) that could cause multiple resizes to occur quickly which could in turn cause ‘cursor: pin S wait on X’ waits. Also under some circumstances (particularly with poorly behaved applications that do not use bind variables correctly) it was prone to grow the shared pool to large sizes which in turn could cause shared pool latching issues. Of course patches are available for the bugs and later versions continue to improve.
The new wrinkle is that in 11.2 ASSM resizes can happen automatically even when both sga_target=0 and memory_target=0 i.e. when this feature was formerly turned off. Here is a query from v$memory_resize_ops from a client that was started with a pfile that has sga_target=0 and no memory target defined.
|java pool||STATIC||0||768||768||16-MAR-2012 01:59:04|
|DEFAULT buffer cache||INITIALIZING||61440||61440||0||16-MAR-2012 01:59:07|
|streams pool||STATIC||0||256||256||16-MAR-2012 01:59:04|
|DEFAULT buffer cache||STATIC||0||61440||61440||16-MAR-2012 01:59:04|
|large pool||STATIC||0||768||768||16-MAR-2012 01:59:04|
|shared pool||STATIC||0||1536||1536||16-MAR-2012 01:59:04|
|DEFAULT buffer cache||SHRINK||IMMEDIATE||61440||61184||-256||16-MAR-2012 05:10:21|
|shared pool||GROW||IMMEDIATE||1536||1792||256||16-MAR-2012 05:10:21|
|DEFAULT buffer cache||SHRINK||IMMEDIATE||61184||60928||-256||16-MAR-2012 05:36:28|
|shared pool||GROW||IMMEDIATE||1792||2048||256||16-MAR-2012 05:36:28|
|DEFAULT buffer cache||SHRINK||IMMEDIATE||60928||60672||-256||16-MAR-2012 06:07:04|
|shared pool||GROW||IMMEDIATE||2048||2304||256||16-MAR-2012 06:07:04|
|DEFAULT buffer cache||SHRINK||IMMEDIATE||60672||60416||-256||16-MAR-2012 06:07:04|
|shared pool||GROW||IMMEDIATE||2304||2560||256||16-MAR-2012 06:07:04|
As you can see, after the database started the shared pool was grown 4 times each by 256M over a 4 hour period. For those of us with Oracle support you can find more details in metalink note 1269139.1 but the main message here is that ‘turning off’ ASMM no longer prevents resize operations from running and there is nothing logged into the alert log at present so the only way to detect it is happening is to be monitoring v$sga_resize_ops or v$memory_resize_ops.