Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Shared pool from 8i to 9i

Re: Shared pool from 8i to 9i

From: Alexander Gorbachev <gorbyx_at_gmail.com>
Date: Tue, 25 Jan 2005 01:06:23 +0100
Message-ID: <c2213f6805012416066f886cf1@mail.gmail.com>


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
Received on Mon Jan 24 2005 - 19:08:49 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US