DB日志 INSERT产生异常死锁

今天发现一个非常有趣的问题，定位及修正花了很多时间，在这里分享一下。

问题背景，存储过程和JOB共同构成一个定时任务，过程中通过execute immediate 执行一个insert 语句,这是一个按天定时执行的语句，语句执行的目的是每天8：00-9：00，查询某分区表前日8：00-当日8：00中间24小时时段数据，并分组，时间分组取整值-8小时取整到天。
会形成
XX|XX|XX|2020-04-27
XX|XX|XX|2020-04-26
的数据结构
正常执行只需要4秒，但是今天定时任务执行时严重超时。通过debug将动态SQL取出，本地窗口执行测试，发现一个很奇怪的现象。执行以下语句会触发死锁

insert into T_XXXXXXXXX(en_station_id,ex_station_id,obu_total_count,no_card_count,fee,fee_mileage,cnt,zlc,tic_count,DEAL_FLAG,receive_time)
select t.en_station_id,t.ex_station_id,obu_total_count,sum(nvl(t.no_card_count, 0)) no_card_count,sum(t.fee) fee,fee_mileage,count(*) cnt,t1.drive_disance zlc,t1.tic_count,case when t.fee_mileage=t1.drive_disance and t.obu_total_count<t1.tic_count then 1 when t.fee_mileage<t1.drive_disance then 2 when t.fee_mileage<t1.drive_disance then 3 when t1.tic_count-t.obu_total_count>5 then 4 else 0 end,trunc(t.receive_time-8/24)
from T_XXXXXXX t
left join T_XXXXXXX t1 on t1.path_type = 1 and t1.en_station_id = t.en_station_id and t1.ex_station_id = t.ex_station_id
where receive_time>= trunc(to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1,‘dd’)+8/24 and receive_time <trunc(to_date(‘202004280000’,‘YYYY-MM-DD hh24:mi:ss’),‘dd’)+8/24
and SUBSTR(t.EN_TOLL_LANE_HEX,1,2)=62 and t.PROVINCE_COUNT=1 and t.EN_STATION_ID != t.EX_STATION_ID group by t.en_station_id, t.ex_station_id,t.obu_total_count,fee_mileage,t1.drive_disance,t1.tic_count,trunc(t.receive_time-8/24)

初步分析，怀疑定时任务与本地窗口语句抢占资源导致TM级死锁。
解决思路，查找TM级死锁，使用alter table T_XXXXXXX move;
再次尝试，问题没有解决。
为验证语句准确性，去除insert，直接查询，结果集正常，查询时间0.7秒，查看执行计划无异常。

再次思考insert 目标表主键，并不存在因主键冲突导致的死锁，
多次进行试验，insert 时才产生死锁，中止操作无锁。查找很多资料并没有找到类似的情况。
不中止insert，杀灭inactive死锁，立即会产生固定数量新锁，部分锁中止会话时报错ora-00027，无法中止当前会话，尝试通过paddr查询gv$process视图，找到rac节点号以及进程号，登陆linux，使用oracle账号kill 进程，死锁依然存在，状态变为kill，active状态锁杀灭会中止会话。

再次查看JOB

因进程杀灭已停止工作，并在结束时产生日志。

已经没有其他进程抢占资源了，这下问题应该解决了吧。运行insert依然出现死锁。
怀疑可能是多次实验导致的回滚段未提交或回滚依然占用资源，查看是否有TM级锁。

发现无表锁存在。

耐心耗尽，打算使用简单暴力的方法不再追究产生原因，truncate+drop+create其他表名，如果有资源抢占，这样的方法可摒除其他干扰。

新建表后以为可以见证奇迹了。结果让人崩溃。

冷静下来分析语句结构，
insert into T1(1,2,3,time)
select 1,2,sum(3),trunc(t.receive_time-8/24)
from t2
left join t3 on xx=xx
where receive_time>=to_date(‘2020-04-20 08:00:00’,‘yyyy-mm-dd hh24:mi:ss’) and receive_time<to_date(‘2020-04-27 08:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
group by 1,2,trunc(t.receive_time-8/24)

手动写下如下语句，执行，结果耗时1.7秒执行成功，跟存储过程中动态拼接语句比较后，发现
trunc(to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1,‘dd’)+8/24
to_date(‘2020-04-20 08:00:00’,‘yyyy-mm-dd hh24:mi:ss’)
唯一的差别就在于这里。
修改语句to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1+8/24
执行依然未成功
改为
receive_time-8/24>=trunc(to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1,‘dd’)
成功插入。执行时间1.7秒。

考虑数据库资源占用问题，过2个小时至波谷时段再次尝试trunc(to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1,‘dd’)+8/24执行成功。
怀疑与临时表空间占用有关，查看临时表空间使用情况

至此问题完美解决，只是留有疑问，因执行计划只能查看select语句，不能查看insert语句。
select 时trunc(to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1,‘dd’)+8/24
与receive_time-8/24>=trunc(to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1,‘dd’)并无差别，
insert结果，差别巨大。
求同行帮忙分析一下。以及，insert执行语句时为何会产生无限死锁。锁的机制并不能从资料上找到相关的解释。

最终优化方案，扩容temp临时表空间，语句修正为
receive_time-8/24>=trunc(to_date(‘202004280000’,‘YYYYMMDDhh24mi’)-1/1,‘dd’)
留待后续观察。