背景

mysql定期归档到oracle12c任务，这个月突然卡住了，mysql 批量生成的insert 脚本执行时hang住了。脚本如下:

#!/bin/bash
$ORACLE_HOME/bin/sql usern/pass >> imp.log <<eof
set define off
show user;
@imp.sql
commit;
set time on;
exit;
eof

问题分析

1、等待事件趋势 第一次排查hang后通过event入手

TO_CHAR(SAMPL SESSION_ID SESSION_SERIAL# EVENT                                                            STATUS  BLOCKING_SESSION   COUNT(*)
------------- ---------- --------------- ---------------------------------------------------------------- ------- ---------------- ----------
2021-08-12 18       2003            9370 latch: shared pool                                               WAITING             1429          1
2021-08-12 18       2003            9370                                                                  ON CPU                         1912
2021-08-12 19       2003            9370                                                                  ON CPU                         1833
2021-08-12 20       2003            9370 latch: shared pool                                               WAITING             1706          1
2021-08-12 20       2003            9370                                                                  ON CPU                         1912
2021-08-12 21       2003            9370                                                                  ON CPU                         1880
2021-08-12 22       2003            9370 latch: shared pool                                               WAITING              866          2
2021-08-12 22       2003            9370                                                                  ON CPU                         1900
2021-08-12 23       2003            9370                                                                  ON CPU                         1917
2021-08-13 00       2003            9370 SQL*Net break/reset to client                                    WAITING                           1
2021-08-13 00       2003            9370                                                                  ON CPU                         1923

TO_CHAR(SAMPL SESSION_ID SESSION_SERIAL# EVENT                                                            STATUS  BLOCKING_SESSION   COUNT(*)