FreeLists / oracle-l / RE: sub heap & ORA-4031

[Bernard Polarski <bpolarsk@xxxxxxxxx>]

Hello,
 
 time to re-read the booklet of steve Adams, since you are using its APT 
scripts.
 
 a) The size of 25000 bytes sound like the initial memory of the session. It is 
around 30k per session and appear in x$ksms under the lablel of 'session param 
v '. But I have seen this value of 30k as low as 15k. there are very few 
objects in oracle that still requieres more than 5k of mem.
 
 
 b) x$kghlu deals with the LRU of the shared pool. You will not find the 
distribution of the chunks of memory and distribution of type of chhunks there. 
Rather use x$ksmsp for that. Pay a specifi attention to the namespace 
'permanent memo' as it hid lot of free memory. if number of chunk is high (more 
than 10.000) you probably wasting tons of memory there. I posted on this topic 
yesterday, but no luck on response.
 
 Get your number of chunks in the shared pool this way :
 
 col contents format a30
 set linesize 124 head on pause off pagesize 333
 select
   ksmchcom  contents,
   count(*)  chunks,
   sum(decode(ksmchcls, 'recr', ksmchsiz))  recreatable,
   sum(decode(ksmchcls, 'freeabl', ksmchsiz))  freeable,
   sum(ksmchsiz)  total
 from sys.x$ksmsp where inst_id = userenv('Instance') and ksmchcls not like 'R%'
 group by ksmchcom
 
 
 This query from metallink will give you the distribution of the buckets :
 
 set linesize 124 head on pause off pagesize 333
 col bucket format A20
 col KSMCHCLS format a10
 
 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);
 
 
 Now if the memory is fragmented before the chunk type 4400 (which correspond 
to _shared_pool_size_min_alloc), then you have to resort to 
shared_pool_reserved_size and you will find the state and distribution  of the 
reserved pool with x$ksmspr
 
 
 set linesize 124 pagesize 33 head on
 select
   ksmchcom  contents,
   count(*)  chunks,
   sum(decode(ksmchcls, 'R-recr', ksmchsiz))  recreatable,
   sum(decode(ksmchcls, 'R-freea', ksmchsiz))  freeable,
   sum(ksmchsiz)  total
 from
   sys.x$ksmspr
 where
   inst_id = userenv('Instance')
 group by
   ksmchcom
 /
 
 
 Now you have enough info to reply to your question as of what is left where.
 
 You can try your luck with the reason of who has flushed and get some 
statement SQL responsible for that. it may help to tract the type of operation 
that kills your shared pool. Remember this table that is emptied every time you 
query it?
 
 set linesize 124 pagesize 66 head on
 col ksmlrcom format A20 head "Namespace|affected"
 col ksmlrsiz format 99999 head "Request|Size"
 col ksmlrnum format 99999 head "Num Object|Flushed out"
 col ksmlrhon format A27  head "What is loaded"
 col ksmlrohv format 9999999999999 head "Hash_value"
 col username format a15 head "Username"
 col sid format 9999 head "Sid"
 
 spool KSMRLU.txt
 select
     ksmlrcom, ksmlrsiz, ksmlrnum, ksmlrhon, ksmlrohv,
     sid,username, a.addr
 from x$ksmlru a,v$session b where a.addr=b.saddr (+)
 /
 
 Note that contrary to a note to mettalink I could never join saddr with the 
addr. they does not seems to follow the same pattern of raw. But the hash value 
is correct and help detect the SQL. Interrsting is the number of objects that 
were flush out.
 
 
 B. Polarski
 http://www.smenu.org