ORA-04031 errors and size of 'sql area'

From: vsevolod afanassiev <vsevolod.afanassiev_at_gmail.com>
Date: Tue, 8 Jun 2010 18:49:34 -0700 (PDT)
Message-ID: <75d2e96b-8820-41bb-8ad7-3d799575ef3e_at_a39g2000prb.googlegroups.com>

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

Received on Tue Jun 08 2010 - 20:49:34 CDT