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
