Re: Shared pool from 8i to 9i

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_at_aetn.com> 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_at_gmail.com]
> > Sent: Monday, January 24, 2005 7:06 PM
> > To: Nagarajan.Subbiah_at_aetn.com
> > Cc: Wolfgang Breitling; Paula_Stankus_at_doh.state.fl.us; DGoulet_at_vicr.com;
> > BSpears_at_Limitedbrands.com; Michael.Kline_at_SunTrust.com;
> > oracle-l_at_freelists.org
>
> > 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
--
http://www.freelists.org/webpage/oracle-l