Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Strange buffer resizing in 10gR2

RE: Strange buffer resizing in 10gR2

From: Jesse, Rich <>
Date: Wed, 29 Nov 2006 15:10:08 -0600
Message-ID: <>

Part IV: The Thickened Plot Thins  

At least I found the part in the 10gR2 docs where it defines this behavior:  

It says that with ASMM disabled, the Streams pool will allocate from the buffer cache 10% of the size of the shared pool.  

Now I just need to know what fires up Streams...  


From: Jesse, Rich
Sent: Wednesday, November 29, 2006 1:04 PM Cc: ''
Subject: RE: Strange buffer resizing in 10gR2

OK, a little more investigation shows:  

  1. Streams seems to be used internally by the 10gR2 database, based on the contents of the DBA_FEATURE_USAGE_STATISTICS view.
  2. Even with ASMM disabled, Oracle will adjust STREAMS_POOL_SIZE automatically, based on the V$SGA_RESIZE_OPS view.
  3. Metalink 273674.1 says "If the size of the Streams pool is zero, then the memory used by Streams is allocated from the shared pool", but empirical data suggests it's coming from DB_CACHE_SIZE, at least in my case.

So we'll see what Oracle Support says. Abraham, you might want to note this in your 9i->10g upgrade! :)  


From: Jesse, Rich
Sent: Tuesday, November 28, 2006 3:47 PM Cc:
Subject: RE: Strange buffer resizing in 10gR2

Bingo! It shows that streams_pool_size was increased from 0 to the amount that db_buffer_size was decreased. Funny, but the entry for the increase comes logically in the view before the decrease, but I won't pick nits. :)  

I don't think this would have been done manually, but I should be able to find something related to this behavior. Not used to this fun self-managing stuff just yet...  

In the meantime, I have other fires to put out now...  

Thanks, Charles!

From: Charles Schultz
Sent: Tuesday, November 28, 2006 3:07 PM To: Jesse, Rich
Subject: Re: Strange buffer resizing in 10gR2

What does v$SGA_RESIZE_OPS show? If your SGA is resizing itself (due to sga_target = 0), the pools will show multiple dynamic entries. If the sizes are static, then they were set once the database started and have not resized dynamically. Does your spfile parameter show a value, or is it blank?

On 11/28/06, Jesse, Rich <> wrote:

        So, here I am sitting in a developer's class for our new ERP package,

        and I decided to monitor the DB on AIX 5.3 that all the dev's

        individual JBoss servers are hammering. The first thing I noticed is

        that the buffer cache appears to be a whopping 16MB. Hmmm...I could

        have sworn that I set that higher. And I did.         

        The init.ora shows db_cache_size to be "50MB" and log_buffer to be

        "524288". sga_target is unset and is "0" according to V$PARAMETER. The

        init.ora file is dated November 5th, and V$INSTANCE shows that it's been

	up and running since the 9th.  There is no spfile (on purpose).
	Attempts to increase db_cache_size fail, like one would expect
with the
	default sga_max_size.
	Confucious.  There's some things in this database that are out
of my
	control (despite my protests), but I don't see how this scenario
	happen from within the DB.  The only thing I can think of is
that the 
	instance was started with an SPFILE and that file has since been
	deleted, but I'm not sure how to prove this.

Received on Wed Nov 29 2006 - 15:10:08 CST

Original text of this message