RE: Strange buffer resizing in 10gR2
- From: "Jesse, Rich" <Rich.Jesse@xxxxxx>
- Date: Wed, 29 Nov 2006 15:10:08 -0600
Part IV: The Thickened Plot Thins
At least I found the part in the 10gR2 docs where it defines this
behavior:
http://tinyurl.com/ybtewh
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...
Rich
________________________________
From: Jesse, Rich
Sent: Wednesday, November 29, 2006 1:04 PM
Cc: 'oracle-l@xxxxxxxxxxxxx'
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! :)
Rich
________________________________
From: Jesse, Rich
Sent: Tuesday, November 28, 2006 3:47 PM
Cc: oracle-l@xxxxxxxxxxxxx
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!
Rich
________________________________
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 <Rich.Jesse@xxxxxx> wrote:
So, here I am sitting in a developer's class for our new ERP
package,
and I decided to monitor the 10.2.0.2.0 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
could
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.
Thoughts?
Rich
Other related posts:
