今天在执行在线重定义的时候触发了一个BUG,表大小200G，当时做了如下分析和处理

1.    第一查看在线重定义的会话在执行的SQL和等待事件

 DELETE FROM "TOTAL"."TB_RZRQ_CLIENT_ASSET_QY" SNAP$

 WHERE ("TRADEDATE", "BRANCH_CODE", "CUSTOMER_NO", "CAPITAL_ACCOUNT",

       "CURRENCY_CODE") IN

      (SELECT DISTINCT MLOG$."TRADEDATE",

                       MLOG$."BRANCH_CODE",

                       MLOG$."CUSTOMER_NO",

                       MLOG$."CAPITAL_ACCOUNT",

                       MLOG$."CURRENCY_CODE"

         FROM "TOTAL"."MLOG$_TB_RZRQ_CLIENT_ASSET" MLOG$, ALL_SUMMAP MAP$

        WHERE MLOG$.XID$$ = MAP$.XID

          AND MAP$.COMMIT_SCN > :1

          AND MAP$.COMMIT_SCN <= :2

          AND ("DMLTYPE$$" != 'I'));

2.       等待事件GC CR REQUEST

3.       查看执行计划 ,发现执行计划不对这里应该是HASH操作（这里当时查看是通过dbms_xplan,因为BLOG是事后写的）

4.     发生该问题后，我查看MOS发现是一个BUG具体参考MOSBug 17469077 - DBMS_REDEFINITION.SYNC_INTERIM_TABLE performance issue if table has many rows (文档 ID 17469077.8)

我做了如下处理我估算了下逻辑读有几千亿，当时执行了3个小时才9亿多逻辑读：

1.    停掉SYNC动作

2.       分析表统计信息（"TOTAL"."MLOG$_TB_RZRQ_CLIENT_ASSET" MLOG$和TB_RZRQ_CLIENT_ASSET_QY）

 begin

  dbms_stats.gather_table_stats('TOTAL','TB_RZRQ_CLIENT_ASSET_QY',cascade => true,degree => 32);

  end;

begin

dbms_stats.gather_table_stats('QYUSER','MLOG$_TB_RZRQ_CLIENT_ASSET',degree => 32,cascade => TRUE);

end;

3.       删除shared_pool的执行计划

alter session set events '5614566 trace name context forever';

select distinct address, hash_value from v$sql where sql_id = '2648dqnaqhcwj';

begin

sys.dbms_shared_pool.purge('00000023B78540C8,359150481', 'C');

end;

alter session set events '5614566 trace name context off';

4.       查看执行计划发现执行计划正常

5.       重新执行SYNC同步

6      DBMS_REDEFINITION.finish_redef_table