RE: Shared pool from 8i to 9i

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_at_centrexcc.com] Sent: Thursday, January 20, 2005 2:54 PM To: Paula_Stankus_at_doh.state.fl.us
Cc: DGoulet_at_vicr.com; BSpears_at_Limitedbrands.com; Nagarajan.Subbiah_at_aetn.com; Michael.Kline_at_SunTrust.com; oracle-l_at_freelists.org 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_at_doh.state.fl.us wrote:

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

-- 
Regards

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