RE: sub heap & ORA-4031

From: Bernard Polarski <bpolarsk_at_yahoo.com>
Date: Thu, 6 Jul 2006 06:56:20 -0700 (PDT)
Message-ID: <20060706135620.87068.qmail@web36501.mail.mud.yahoo.com>

Hello,

time to re-read the booklet of steve Adams, since you are using its APT scripts.

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.
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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 06 2006 - 08:56:20 CDT