SO: 0xc000001842ba9750, type: 2, owner: 0x0000000000000000, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
?proc=0xc000001842ba9750, name=process, file=ksu.h LINE:12616 ID:, pg=0
(process) Oracle pid:664, ser:2, calls cur/top: 0xc0000015e1989c10/0xc000001711b743a8
? ? ? ? ? flags : (0x0) -
? ? ? ? ? flags2: (0x0),? flags3: (0x10)
? ? ? ? ? intr error: -2147473260, call error: 0, sess error: 0, txn error 0
? ? ? ? ? intr queue: 2147494036
? ksudlp FALSE at location: 0
? (post info) last post received: 2147494036 136 12
? ? ? ? ? ? ? last post received-location: ksu.h LINE:13927 ID:ksusig
? ? ? ? ? ? ? last process to post me: c000001832b0b620 242 0
? ? ? ? ? ? ? last post sent: 0 0 356
? ? ? ? ? ? ? last post sent-location: kxfp.h LINE:4561 ID:kxfprienq: slave
? ? ? ? ? ? ? last process posted by me: c000001832b0b620 242 0
? (latch info) wait_event=0 bits=50
? ? holding? ? (efd=10) c000001804617de8 Child parallel query alloc buffer level=6 child#=23
? ? ? ? Location from where latch is held: kxfpb.h LINE:496 ID:kxfpbalo: allocate a buffer
? ? ? ? Context saved from call: 13835058152010268280
? ? ? ? state=busy [holder orapid=664] wlstate=free [value=0]
? ? ? ? waiters [orapid (seconds since: put on list, posted, alive check)]:
? ? ? ? 746 (456, 1409025072, 2)
? ? ? ? 1068 (456, 1409025072, 3)
? ? ? ? 747 (456, 1409025072, 2)
? ? ? ? 857 (456, 1409025072, 1)
? ? ? ? 1067 (456, 1409025072, 3)
? ? ? ? 748 (456, 1409025072, 1)
? ? ? ? 983 (434, 1409025072, 4)
? ? ? ? 1070 (384, 1409025072, 3)
? ? ? ? 749 (384, 1409025072, 1)
? ? ? ? 1069 (384, 1409025072, 2)
? ? ? ? 912 (346, 1409025072, 4)
? ? ? ? 662 (323, 1409025072, 3)
? ? ? ? 871 (308, 1409025072, 4)
? ? ? ? 906 (270, 1409025072, 4)
? ? ? ? 735 (209, 1409025072, 4)
? ? ? ? 786 (157, 1409025072, 1)
? ? ? ? 972 (74, 1409025072, 1)
? ? ? ? waiter count=17
? ? holding? ? (efd=10) c00000168571bdd0 Child process queue reference level=4 child#=10989
? ? ? ? Location from where latch is held: kxfp.h LINE:4438 ID:kxfprialo2: process qref child init
? ? ? ? Context saved from call: 13835058150030153416
? ? ? ? state=busy [holder orapid=664] wlstate=free [value=0]
? Process Group: DEFAULT, pseudo proc: 0xc0000018330dea98
? O/S info: user: oracle, term: UNKNOWN, ospid: 28873
? OSD pid info: Unix process pid: 28873, image: oracle@xx01 (P485)
PSO child state object changes :
这个latch Child parallel query alloc buffer 也就是在进行Px msg pool 内存分配的时候需要获得的，当内存分配完毕之后latch会立刻释放，通常情况下，Oracle 对于Latch的申请和释放是非常之快的。很明显这里这个进程由于申请内存失败，导致latch也没有释放，还阻塞了一堆的会话。
由于朋友这里没有其他的信息，所以很难准确的定位到是不是碎片或者其他的问题。但是从trace 里面也不难发现一些信息。前面提到进程申请内存，势必就要看看下这个会话是什么操作了，不看不知道，一看吓一跳，如下：
MERGE INTO xx.xxxx_A A
USING (SELECT /*+ parallel(a,512) parallel(b,8)? */
? ? ? ? :B2 STATIS_MONTH,
? ? ? ? NVL(B.PROV_ID, '000') PROV_ID,
? ? ? ? COUNT(DISTINCT A.SERV_NUM) CNT
? ? ? ? FROM (SELECT DISTINCT SERV_NUM
? ? ? ? ? ? ? ? FROM xxx.TDW_XXXXX_MIGU_MEMBER_M A
? ? ? ? ? ? ? ? WHERE STATIS_MONTH = :B1
? ? ? ? ? ? ? ? ? AND MEMBER_LVL = '1'
? ? ? ? ? ? ? ? ? AND MEMBER_STAT IN ('1', '0')
? ? ? ? ? ? ? ? ? AND EXISTS (SELECT /*+ parallel(b,64)*/
? ? ? ? ? ? ? ? ? ? ? ? 1
? ? ? ? ? ? ? ? ? ? ? ? FROM xxx.TKR_DEV_XXXXX_CORE_M B
? ? ? ? ? ? ? ? ? ? ? ? WHERE A.SERV_NUM = B.SERV_NUM)) A,
? ? ? ? ? ? ? xxx.VDW_NUMBER_SEGMENT B
? ? ? ? WHERE SUBSTR(A.SERV_NUM, 1, 7) = B.SEGMENT(+)
? ? ? ? GROUP BY B.PROV_ID) B
ON (A.STATIS_MONTH = B.STATIS_MONTH AND A.PROV_ID = B.PROV_ID)
WHEN MATCHED THEN
? UPxxTE SET CORE_NRML = CNT
大家可以看到，这个sql居然开了512个并行，如果把几个表的并行都加起来，高达584个。问题应用这里还不止这一个SQL。
对于并行查询，我们知道，即使你的并行度设置的再高，也不一定能用这么多，因为这是受限制数据库参数的。不过，悲剧的是，他这里默认参数都是比较大的。cpu_count=114，默认的dop都高达228