likes
comments
collection
share

噩梦般的经历-一次数据库锁表问题解决

作者站长头像
站长
· 阅读数 19

本文正在参加「技术专题19期 漫谈数据库技术」活动

背景

  作者所在公司提供政府信息化软件的公司(其他文章也提及过)，某天我们收到现场实施的反馈，系统经常性大面积卡死，表现为前端页面一直转圈圈无响应，有时候等待几分钟就恢复了，有时候需要重启服务器才行，发生问题的时间点也比较随机，非高峰时期也会发生。我们收到消息以后就派人去现场解决问题(当时内网环境不允许远程)，当时内部分析猜测可能是部分服务存在问题，造成大面积卡死。所以直接派人过去现场查看下服务器相关的日志，看看能不能发现问题。

由此就开启了一段堪称噩梦般的旅程，当时问题很快就定位到了，是数据库层面出现了，但是为了彻底解决这个问题，从开始到结束差不多花了一年时间。

数据库运行架构

  在介绍整段经历之前，有必要先提一下整套系统关于数据库层面的架构，当时系统所属的业务是国家刚刚改革开始的业务，所以里面涉及很多复杂且不确定的因素，比如数据共享涉及的数据共享方式，数据共享内容等等，为了方便且快速适应业务调整，当时都是在数据库层面做的文章。

噩梦般的经历-一次数据库锁表问题解决

  数据库是Oracle的，然后采用了RAC挂载两个节点，由第三方硬件公司维护。数据库除了要用于日常生产的查询和写入以外，还有四条路径要用于其他数据共享。

  第一个是轮询程序扫描数据库，因为数据汇交厅级部门的要求，需要定时把生产库的数据进行业务处理以后，汇交到厅级数据库。所以部署了一个定时任务，差不多10S轮询一次。

涉及复杂查询，对数据库读有一定要求

  第二个是触发器，因为汇交省级政府部门的要求，需要记录业务所有的增删改查过程(单单结果不够)，受限于当时的环境，客户和硬件公司不允许我们去基于日志做分析。Oracle 8i以后自带的LogMinder其实可以解决这个问题，开启Oracle的归档日志以后，通过LogMinder可以查询出所有的DDL和DML语句，就可以把所有的增删改查都记录下来。

后来就是采用了最Low的方式，在表上加触发器来记录增删改查过程，对生产环境其实影响很大。

  第三个是物化视图，这个其实最开始我们是拒绝的，但是这个是市政府要求的，统一建立一个业务快照库，供其他部门使用数据。所以就在我们生产库上建立了大量的物化视图，同步到市政府统一建立的共享库，当时因为业务场景不明确，市政府就要求实时同步，所以物化视图的刷新方式是ON COMMIT，也就是数据库一发生事务提交就要触发刷新。

  第四个是必须的场景，政府部门业务办理要求数据共享，通过传统的数据库共享，会存在延时，错误等各类情况，对一些办理时限要求非常高的业务是不适合的，所以就开放一批接口，供其他部门查询和写入(基于生产库)。

  最后还有一个是开启了Oracle的全文索引，当时受限于技术能力，无法引入ES等全文索引框架。Oracle全文索引可以解决模糊查询方面的性能问题。

事件经过

  当时人员过去的现场的时候，发现应用服务器看起来还是比较正常的，通过jstack查询挂起任务，发现有大量请求挂起，经过一系列分析以后，很快发现是数据库问题，数据库把核心表给锁了，导致其他事务都挂起在那。

查询脚本

select  l.session_id sid, 
       s.serial#, 
       case when l.locked_mode =0 or l.locked_mode=1  then '正常'
         when l.locked_mode=2 then '行共享(RS)：共享表锁，sub share'
         when l.locked_mode=3 then '行独占(RX)：用于行的修改，sub exclusive'
         when l.locked_mode=4 then '共享锁(S)：阻止其他DML操作 share'
         when l.locked_mode=5 then '共享行独占(SRX)：阻止其他事务操作，share/sub exclusive '
         when l.locked_mode=6 then '独占(X)：独立访问使用，exclusive' end as loacked_mode , 
       l.oracle_username, 
       s.user#, 
       l.os_user_name, 
       s.machine, 
       s.terminal,  a.SQL_FULLTEXT,s.program,
       a.action,o.object_name,o.OBJECT_TYPE,o.SUBOBJECT_NAME
  from v$sqlarea a, v$session s, v$locked_object l ,dba_objects o
where l.session_id = s.sid 
   and s.prev_sql_addr = a.address and l.OBJECT_ID=o.OBJECT_ID
order by sid, s.serial#;

  当时为了快速恢复业务，就直接采用数据库解锁的方式。解锁以后，业务马上就正常了，有点问题的是有些业务办理的数据都被回滚掉了，要前端重新办理下。

alter system kill '#sid,#serial'

  本来以为这个事情很简单，找到引起锁表的罪魁祸首就行了。Oracle里面有个比较强大的监控程序叫AWR报告，当时Oracle是专门的硬件公司维护的，开启了所有归档日志，所以我们就让硬件公司提供这几天出现问题时段的AWR报告来分析，是否有共性的脚本导致的锁表。

AWR报告

报告大概长这个样子(收集了两个小时内数据)

噩梦般的经历-一次数据库锁表问题解决

噩梦般的经历-一次数据库锁表问题解决

  当时看应用服务器整体状态没什么大问题的，所以首先就怀疑是SQL脚本的脚本，AWR报告中的SQL Statistics可以查看top 10的sql语句

噩梦般的经历-一次数据库锁表问题解决

仔细查看以后，发现每次top1的sql都是不一样的

这些后面定位到是硬件公司加入的监控程序所用的脚本

select a.MsgServiceId, a.MsgTypeId, b.MsgTypeName, a.MsgId, a.OperationType, MsgParam1, MsgParam2, MsgParam3, MsgParam4, MsgParam5, MsgParam6, MsgParam7, MsgParam8, MsgParam9, MsgParam10 from BCG_Msg a, BCG_MsgType b where a.MsgServiceId = :1 and a.MsgTypeId=b.MsgTypeId and a.MsgStatus = 'NEW' order by a.MsgId asc

select ts.tablespace_name "Tablespace", ts.status "Status", ts.contents "Contents", ts.logging "Logging", ts.block_size "Block Size", ts.initial_extent "Initial Extent", ts.next_extent "Next Extent", ts.min_extents "Min Extents", ts.max_extents "Max Extents", ts.pct_increase "PCT Increase", ts.min_extlen "Min Extent length", co.percent_blocks_coalesced "% Blocks Coalesced", ' ' " Size ", ' ' " Free ", ' ' " %Free ", ' ' " %Used " from sys.dba_tablespaces ts, sys.dba_free_space_coalesced co where ts.tablespace_name = co.tablespace_name (+) order by ts.tablespace_name

删除数据的存储过程

BEGIN proc_cancel_project(:1, :2); END;

推送数据的存储过程

Begin pro_push_qzk(:v0, :v1, :v2, :v3, :v4); End;

后面连续好几天查看awr报告，没发现什么有价值的线索，我们自己也是摸不着头脑，程序也没有升级，为什么突然就这样了。

SQL优化

  中间还有个插曲，一直看AWR报告，也没有发现什么问题，倒是客户看到我们的SQL异常复杂(客户有Oracle DBA的证书)，觉得这些SQL肯定有影响的。 噩梦般的经历-一次数据库锁表问题解决

这个是我们一个业务场景的查询SQL，好几百行，确实很复杂

  我们当时把SQL复制出来，然后在PLSQL中执行，其实效率没什么太大的影响，但是确实从设计上来说，这种SQL肯定是有问题。我们在最开始设计的时候是盲目迷信三范式，数据库没有丝毫冗余字段，也没有任何外键关联，都是通过程序去管理。我们一个业务场景，大概会涉及7-10张表，关键属性也是分布在各张表上，这样就导致业务查询需要关联很多张表联合查询，而且表之间会出现多对多的关系(这个后面IO读写里面会详细说明)，以及数据需要组合显示等等，造成了这种异常复杂的SQL。这些SQL出现的场景还很多。

  这种改造牵涉的内容太多了，一下子也是不太敢去改造的，当时也不确定这些SQL和锁表有没有直接关系。后面是制定了一个详细的计划，通过三个大版本的迭代，把这些业务场景去处理掉。

当时没有数据库DBA，很多工作都要自己去做

  1. 部分关键字段增加冗余设置，尽量把前端需要的属性都集中到一起，这个牵涉改造工作量比较大。
  2. 基于Oracle的解释计划进行SQL优化，一段段脚本分析测试过去。比如select (*或1), 内联外联的使用(inner join, left join)，独立索引和联合索引的设置优化，查询条件的前后顺序、关联关系，以及统计信息和索引的关等等，那段时间积累了大量优化的经验。
  3. 部分逻辑计算放到程序里去计算，以前为了方便，什么字段组合、拆分都会放到SQL语句里，后面就是把基础数据查询出来，然后通过程序去计算，这样一方面减少数据库的压力，另一方面性能也会提升。

给后来人的经验就是，设计数据库的时候，该冗余的时候一定要冗余，这样能大大提升效率，数据冗余的问题通过程序去保证他的一致性，基本上没什么太大的问题。

SQL脚本优化是一门很深的学问，需要花大精力下去。

IO读写

  后来一次在看总指标的时候，发现了一些端倪，两小时内磁盘的IO读写非常大。因为这块在非业务高峰的时候，也会引起锁表，并且当时磁盘的IO读写也非常高。本来这块开始也没太关注，但是后面想到我们的业务读写模型，感觉感觉这里会有问题。

噩梦般的经历-一次数据库锁表问题解决

业务数据读写的模式

  我们当时的业务场景比较复杂，其中涉及一个笛卡尔积的操作，比如A记录又10条，B记录也有10条，那对应的生成C记录= A记录 * B记录也就是100条。当时为了简化数据的判断(做update操作逻辑判断起来比较复杂，要考虑很多种情况)，所以就采用了简单粗暴的方式，每次保存的时候，先根据业务全局编码删除所有表的记录，然后再重新写一遍。

  其实这种方式在项目初期没什么太大的问题，但是随着业务改革和访问量上升，问题就来了。

技术上

  后来去看了下Oracle的归档日志，异常庞大。就这个业务模型，硬件公司还跟我们交涉过很多次，但是因为业务在稳定运行，改造也非一早一夕，所以后来是他们提前准备了磁盘阵列，来支持日志的存储。

  数据库里有大量的触发器和物化视图，都是会实施触发的，尤其是物化视图这块，因为是ON COMMIT刷新的，所以它被触发的也非常频繁。触发器就更加了，后面为了记录的正确性，还在触发器里加了大量逻辑来处理数据，

业务上

  一笔业务下面产生的数据，都会自带内码，很多共享的场景，都会根据内码去关联记录，然后每次删除重写，内码都会重新生成，很多时候会造成两边数据不一致，这个也给我们带来了很大的麻烦。

这个模式后面完全重构了，确实很不合理。

“罪魁祸首”

  说回正题，因为基于这个业务架构背景，后来看到这个读写这么大的时候，本能的觉得这里是不是会有问题，跟读写有没有关系，因为其他地方曾出现过磁盘出现坏道，导致的系统异常。

最难的其实是交涉过程，因为硬件不是我们管的，都是甲方联系硬件公司去弄的。当时我们只是怀疑IO有问题，没有确实证据，硬件公司就一句话，我们没问题的，然后就没下文了。

当时是这个事件闹到上级领导那了，硬件公司才勉强派人过来，打车费都是我们出的，因为是我们要求他们来的。

  检测过程也不是很顺利，就是很敷衍，随便拿了一些工具测试了以后，就说磁盘是没问题的。当时我们也没辙，继续想其他招，这个事情也搁置了。

  过了一段时间以后，甲方跟我们说硬件公司打报告上来了，要求更换磁盘，开始也没多想，以为是为了Oracle归档日志的。后来甲方告诉我们的，就是数据库那批磁盘有问题，IO有性能瓶颈，硬件公司后来回去翻查以前的日志(甲方领导强烈要求的)，发现这个情况了。时间点和出异常的时候，有些能对上，有些对不上，但是至少证明磁盘还是存在问题的。

不是说因为业务场景引起的大量磁盘读写，是磁盘本身确实存在问题。然后业务大量读写+物化视图、触发器等模式就更加造成了磁盘IO瓶颈。

  更换了磁盘以后，我们都，以为问题解决了中间差不多有两个月左右的时间，没有出现大问题。偶尔出现了一两次，没当回事，就让现场直接解锁处理了。

外部影响

  在两个多月以后的某一天，现场又找来了，说是又频繁出现锁表的问题了，现象和第一次基本上一模一样，当时心理还是有点懵的，怎么又来了，难道磁盘IO又达到瓶颈了？

  我们去现场的第一件事情，就找硬件公司要监控报告，硬件公司也提前去分析过了，大家一沟通，这次确实也磁盘IO没什么太大的关系，各项指标都很正常。

  然后又是经历了各种看AWR报告，各种原因猜测，然后在一次锁表语句查询的结果中发现一些端倪。里面有大量的非系统SQL被锁了(上一次的时候没有这些)。

全文索引同步

噩梦般的经历-一次数据库锁表问题解决

物化视图同步

噩梦般的经历-一次数据库锁表问题解决

RAC相关的日志

噩梦般的经历-一次数据库锁表问题解决

这里我们有了猜测，是不是这些同步的内容，造成了资源锁。

RAC

  当时对RAC这块也没这么了解，看了很多资料关于RAC同步的机制，想借此分析，这块会不会存在性能瓶颈。

  RAC本身会提供一个虚拟IP出来，正常你用这个虚拟IP去访问就行了，不用关心实际内部是怎么去分配多台数据库实例，有点类似负载均衡。

但是实际运行过程中，没这么简单， NET，PLSQL都可以直接用虚拟IP连接，Java连接池不行

程序连接

  正常来说，jdbc连接oracle只要 jdbc:oracle:thin:ip:1521/orcl就行，但是当时设备不支持这种方式，需要多个实际服务器同时配置，如下所示。

jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.2)(PORT = 1521))(LOAD_BALANCE = yes)(FAILOVER = ON)(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = orcl)(FAILOVER_MODE=(TYPE = SELECT)(METHOD = BASIC)(RETIRES = 20)(DELAY = 15))))

  本来使用也没什么问题，只要能正常负载就行了，但是实际的过程可就复杂了。当时发现这个RAC有锁占用的时候，我们去看了下两边数据库的负载，发现基本上是八二开，也就是一台服务器被分配了**80%**请求，这种负载均衡的角度来说，肯定是有问题的。我们也咨询了对方的Oracle DBA，他说你要是Java程序的连的，这种情况都是正常的，很多地方都是这样的。他也说不上来原因。

我这个人也就是这样，碰到这样的情况，如果不知道个所以然，就会很难受

  后来我去网上找资料，结合自己的实际工作经验，总算发现了问题所在。原因说起来也不复杂。

   当时连接字符串里没有加LOAD_BALANCE = yes参数，这样就导致了所有请求会优先走第一个地址，只有第一个地址不同了，才会去走第二个地址。为什么出现两台数据库八二开，因为有部分程序连接串写法反了....

  后来调整了连接串，加上了LOAD_BALANCE = yes，情况相对好一点，但是还是不太均匀。分析以后发现，因为我们是采用的基于客户端的负载均衡，也就是上面连接串的写法。这样当客户端连接RAC数据库时，会随机在TNS里面挑个监听地址进行连接，一般情况下应该没什么问题。我们的程序是采用连接池的模式，并且在程序一启动的时候就去拿连接了，启动的时候客户端并不知道RAC各节点的负荷及连接数情况，有可能负荷大的节点还会源源不断地增加新的连接，导致RAC节点无法均衡工作。正常应该采用RAC服务端的负载。

后面进一步了解RAC以后也发现，10G以后，RAC服务端负载就支持按照节点负荷或者连接数来判断，那样效果就会更好。

  数据库服务器这边配置其实还是可以的，性能很好，就算分配80%上去，处理也没什么问题，所以可能因为负载分配不均衡造成锁的情况，我们也就排除了。

RAC同步

  缘由也是一次用PLSQL改数据的过程中，原先是要求用PLSQL直连RAC的虚拟IP去访问，这样你进去看数据就没什么异常。有次不小心连了其中一台实体数据库，发现部分数据看不到，问起来才发现数据还没同步过来。而且发现锁表语句有RAC相关的记录，由此就想到，这个RAC同步的过程到底在干什么。

噩梦般的经历-一次数据库锁表问题解决

噩梦般的经历-一次数据库锁表问题解决

从侧面分析，RAC同步应该是个实例锁

  惭愧，这块因为确实不专业，后面也没有模拟环境去测试RAC，验证RAC同步是不是造成锁表的原因，后面就搁置了，有专业懂行的朋友可以帮忙解释下。

物化视图

  这块当时看到日志的时候就很郁闷，因为只有这个地方存在这种情况，其他任何一个项目都没有。所以我们看到日志的时候，就像验证下关掉物化视图是不是就没问题。

  遗憾的是，这个物化视图是市政府牵头的，和其他部门息息相关。系统不是市政府的，他们不负责协助，但是数据又是需要的，你也不能动。甲方也打了报告上去，最后都被打回来了。

这件事情和RAC一样，最后我们也不知道物化视图到底有没有影响。

触发器

  虽然没有出现触发器相关的锁定记录，但是我们评估一下，还是觉得这种方式很不好。后开和甲方以及共享部门沟通以后，换了其他的方式去汇交数据，触发器也全部被我们干掉了。

全文索引

  Oracle数据库相对来说，比较能抗，有几张核心查询表，数据量少的两千万，多的有一亿多，模糊查询就是个灾难，所以那时候在研究技术的时候选择了最方便的Oracle全文索引。

oracle创建全文索引的方式


declare 
num_count number;
begin
  select count(*) into num_count
  from ctxsys.DR$PREFERENCE a, ctxsys.DR$PREFERENCE_VALUE b, USER_USERS c
 where a.pre_id = b.prv_pre_id
   and a.pre_name = 'XXXXX_STORAGE'
   and a.pre_owner# = c.user_id;
  if num_count=0 then
    ---创建存储
    BEGIN
    ---相关词表标识用于索引的词干和模糊匹配查询选项的语言 红色字段是自己需要命名的后面将会用到
     CTX_DDL.CREATE_PREFERENCE('XXXXX_WORDLIST_STORAGE', 'BASIC_WORDLIST');
    --设置相关词表查询属性
     CTX_DDL.SET_ATTRIBUTE('XXXXX_WORDLIST_STORAGE', 'SUBSTRING_INDEX', 'TRUE');
    --存储类指定构成Oracle Text索引的数据库表和索引的表空间参数和创建参数 
     CTX_DDL.CREATE_PREFERENCE('XXXXX_STORAGE', 'BASIC_STORAGE');
    ---设置存储类的六个基本属性 放在INDEXTEST 表空间 这里也可以把分别放在不同表空间 根据自己需要
     CTX_DDL.SET_ATTRIBUTE('XXXXX_STORAGE','I_TABLE_CLAUSE', 'TABLESPACE  XXXXX_INDEX');
     CTX_DDL.SET_ATTRIBUTE('XXXXX_STORAGE','K_TABLE_CLAUSE', 'TABLESPACE  XXXXX_INDEX');
     CTX_DDL.SET_ATTRIBUTE('XXXXX_STORAGE','R_TABLE_CLAUSE', 'TABLESPACE  XXXXX_INDEX');
     CTX_DDL.SET_ATTRIBUTE('XXXXX_STORAGE','N_TABLE_CLAUSE', 'TABLESPACE  XXXXX_INDEX');
     CTX_DDL.SET_ATTRIBUTE('XXXXX_STORAGE','P_TABLE_CLAUSE', 'TABLESPACE  XXXXX_INDEX');
     CTX_DDL.SET_ATTRIBUTE('XXXXX_STORAGE', 'I_INDEX_CLAUSE', 'TABLESPACE  XXXXX_INDEX');
    END;

  end if;

end;
/


declare 
num_count number;
begin

  select count(*) into num_count
  from USER_USERS a, ctxsys.DR$PREFERENCE b
  where a.user_id = b.pre_owner#
  and upper(pre_name) = 'XXXXX_LEXER';

    if num_count=0 then
    ---创建词法分析器
     begin
      ctx_ddl.create_preference ('XXXXX_LEXER', 'chinese_vgram_lexer');
     end;
    end if;

end;
/


----创建全文索引
declare 
num_count number;
begin

  select count(*) into num_count from
   user_indexes a where a.index_type='DOMAIN' and a.index_name='DOMAIN_QLR_QLRMC';
   if num_count=0 then
     
    Execute immediate '
    CREATE INDEX DOMAIN_QLR_QLRMC ON QLR(QLRMC)
    INDEXTYPE  IS  CTXSYS.CONTEXT
    PARAMETERS (
       ''SYNC (ON COMMIT)
       LEXER XXXXX_LEXER
       STORAGE XXXXX_STORAGE'' --使用实时同步（DML提交时同步全文索引）
    )';
    
    end if;
end;
/

  因为当时考虑业务的实时性，业务刚刚办理完成就需要能被查询，所以全文索引也是设置了实时同步的方式(DML提交时同步全文索引)。开始的时候数据量小，可能问题还没发现，后面随着数据量越来越多，全文索引同步需要的资源也就越来越多了。

  全文索引所在的表和字段也是业务核心表(当时设计不合理)，前面提到的也会频繁的删除和新增，就会触发大量的索引同步。

  后来看到锁表的记录也有大量全文索引同步的记录，就想着这个会不会是源头。我们就做了改动，数据写入后不实时同步，每天晚上定时同步，也就是新增的数据要第二天才能查询(业务上能接受)

---定时同步与优化重新同步与优化的存储过程
create or replace procedure PROC_SYNC_OPTIMIZE_Index IS
begin

     begin
          ctx_ddl.sync_index ('DOMAIN_QLR_QLRMC');
          ctx_ddl.sync_index ('DOMAIN_QLR_LS_QLRMC');
          ctx_ddl.sync_index ('DOMAIN_DJFZ_CQZS_BDCQZH');
          ctx_ddl.sync_index ('DOMAIN_DJFZ_CQZS_ZL');
          ctx_ddl.sync_index ('DOMAIN_CQZS_LS_BDCQZH');
          ctx_ddl.sync_index ('DOMAIN_CQZS_LS_ZL');
          ctx_ddl.sync_index ('DOMAIN_DYMX_BDCDYH');
          ctx_ddl.sync_index ('DOMAIN_DYMX_ZL');
          ctx_ddl.sync_index ('DOMAIN_H_HH');
          ctx_ddl.sync_index ('DOMAIN_H_BDCDYH');
          ctx_ddl.sync_index ('DOMAIN_H_LS_HH');
          ctx_ddl.sync_index ('DOMAIN_H_LS_BDCDYH');
          ctx_ddl.sync_index ('DOMAIN_DJZM_DJZMH');
          ctx_ddl.sync_index ('DOMAIN_DJZM_ZL');
      EXCEPTION
      WHEN OTHERS THEN
      null;
     end;
     begin
        ctx_ddl.optimize_index ('DOMAIN_QLR_QLRMC' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_QLR_LS_QLRMC' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_DJFZ_CQZS_BDCQZH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_DJFZ_CQZS_ZL' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_CQZS_LS_BDCQZH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_CQZS_LS_ZL' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_DYMX_BDCDYH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_DYMX_ZL' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_H_HH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_H_BDCDYH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_H_LS_HH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_H_LS_BDCDYH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_DJZM_DJZMH' ,'FULL');
        ctx_ddl.optimize_index ('DOMAIN_DJZM_ZL' ,'FULL');
      EXCEPTION
      WHEN OTHERS THEN
      null;
     end;
end ;
/


----每天晚上22点执行建立job后默认是立即执行的，所以需要改一下时间。 
declare 
job_num number;
num_count number;
begin
   select count(*) into num_count from user_jobs a where a.WHAT='PROC_SYNC_OPTIMIZE_Index;';
   if num_count =0 then
     dbms_job.submit(job_num,'PROC_SYNC_OPTIMIZE_Index;',to_date('2016-01-28 22:00:00', 'yyyy-mm-dd hh24:mi:ss'),'TRUNC(SYSDATE + 1)+22/24');
     commit;
   end if;
end;
/

后面发现效果不大，该锁表的还是会锁表，没有特别明显的改进

专家DBA

  经过一段时间分析，有些场景我们无法验证，我们能验证的场景都没有效果，整个事件也陷入僵局了，毕竟我们不是专业搞数据库的公司。

  后来，甲方听从了我们的建议，请了北京一个Oracle专家过来，过来以后真的让我涨见识了。

  • Oracle专家很牛，从北京出门开始就算钱的，据说挺贵的。
  • 到了现场以后，大致了解下情况，然后登录数据库看了些日志，然后从容的打开百度搜索数据库锁表有哪些原因。别问我怎么知道，我当时就在他后面看着。
  • 然后跟硬件公司DBA交流了一堆专业术语，专业词汇。
  • 然后待了两三天回去了，说解决不了

我当时真的很失望，这么贵请过来，上来就百度，关键问题还解决不了，看着说专业术语挺溜的。我没有能力去评价他的能力，但是确实挺失望的，不知道是不是这个问题确实太棘手了。

事情转机

  有时候真是无巧不成书。后面出差其他项目的时候，碰到客户那的技术负责人(平常也交流比较多，技术出身，挺厉害的)，他们项目很多软硬件维护都是他来做的。本来准备晚上回去的，但是开会太晚，错过了班车，就准备第二天回去，晚上和客户一起去吃饭的时候，我想起这个问题，准备让他参谋参谋。

  前面过程大同小异，他把常规的思路和想法都说了下，发现没效果。然后他忽然意识到什么，问我们Oracle版本是多少，当我把版本号告诉他的时候，他就说大概知道原因了，他(顶级学府的研究生)平常就爱逛技术论坛官网什么的，他看到Oracle官网的一块英文说明，指定版本下的Oracle全文索引存在Bug，需要修复，当时他记不清楚位置了，回去以后再电脑上翻出来发我了。

当然如果赶上班车了，不知道这个问题还要多久才被解决

  回来以后我仔细读了下说明，可能确实有关系，就联系硬件公司DBA去看下，当时有两种解决方式，要么是装Oracle11g指定版本(升级代价有点大)，要么就是打个补丁。

Oracle有个很恶心的地方，这些补丁只向企业提供，个人无法下载，网上也找不到这些资源，后来是通过硬件公司的渠道拿到了这个补丁。

  升级过程也是一波三折，第一次升级失败了回滚了，不知道是不是跟RAC环境有关系。后来硬件公司研究了一段时间，第二次升级的时候，成功升级上去了。

  观望了一个月，没有出现异常，观望了半年，没有出现异常，至此我们基本上判断，就是这个Oracle的Bug引起的异常。

  中途有一次系统升级出现偶发性的锁表，又把我们吓了一跳。但是这次症状比较集中，只有特定场景才会触发，那肯定是我们程序的问题，后来就排查出来是ThreadLoad引起的异常。

刚发生的时候，客户不是这么想的，他们认为全文索引还有问题，所以那时候就要求我们把这个功能去掉，他们宁可慢一点的。我们后来改了下程序，把全文索引功能给完全去掉了。

总结

  整个事件非常痛苦，周期很长，客户也受不了，我们也被折腾的一塌糊涂。现场也是偶发的，必需要等他发生了才能去排查，所以中间经历的时间非常长。好在最终是解决了。

  经历过才会变成有经验，这个事情持续了很久，我也学习了很多数据库的知识。对我后续开展工作也带来了很多帮助。

本文正在参加「技术专题19期 漫谈数据库技术」活动