Knowledge increases exponentially. Today, you probably own more books than great universities of times past—Cambridge University owned less than two hundred books in the fifteenth century. First came the invention of writing, then alphabets, then paper, then the printing press, then mechanization. Each step caused an exponential increase in the collective human knowledge. In our generation, Al Gore invented the internet and the last barriers to the spread of knowledge have been broken. Today, everybody has the ability to contribute, communicate, and collaborate. We are all caught up in a tsunami, an avalanche, a conflagration, a veritable explosion of knowledge for the betterment of humankind. This is the blog of the good folks at Database Specialists, a brave band of Oracle database administrators from the great state of California. We bid you greeting, traveler. We hope you find something of value on these pages and we wish you good fortune in your journey.

Automatic Shared Memory Management (ASMM) - even when not configured

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:

shared pool
large pool
java pool
buffer cache
streams pool
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

sga_target=10G
db_cache_size=6G

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. 

COMPONENT OPER_TYPE OPER_MODE INITIAL(M) FINAL(M) CHANGE(M) END_TIME
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.

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>