RE: Shared pool from 8i to 9i
- From: "Michael Kline" <mkline1@xxxxxxxxxxx>
- To: <Nagarajan.Subbiah@xxxxxxxx>, "'Alexander Gorbachev'" <gorbyx@xxxxxxxxx>
- Date: Mon, 24 Jan 2005 23:04:59 -0500
And where is the "alter shared_pool coalesce;" ????
:-)
Michael Kline
Principal Consultant
Business to Business Solutions
13308 Thornridge Ct
Midlothian, VA 23112
O: 804.744.1545
Fax: 804.763.0114
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Subbiah, Nagarajan
Sent: Monday, January 24, 2005 10:56 PM
To: 'Alexander Gorbachev'; Subbiah, Nagarajan
Cc: Wolfgang Breitling; Paula_Stankus@xxxxxxxxxxxxxxx; DGoulet@xxxxxxxx;
BSpears@xxxxxxxxxxxxxxxxx; Michael.Kline@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: 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@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
>
> > 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);
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
