sql优化，生产真实案例5

生产库查询存储过程执行慢

状况：
存储过程

点击画面中“x_ret_data”后面的 cursor查看数据，需要花费约20多秒

尝试使用plsql返回游标解析的方法测试速度也一样慢：

DECLARE
  v_cursor SYS_REFCURSOR;
  l_msg    VARCHAR2(400);
  l_code   VARCHAR2(400);

  TYPE v_tab_type IS TABLE OF temp20170320%ROWTYPE;
  v_tab v_tab_type;

BEGIN
  mes_cs_warehouse_approved.get_instoage_batch_dtail(p_orgid    => 329,
                                                     p_billtype => 1,
                                                     p_batchid  => '1299650',
                                                     p_cartonno => NULL,
                                                     p_barcode  => NULL,
                                                     x_ret_code => l_code,
                                                     x_ret_msg  => l_msg,
                                                     x_ret_data => v_cursor);

  FETCH v_cursor BULK COLLECT
    INTO v_tab;
  FOR i IN 1 .. v_tab.count
  LOOP
    dbms_output.put_line('字段1：' || v_tab(i).mitemname);
  END LOOP;


END;

分析：
Plsql里对当前会话做10046 trace

–开启10046

alter session set events '10046 trace name context forever,level 12';

–执行存储过程

DECLARE
  v_cursor SYS_REFCURSOR;
  l_msg    VARCHAR2(400);
  l_code   VARCHAR2(400);

  TYPE v_tab_type IS TABLE OF temp20170320%ROWTYPE;
  v_tab v_tab_type;

BEGIN
  mes_cs_warehouse_approved.get_instoage_batch_dtail(p_orgid    => 329,
                                           p_billtype => 1,
                                           p_batchid  => 1299650,
                                           p_cartonno => NULL,
                                           p_barcode  => '863882038348691',
                                           x_ret_code => l_code,
                                           x_ret_msg  => l_msg,
                                           x_ret_data => v_cursor);

  FETCH v_cursor BULK COLLECT
    INTO v_tab;
  FOR i IN 1 .. v_tab.count
  LOOP
    dbms_output.put_line('barcode：' || v_tab(i).barcode);
  END LOOP;

END;

–关闭10046

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

–查看trace文件路径

select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat));

然后登陆到操作系统trace文件的目录对trace文件tkprof

tkprof xxx.trc xxx.txt

查看文本文件内容，其中下图中的执行计划显示fetch了200多万行

执行计划效率不佳

而如果将package中sql查询的那段SQL代码复制出来，代入查询条件却不慢，仅仅是执行存储过程慢，收集sql查询到的表的统计信息也没有作用。

处理：
存储过程传入参数p_batchid为varchar，与表字段类型number不一致，有时会导致索引用不上的问题，因此首先修改该参数类型。

修改参数类型后，执行速度依然没有改善，原因应该是该sql中is null or 这种语法太多，导致执行计划不正确，这种情况以前在其他程序里也出现过。

总结
对于PLSQL中的执行计划，分析时需要通过10046这种跟踪才能准确找的出来，后续遇到此类问题时可以作为参考