http://www.mail-archive.com/oracle-l@fatcity.com/msg79721.html
Oh yeah, I forgot to say that in 9.2.0.3 the shared pool was broken up into “heaps” (Oracle terminology) but whatever fancy stuff they were trying to accomplish by doing this (I think part of the magical self-tuning initiative) was buggy and this hastened the fatal 4031 situation. There is a parameter (so I have been told) Shared pool has always been a heap (inside SGA heap, and can contain heaps as well if requested). Shared pool heap is physically divided to equal sized extents. But space inside shared pool heap is allocated in “chunks” and there are freelists&lru lists for tracking free and unpinned recreable
tchunks. There is actually 255 freelists, each for different allocation size range, starting from 16 bytes up to about 64+ kilobytes. Depending on how much memory allocation request has been made, the proper freelist is scanned. When matching size chunks aren’t found, the closest match is split. Anyway, lets call these 255 freelists a shared pool “freelist set” (don’t
know the correct Oracle term). But people often suffer from shared pool latch contention when a lot of shared pool memory allocation occurs (no bind variables are used in SQL etc etc).
Lots of concurrent memory allocation requests mean lots of processes trying to acquire shared pool latch and scan through relevant freelist, if no sufficiently large chunk found, then LRU list as well. During these operations, the shared pool latch is being hold by session doing the scanning causing shared pool latch contention. Various “experts” suggesting to increase your shared pool make the situation even worse, because in time, the LRU and freelists get even longer, thus
one scan takes more time to complete or fail.
_kghsidx_count = 1
(The correct name is _kghdsidx_count)
So, in 9i Oracle introduced the ability to create several heaps for
serving
shared pool. If you’ve set the shared_pool_size to 64M and
_kghdsidx_count
to 4, you’ll have 4*16M shared pool heaps, every heap having its own
descriptor, extents, LRU lists, freelists and shared pool child latch
(there
is no such child latch in 8i). Shorter lists mean faster scanning, more
latches mean more scalability when serializing access to a resource. The
downside is, that usually the “resource” has to be split that every latch
protects one and only one part of resource. Thus if the shared pool is
split
into 4 parts and all memory allocation requests happen to use the same
freelist for some reason, only 25% of memory can be used. This is the
reason
why you can avoid ORA-4031s when setting _kghsidx_count to 1 – this
practically enables the old behaviour.
In 8i there is only one set of freelists, in 9i there can be more sets,
default is 1 and max limit is 7 as far as simple testing on my
9.2.0.4/W2k
has showed – I have only 7 shared pool child latches and didn’t find a
way
to increase them.
When you increase _kghdsidx_count, you see more lines in x$kghlu as well,
one for each heap (normally there was only one). Also you can verify the
behaviour when taking SGA heapdump at level 2 and search for “HEAP DUMP”
or
“FREE LISTS” in trace, there are as many free lists in dump, as you’ve
stated with _kghdsidx_count init parameter. (there is one “extra” heap in
dump, this is the SGA parent heap for shared pool heaps).
that makes the shared pool one big memory area.
One of the “fixes” in 9.2.0.4 is to make this the default now (so I
have
been told).
Can’t get my hands oon 9.2.0.2 or 0.3, but in 0.4 (on Windows), the
_kghsidx_count defaults to 1 anyway, check it out on your systems.
Tanel.