问题描述
在 Oracle 模式下执行 SQL 报错:internal error code, arguments: -4016, Internal error。
报错 SQL 如下:
select *
from (select row_.*, rownum rownum_
from (select pc.pop_id,pm.name civic_name,pm.card_no civic_card_no,to_char(pm.birth_dt,'yyyy-MM-dd') civic_birth_dt,(select decode(a.is_only_child,
'Y','是','N','否','')
from pm_population_ext a where a.pop_id = pc.pop_id) pop_is_only,
pf.id sps_pop_id2,
pf.name sps_name,
pf.card_no sps_card_no2,
to_char(pf.birth_dt,'yyyy-MM-dd') sps_birth_dt2,
(select decode(a.is_only_child,
'Y','是','N','否','')
from pm_population_ext a where a.pop_id = pc.sps_pop_id) sps_is_only,
to_char(pc.civ_rp_dt, 'yyyy-MM-dd')
from pr_civic pc, pm_population pm, pm_population pf
where pc.pop_id = pm.id(+) and pc.sps_pop_id = pf.id(+) and pc.report_code='330106011018' and pc.sex ='2' and pc.civic_status in ('21','90') and pc.sts in ('Y','H') and to_char(pc.civ_rp_dt,'yyyy-MM')= '2021-10') row_
where rownum<= 15)
where rownum_ > 0;
返回信息如下:
internal error code, arguments: -4016, Internal error
具体错误日志如下:
observer.log.20221108163454:[2022-11-08 16:28:11.502651] WARN [SQL.DTL] process_base (ob_dtl_channel_loop.cpp:246) [126489][0][YB4264 40842E-0005E712C31EC2DF-0-0] [lt=15] [dc=0] message loop is interrupted(code={code:-4016, info:{msg:"tid:6334,from:" xxx.xxx.xxx.xxx:2882 ",TASK ABORT QC"}}, ret=-4016) observer.log.20221108163454:[2022-11-08 16:28:11.502673] WARN [SQL.ENG] fetch_rows (ob_px_fifo_coord_op.cpp:187) [126489][0][YB426440 842E-0005E712C31EC2DF-0-0] [lt=20] [dc=0] fail process message(ret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502758] WARN [SQL.ENG] terminate_running_dfos (ob_px_coord_op.cpp:483) [126489][0][Y B426440842E-0005E712C31EC2DF-0-0] [lt=8] [dc=0] fail to exit dfo(ret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502768] WARN [SQL.ENG] fetch_rows (ob_px_fifo_coord_op.cpp:214) [126489][0][YB426440 842E-0005E712C31EC2DF-0-0] [lt=8] [dc=0] QC get error code(ret=-4016, ret_terminate=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502775] WARN [SQL.ENG] get_next_row (ob_operator.cpp:754) [126489][0][YB426440842E-0 005E712C31EC2DF-0-0] [lt=4] [dc=0] inner get next row failed(ret=-4016, type=77, op="PHY_PX_FIFO_COORD")
observer.log.20221108163454:[2022-11-08 16:28:11.502782] WARN [SQL.ENG] inner_get_next_row (ob_count_op.cpp:135) [126489][0][YB426440 842E-0005E712C31EC2DF-0-0] [lt=6] [dc=0] get row from child failed(ret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502792] WARN [SQL.ENG] get_next_row (ob_operator.cpp:754) [126489][0][YB426440842E-0 005E712C31EC2DF-0-0] [lt=9] [dc=0] inner get next row failed(ret=-4016, type=49, op="PHY_COUNT")
observer.log.20221108163454:[2022-11-08 16:28:11.502798] WARN [SQL.ENG] get_next_row (ob_count_op.cpp:123) [126489][0][YB426440842E-0 005E712C31EC2DF-0-0] [lt=5] [dc=0] operator get next row failed(ret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502804] WARN [SQL.EXE] get_next_row (ob_execute_result.cpp:211) [126489][0][YB426440 842E-0005E712C31EC2DF-0-0] [lt=5] [dc=0] get next row from operator failed(ret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502811] WARN [SQL.SESSION] get_next_row (ob_result_set.cpp:570) [126489][0][YB426440 842E-0005E712C31EC2DF-0-0] [lt=6] [dc=0] get next row from exec result failed(ret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502822] WARN [SERVER] response_query_result (ob_query_driver.cpp:232) [126489][0][YB 426440842E-0005E712C31EC2DF-0-0] [lt=9] [dc=0] fail to iterate and response(ret=-4016, row_num=0, can_retry=true)
observer.log.20221108163454:[2022-11-08 16:28:11.502829] WARN [SERVER] response_result (ob_sync_plan_driver.cpp:103) [126489][0][YB42 6440842E-0005E712C31EC2DF-0-0] [lt=5] [dc=0] response query result fail(ret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502844] WARN [SERVER] test_and_save_retry_state (ob_query_retry_ctrl.cpp:541) [12648 9][0][YB426440842E-0005E712C31EC2DF-0-0] [lt=8] [dc=0] do not need retry(client_ret=-4016, err=-4016, expected_stmt=true, THIS_WORKER. get_timeout_ts()=1667896101369799, retry_type_=0, result.get_stmt_type()=1, result.get_exec_context().need_change_timeout_ret()=false, session->get_retry_info().get_last_query_retry_err()=0)
observer.log.20221108163454:[2022-11-08 16:28:11.502876] WARN [SERVER] response_result (ob_sync_plan_driver.cpp:115) [126489][0][YB42 6440842E-0005E712C31EC2DF-0-0] [lt=12] [dc=0] result response failed, check if need retry(ret=-4016, cli_ret=-4016, retry_ctrl_.need_r etry()=0) observer.log.20221108163454:[2022-11-08 16:28:11.502911] WARN [SERVER] response_result (ob_sync_plan_driver.cpp:124) [126489][0][YB42 6440842E-0005E712C31EC2DF-0-0] [lt=5] [dc=0] close result set fail(cret=-4016)
observer.log.20221108163454:[2022-11-08 16:28:11.502954] WARN [SERVER] do_process (obmp_query.cpp:743) [126489][0][YB426440842E-0005E 712C31EC2DF-0-0] [lt=5] [dc=0] execute query fail(ret=-4016, timeout_timestamp=1667896101369799)
问题原因
上述报错 SQL 的执行计划如下:

如果计划的形态类似上图中 subplan filter 右支存在多个子查询,并且 subplan filter 左支存在 pkey,则可能会出现该问题,再结合错误日志及代码分析,得出由于存在未打上标记的内容,因此导致 SQL 查询报错 4016。
适用版本
- OceanBase 数据库 V2.x 版本。
- OceanBase 数据库 V3.2.3 BP6(oceanbase-3.2.3.3-106000102022111521) 之前版本。
解决方法
可使用
hint /+no_use_px/绕过。注意
该 hint 风险较高,不推荐使用。推荐使用升级 OceanBase 数据库解决。
升级 OceanBase 数据库 V3.2.3 BP6(oceanbase-3.2.3.3-106000102022111521) 及之后版本。