Hi! > 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. > > > -----Original Message----- > > From: Stephen Lee > > Sent: Thursday, September 11, 2003 11:39 AM > > To: Multiple recipients of list ORACLE-L > > Subject: RE: 9.2.0.4 anyone > > > > > > > > Initial testing indicates that the bug(s) that caused index > > create/rebuild > > online to lock a table and then get permanently stuck in a > > hung state have > > been fixed. > > > > It looks like 9.2.0.4 does NOT fix the problem of fatal 4031 > > situations that > > can only be cleared by restarting the instance. So you are > > still stuck with > > by guess and by golly fiddling with the shared pool. > > > > > -----Original Message----- > > > > > > Anyone have any negative experiences with 9.2.0.4 yet? > > > > > -- > > Please see the official ORACLE-L FAQ: http://www.orafaq.net > > -- > > Author: Stephen Lee > > INET: [EMAIL PROTECTED] > > > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > > San Diego, California -- Mailing list and web hosting services > > --------------------------------------------------------------------- > > To REMOVE yourself from this mailing list, send an E-Mail message > > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > > the message BODY, include a line containing: UNSUB ORACLE-L > > (or the name of mailing list you want to be removed from). You may > > also send the HELP command for other information (like subscribing). > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephen Lee > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
