ORA-00600[kkqjpdpvpd No join pred found.]错误
Mar202019
今天，用友NC的一个数据库突然触发了ORA-00600错误，数据库版本11.2.0.4.0，具体告警信息如下：
Wed Mar 20 10:12:46 2019 Errors in file /u01/app/oracle/diag/rdbms/ncdb3/ncdb3/trace/ncdb3_ora_433459.trc (incident=84497): ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/ncdb3/ncdb3/incident/incdir_84497/ncdb3_ora_433459_i84497.trc Wed Mar 20 10:12:48 2019 Dumping diagnostic data in directory=[cdmp_20190320101248], requested by (instance=1, osid=433459), summary=[incident=84497]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Mar 20 10:12:48 2019 Sweep [inc][84497]: completed Sweep [inc2][84497]: completed Errors in file /u01/app/oracle/diag/rdbms/ncdb3/ncdb3/trace/ncdb3_ora_433459.trc (incident=84498): ORA-00600: internal error code, arguments: [kkqjpdpvpd: No join pred found.], [], [], [], [], [], [], [], [], [], [], [] Incident details in: /u01/app/oracle/diag/rdbms/ncdb3/ncdb3/incident/incdir_84498/ncdb3_ora_433459_i84498.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Mar 20 10:12:54 2019 Sweep [inc][84498]: completed Sweep [inc2][84498]: completed Dumping diagnostic data in directory=[cdmp_20190320101254], requested by (instance=1, osid=433459), summary=[incident=84498].
通过600错误的参数，猜测可能和表连接有关，通过查看trace文件，发现是下面的SQL触发的。
*** 2019-03-20 10:12:46.437
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=gv7tagc3q502n) -----
SELECT o.orderno,
o.orderseq,
op.ordertype,
o.saleorg,
o.cooorg,
o.ivtype,
o.isroll,
o.isreturn,
o.num,
o.invoiceamount,
op.payment,
op.amount
FROM busi_order_2019_03 o
join busi_orderpay op
on o.orderno = op.orderid
and o.dr = 0
and op.dr = 0
and o.orderno in
(SELECT a.orderno
FROM (SELECT o.orderno, sum(o.invoiceamount) amount
FROM busi_order_2019_03 o
WHERE o.dr = 0
and o.ivtype in
('9ef4832bfa7c42fdb709af44f53e8df0',
'e41166ae18dd46cbb1f4a7236c28a497')
and substr(o.saledate, 0, 10) >= '2019-03-01'
and substr(o.saledate, 0, 10) <= '2019-03-01'
group by o.orderno) a
join (SELECT op.orderid orderno, sum(op.amount) amount
FROM busi_orderpay op
WHERE op.dr = 0
group by op.orderid) b
on a.orderno = b.orderno
WHERE a.amount - b.amount <> 0
union all
SELECT a.orderno
FROM (SELECT o.orderno, o.orderseq, sum(o.invoiceamount) amount
FROM busi_order_2019_03 o
WHERE o.dr = 0
and o.ivtype = '301f75fe314d403393ffa49deebb6d8d'
and substr(o.saledate, 0, 10) >= '2019-03-01'
and substr(o.saledate, 0, 10) <= '2019-03-01'
group by o.orderno, o.orderseq) a
join (SELECT op.orderid orderno, op.orderseq, sum(op.amount) amount
FROM busi_orderpay op
WHERE op.dr = 0
group by op.orderid, op.orderseq) b
on a.orderno = b.orderno
and a.orderseq = b.orderseq
WHERE a.amount - b.amount <> 0)
网上资料说这是个BUG，需要打补丁或者通过在执行SQL之前设置数据库的隐含参数解决。
alter session set "_optimizer_push_pred_cost_based" = FALSE;
也就是在程序里，这个SQL的前面加上上面的SQL即可解决。
alter session set "_optimizer_push_pred_cost_based" = FALSE;
SELECT o.orderno,
o.orderseq,
op.ordertype,
o.saleorg,
o.cooorg,
o.ivtype,
o.isroll,
o.isreturn,
o.num,
o.invoiceamount,
op.payment,
op.amount
FROM busi_order_2019_03 o
join busi_orderpay op
on o.orderno = op.orderid
and o.dr = 0
and op.dr = 0
and o.orderno in
(SELECT a.orderno
FROM (SELECT o.orderno, sum(o.invoiceamount) amount
FROM busi_order_2019_03 o
WHERE o.dr = 0
and o.ivtype in
('9ef4832bfa7c42fdb709af44f53e8df0',
'e41166ae18dd46cbb1f4a7236c28a497')
and substr(o.saledate, 0, 10) >= '2019-03-01'
and substr(o.saledate, 0, 10) <= '2019-03-01'
group by o.orderno) a
join (SELECT op.orderid orderno, sum(op.amount) amount
FROM busi_orderpay op
WHERE op.dr = 0
group by op.orderid) b
on a.orderno = b.orderno
WHERE a.amount - b.amount <> 0
union all
SELECT a.orderno
FROM (SELECT o.orderno, o.orderseq, sum(o.invoiceamount) amount
FROM busi_order_2019_03 o
WHERE o.dr = 0
and o.ivtype = '301f75fe314d403393ffa49deebb6d8d'
and substr(o.saledate, 0, 10) >= '2019-03-01'
and substr(o.saledate, 0, 10) <= '2019-03-01'
group by o.orderno, o.orderseq) a
join (SELECT op.orderid orderno, op.orderseq, sum(op.amount) amount
FROM busi_orderpay op
WHERE op.dr = 0
group by op.orderid, op.orderseq) b
on a.orderno = b.orderno
and a.orderseq = b.orderseq
WHERE a.amount - b.amount <> 0)
将方法告诉开发人员后，通过修改程序代码，这个错误已经解决，但是下午又发现另一条SQL也触发了这个BUG。
select count(1)
from (SELECT o.ivtype,
o.saleorg,
o.deliveryorg,
op.payment,
CASE o.isreturn
WHEN 0 THEN
op.amount
ELSE
op.amount * -1
END amount
FROM busi_order_2019_03 o, busi_orderpay op
WHERE o.orderno = op.orderid
AND o.orderno IN
(SELECT a.orderno
FROM (SELECT o.orderno, sum(o.invoiceamount) amount
FROM busi_order_2019_03 o
WHERE o.ivtype IN
('9ef4832bfa7c42fdb709af44f53e8df0',
'e41166ae18dd46cbb1f4a7236c28a497')
AND o.dr = 0
AND substr(o.saledate, 0, 10) >= :1
GROUP BY o.orderno) a
JOIN (SELECT op.orderid orderno, sum(op.amount) amount
FROM busi_orderpay op
WHERE op.dr = 0
GROUP BY op.orderid) b
ON a.orderno = b.orderno
UNION ALL
SELECT a.orderno
FROM (SELECT o.orderno,
o.orderseq,
sum(o.invoiceamount) amount
FROM busi_order_2019_03 o
WHERE o.dr = 0
AND o.ivtype = '301f75fe314d403393ffa49deebb6d8d'
AND substr(o.saledate, 0, 10) >= :2
GROUP BY o.orderno, o.orderseq) a
JOIN (SELECT op.orderid orderno,
op.orderseq,
sum(op.amount) amount
FROM busi_orderpay op
WHERE op.dr = 0
GROUP BY op.orderid, op.orderseq) b
ON a.orderno = b.orderno
AND a.orderseq = b.orderseq))
JOIN bd_dict dict
ON dict.code = deliveryorg
AND dict.fk_dicttype = 'f39d55e9401246bba26d5f452afe53d8'
补丁目前打不了，只能通过上面的方式去解决了。很纳闷，用友这么大公司开发的产品，怎么这么容易触发600错误呢，前几天的600错误，数据库都干挂了。而且这个项目马上就要验收交接啦，真为交接的开发同事担忧啊。
ORA-00600[kkqjpdpvpd No join pred found.]错误：等您坐沙发呢！
发表评论
