RE: Shared pool from 8i to 9i

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
>
> > select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
> "From",
> > count(*) "Count" , max(KSMCHSIZ) "Biggest",
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ<140
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
> > UNION ALL
> > select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ between 140 and 267
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
> > UNION ALL
> > select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ between 268 and 523
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
> > UNION ALL
> > select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX,
> 500*trunc(KSMCHSIZ/500)
> > ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ between 524 and 4107
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
> > UNION ALL
> > select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX,
> 1000*trunc(KSMCHSIZ/1000) ,
> > count(*) , max(KSMCHSIZ) ,
> > trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
> > from x$ksmsp
> > where KSMCHSIZ >= 4108
> > and KSMCHCLS='free'
> > group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);

--
http://www.freelists.org/webpage/oracle-l