DOMAIN INDEX失效引起SQL执行缓慢

1、在故障期间服务器CPU使用率高居不下，检查发现有大量的External Procedure call等待事件，执行语句为36rp0shxcvaa1；

SQL>select event,count(*),to_char(SAMPLE_TIME,'yyyymmdd hh24:mi:ss'),sql_id  
from DBA_HIST_ACTIVE_SESS_HISTORY 
where snap_id in ('29601') and WAIT_CLASS<>'Idle' 
and event in('External Procedure call') 
having count(*) >100
group by event,SAMPLE_TIME,sql_id
order by 3;

EVENT                            COUNT(*)    TO_CHAR(SAMPLE_TI SQL_ID
-------------------------------- -------------- ------------------- -----------------------
External Procedure call      109 20160825 08:18:53 36rp0shxcvaa1
External Procedure call      101 20160825 08:19:03 36rp0shxcvaa1
External Procedure call      109 20160825 08:19:33 36rp0shxcvaa1
External Procedure call      113 20160825 08:19:54 36rp0shxcvaa1
External Procedure call      119 20160825 08:20:04 36rp0shxcvaa1
External Procedure call      124 20160825 08:20:14 36rp0shxcvaa1
External Procedure call      118 20160825 08:20:24 36rp0shxcvaa1
External Procedure call      119 20160825 08:20:34 36rp0shxcvaa1
External Procedure call      120 20160825 08:20:44 36rp0shxcvaa1
External Procedure call      116 20160825 08:20:54 36rp0shxcvaa1
External Procedure call      119 20160825 08:21:04 36rp0shxcvaa1
External Procedure call      119 20160825 08:21:14 36rp0shxcvaa1
External Procedure call      132 20160825 08:21:24 36rp0shxcvaa1
External Procedure call      131 20160825 08:21:34 36rp0shxcvaa1
External Procedure call      135 20160825 08:21:44 36rp0shxcvaa1
External Procedure call      128 20160825 08:21:54 36rp0shxcvaa1
External Procedure call      135 20160825 08:22:05 36rp0shxcvaa1
External Procedure call      136 20160825 08:22:15 36rp0shxcvaa1
External Procedure call      147 20160825 08:22:25 36rp0shxcvaa1
External Procedure call      129 20160825 08:22:35 36rp0shxcvaa1
External Procedure call      140 20160825 08:22:45 36rp0shxcvaa1
External Procedure call      139 20160825 08:22:55 36rp0shxcvaa1
External Procedure call      115 20160825 08:23:05 36rp0shxcvaa1
External Procedure call      135 20160825 08:23:15 36rp0shxcvaa1
External Procedure call      152 20160825 08:23:25 36rp0shxcvaa1
External Procedure call      148 20160825 08:23:35 36rp0shxcvaa1
External Procedure call      134 20160825 08:23:45 36rp0shxcvaa1
External Procedure call      150 20160825 08:23:55 36rp0shxcvaa1
External Procedure call      142 20160825 08:24:06 36rp0shxcvaa1
External Procedure call      135 20160825 08:24:16 36rp0shxcvaa1
External Procedure call      157 20160825 08:24:26 36rp0shxcvaa1
External Procedure call      156 20160825 08:24:36 36rp0shxcvaa1
External Procedure call      160 20160825 08:24:46 36rp0shxcvaa1
External Procedure call      154 20160825 08:24:56 36rp0shxcvaa1
External Procedure call      147 20160825 08:25:06 36rp0shxcvaa1
External Procedure call      151 20160825 08:25:16 36rp0shxcvaa1
External Procedure call      159 20160825 08:25:26 36rp0shxcvaa1
External Procedure call      166 20160825 08:25:36 36rp0shxcvaa1
External Procedure call      170 20160825 08:25:46 36rp0shxcvaa1
External Procedure call      161 20160825 08:25:56 36rp0shxcvaa1
External Procedure call      172 20160825 08:26:07 36rp0shxcvaa1
External Procedure call      174 20160825 08:26:17 36rp0shxcvaa1
External Procedure call      161 20160825 08:26:27 36rp0shxcvaa1
External Procedure call      153 20160825 08:26:37 36rp0shxcvaa1
External Procedure call      171 20160825 08:26:47 36rp0shxcvaa1
External Procedure call      156 20160825 08:26:57 36rp0shxcvaa1
External Procedure call      159 20160825 08:27:07 36rp0shxcvaa1
External Procedure call      169 20160825 08:27:17 36rp0shxcvaa1
External Procedure call      162 20160825 08:27:27 36rp0shxcvaa1
External Procedure call      162 20160825 08:27:37 36rp0shxcvaa1
External Procedure call      163 20160825 08:27:47 36rp0shxcvaa1
External Procedure call      175 20160825 08:27:57 36rp0shxcvaa1
External Procedure call      161 20160825 08:28:07 36rp0shxcvaa1
External Procedure call      168 20160825 08:28:18 36rp0shxcvaa1
External Procedure call      162 20160825 08:28:28 36rp0shxcvaa1
External Procedure call      146 20160825 08:28:38 36rp0shxcvaa1
External Procedure call      165 20160825 08:28:48 36rp0shxcvaa1
External Procedure call      156 20160825 08:28:58 36rp0shxcvaa1
External Procedure call      160 20160825 08:29:08 36rp0shxcvaa1
External Procedure call      152 20160825 08:29:18 36rp0shxcvaa1
External Procedure call      163 20160825 08:29:28 36rp0shxcvaa1
External Procedure call      175 20160825 08:29:38 36rp0shxcvaa1
External Procedure call      160 20160825 08:29:48 36rp0shxcvaa1
External Procedure call      150 20160825 08:29:58 36rp0shxcvaa1
External Procedure call      141 20160825 08:30:08 36rp0shxcvaa1
External Procedure call      150 20160825 08:30:19 36rp0shxcvaa1
External Procedure call      143 20160825 08:30:29 36rp0shxcvaa1
External Procedure call      134 20160825 08:30:39 36rp0shxcvaa1
External Procedure call      138 20160825 08:30:49 36rp0shxcvaa1
External Procedure call      152 20160825 08:30:59 36rp0shxcvaa1
External Procedure call      152 20160825 08:31:09 36rp0shxcvaa1
External Procedure call      134 20160825 08:31:19 36rp0shxcvaa1
External Procedure call      150 20160825 08:31:29 36rp0shxcvaa1
External Procedure call      152 20160825 08:31:39 36rp0shxcvaa1
External Procedure call      150 20160825 08:31:49 36rp0shxcvaa1
External Procedure call      150 20160825 08:31:59 36rp0shxcvaa1
External Procedure call      147 20160825 08:32:09 36rp0shxcvaa1
External Procedure call      132 20160825 08:32:20 36rp0shxcvaa1
External Procedure call      129 20160825 08:32:30 36rp0shxcvaa1
External Procedure call      132 20160825 08:32:40 36rp0shxcvaa1
External Procedure call      121 20160825 08:32:50 36rp0shxcvaa1
External Procedure call      130 20160825 08:33:00 36rp0shxcvaa1
External Procedure call      129 20160825 08:33:10 36rp0shxcvaa1
External Procedure call      135 20160825 08:33:20 36rp0shxcvaa1
External Procedure call      131 20160825 08:33:30 36rp0shxcvaa1
External Procedure call      119 20160825 08:33:40 36rp0shxcvaa1
External Procedure call      134 20160825 08:33:50 36rp0shxcvaa1
External Procedure call      124 20160825 08:34:00 36rp0shxcvaa1
External Procedure call      131 20160825 08:34:10 36rp0shxcvaa1
External Procedure call      125 20160825 08:34:20 36rp0shxcvaa1
External Procedure call      134 20160825 08:34:31 36rp0shxcvaa1
External Procedure call      112 20160825 08:34:41 36rp0shxcvaa1
External Procedure call      134 20160825 08:34:51 36rp0shxcvaa1
External Procedure call      134 20160825 08:35:01 36rp0shxcvaa1
External Procedure call      121 20160825 08:35:11 36rp0shxcvaa1
External Procedure call      127 20160825 08:35:21 36rp0shxcvaa1
External Procedure call      118 20160825 08:35:31 36rp0shxcvaa1
External Procedure call      121 20160825 08:35:41 36rp0shxcvaa1
External Procedure call      118 20160825 08:35:51 36rp0shxcvaa1
External Procedure call      123 20160825 08:36:01 36rp0shxcvaa1
External Procedure call      117 20160825 08:36:11 36rp0shxcvaa1
External Procedure call      118 20160825 08:36:21 36rp0shxcvaa1
External Procedure call      114 20160825 08:36:31 36rp0shxcvaa1
External Procedure call      105 20160825 08:36:42 36rp0shxcvaa1
External Procedure call      118 20160825 08:36:52 36rp0shxcvaa1
External Procedure call      103 20160825 08:37:02 36rp0shxcvaa1
External Procedure call      107 20160825 08:37:22 36rp0shxcvaa1
External Procedure call      109 20160825 08:37:42 36rp0shxcvaa1
External Procedure call      102 20160825 08:38:02 36rp0shxcvaa1
External Procedure call      106 20160825 08:48:41 36rp0shxcvaa1
External Procedure call      114 20160825 08:49:06 36rp0shxcvaa1
External Procedure call      136 20160825 08:49:16 36rp0shxcvaa1
External Procedure call      144 20160825 08:49:27 36rp0shxcvaa1
External Procedure call      185 20160825 08:49:37 36rp0shxcvaa1
External Procedure call      237 20160825 08:49:54 36rp0shxcvaa1
External Procedure call      210 20160825 08:50:12 36rp0shxcvaa1
External Procedure call      228 20160825 08:50:37 36rp0shxcvaa1
External Procedure call      193 20160825 08:50:51 36rp0shxcvaa1
External Procedure call      268 20160825 08:51:18 36rp0shxcvaa1
External Procedure call      217 20160825 08:51:31 36rp0shxcvaa1
External Procedure call      229 20160825 08:51:55 36rp0shxcvaa1
External Procedure call      225 20160825 08:52:05 36rp0shxcvaa1
External Procedure call      223 20160825 08:52:18 36rp0shxcvaa1
External Procedure call      217 20160825 08:52:30 36rp0shxcvaa1
External Procedure call      277 20160825 08:52:45 36rp0shxcvaa1
External Procedure call      218 20160825 08:53:05 36rp0shxcvaa1
External Procedure call      220 20160825 08:53:17 36rp0shxcvaa1
External Procedure call      235 20160825 08:53:30 36rp0shxcvaa1
External Procedure call      267 20160825 08:53:45 36rp0shxcvaa1
External Procedure call      283 20160825 08:54:00 36rp0shxcvaa1
External Procedure call      235 20160825 08:54:21 36rp0shxcvaa1
External Procedure call      197 20160825 08:54:33 36rp0shxcvaa1
External Procedure call      243 20160825 08:54:48 36rp0shxcvaa1
External Procedure call      239 20160825 08:55:20 36rp0shxcvaa1
External Procedure call      230 20160825 08:55:52 36rp0shxcvaa1
External Procedure call      217 20160825 08:56:03 36rp0shxcvaa1
External Procedure call      241 20160825 08:56:13 36rp0shxcvaa1
External Procedure call      235 20160825 08:56:24 36rp0shxcvaa1
External Procedure call      249 20160825 08:56:35 36rp0shxcvaa1
External Procedure call      224 20160825 08:56:46 36rp0shxcvaa1
External Procedure call      274 20160825 08:56:57 36rp0shxcvaa1
External Procedure call      254 20160825 08:57:08 36rp0shxcvaa1
External Procedure call      245 20160825 08:57:19 36rp0shxcvaa1
External Procedure call      265 20160825 08:57:32 36rp0shxcvaa1
External Procedure call      227 20160825 08:57:45 36rp0shxcvaa1
External Procedure call      248 20160825 08:58:05 36rp0shxcvaa1
External Procedure call      236 20160825 08:58:16 36rp0shxcvaa1
External Procedure call      242 20160825 08:58:27 36rp0shxcvaa1
External Procedure call      255 20160825 08:58:38 36rp0shxcvaa1
External Procedure call      250 20160825 08:58:48 36rp0shxcvaa1
External Procedure call      303 20160825 08:59:01 36rp0shxcvaa1
External Procedure call      255 20160825 08:59:30 36rp0shxcvaa1
External Procedure call      259 20160825 08:59:40 36rp0shxcvaa1
External Procedure call      252 20160825 08:59:51 36rp0shxcvaa1
External Procedure call      223 20160825 09:00:02 36rp0shxcvaa1
External Procedure call      254 20160825 09:00:15 36rp0shxcvaa1
External Procedure call      237 20160825 09:00:35 36rp0shxcvaa1
External Procedure call      243 20160825 09:00:46 36rp0shxcvaa1
External Procedure call      208 20160825 09:00:57 36rp0shxcvaa1
External Procedure call      267 20160825 09:01:09 36rp0shxcvaa1
External Procedure call      254 20160825 09:01:19 36rp0shxcvaa1
External Procedure call      286 20160825 09:01:31 36rp0shxcvaa1
External Procedure call      304 20160825 09:01:49 36rp0shxcvaa1
External Procedure call      270 20160825 09:02:10 36rp0shxcvaa1
External Procedure call      259 20160825 09:02:20 36rp0shxcvaa1
External Procedure call      206 20160825 09:02:57 36rp0shxcvaa1
External Procedure call      227 20160825 09:03:13 36rp0shxcvaa1
External Procedure call      307 20160825 09:04:03 36rp0shxcvaa1
External Procedure call      279 20160825 09:04:20 36rp0shxcvaa1
External Procedure call      269 20160825 09:04:35 36rp0shxcvaa1
External Procedure call      291 20160825 09:04:50 36rp0shxcvaa1
External Procedure call      308 20160825 09:05:15 36rp0shxcvaa1
External Procedure call      263 20160825 09:05:32 36rp0shxcvaa1
External Procedure call      287 20160825 09:05:48 36rp0shxcvaa1
External Procedure call      306 20160825 09:06:06 36rp0shxcvaa1
External Procedure call      284 20160825 09:06:24 36rp0shxcvaa1
External Procedure call      285 20160825 09:06:57 36rp0shxcvaa1
External Procedure call      276 20160825 09:07:13 36rp0shxcvaa1
External Procedure call      347 20160825 09:07:38 36rp0shxcvaa1
External Procedure call      289 20160825 09:08:18 36rp0shxcvaa1
External Procedure call      275 20160825 09:08:29 36rp0shxcvaa1

2、检查36rp0shxcvaa1语句的执行情况发现执行计划发生改变，正常时间段执行，每次只读取几百上千个块，但是在故障期间每次读取上百万个块。

SQL> set line 200
col sp_begin_time for a20
col sp_end_time for a20
col s.sql_id for a20
col s.PLAN_HASH_VALUE for a20
select  distinct s.SNAP_ID,to_char(p.BEGIN_INTERVAL_TIME,'yyyymmdd-hh24:mi:ss') sp_begin_time,to_char(p.END_INTERVAL_TIME,'yyyymmdd-hh24:mi:ss') sp_end_time,s.sql_id,s.PLAN_HASH_VALUE   ,
round(s.BUFFER_GETS_DELTA/decode(s.EXECUTIONS_DELTA,0,1,s.EXECUTIONS_DELTA),0) buffergets_per,round(s.CPU_TIME_DELTA/decode(s.EXECUTIONS_DELTA,0,1,s.EXECUTIONS_DELTA)/1000,0) cputime_ms_per,s.EXECUTIONS_DELTA execs
from dba_hist_sqlstat s, dba_hist_snapshot p                                              
where s.sql_id='36rp0shxcvaa1' and s.snap_id=p.snap_id ORDER by s.snap_id,s.plan_hash_value;  

   SNAP_ID SP_BEGIN_TIME        SP_END_TIME          SQL_ID        PLAN_HASH_VALUE BUFFERGETS_PER CPUTIME_MS_PER      EXECS
---------- -------------------- -------------------- ------------- --------------- -------------- -------------- ----------
     29579 20160824-10:00:32    20160824-11:00:41    36rp0shxcvaa1       805766360            127             49       4676
     29580 20160824-11:00:41    20160824-12:00:50    36rp0shxcvaa1       805766360            125             58       3391
     29580 20160824-11:00:41    20160824-12:00:50    36rp0shxcvaa1       805766360            127             23        244
     29580 20160824-11:00:41    20160824-12:00:49    36rp0shxcvaa1       805766360            127             23        244
     29580 20160824-11:00:41    20160824-12:00:49    36rp0shxcvaa1       805766360            125             58       3391
     29581 20160824-12:00:50    20160824-13:00:02    36rp0shxcvaa1       805766360            132            160        830
     29581 20160824-12:00:49    20160824-13:00:02    36rp0shxcvaa1       805766360            126             26        106
     29581 20160824-12:00:50    20160824-13:00:02    36rp0shxcvaa1       805766360            126             26        106
     29581 20160824-12:00:49    20160824-13:00:02    36rp0shxcvaa1       805766360            132            160        830
     29582 20160824-13:00:02    20160824-14:00:10    36rp0shxcvaa1       805766360            131            213        685
     29582 20160824-13:00:02    20160824-14:00:10    36rp0shxcvaa1       805766360            134             29         88
     29583 20160824-14:00:10    20160824-15:00:17    36rp0shxcvaa1       805766360            131             80       1705
     29583 20160824-14:00:10    20160824-15:00:17    36rp0shxcvaa1       805766360            139             29        147
     29591 20160824-22:00:12    20160824-23:00:20    36rp0shxcvaa1      2702454306           2439            102         35
     29591 20160824-22:00:12    20160824-23:00:20    36rp0shxcvaa1      2702454306           2331            538        328
     29592 20160824-23:00:20    20160825-00:00:09    36rp0shxcvaa1      2702454306           2328            441        347
     29592 20160824-23:00:20    20160825-00:00:09    36rp0shxcvaa1      2702454306           2336             91         23
     29600 20160825-07:00:31    20160825-08:00:55    36rp0shxcvaa1      2702454306              0              0          0
     29600 20160825-07:00:31    20160825-08:00:55    36rp0shxcvaa1      3278461448         802308          23095          8
     29601 20160825-08:00:55    20160825-09:00:44    36rp0shxcvaa1      2702454306              0              0          0
     29601 20160825-08:00:55    20160825-09:00:44    36rp0shxcvaa1      3278461448        1746635         101924        348
     29602 20160825-09:57:49    20160825-10:00:48    36rp0shxcvaa1      3278461448        1413796          69014        111
     29602 20160825-09:21:36    20160825-10:00:48    36rp0shxcvaa1      3278461448        1413796          69014        111

3、故障期间的执行计划为全表扫描，正常时间段位domain 索引扫描

故障期间执行计划：

正常时间段执行计划

4、检查发现是DOMAIN INDEX失效导致执行计划发生改变，经过确认是在做数据更新时，把原表删掉，重新导入数据时，导致索引失效，造成所有访问这张数据表的语句都用了全表扫描。经过重建该DOMAIN INDEX后执行计划恢复正常。