Oracle 执行计划更改导致数据加工未完成

Oracle 执行计划更改导致数据加工未完成

现象：job一直运行，抽取一直在运行，通过过程中的日志记录定位到是因为抽取X系统数据一直卡着。但是查询正在执行的sql，没有任何结果，抽取这部分数据一直运行了一天，还未完。
 
因为定位不到是哪条sql在运行，于是停job，手动运行这部分的数据抽取，运行时间与正常抽取的时间相近，之前查看系统cpu,io均没有异常，怀疑是因为这部分sql的执行计划改变所致。
 调出系统中的执行计划与手动运行的sql的执行计划进行对比，一条sql的执行计划在月初由hash join 变为nested loops。
 --1.由于定位了sql,查找sql的sql_id
 select sql_id,address,plan_hash_value,hash_value,child_number,sql_text from v$sql                                         
 where sql_text like 'INSERT INTO XXX%'
 
--2.dba_hist_sql_plan视图显示了历史的执行计划，TIMESTAMP字段指该执行计划生成的时间
 select * from dba_hist_sql_plan h where sql_id='7sjn92j0k8csm';
 每个不同的plan_hash_value值代表一个不同的执行计划。这条对应两个执行计划，在月初的时候新生成了一个计划(连接方式是nested loops)，Oracle使用了最新的执行计划。
 
 --3.显示所有详细的执行计划
 select plan_table_output from table(dbms_xplan.display_awr('7xh0wcw1qt6d7'));
 
解决方法：使用use_hash hint或使用outlines稳固执行计划，  因为时间原因，最后采用了使用hint的方式。hash join 不仅解决了nested loops连接中大量随机读取的问题，还解决了排序合并连接中排序代价过大的问题。hash join不同于nested loop按照随机读取的方式进行连接，它需要提前将连接对象集中到一起，然后在内存中按照哈希读取的方式进行连接。排序合并连接可以参考：
 
查看计划的两种方式：
 DBMS_XPLAN.DISPLAY_AWR(
    sql_id            IN      VARCHAR2,
    plan_hash_value  IN      NUMBER DEFAULT NULL,
    db_id            IN      NUMBER DEFAULT NULL,
    format            IN      VARCHAR2 DEFAULT TYPICAL);
 
sql_id
 Specifies the SQL_ID of the SQL statement. You can retrieve the appropriate value for the SQL statement of interest by querying the column SQL_ID in DBA_HIST_SQLTEXT.
 
DBMS_XPLAN.DISPLAY_CURSOR(
    sql_id        IN  VARCHAR2  DEFAULT  NULL,
    child_number  IN  NUMBER    DEFAULT  NULL,
    format        IN  VARCHAR2  DEFAULT  'TYPICAL');
 
sql_id
 Specifies the SQL_ID of the SQL statement in the cursor cache. You can retrieve the appropriate value by querying the column SQL_ID in V$SQL or V$SQLAREA. Alternatively, you could choose the column PREV_SQL_ID for a specific session out of V$SESSION. This parameter defaults to NULL in which case the plan of the last cursor executed by the session will be displayed.