My customer has an application which using a lots of "temporary" table. They create so many tables but only operate once.And they are using automatic SGA management,all this cause large memory usage in shared pool, especially the "obj stat memo".
Recently they encountered the error 4031 , there are still 1G space available in shared pool, so i believe the root cause is too many obj stat fragment .
Verified the issue in the log file as noted below:
LOG FILE
-----------------------
Filename =gpnms4_j000_22234.trc.log
See the following error:
*** 2010-05-22 21:47:37.388
ORA-12012: error on auto execute of job 2
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select SYSDATE+(30 / (24*60)...","sga heap(4,0)","kglsim heap")
*** 2010-05-22 21:47:37.410
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select sysdate+(64)/(24*60) ...","sga heap(6,0)","kglsim heap")
ORA-12012: error on auto execute of job 2
ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select SYSDATE+(30 / (24*60)...","sga heap(4,0)","kglsim heap")
4031 specified trace as noted below:
TRACE FILE gpnms4_j000_22234.trc
------------ *** SERVICE NAME:(SYS$USERS) 2010-05-22 21:47:37.240 *** SESSION ID:(121.20137) 2010-05-22 21:47:37.240 *** 2010-05-22 21:47:37.240 ================================= 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 _4031_dump_bitvec = 0 ===================================== Allocation Request Summary Informaton ===================================== Current information setting: 04014fff SGA Heap Dump Interval=3600 seconds Dump Interval=300 seconds Last Dump Time=05/22/2010 21:47:37 Dump Count=1 Allocation request for: kglsim heap Heap: 380048e48, size: 4064 ****************************************************** HEAP DUMP heap name="sga heap(4,0)" desc=380048e48 extent sz=0xfe0 alt=216 het=32767 rec=9 flg=-125 opc=2 parent=0 owner=0 nex=0 xsz=0x1000000 latch set 4 of 6 durations enabled for this heap reserved granules for root 0 (granule size 16777216) ==================== Process State Object ==================== ---------------------------------------- SO: 5761a3750, type: 2, owner: 0, flag: INIT/-/-/0x00 (process) Oracle pid=37, calls cur/top: 56e79bdc8/572cd9160, flag: (0) - int error: 0, call error: 0, sess error: 0, txn error 0 (post info) last post received: 1359 0 4 last post received-location: kslpsr last process to post me: 57f156848 1 6 last post sent: 0 0 24 last post sent-location: ksasnd last process posted by me: 57f156848 1 6 (latch info) wait_event=0 bits=60 holding (efd=19) 5731bf3a0 Child shared pool sim alloc level=6 child#=12 Location from where latch is held: kglsim_chk_heaplist: alloc: Context saved from call: 0 state=busy, wlstate=free holding (efd=19) 5711d3de0 Child library cache level=5 child#=15 Location from where latch is held: kglobpn: child:: latch Context saved from call: 22 state=busy, wlstate=free Process Group: DEFAULT, pseudo proc: 57416ad08 O/S info: user: oracle, term: UNKNOWN, ospid: 22234 OSD pid info: Unix process pid: 22234, image: oracle@SHUDBa1 (J000) SO: 5761d48b0, type: 4, owner: 5761a3750, flag: INIT/-/-/0x00 (session) sid: 121 trans: 0, creator: 5761a3750, flag: (40000041) USR/- BSY/-/-/-/-/- DID: 0001-0025-0060BC7E, short-term DID: 0000-0000-00000000 txn branch: 0 oct: 0, prv: 0, sql: 0, psql: 0, user: 93/WUBIAO O/S info: user: oracle, term: UNKNOWN, ospid: 22234, machine: SHUDBa1 program: oracle@SHUDBa1 (J000) last wait for 'SGA: allocation forcing component growth' blocking sess=0x0 seq=2 wait_time=10349 seconds since wait started=0 =0, =0, =0 Dumping Session Wait History for 'SGA: allocation forcing component growth' count=1 wait_time=10349 =0, =0, =0 for 'db file sequential read' count=1 wait_time=89 file#=7, block#=6f767, blocks=1 temporary object counter: 0 ----- Call Stack Trace ----- ksm_4031_dump <- ksmasg <- kghnospc <- kghalo <- kglsim_chk_heaplist <- kglsim_upd_newhp <- 3076 <- kglUpdateSimulator <- kglobpn <- kglpim <- qcdlgtd <- qcsfplob <- qcsprfro <- qcsprfro_tree <- qcsprfro_tree <- qcspafq <- qcspqbDescendents <- qcspqb <- kkmdrv <- opiSem <- opiprs <- kksParseChildCursor <- rpiswu2 <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- rpidrv <- rpisplu <- rpispl <- kkjfnd <- kkjex1e <- kkjsexe <- kkjrdp <- opirip <- opidrv <- sou2o <- opimai_real <- main ============================== Memory Utilization of Subpool 1 ================================ Allocation Name Size _________________________ __________ "free memory " 390197664 "obj stat memo " 403349328 "KGH: NO ACCESS " 16695456 ============================== Memory Utilization of Subpool 2 ================================ Allocation Name Size _________________________ __________ "free memory " 319286368 "obj stat memo " 380580336 "KGH: NO ACCESS " 16728160 ============================== Memory Utilization of Subpool 3 ================================ Allocation Name Size _________________________ __________ "free memory " 325262592 "obj stat memo " 351782568 "KGH: NO ACCESS " 16728160 ============================== Memory Utilization of Subpool 4 ================================ Allocation Name Size _________________________ __________ "free memory " 556035112 "obj stat memo " 551594016 "KGH: NO ACCESS " 32394336 ============================== Memory Utilization of Subpool 5 ================================ Allocation Name Size _________________________ __________ "free memory " 577033336 "obj stat memo " 432672408 "KGH: NO ACCESS " 16736320 ============================== Memory Utilization of Subpool 6 ================================ Allocation Name Size _________________________ __________ "free memory " 333050648 "obj stat memo " 406731024 "KGH: NO ACCESS " 16719968 LIBRARY CACHE STATISTICS: namespace gets hit ratio pins hit ratio reloads invalids -------------- --------- --------- --------- --------- ---------- ---------- CRSR 2214572558 0.355 3977649054 0.403 81251674 34891116 TABL 69900473 0.844 438948912 0.885 11952398 0 BODY 1126452 0.970 86610514 0.999 50956 0 TRGR 104735463 0.994 156443548 0.994 369130 0 INDX 23588405 0.667 47086339 0.801 1478850 0 CLST 2746262 0.997 4303977 0.994 15628 0
At first, i think this "obj stat memory" subheap can be cleaned up by flush shared pool, but i 'm wrong ; The component is permanent in shared pool.
SQL> select * from v$sgastat where name='obj stat memo'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool obj stat memo 1454448 SQL> alter system flush shared_pool; System altered. SQL> select * from v$sgastat where name='obj stat memo'; POOL NAME BYTES ------------ -------------------------- ---------- shared pool obj stat memo 1454448
After that I read the metalink note "Bug 5573238 Shared pool memory use / ORA-4031 due to "obj stat memo" in one subpool", oracle support advise to workaround this problem by setting STATISTICS_LEVEL=BASIC or "_object_statistics"=false. But STATISTICS_LEVEL=BASIC will disable many automatic features like ASMM, Automatic statistics gathering;If you set "_object_statistics"=false, then as _object_statistics control the population of segment stats in the v$segstat andv$segment_statistics views. So setting this to false will result in no data populating these v$views.Both the parameters will require a database restart.
And i have opened a new tar , the oracle support advise as below:
Trace file shows indeed a high size for the 'obj stat memo' component but also some imbalance between the subpools. Bug 5573238 is not an issue here as fixed as from 10.2.0.4 but the workaround can be used here as well. So, setting "statistics_level"=basic or "_object_statistics"=false should also workaround this issue. The imbalance of the subpools on the other hand might be caused by bug 6271590 which is fixed as from PSU patch 10.2.0.4.2. I would suggest to apply the latest PSU patch available right now and that is 10.2.0.4.4. For the 'obj stat memo' issue, you have to choose between further investigating the issue or using the workaround by setting "statistics_level"=basic or "_object_statistics"=false. If you want to further investigate the issue, we will need a more detailed heapdump of the ORA-4031. To Do: 1. Apply PSU patch 10.2.0.4.4 (patch 9352164) on top of patchset 10.2.0.4 2. Regarding the 'obj stat memo' issue: a) Workaround the issue by setting "statistics_level"=basic or "_object_statistics"=false + Bounce the instance -OR- b) Further investigate the issue: Set following parameters in the init.ora file (SPFILE/PFILE): SQL> alter system set max_dump_file_size = unlimited scope=spfile; SQL> alter system set events '4031 trace name heapdump level 536870914' scope=spfile; Bounce the instance Once the ORA-4031 reoccurs, provide alert+trace fileSo imbalance between subpool can be fixed , and will reduce the likelihood of 4031 occurrence. But we can never flush huge memory used by "obj stat memory",i think it's awful .