Solved: ORA-04031: unable to allocate 664 bytes of shared ...

Former Member · ‎10-03-2012

Hi All,

We had recently encountered ORA-4031 error in our Production environment. At this time whole memory in shared SQL area of oracle shared pool had exhausted.

SAP Application instances including the CI hanged, even we couldn't connect to database through SQLPLUS at this time. We had to forecully bounce all the

Instances including database.

ORA-04031: unable to allocate 664 bytes of shared memory ("shared pool","SELECT "MANDT" , "OBJNR" , ...","sql area","ctxdef : kkslod")

ORA-04031: unable to allocate 664 bytes of shared memory ("shared pool","SELECT "MANDT" , "ENGMARKID...","sql area","ctxdef : kkslod")

Upon further analyzing the trace files noticed that the issue was due to the complex SELECT queries had run with huge list of input parameters to IN operator
and with OR concatenation given below

*** 2012-09-18 10:31:47.412
*** SESSION ID:(125.57876) 2012-09-18 10:31:47.391
=================================
Begin 4031 Diagnostic Information
=================================
The following information assists Oracle in diagnosing
causes of ORA-4031 errors. This trace may be disabled
by setting the init.ora parameter _4031_dump_bitvec = 0
======================================
Allocation Request Summary Information
======================================
Current information setting: 00654fff
Dump Interval=300 seconds SGA Heap Dump Interval=3600 seconds
Last Dump Time=09/18/2012 10:31:44
Allocation request for: logdef : apaclg
Heap: c00000020bdc8a58, size: 40
******************************************************
HEAP DUMP heap name="sga heap" desc=c000000218076030
extent sz=0xfe0 alt=200 het=32767 rec=9 flg=-126 opc=0
parent=0000000000000000 owner=0000000000000000 nex=0000000000000000 xsz=0x0
******************************************************
HEAP DUMP heap name="sql area" desc=c00000020bdc8a58
extent sz=0x1040 alt=32767 het=32 rec=0 flg=2 opc=2
parent=c000000218076030 owner=c00000020bdc8938 nex=0000000000000000 xsz=0x1
Subheap has 537424184 bytes of memory allocated
====================
Process State Object
====================
----------------------------------------
SO: c0000001bf181a60, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=17, calls cur/top: c0000001bf1ebc38/c0000001bf1ebc38, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 199 0 4
              last post received-location: kslpsr
              last process to post me: c0000001bf17d480 1 6
              last post sent: 0 0 16
              last post sent-location: ksasnd
              last process posted by me: c0000001bf17d480 1 6
(latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: c0000001be18be10
    O/S info: user: , term: UNKNOWN, ospid: 22414
    OSD pid info: Unix process pid: 22414, image: (TNS V1-V3)
=========================
User Session State Object
=========================
----------------------------------------
SO: c0000001bf1d7d30, type: 4, owner: c0000001bf181a60, flag: INIT/-/-/0x00
(session) trans: c0000001cf87d7e8, creator: c0000001bf181a60, flag: (8100041) USR/- BSY/-/-/-/-/-
            DID: 0001-0011-00152926, short-term DID: 0000-0000-00000000
            txn branch: 0000000000000000
            oct: 0, prv: 0, sql: c0000002110c0610, psql: 0000000000000000, user: 8/SAPR3
O/S info: user: sidadm, term: , ospid: 22410, machine:

program:    ? (TNS V1-V3)
application name:    ? (TNS V1-V3), hash value=0
last wait for 'latch free' blocking sess=0x0 seq=1737 wait_time=536
          address=c0000002180e0d20, number=9c, tries=0
temporary object counter: 0
=========================
Current Parent KGL Object
=========================
LIBRARY OBJECT HANDLE: handle=c0000002110c0610
name=
SELECT "MANDT" , "TPLNR" , "MLANG" , "TPLKZ" , "FLTYP" , "TPLMA" , "ERDAT" , "ERNAM" , "AEDAT" , "AENAM" , "DATAB" , "BEGRU" , "TRPNR" , "PSTAE" , "STNAM" ,

"LVORM" , "SUBMT" , "SUBMTI" , "MAPAR" , "MAPARI" , "IEQUI" , "IEQUII" , "EINZL" , "EINZLI" , "IWERK" , "IWERKI" , "INGRP" , "INGRPI" , "PM_OBJTY" , "LGWID"

, "LGWIDI" , "RBNR" , "RBNR_I" , "ILOAN" , "OBJNR" , "POSNR" FROM "IFLOT" WHERE ( "MANDT" IN ( :A0 , :A1 , :A2 , :A3 , :A4 , :A5 , :A6 , :A7 ,

:A8 , :A9 , :A10 , :A11 , :A12 , :A1
hash=b2ef58b9 timestamp=09-18-2012 10:29:16
namespace=CRSR flags=RON/KGHP/TIM/PN0/[10010000]
kkkk-dddd-llll=0000-0001-0001 lock=N pin=S latch#=4
lwt=c0000002110c0640[c0000002110c0640,c0000002110c0640] ltm=c0000002110c0650[c0000002110c0650,c0000002110c0650]
pwt=c0000002110c0670[c0000002110c0670,c0000002110c0670] ptm=c0000002110c0700[c0000002110c0700,c0000002110c0700]
ref=c0000002110c0620[c0000002110c0620, c0000002110c0620] lnd=c0000002110c0718[c0000002110c0718,c0000002110c0718]
    LIBRARY OBJECT: object=c0000001f5de7178
    type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
    CHILDREN: size=16
    child#    table reference   handle
    ------ -------- --------- --------
         0 c0000001f5de73d8 c0000002110bf270 c0000001f7da1700
==================
Current KGL Object
==================
LIBRARY OBJECT HANDLE: handle=c0000001f7da1700
namespace=CRSR flags=RON/KGHP/PN0/[10010000]
kkkk-dddd-llll=0000-0041-0041 lock=N pin=X latch#=4
lwt=c0000001f7da1730[c0000001f7da1730,c0000001f7da1730] ltm=c0000001f7da1740[c0000001f7da1740,c0000001f7da1740]
pwt=c0000001f7da1760[c0000001f7da1760,c0000001f7da1760] ptm=c0000001f7da17f0[c0000001f7da17f0,c0000001f7da17f0]
ref=c0000001f7da1710[c0000002110bf270, c0000002110bf270] lnd=c0000001f7da1808[c0000001f7da1808,c0000001f7da1808]
    LIBRARY OBJECT: object=c00000020bdc8938
    type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
===========================
Current Instatiation Object
===========================
-------------------------------------
INSTANTIATION OBJECT: object=80000001002867d0
type="cursor"[2] lock=c000000217f30308 handle=c0000002110c0610 body=0000000000000000 level=0
flags=FST[60] executions=0
cursor name:
SELECT "MANDT" , "TPLNR" , "MLANG" , "TPLKZ" , "FLTYP" , "TPLMA" , "ERDAT" , "ERNAM" , "AEDAT" , "AENAM" , "DATAB" , "BEGRU" , "TRPNR" , "PSTAE" , "STNAM" ,

"LVORM" , "SUBMT" , "SUBMTI" , "MAPAR" , "MAPARI" , "IEQUI" , "IEQUII" , "EINZL" , "EINZLI" , "IWERK" , "IWERKI" , "INGRP" , "INGRPI" , "PM_OBJTY" , "LGWID"

, "LGWIDI" , "RBNR" , "RBNR_I" , "ILOAN" , "OBJNR" , "POSNR" FROM "IFLOT" WHERE ( "MANDT" IN ( :A0 , :A1 , :A2 , :A3 , :A4 , :A5 , :A6 , :A7 ,

:A8 , :A9 , :A10 , :A11 , :A12 , :A13 , :A14 , :A15 , :A16 , :A17 , :A18 , :A19 , :A20 , :A21 , :A22 , :A23 , :A24 , :A25 , :A26 , :A27 ,

:A28 , :A29 , :A30 , :A31 , :A32 , :A33 , :A34 , :A35 , :A36 , :A37 , :A38 , :A39 , :A40 , :A41 , :A42 , :A43 , :A44 , :A45 , :A46 , :A47

, :A48 , :A49 , :A50 , :A51 , :A52 , :A53 , :A54 , :A55 , :A56 , :A57 , :A58 , :A59 , :A60 , :A61 , :A62 , :A63 , :A64 , :A65 , :A66 ,

:A67 , :A68 , :A69 , :A70 , :A71 , :A72 , :A73 , :A74 , :A75 , :A76 , :A77 , :A78 , :A79 , :A80 , :A81 , :A82 , :A83 , :A84 , :A85 , :A86

, :A87 , :A88 , :A89 , :A90 , :A91 , :A92 , :A93 , :A94 , :A95 , :A96 , :A97 , :A98 , :A99 , :A100 , :A101 , :A102 , :A103 , :A104 , :A105

, :A106 , :A107 , :A108 , :A109 , :A110 , :A111 , :A112 , :A113 , :A114 , :A115 , :A116 , :A117 , :A118 , :A119 , :A120 , :A121 , :A122 ,

:A123 , :A124 , :A125 , :A126 , :A127 , :A128 , :A129 , :A130 , :A131 , :A132 , :A133 , :A134 , :A135 , :A136 , :A137 , :A138 , :A139 ,

:A140 , :A141 , :A142 , :A143 , :A144 , :A145 , :A146 , :A147 , :A148 , :A149 , :A150 , :A151 , :A152 , :A153 , :A154 , :A155 , :A156 ,

:A157 , :A158 , :A159 , :A160 , :A161 , :A162 , :A163 , :A164 , :A165 , :A166 , :A167 , :A168 , :A169 , :A170 , :A171 , :A172 , :A173 ,

:A174 , :A175 , :A176 , :A177 , :A178 , :A179 , :A180 , :A181 , :A182 , :A183 , :A184 , :A185 , :A186 , :A187 , :A188 , :A189 , :A190 ,

:A191 , :A192 , :A193 , :A194 , :A195 , :A196 , :A197 , :A198 , :A199 , :A200 , :A201 , :A202 , :A203 , :A204 , :A205 , :A206 , :A207 ,

:A208 , :A209 , :A210 , :A211 , :A212 , :A213 , :A214 , :A215 , :A216 , :A217 , :A218 , :A219 , :A220 , :A221 , :A222 , :A223 , :A224 ,

:A225 , :A226 , :A227 , :A228 , :A229 , :A230 , :A231 , :A232 , :A233 , :A234 , :A235 , :A236 , :A237 , :A238 , :A239 , :A240 , :A241 ,

:A242 , :A243 , :A244 , :A245 , :A246 , :A247 , :A248 , :A249 , :A250 , :A251 , :A252 , :A253 ) OR "MANDT" IN ( :A254 , :A255 , :A256 , :A257

, :A258 , :A259 , :A260 , :A261 , :A262 , :A263 , :A264 , :A265 , :A266 , :A267 , :A268 , :A269 , :A270 , :A271 , :A272 , :A273 , :A274 ,

:A275 , :A276 , :A277 , :A278 , :A279 , :A280 , :A281 , :A282 , :A283 , :A284 , :A285 , :A286 , :A287 , :A288 , :A289 , :A290 , :A291 ,

:A292 , :A293 , :A294 , :A295 , :A296 , :A297 , :A298 , :A299 , :A300 , :A301 , :A302 , :A303 , :A304 , :A305 , :A306 , :A307 , :A308 ,

:A309 , :A310 , :A311 , :A312 , :A313 , :A314 , :A315 , :A316 , :A317 , :A318 , :A319 , :A320 , :A321 , :A322 , :A323 , :A324 , :A325 ,

:A326 , :A327 , :A328 , :A329 , :A330 , :A331 , :A332 , :A333 , :A334 , :A335 , :A336 , :A337 , :A338 , :A339 , :A340 , :A341 , :A342 ,

:A343 , :A344 , :A345 , :A346 , :A347 , :A348 , :A349 , :A350 , :A351 , :A352 , :A353 , :A354 , :A355 , :A356 , :A357 , :A358 , :A359 ,

:A360 , :A361 , :A362 , :A363 , :A364 , :A365 , :A366 , :A367 , :A368 , :A369 , :A370 , :A371 , :A372 , :A373 , :A374 , :A375 , :A376 ,

:A377 , :A378 , :A379 , :A380 , :A381 , :A382 , :A383 , :A384 , :A385 , :A386 , :A387 , :A388 , :A389 , :A390 , :A391 , :A392 , :A393 ,

:A394 , :A395 , :A396 , :A397 , :A398 , :A399 , :A400 , :A401 , :A402 , :A403 , :A404 , :A405 , :A406 , :A407 , :A408 , :A409 , :A410 ,

:A411 , :A412 , :A413 , :A414 , :A415 , :A416 , :A417 , :A418 , :A419 , :A420 , :A421 , :A422 , :A423 , :A424 , :A425 , :A426 , :A427 ,

:A428 , :A429 , :A430 , :A431 , :A432 , :A433 , :A434 , :A435 , :A436 , :A437 , :A438 , :A439 , :A440 , :A441 , :A442 , :A443 , :A444 ,

:A445 , :A446 , :A447 , :A448 , :A449 , :A450 , :A451 , :A452 , :A453 , :A454 , :A455 , :A456 , :A457 , :A458 , :A459 , :A460 , :A461 ,

:A462 , :A463 , :A464 , :A465 , :A466 , :A467 , :A468 , :A469 , :A470 , :A471 ) ) AND "TPLNR" IN ( :A472 , :A473 , :A474 , :A475 , :A476 ,

:A477 , :A478 , :A479 , :A480 , :A481 , :A482 , :A483 , :A484 , :A485 , :A486 , :A487 , :A488 , :A489 , :A490 , :A491 , :A492 , :A493 ,

:A494 , :A495 , :A496 , :A497 , :A498 , :A499 , :A500 , :A501 , :A502 , :A503 , :A504 , :A505 , :A506 , :A507 , :A508 , :A509 , :A510 ,

:A511 , :A512 , :A513 , :A514 , :A515 , :A516 , :A517 , :A518 , :A519 , :A520 , :A521 , :A522 , :A523 , :A524 , :A525 , :A526 , :A527 ,

:A528 , :A529 , :A530 , :A531 , :A532 , :A533 , :A534 , :A535 , :A536 , :A537 , :A538 , :A539 , :A540 , :A541 , :A542 , :A543 , :A544 ,

:A545 , :A546 , :A547 , :A548 , :A549 , :A550 , :A551 , :A552 , :A553 , :A554 , :A555 , :A556 ) AND "FLTYP" = :A557 AND "TPLKZ" = :A558
child pin: c0000001f10ed688, child lock: c0000001f10ef078, parent lock: c000000217f30308
xscflg: 100423, parent handle: c0000002110c0610, xscfl2: 11140801

Along with the above query some more similar queries with IN operator had run at the same time.

Below is our Production environment information.

SAP: SAP R/3 3.0F
Oracle: 9.2.0.8
OS: HP-UX 11.23
CPUs: 8

SGA is 7.36 GB
Size of DB buffers: 5.92 GB
Current size of shared pool - 1144 MB

CI server has 32GB of main memory of which only 44% is currently used. As we have more memory available we are planing to increase shared pool size by 500

MB, with this oracle shared pool size will be 1644MB.

We are also planning to implement the two solutions suggested in SAP Note 133351.In Note 690241 has explanation on how to comoute the shared pool size and in

the example it is given as 1100MB fowith 8 CPUs and upto 20 GB DB buffer cache. I believe this is not the max size of shared pool.

Appreciate your time and suggestions on this.

Thanks,

Vasu.

stefan_koehler · ‎10-03-2012

Hi Vasu,

well where should i start. This issue is pretty tough to analyze in detail, but i would like to give you some general and detailed hints.

Such large IN-LISTs are not "common". It seems like your DBSL is configured to use such large IN-LISTs (depending on the code). I have described an impact and one side effect in my blog (http://scn.sap.com/community/oracle/blog/2012/08/03/oracle-db-optimizer-part-i--understanding-in-lis...)
Increasing the shared pool can solve your issue, but it does not need to necessarily. You need to know some details about the internal handling of the shared pool (and its sub pools) and possible fragmentation. Your memory request was also not large enough for the "reserved shared pool". Metalinknote #396940.1 is a very good point to start.
Your instance was not hanging, because of that ORA-0431. It was "hanging" due to a latching issue. You can use the prelim option (with an oracle client => 10g .. there are also other ways, but this is the easiest) and create a hanganalyze dump. The blog of Tanel Poder is a pretty good point to start (http://blog.tanelpoder.com/2012/05/08/oradebug-hanganalyze-with-a-prelim-connection-and-error-can-no...)

.. well there is so much more to say, but try to increase your shared pool first (it is not that "big") and take a look at it in the future.

Regards

Stefan

Former Member · ‎10-04-2012

Now i have seen really everything, how many clients do you have in that system? 253?

>> WHERE MANDT" IN ( :A0 , :A1 , :A2 , :A3... :A253

Fix your application, do not try to make nonsense run by increasing the shared pool.

No offense and good luck, Michael

ORA-04031: unable to allocate 664 bytes of shared memory ("shared pool","SELECT "MANDT" , "OBJNR" , ...","sql area","ctxdef : kkslod")

Accepted Solutions (1)

Accepted Solutions (1)

Answers (1)

Answers (1)

Análise Combinatória de dados de bancos diferentes

Re: generating qrcode using image url in crystal r...

SOAMANAGER - Field Cardinality - Min Occurence - W...

Re: SWPSTEPLOG table partitioning

SWPSTEPLOG table partitioning