oracle px msg pool,ora

admin 管理员组

文章数量: 1086019

oracle px msg pool,ora

本帖最后由 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 不够大；

在线等待。。。。

本文标签： oracle px msg pool ORA