Oracle Exadata环境下查询导致的阻塞问题分享

环境:

平台:Exadata

操作系统:Oracle操作系统

内核版本:2.6.39-400.126.1.el5uek

数据库版本:oracle 11.2.0.4

前言:

正常情况下,Oracle数据库的读是不阻塞写的,并且针对不同分区的操作互不影响;现场碰到这么一则情况:对A分区表A1分区查询并插入到B表,导致A表的A2分区不能truncate;情况稍微有点特别,特拿出来分享,供大家日后遇到类似情况参考。

说明:由于本案例是某银行系统生产环境,具体用户、表名等敏感信息已替换。

问题现象:

对A分区表A1分区查询并插入到B表,导致A表的A2分区不能TRUNCATE

某日,经某银行客户反馈,他们的某个批次存在延时,平常运行半个小时的批次,目前运行了5个小时,还没结束。经查,该批次的一条SQL存在阻塞情况,但是初查,该目标对象并未存在其他dml语句。于是进行hang analyzed分析,过程如下:

问题分析:

  • 正常情况下,对oracle表的不同分区操作是互不干扰的,truncate不同分区可以正常执行;

参见范例:(本地环境验证)

环境构造

环境测试

  • 但是在此案例中,对A分区表A1分区查询并插入到B表,会导致A表的A2分区不能TRUNCATE,并报ora-04021的错误提示,

查询ora-04021报错提示:

  • 提取hanganalyze日志,可以看到如下内容:

  • 从3可以看出,节点3的INSERT /*+ append */ INTO EXA1.EXA_TABLE1 nologging阻塞了节点1的 ALTER TABLE EXA.EXA_TABLE TRUNCATE PARTITION FOR (’49’) REUSE STORAGE,阻塞会话:process_id=48,session_id=14。

节点3这条阻塞源语句,从当时的session中可以确定,类似的语句,只有:INSERT…select FROM ${EXA_DB}.EXA_TABLE TRUNCATE PARTITION FOR (P_5024) 即为阻塞源。

  • 从节点3找到hanganalyze报告:EXADATA3_diag_103065.trc,分析阻塞会话:process_id=48,session_id=14。

  • 从以上信息可以得到,SO:0x558f5a098产生了X模式的enqueue(CU-5346CED0-00000004),且在Name=$BUILD$.dbf0c32e 2bd7481上持有MODE=X的LIBRARY OBJECT LOCK,其中5346CED0即为其child object handle,
  • 根据5346CED0,可以找到如下信息:

  • 从上一条可知,持有该模式的,正是session 14,

另外,select sql_text from dba_hist_sqltext where sql_id=’drw635s1bux41′;

也可以验证,为INSERT…select FROM ${EXA_DB}.EXA_TABLE TRUNCATE PARTITION FOR (P_5024) 。

  • 综上,由inst 3执行的sql_id=’drw635s1bux41’持有X模式的LIBRARY OBJECT LOCK阻塞了inst 1的truncate;

结合类似SR场景:CU – Bind Enqueue (文档 ID 960106.1),现场EXA_TABLE表由于经常truncate,未收集统计信息,当执行insert…select..EXA_TABLE时,会产生优化器动态采样,导致相同表的其他分区也不能执行truncate。

解决方案:

(1)truncate操作和insert..select操作错开执行;(如果现场条件允许)

(2)关闭动态采样(alter session set optimizer_dynamic_sampling=0)。

说明:

后经Oracle原厂工程师确认,该问题关联Oracle未公开BUG号:21032243

未经允许不得转载:Oracle一体机用户组 » Oracle Exadata环境下查询导致的阻塞问题分享

相关推荐