Well, in fact our monitoring script was supposed to determine fragmentation level and how much free memory left. If I recall correctly, the script initiated flush shared_pool once biggest free chunk was smaller than the certain thresold. But as I mentioned, our problem was "hangs" on free latch because of this statement, but not ORA-4030. We were receiving ORA-4030 relatively often in the past on few of our RAC databases, which even caused instance failures, but it seems that with this patchset (plus some one-off's) we are more or less stable. Alex On Mon, 24 Jan 2005 22:56:18 -0500, Subbiah, Nagarajan <Nagarajan.Subbiah@xxxxxxxx> wrote: > This could be the reason running the query in every 15 minutes to monitor > the shared_pool fragmentation, gives the ORA-4030 error. I think as the > library cache latch contention causes the performance issue and the number > or sessions are increased and then ORA-4030 appears. > > I am interested to know how to avoid the ORA-4031 error. Even if this query > gives the shared_pool fragmentation chunks, Do we have any control over the > de-fragmentation? By default LRU algorithm is being used to find the > contiguous required space for the SQL queries. > > Can we have different sub pools of shared pool and specify the range for the > SQL size to go to the particular sub pool? I know that the > shared_pool_reserved_min_alloc parameter helps to certain extent. Is there > any view to find out what is the usage of the reserved size of the > shared_pool and how to find out the optimal value of the > shared_pool_reserved_size? > > We are on HP-UX 11.11 and Oracle 9.2.0.5 > > Raja > > > -----Original Message----- > > From: Alexander Gorbachev [SMTP:gorbyx@xxxxxxxxx] > > Sent: Monday, January 24, 2005 7:06 PM > > To: Nagarajan.Subbiah@xxxxxxxx > > Cc: Wolfgang Breitling; Paula_Stankus@xxxxxxxxxxxxxxx; DGoulet@xxxxxxxx; > > BSpears@xxxxxxxxxxxxxxxxx; Michael.Kline@xxxxxxxxxxxx; > > oracle-l@xxxxxxxxxxxxx > > > Subject: Re: Shared pool from 8i to 9i > > > > Hi, > > I suggest to be very careful about using this query. We have run into > > a nasty bug queryng X$KSMSP view in our monitoring job that determined > > shared_pool fragmentation. The problem was that as usage of > > shared_pool was growing most of our sessions were "hanging" for a > > while on latch free on shared_pool_latch and library_cache_latch. It > > turned out that the statement on this view was the holder of the > > latch. Ref bug 3938739. > > Our platform is HP-UX and we are on 9.2.0.5. > > > > Cheers, > > Alex > > -- Best regards, Alex Gorbachev -- //www.freelists.org/webpage/oracle-l