REBUILD INDEX with PARALLELISM引发的性能问题

说说背景：生产环境大部分都会有定期重建索引的Job，年前上线了这样一个Job，每个月初执行一次。后来开发报出来生产环境偶尔有Timeout问题报出，然后问题邮件到我这里，根据日志查到执行的SQL语句，非常简单，正常情况下只要几十毫秒，表也不大，3000多条记录。
日志：
2017-03-27 09:40:39,361 [http-server-1] INFO com.citi.ficc.profiling.DefaultProfiler - SimpleJdbcProcedureCall.GET_BOOK_ID [time=30911ms]
SQL：
SELECT BOOK_ID FROM FXLM_BOOK;
在BOOK_ID上还有PK，所以这也就是一个INDEX FAST FULL SCAN的操作，怎么也是不能要近31s的。

还有一张表，也不大，1066条记录，这个超时就更频繁了。
日志：

2017-03-28 05:00:45,691 [http-server-16] INFO com.citi.ficc.profiling.DefaultProfiler - SimpleJdbcProcedureCall.get_fasb_type [time=10359ms]

2017-03-28 06:58:34,933 [http-server-2] INFO com.citi.ficc.profiling.DefaultProfiler - SimpleJdbcProcedureCall.get_fasb_type [time=12202ms]

2017-03-28 09:59:17,609 [http-server-4] INFO com.citi.ficc.profiling.DefaultProfiler - SimpleJdbcProcedureCall.get_fasb_type [time=26419ms]

2017-03-28 12:56:04,153 [http-server-18] INFO com.citi.ficc.profiling.DefaultProfiler - SimpleJdbcProcedureCall.get_fasb_type [time=10087ms]
SQL：

SELECT count(*) into hedgeRec  FROM FASBLINK  WHERE HEDGEOASYSDEALID = oasysId;
在列HEDGEOASYSDEALID也是有Index的，所以出现十几二十几秒的查询时间也是不正常的。

后来生成ASH report，直接到那报超时的一分钟，也没看出有啥问题，关键也是没有想到INDEX DEGREE会影响执行计划上面。

最后没办法，找Oracle Support说明这个问题，看能不能给点建议。后来Oracle Support回复说查查INDEX的DEGREE是不是设成DEFAULT了，改成1试一下。

Select degree, table_name,instances from dba_tables where table_name in ('List of Tables used in the query'); 

Select degree, index_name, table_name,instances from dba_indexes where table_name in ('List of Tables used in the query');
Select owner,segment_name,round (sum(bytes)/1024/1024/1024,2) "Size in GB" from dba_segments where owner='<>' group by owner,segment_name order by 3 desc

for all small tables, set it's dop =1 (if not 1) and it's indexes should match the table dop.

 

To set DOP=1 on a table, please do:

ALTER TABLE table_name PARALLEL (DEGREE 1 INSTANCES 1);
ALTER INDEX index_name PARALLEL (DEGREE 1 INSTANCES 1);

经过确认，发现这一问题所在。后来也就知道了INDEX DOP被设置成DEFAULT的原因就是REBUILD INDEX的时候使用了PARALLEL。

ALTER INDEX index_name REBUILD PARALLEL; 这个SQL会把INDEX的DEGREE改成DEFAULT，如果后面指定PARALLEL n，那么DEGREE就是n。

一直以为加PARALLEL只是加快INDEX的创建速度，没想到这么一个小小的操作带来这么大的影响。
正确的方法就是REBUILD完Index后，再设成NOPARALLEL。
SQL：
ALTER INDEX index_name REBUILD PARALLEL;
ALTER INDEX index_name NOPARALLEL;

附上INDEX REBUILD脚本：
PROCEDURE REBUILD_INDEX(V_ONLINE IN VARCHAR2 DEFAULT NULL)
AS
ls_index_name  VARCHAR2(40)   ;
ls_part_type   VARCHAR2(40)   ;
ls_part_name   VARCHAR2(40)   ;
ls_sql         VARCHAR2(400)  ;
ls_nop         VARCHAR2(400)  ;
ld_start_date  DATE           ;
ln_SECONDS     NUMBER(10)     ;

CURSOR cur_indexes IS
  SELECT UI.INDEX_NAME,
         DECODE(UIP.COMPOSITE, 'NO', ' PARTITION ', 'YES', ' SUBPARTITION ', NULL) PARTITION_TYPE,
         DECODE(UIP.COMPOSITE,'NO',UIP.PARTITION_NAME, 'YES', UIS.SUBPARTITION_NAME, NULL) PARTITION_NAME
  FROM USER_INDEXES UI
  LEFT JOIN USER_IND_PARTITIONS UIP
  ON   UI.INDEX_NAME = UIP.INDEX_NAME
  LEFT JOIN USER_IND_SUBPARTITIONS UIS
  ON   UI.INDEX_NAME = UIS.INDEX_NAME
  WHERE UI.INDEX_NAME NOT LIKE 'SYS_IL%'
  AND   UI.INDEX_TYPE <> 'IOT - TOP'
  ORDER BY UI.TABLE_NAME, UI.INDEX_NAME, UIP.PARTITION_POSITION,
           UIS.PARTITION_NAME, UIS.SUBPARTITION_POSITION;

BEGIN
  SELECT SYSDATE INTO ld_start_date FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('Rebuild INDEX at '||TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH24:MI:SS')||CHR(10));
  FOR cur_1 IN cur_indexes LOOP
    ls_index_name := cur_1.INDEX_NAME;
    ls_part_type  := cur_1.PARTITION_TYPE;
    ls_part_name  := cur_1.PARTITION_NAME;

    ls_sql := 'ALTER INDEX '||ls_index_name||' REBUILD'||ls_part_type||ls_part_name||' PARALLEL '||V_ONLINE;
    ls_nop := 'ALTER INDEX '||ls_index_name||' NOPARALLEL ';
    --DBMS_OUTPUT.PUT_LINE(ls_sql||';');

    -- Execute
    BEGIN
      EXECUTE IMMEDIATE ls_sql;
      EXECUTE IMMEDIATE ls_nop;
    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(ls_sql||'; '||SQLERRM);
      DBMS_OUTPUT.PUT_LINE(ls_nop||'; '||SQLERRM);
    END;
  END LOOP;
  SELECT (SYSDATE - ld_start_date)*24*3600 INTO ln_SECONDS FROM DUAL;
  DBMS_OUTPUT.PUT_LINE('COMPLETED... at '||TO_CHAR(SYSDATE, 'YYYY-Mon-DD HH24:MI:SS')||CHR(10));
END REBUILD_INDEX;

参考资料：Oracle rebuild index 使用 parallel 时 与 并行度 的注意事项(http://blog.csdn.net/tianlesoftware/article/details/7734344)