oracle中單獨執行Insert快，但放到PROCEDURE中慢-有解無憂

CREATE OR REPLACE PROCEDURE cs(pi_StartFetchTime IN DATE,
                                             pi_EndFetchTime   IN DATE,
                                             po_OutErrCode     OUT VARCHAR2,
                                             po_OutErrMsg      OUT VARCHAR2) IS
  l_sql varchar(2500);
BEGIN

   l_sql := 'insert into TEMP_DWD_TEST_SBLSYL'
   || '('
   || 'OBJECT_RRN,'
   || 'LOT_ID,'
   || 'SHIFT,'
   || 'CLASS,'
   || 'APS_DATE,'
   || 'APS_MONTH,'
   || 'PART_NAME,'
   || 'APS_WEEK,'
   ||'CUSTOM_GROUP,'
   || 'PKG_TYPE,'
   || 'PKG_SORT'
   || ')'
   || ' SELECT T.OBJECT_RRN,'
           || 'T.LOT_ID,'
           || 'T.SHIFT,'
           || 'T.CLASS,'
           || 'T.APS_DATE,'
           || 'T.APS_MONTH,'
           || 'T.PART_NAME,'
           || 'T.APS_WEEK,'
           || 'CASE WHEN T.PART_NAME LIKE ''S-%'' THEN ''AUTO'' WHEN T.PART_NAME LIKE ''%-HW'' THEN ''HW'' ELSE NULL END  AS CUSTOM_GROUP,'
           || 'T.PKG_TYPE,'
           || 'W.PKG_SORT'
    || ' FROM ( SELECT A.OBJECT_RRN,'
  || '       A.m_lot LOT_ID,'
    || '       fnc_get_lot_device(A.sub_lot) part_name,'
    || '       CASE WHEN B.SYS_DATE < B.SYS_DATE+ 1 / 2 THEN ''白班'' ELSE ''夜班'' END AS SHIFT,'
    || '       FNC_GET_CLASS(A.UPDATED) CLASS,'
    || '       TRUNC(A.UPDATED - 11 / 32) APS_DATE, '
    || '       B.YEAR ||LPAD(B.WEEK, 2, ''0'') APS_WEEK, '
    || '       B.YEAR||LPAD(B.MONTH, 2, ''0'') APS_MONTH,'
    || '       a.sub_lot,'
    || '       fnc_get_lot_PKG(A.sub_lot) PKG_TYPE'
    || '       FROM rpt_ods_test_sblsyl A,RPT_RBD_CALENDAR_VIEW B '
    || ' where B.SYS_DATE= TRUNC(A.UPDATED - 11 / 32 )  '
    || '    AND A.SUB_LOT IS NOT NULL '
    || '   AND  A.IS_EXCEPTION = ''1'' '
|| '   AND A.UPDATED >='''||pi_StartFetchTime||''' '
    || '   AND A.UPDATED < '''||pi_EndFetchTime||'''  ) T'
|| '   LEFT JOIN RPT_DIM_PKG_SORT W '
|| '   ON t.PKG_TYPE = W.PKG_TYPE ';
execute immediate l_sql;

    COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    po_OutErrCode := SQLCODE;
    po_OutErrMsg  := SQLERRM(SQLCODE);
    INSERT INTO RFD_TIMER_ERROR
      (OBJECT_ID,
       OBJECT_TYPE,
       ERROR_CODE,
       ERROR_MESSAGE,
       ERROR_DATE,
       START_FETCH_TIME,
       END_FETCH_TIME)
    VALUES
      ('cs',
       'PRC',
       po_OutErrCode,
       po_OutErrMsg,
       SYSDATE,
       pi_StartFetchTime,
       pi_EndFetchTime);
    COMMIT;

END cs;

PROCEDURE
單獨把insert陳述句拿出來執行，10多秒就成功了，但是放到PROCEDURE中跑不成功，求大神指教。

uj5u.com熱心網友回復：

參考排查https://blog.csdn.net/jycjyc/article/details/106999386

uj5u.com熱心網友回復：

你把select列印出來，拉出來執行看看慢不慢

uj5u.com熱心網友回復：

1、把程序中的SQL陳述句打出來看看，與單拿出來的SQL是否完全一樣；
2、性能差別大，應該還是執行計劃不同，可以把打出來的SQL拿下執行計劃看看。

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/8500.html

標籤：開發

上一篇：OSB發布介面的時候出現帶有計算機名而不是IP地址，求問如何配置

下一篇：求大神指點。。。急！！