RE: Manual mem management in 10g

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_at_OneNeck.com>
>

> 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":
>

--
http://www.freelists.org/webpage/oracle-l