There is a known bug in Oracle's handling of the shared pool in 9.2.0.X (at least) where the shared pool becomes increasingly fragmented and eventually gets to where no more space can be allocated. Attempts to flush the shared pool to clear it fail (Yes, I know this means everything has to be re-parsed) and the instance must be restarted. Even attempts to flush the shared pool at regular intervals as a preventative measure fail. The latest TAR we had on this proved to be another worthless endeavor with me going on hours of wild goose chases with RDA reports, stats pak reports, trace files, and e-mail writing, all the while I keep trying to get them to acknowledge this is a known bug; and does Oracle have any known useful work arounds for it; and has Oracle determined a release in which the bug is fixed; only, end the end, to have them "recommend" two things that *I* told them: _KGHDSIDX_COUNT=1 (don't know how I could have possibly misspelled it earlier; it's such a natural name) and apply the 9.2.0.4 patch. Plus they decided to go off on some tangent about performance tuning when performance tuning was not the issue. You know, when you have this fatal 4031 thing constantly hanging over your head like some Sword of Damocles, you really would like to concentrate on that right now.
I especially like it when they tell you to try this or try that in your production database to "see what happens." Then when you continue to press the issue about this being a bug and insist that rather than banging around in the production database to "see what happens", we need to know what work around Oracle has for this bug, they accuse you of not working with them to diagnose the problem with YOUR database. Hell! It isn't a problem with the database! It's a goddam bug in the Oracle code! What's the work around? When is it fixed? Besides, field services has been out here and looked at our configuration twice already. It's difficult to get them to understand that, just because there is some SQL that isn't using bind variables (Sorry, development is under a different management hierarchy), that shouldn't put the instance into a completely hosed and unusable state. Let's see now, we're talking about a product that claims it is worth $40,000 per CPU. I don't think MS Access has this problem. Whatever happened to Larry's "Unbreakable" label? For what it's worth, we were told by field services that the _KGHDSIDX_COUNT=1 essentially makes the shared pool into an 8.1.7 shared pool. Don't know how accurate that is; don't care; just want to know how to make the problem go away. Attempts to find out if any of this has been fixed in 9.2.0.4 were never answered, but the fact that 9.2.0.4 makes the _KGHDSIDX_COUNT=1 gives one the feeling Oracle chose to sweep the bug under the rug for now. Any useful suggestions on this would be most welcomed. While I'm at it here ... I mentioned in a previous post that it looks like the lock-ups on index builds appears to be fixed in 9.2.0.4. I recall there have been some posts about unexplainable, weird locking issues that nobody could answer. I wonder if maybe this is all related. > -----Original Message----- > > 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) > -- 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).
