Re: Shared pool from 8i to 9i

  • From: Alexander Gorbachev <gorbyx@xxxxxxxxx>
  • To: "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
  • Date: Tue, 25 Jan 2005 07:18:12 +0100

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.


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
> 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
> >
> > Cheers,
> > Alex
> >

Best regards,
Alex Gorbachev

Other related posts: