Dataphin数据集成同步数据至Oracle时preSql超时-阿里云帮助中心

问题描述

Dataphin数据集成同步数据至Oracle时preSql超时。Oracle数据库侧检查SQL已经执行完成，最后手动终止同步任务。

2022-11-08 21:17:15.816 [job-0] INFO  JobContainer - DataX Reader.Job [odpsreader] do prepare work .
2022-11-08 21:17:15.816 [job-0] INFO  JobContainer - DataX Writer.Job [oraclewriter] do prepare work .
2022-11-08 21:17:15.958 [job-0] INFO  CommonRdbmsWriter$Job - Begin to execute preSqls:[delete from ads_pay_inv_index_list where data_dt <= '20221107' and data_dt >= to_char(to_date('20221107','yyyymmdd')-92 ,'yyyymmdd')]. context info:jdbc:oracle:thin:@192.168.XX.XX:1521:orcl. in transaction:false.
2022-11-08 22:56:46.016 Receive kill signal
2022-11-08 22:56:54.637 No outputData produced.
2022-11-08 22:56:54.621 DataX command exit with code: 0
2022-11-08 22:56:54.707 =================================================================
2022-11-08 22:56:54.707 Current task status: KILLED [TASK KILLED]
2022-11-08 22:56:54.707 Elapsed time: 1.661 h( Estimated: 10m )
2022-11-08 22:56:54.707 ---------------- voldemort task ends ----------------

这张表目前已经存在where 条件的索引。

问题原因

Oracle侧没有返回导致该问题。

解决方案

1.Oracle数据库没用返回，delete 时间过长，如果where后的字段没有添加索引，建议在delete where 条件字段上面填加索引。

2.Oracle删除表慢的原因可能是存在外键、高水位线等因素有关。根本原因，需要DBA检查下oradebug来看下delete过程做的具体操作。

Oracle删除数据慢

适用于

Dataphin