Prev: OLM Functional Lead - St. Louis, MO
Next: Is there way to do Custom installation of 11gR2?
From: vsevolod afanassiev on 8 Jun 2010 21:49
We started getting ORA-04031 errors in a small 9.2.0.8. It happened
for no apparent reason. The errors produced trace files (see below),
while going through the trace I noticed following:
- Only one sub-pool - this makes analysis much easier as it is
possible to use V$SGASTAT/STATS$SGASTAT
- Very small 'sql area' at the time of the error. I expected size of
'sql area' to be at least 10 MB but trace file shows 168 KB (some
traces show even smaller 'sql area').
- Large 'miscellaneous' - 90 MB
- Plenty of memory in 'free memory'

I extracted size of various components of the shared pool from STATS
$SGASTAT as function of time (we keep 35 days of snapshots). It shows
that for a while size of
'sql area' varied between 30MB and 40 MB in slow 'sin(x)-like'
changed, then something happened and it started experiencing rapid
changes between 0 and 50 MB. This is roughly the time when we started
getting ORA-04031 errors. This matches data from another database:
ORA-04031 errors tend to occur when 'sql area' falls below 1 MB while
we still have 50MB+ in the 'free memory'.

Do you agree with this analysis?



===============================
Memory Utilization of Subpool 1
===============================
Allocation Name Size
_________________________ __________
"free memory " 81818824
"miscellaneous " 90229816
"XDB Schema Cac " 5088352
"KQR X PO " 5176
"PLS non-lib hp " 3672
"KGLS heap " 91920
"partitioning d " 0
"trigger inform " 0
"errors " 0
"session param values " 2041656
"KGSKI schedule " 18944
"PL/SQL MPCODE " 39136
"trigger source " 0
"pl/sql source " 0
"PL/SQL DIANA " 40312
"sim memory hea " 332568
"joxs heap init " 4240
"KQR L PO " 1058864
"temporary tabl " 0
"table definiti " 0
"fixed allocation callback" 2576
"KGK heap " 33368
"dictionary cache " 4274432
"trigger defini " 0
"joxlod: in phe " 0
"joxlod: in ehe " 267448
"subheap " 103552
"library cache " 14185456
"parameters " 0
"sql area " 168000
"KQR M PO " 1034776
"type object de " 0
"MTTR advisory " 136408
"PL/SQL PPCODE " 0
"partitioning i " 0
"KQR M SO " 16416
"PL/SQL SOURCE " 0
"KGSK scheduler " 330680
 | 
Pages: 1
Prev: OLM Functional Lead - St. Louis, MO
Next: Is there way to do Custom installation of 11gR2?