My Life My Dream!

守信 求实 好学 力行
  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

部分项目反馈系统整体突然变慢,经查询发现一个系统核心的大数据表的索引状态全部是Unusable。

导致索引失效的直接原因:当某些操作导致数据的rowid改变,索引就会完全失效。

那什么时候会导致rowid改变使得索引unuseable或者invalid呢?

一般普通表在在如下3个情况下可以使index unusable
1) 手动alter index unusable

2) Move 【alter table move】【alter table t02 move tablespace tbs01;】包括分区操作

3) sqlldr 【sqlldr ( parallel or direct ) append 】【sqlldr direct=y + 主键重复】

 

匹配三个选项:

1、检查dba_objects的last_ddl_time,对应的时间远早于问题发生的时间段,第一种可能性排除了

2、检查问题表没有分区,也没有做数据移动、统计信息收集之类的操作,last_ddl_time也可以作为佐证

3、回忆一下,貌似没有使用过sqlldr,很奇怪、郁闷

 

从AWR、ASH了解到问题发生的时间段索引失效的表发生了严重的library cache lock等待,查询历史性能视图dba_hist_active_sess_history,发现大量library cache lock等待的会话都在被同一个会话阻塞,而该会话没有被其他阻塞,等待事件为db file sequential read,不可理解,为什么会是索引查找呢?根据sql_id,已经查不到当前会话正在执行的SQL脚本了。

等待下一次问题重现,希望能够看到同样的信息,看一下阻塞源头的会话正在执行一个什么样的东东。

果然,第二天问题重现了,抓取所有会话及当前正在执行SQL,马上发现了问题:INSERT /*+ SYS_DL_CURSOR */ INTO "LC0079999"."JKL_TEST" ("ID", "C1","C2","C3",) VALUES (NULL,NULL,NULL,NULL)

 

第一、这是我们的数据表

第二、好像不是我们手工产生的SQL语句

第三、查了一下SYS_DL_CURSOR 关键字,等同于sqlldr(direct=true),重现一把BulkCopy,果然可以看到这条语句

 

注意:

Bulkcopy的方式本身产生的语句为:INSERT /*+ SYS_DL_CURSOR */ INTO "LC0079999"."JKL_TEST" ("ID", "C1","C2","C3",) VALUES (NULL,NULL,NULL,NULL)

/*+ SYS_DL_CURSOR */ 就是SQL Loader的方式,也就是说Bulkcopy需要谨慎使用,因为他会造成该表的索引失效。

并且在执行Sql Loader前,会执行对表的排他锁:LOCK TABLE "LC0079999"."JKL_TEST" IN EXCLUSIVE MODE NOWAIT

 

结论:按照目前发现的问题,BulkCopy应该仅适用于日志之类的极少并发的场景,建议谨慎使用Bulkcopy