本帖最后由 snowermine 于 2013-3-12 13:32 编辑
各位大侠，
遇到一问题，数据库oracle11.2.0.3在跑一些存储过程的时候会遇到错误：
alert.log如下：
Thu Mar 07 22:13:11 2013
Errors in file /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/trace/rwaprod1_p127_25297088.trc (incident=215331):
ORA-04031: 无法分配 512024 字节的共享内存 ("large pool","unknown object","large pool","PX msg pool")
Incident details in: /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/incident/incdir_215331/rwaprod1_p127_25297088_i215331.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Thu Mar 07 22:13:13 2013
Dumping diagnostic data in directory=[cdmp_20130307221313], requested by (instance=1, osid=25297088 (P127)), summary=[incident=215331].
Thu Mar 07 22:13:13 2013
Errors in file /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/trace/rwaprod1_p133_40501298.trc (incident=215387):
ORA-04031: 无法分配 512024 字节的共享内存 ("large pool","unknown object","large pool","PX msg pool")
Thu Mar 07 22:13:13 2013
Errors in file /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/trace/rwaprod1_p149_11337858.trc (incident=215515):
ORA-04031: 无法分配 512024 字节的共享内存 ("large pool","unknown object","large pool","PX msg pool")
Incident details in: /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/incident/incdir_215387/rwaprod1_p133_40501298_i215387.trc
Incident details in: /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/incident/incdir_215515/rwaprod1_p149_11337858_i215515.trc
Thu Mar 07 22:13:13 2013
trace内容如下：
Trace file /oracle/app/oracle/diag/rdbms/rwaprod/rwaprod1/trace/rwaprod1_p164_44695748.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/oracle/product/11.2.0.3/dbhome_1
System name: AIX
Node name: rwadb1
Release: 1
Version: 6
Machine: 00F70A334C00
Instance name: rwaprod1
Redo thread mounted by this instance: 1
Oracle process number: 244
Unix process pid: 44695748, image: oracle@rwadb1 (P164)
=================================
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=03/07/2013 22:13:15
Dump Count=1
Allocation request for: PX msg pool
Heap: 700000000148d60, size: 512024
******************************************************
HEAP DUMP heap name="large pool" desc=700000000148d60
extent sz=0xfe0 alt=248 het=32767 rec=9 flg=-126 opc=0
parent=0 owner=0 nex=0 xsz=0x4000000 heap=0
fl2=0x20, nex=0, dsxvers=1, dsxflg=0x0
dsx first ext=0x74000000
latch set 7 of 7
durations disabled for this heap
reserved granules for root 0 (granule size 67108864)
====================
Process State Object
====================
----------------------------------------
SO: 0x7000005fc9261b8, type: 2, owner: 0x0, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x7000005fc9261b8, name=process, file=ksu.h LINE:12616 ID:, pg=0
(process) Oracle pid:244, ser:43, calls cur/top: 0x70000059945c090/0x70000059945e918
flags : (0x0) -
flags2: (0x0), flags3: (0x10)
intr error: 0, call error: 0, sess error: 0, txn error 0
intr queue: empty
ksudlp FALSE at location: 0
(post info) last post received: 4288 0 2
last post received-location: ksl2.h LINE:2362 ID:kslpsr
last process to post me: 7000006008f5e78 1 6
last post sent: 0 0 26
last post sent-location: ksa2.h LINE:285 ID:ksasnd
last process posted by me: 7000006008f5e78 1 6
(latch info) wait_event=0 bits=50
holding (efd=10) 7000005ee533760 Child parallel query alloc buffer level=6 child#=2
Location from where latch is held: kxfpb.h LINE:496 ID:kxfpbalo: allocate a buffer
Context saved from call: 504403171552774448
state=busy [holder orapid=244] wlstate=free [value=0]
waiters [orapid (seconds since: put on list, posted, alive check)]:
245 (3, 1362665595, 0)
246 (3, 1362665595, 0)
247 (3, 1362665595, 0)
248 (3, 1362665595, 0)
249 (3, 1362665595, 0)
250 (3, 1362665595, 0)
251 (3, 1362665595, 0)
252 (3, 1362665595, 0)
253 (3, 1362665595, 0)
254 (3, 1362665595, 0)
255 (3, 1362665595, 0)
256 (3, 1362665595, 0)
257 (3, 1362665595, 0)
258 (3, 1362665595, 0)
259 (3, 1362665595, 0)
260 (3, 1362665595, 0)
261 (3, 1362665595, 0)
262 (3, 1362665595, 0)
263 (3, 1362665595, 0)
264 (3, 1362665595, 0)
265 (3, 1362665595, 0)
266 (3, 1362665595, 0)
267 (3, 1362665595, 0)
268 (3, 1362665595, 0)
269 (3, 1362665595, 0)
270 (3, 1362665595, 0)
271 (3, 1362665595, 0)
272 (3, 1362665595, 0)
273 (3, 1362665595, 0)
274 (3, 1362665595, 0)
275 (3, 1362665595, 0)
276 (3, 1362665595, 0)
277 (3, 1362665595, 0)
278 (3, 1362665595, 0)
279 (3, 1362665595, 0)
280 (3, 1362665595, 0)
281 (3, 1362665595, 0)
282 (3, 1362665595, 0)
283 (3, 1362665595, 0)
284 (3, 1362665595, 0)
285 (3, 1362665595, 0)
286 (3, 1362665595, 0)
287 (3, 1362665595, 0)
288 (3, 1362665595, 0)
289 (3, 1362665595, 0)
290 (3, 1362665595, 0)
291 (3, 1362665595, 0)
292 (3, 1362665595, 0)
293 (3, 1362665595, 0)
294 (3, 1362665595, 0)
295 (3, 1362665595, 0)
296 (3, 1362665595, 0)
297 (3, 1362665595, 0)
298 (3, 1362665595, 0)
299 (3, 1362665595, 0)
300 (3, 1362665595, 0)
301 (3, 1362665595, 0)
302 (3, 1362665595, 0)
303 (3, 1362665595, 0)
304 (3, 1362665595, 0)
305 (3, 1362665595, 0)
306 (3, 1362665595, 0)
307 (3, 1362665595, 0)
308 (3, 1362665595, 0)
309 (3, 1362665595, 0)
310 (3, 1362665595, 0)
311 (3, 1362665595, 0)
312 (3, 1362665595, 0)
313 (3, 1362665595, 0)
314 (3, 1362665595, 0)
315 (3, 1362665595, 0)
316 (3, 1362665595, 0)
317 (3, 1362665595, 0)
318 (3, 1362665595, 0)
319 (3, 1362665595, 0)
320 (3, 1362665595, 0)
321 (3, 1362665595, 0)
322 (3, 1362665595, 0)
323 (3, 1362665595, 0)
324 (3, 1362665595, 0)
325 (3, 1362665595, 0)
326 (3, 1362665595, 0)
327 (3, 1362665595, 0)
328 (3, 1362665595, 0)
329 (3, 1362665595, 0)
330 (3, 1362665595, 0)
331 (3, 1362665595, 0)
332 (3, 1362665595, 0)
333 (3, 1362665595, 0)
334 (3, 1362665595, 0)
335 (3, 1362665595, 0)
213 (1, 1362665595, 0)
214 (1, 1362665595, 0)
215 (1, 1362665595, 0)
216 (1, 1362665595, 0)
217 (1, 1362665595, 0)
218 (1, 1362665595, 0)
219 (1, 1362665595, 0)
220 (1, 1362665595, 0)
221 (1, 1362665595, 0)
224 (1, 1362665595, 0)
225 (1, 1362665595, 0)
223 (1, 1362665595, 0)
226 (1, 1362665595, 0)
227 (1, 1362665595, 0)
228 (1, 1362665595, 0)
232 (1, 1362665595, 0)
234 (1, 1362665595, 0)
231 (1, 1362665595, 0)
233 (1, 1362665595, 0)
235 (1, 1362665595, 0)
236 (1, 1362665595, 0)
237 (1, 1362665595, 0)
238 (1, 1362665595, 0)
239 (1, 1362665595, 0)
240 (1, 1362665595, 0)
241 (1, 1362665595, 0)
243 (1, 1362665595, 0)
242 (1, 1362665595, 0)
222 (1, 1362665595, 0)
229 (1, 1362665595, 0)
230 (1, 1362665595, 0)
waiter count=122
holding (efd=10) 700000317fbc888 Child process queue reference level=4 child#=17620
Location from where latch is held: kxfp.h LINE:4438 ID:kxfprialo2: process qref child init
Context saved from call: 504403184703517760
state=busy [holder orapid=244] wlstate=free [value=0]
Process Group: DEFAULT, pseudo proc: 0x700000630add978
O/S info: user: oracle, term: UNKNOWN, ospid: 44695748
OSD pid info: Unix process pid: 44695748, image: oracle@rwadb1 (P164)
PSO child state object changes :
Dump of memory from 0x070000060C8ECEA0 to 0x070000060C8ED0A8
70000060C8ECEA0 00000000 00000000 00000000 00000000 [................]
Repeat 31 times
70000060C8ED0A0 00000000 00000000 [........]
=========================
User Session State Object
=========================
----------------------------------------
SO: 0x70000060ce8c520, type: 4, owner: 0x7000005fc9261b8, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
proc=0x7000005fc9261b8, name=session, file=ksu.h LINE:12624 ID:, pg=0
(session) sid: 2552 ser: 9489 trans: 0x0, creator: 0x7000005fc9261b8
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
DID: , short-term DID:
txn branch: 0x0
oct: 6, prv: 0, sql: 0x7000003f2039620, psql: 0x0, user: 1833/BOC_RWA3
ksuxds FALSE at location: 0
service name: rwaprod
client details:
O/S info: user: SYSTEM, term:, ospid: 44695748
machine: WMS\P1RWAJS02 program: oracle@rwadb1 (P164)
application name: ******, hash value=1967618155
action name: Running task 58484(BIS), hash value=3468704563
Current Wait Stack:
Not in wait; last wait ended 0.003618 sec ago
Wait State:
fixed_waits=0 flags=0x21 boundary=0x0/-1
Session Wait History:
elapsed time of 0.003655 sec since last wait
0: waited for 'SGA: allocation forcing component growth'
=0x0, =0x0, =0x0
wait_id=18 seq_num=33 snap_id=7
wait times: snap=0.000000 sec, exc=0.354989 sec, total=0.355672 sec
wait times: max=infinite
wait counts: calls=6 os=6
occurred after 0.000000 sec of elapsed time
程序本身应该没什么问题，很简单：
UPDATE /*+ parallel (c, default) */ table_name C SET column_name1=..;
基本就这一句话；
sga，pga分别超过20g，share_pool_size=6g,large_pool_size 256M;
请大侠帮忙诊断下，我水平有限，看起来不像是share pool 不够大；
在线等待。。。。
扫一扫
860
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
