RE: Shared pool from 8i to 9i

  • From: "Subbiah, Nagarajan" <Nagarajan.Subbiah@xxxxxxxx>
  • To: 'Wolfgang Breitling' <breitliw@xxxxxxxxxxxxx>, "'Paula_Stankus@xxxxxxxxxxxxxxx'" <Paula_Stankus@xxxxxxxxxxxxxxx>
  • Date: Thu, 20 Jan 2005 15:04:55 -0500

I agree with the Java application is doing the fragmentation. Since we use
Java application and PL/SQL code, we are having ORA-4031 problem now
(9.2.0.5 and HP 11.11) and oracle has suggested to use the parameter
_kghdsidx_count=1 . By default the shared_pool is divided into sub pools and
we have 2.

Also, the default statistic_level=TYPICAL. This also was suggested to change
it to BASIC.

How to do the pro-active monitoring and take any action with the following
query?

Using this view you will be able to find out how the free space is currently
allocated, which will be helpful to understand the level of fragmentat
ion of the shared pool. As it was described before, the first place to find
a ch
unck big enough for the cursor allocation is the free list. The following
SQL sh
ows the chucks available in the free list: 

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); 

If the result of the above query shows that most of the space available is
on the top part of the list (meaning available only in very small chuncks
). It is very likely that the error is due to a heavy fragmentation. 


Raja.

-----Original Message-----
From: Wolfgang Breitling [mailto:breitliw@xxxxxxxxxxxxx] 
Sent: Thursday, January 20, 2005 2:54 PM
To: Paula_Stankus@xxxxxxxxxxxxxxx
Cc: DGoulet@xxxxxxxx; BSpears@xxxxxxxxxxxxxxxxx; Nagarajan.Subbiah@xxxxxxxx;
Michael.Kline@xxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Re: Shared pool from 8i to 9i


That was what I was wondering. I have worked with Peoplesoft 
applications for for the past 10+ years and I have never run into shared 
pool memory (4031) problems and I keep the shared pool deliberately 
rather small since Peoplesoft is not very good at cursor sharing. I know
that's not the OP's question, but the answer depends a lot on the 
type of application. If it uses Java or lots of PL/SQL code the shared 
pool footprint may very well change from 8i to 9i. But more than 4-fold 
seems excessive.

Paula_Stankus@xxxxxxxxxxxxxxx wrote:

> Guys,
> 
> How do the longs factor into the problem with shared pool??  =20
> 

-- 
Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com
--
//www.freelists.org/webpage/oracle-l

Other related posts: