It's a hidden parameter, use this query instead: select n.ksppinm name, v.ksppstvl value from x$ksppi n, x$ksppsv v where n.indx = v.indx and n.ksppinm = '_kghdsidx_count';
Tanel. ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Friday, September 12, 2003 5:59 PM > How can I not see the init parameter, _kghdsidx_count in 9.2.0.3.0? > > > >From: "Tanel Poder" <[EMAIL PROTECTED]> > >Reply-To: [EMAIL PROTECTED] > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > >Subject: Re: 9.2.0.4 anyone > >Date: Thu, 11 Sep 2003 17:54:30 -0800 > > > >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). > > _________________________________________________________________ > Get 10MB of e-mail storage! Sign up for Hotmail Extra Storage. > http://join.msn.com/?PAGE=features/es > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Nancy Hu > 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).
