on 10-03-2012 5:46 PM
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.
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.
.. 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Vasu,
well the task is not that difficult. You can use ST04(old) or DBACOCKPIT (depending on your SAP release) and check the shared pool cache. If you have found that particular SQL in the cache you can jump directly to the ABAP coding.
However this is what i have mentioned in my blog about the DBSL. It seems like you are having several client duplicates in your WHERE clause.
If the SQL is currently running in your system you can also perform a errorstack dump to get the bind values and check for duplicates.
Regards
Stefan
...and you might have a bunch of short dumps (transaction ST22) containing the program and everything. Apart from that the workprocess traces (dev_wX files in the work directory) should also contain the ORA- error and program names, have a look in SM21 also for leads.
Is there no plan to do a oracle/SAP upgrade for that system?
Cheers Michael
On older versions of Oracle.. this could be managed with the *blocking_factor* SAP profile parameters.
We have also seen these (although largely eliminated since Oracle 10) when a table did not have statistics gathered on it. We've used the max_in_blocking_factor to control these massive in-lists.
Good luck. But once you get to Oracle 10 & 11.. these largely go away.
Rich
User | Count |
---|---|
95 | |
11 | |
10 | |
9 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.