RE: sub heap & ORA-4031
- From: Bernard Polarski <bpolarsk@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 6 Jul 2006 06:56:20 -0700 (PDT)
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
Other related posts:
