RE: Manual mem management in 10g
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 1 Jul 2006 10:31:01 +0100
A couple of ideas:
If Oracle thinks it needs a large shared pool, it must
be hitting cases where it has to work hard to find
memory. But when that happens, you may find that
a session flushes a huge number of items from the
library cache to free up a sufficiently large chunk
of contiguous memory. So the sequence goes like this:
Session needs 22K contiguous
Can't find it - so Oracle records allocation problem
session flushes 20,000 4KB chunks before managing
to coalesce 22KB.
You peek at v$sgastat and see 80MB free memory
My worst case scenario was seeing several hundred
megabytes of library cache being flushed so that 22KB
could be allocated. In the 'old days' the solution was
to increase the size of the reserved pool.
KGH: NO ACCESS - if I had to guess about this one,
I would say that this was Oracle trying to reduce the size
of the shared pool to allow another cache to grow. Oracle
can only remove an empty granule from the shared pool,
but if there are lots of tiny chunks that are pinned, then it
may be very hard to find a completely freeable granule.
I don't know how Oracle will deal with this, but maybe it
picks a few granules, makes them unavailable for new
accesses, and waits for pins to drop. Henec KGH: No Access.
As the manual says "Oracle is very cautious about growing
the shared pool because it can be hard to shrink".
Personally I wouldn't use automatic SGA sizing at present.
Regards
Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Subject: RE: Manual mem management in 10g
Date: Fri, 30 Jun 2006 09:25:05 -0700
From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
A couple odd things I've
noticed - a lot
of free memory in the shared pool, even though ADDM keeps telling me I
need to increase my SGA, and a lot of memory allocated to "KGH: NO
ACCESS":
--
//www.freelists.org/webpage/oracle-l
Other related posts:
