一个SQL单独执行很快，放到存储过程里执行就慢了 求帮忙

Question

问题是这样的 ，一个SQL，教简单，也就进行了两三层嵌套期间关联了 五六个表，数据量的话有3个表的数据量较大。单独执行很快，1S多， 但是放到存储过程里作为游标的返回结果就很慢，确切的说  执行存储过程很快，执行成功后查看结果数据集的时候就很慢卡住了 ，需要300多 S 才能出来，请问有人遇到过这样的情况么？ 或者教一下怎么进一步调试存储过程的执行方式。

Answer 1

你返回的数据行行数很多？
是不是超过了数据库的最大游标量
看看open_cursors 参数
SHOW  PARAMETER OPEN_CURSOR

Answer 2

存储过程的结果当然是TEST 一下查出来咯  ，
单独执行肯定是把SQL 复制出来单独执行咯  
参数什么的都一样的啦

Answer 3

用PL SQL DEVELOP 单步调试下吧，看卡在哪里了，53行单步调试也很快。。

Answer 4

CREATE OR REPLACE PROCEDURE OQP_INFO_SECURITY_TRD_REV
------------------------------------------------
  --SYSTEM: 
  --SUBSYS: 
  --AUTHOR: 
  --DATE:   
  --DESC:   获取反向交易信息  /* 
   返回数据集包括    
   F_SECURITY_ID 股票代码,
   F_SECURITY_NAME 股票名,
   F_FUND_ID1 基金代码1,
   F_FUND_NAME1 基金名称1,
   F_DIRECTION1 买卖方向1,
   F_BUSINESS_DATE1 交易日期1,
   F_FUND_ID2 基金代码2,
   F_FUND_NAME2 基金名称2,
   F_DIRECTION2 买卖方向2,
   F_BUSINESS_DATE2 交易日期2  
  */
  --HISTORY：
  ------------------------------------------------
(PI_FUNDID     IN VARCHAR2, --资产代码 可传多个 传空则查出全部
 PI_STAT_TYPE  IN VARCHAR2, --计算方法 A 计算对于单只基金的反向交易 ， B计算对于所有基金的反向交易
 PI_ASSET_TYPE IN VARCHAR2, --资产类型 
 PI_START_DATE IN VARCHAR2, --开始日期
 PI_END_DATE   IN VARCHAR2, --结束日期
 PI_DAY_PASS   IN NUMBER, -- T+N 的N 。默认为3 
 PI_MV_RATE    IN NUMBER, --期初个券占净资产比例  如 0.5 则过滤只查询期初个券占组合净资产 0.5% 以上的个券,如果不传入则不过滤
 PI_T_TYPE     IN VARCHAR2, --第一天交易是否参加比对  A 不参加比对 传空 则参加比对
 PO_ERRCODE    OUT VARCHAR2, --错误代码，执行成功为0000
 PO_ERRMSG     OUT VARCHAR2, --错误信息
 PO_CURSOR     OUT SYS_REFCURSOR --输出记录集 
 ) AS  VC_END_DATE   VARCHAR2(8); --期间 +N的期末日期
  VC_SETUP_DATE VARCHAR2(8); --期间 期初日期
  VC_ASSET_TYPE VARCHAR2(20) := NVL(PI_ASSET_TYPE, 'STK');
  VN_DAY_PASS   NUMBER := NVL(PI_DAY_PASS, 3);BEGIN  --获取期初日期
  SELECT MAX(D.F_DATE)
    INTO VC_SETUP_DATE
    FROM BSC_PRD_DAYS D
   WHERE D.F_DATE_STYLE = 'CNSE00'
     AND D.F_WORKR_FLAG = 1
     AND D.F_DATE < PI_START_DATE;  VC_SETUP_DATE := NVL(VC_SETUP_DATE, PI_START_DATE);  --获取 期间 +N的期末日期
  SELECT MIN(A.F_DATE)
    INTO VC_END_DATE
    FROM (SELECT D.F_DATE, ROWNUM AS N
            FROM BSC_PRD_DAYS D
           WHERE D.F_DATE_STYLE = 'CNSE00'
             AND D.F_WORKR_FLAG = 1
             AND D.F_DATE > PI_END_DATE) A
   WHERE N = VN_DAY_PASS;  VC_END_DATE := NVL(VC_END_DATE, PI_END_DATE);  --返回交易详细 
  OPEN PO_CURSOR FOR
    SELECT /*+INDEX(S PK_SUM_HLD_ASSET)*/
     B.F_SECURITY_ID,
     I.F_NAME_SHORT AS F_SECURITY_NAME,
     B.F_FUND_ID1,
     O1.F_FUND_NAME AS F_FUND_NAME1,
     DECODE(B.F_DIRECTION1, 'B', '买', '卖') AS F_DIRECTION1,
     B.F_BUSINESS_DATE1,
     B.F_FUND_ID2,
     O2.F_FUND_NAME AS F_FUND_NAME2,
     DECODE(B.F_DIRECTION2, 'B', '买', '卖') AS F_DIRECTION2,
     B.F_BUSINESS_DATE2
      FROM (SELECT A.F_SETUP_DATE,
                   A.F_DATE,
                   A.F_DATE_N,
                   --
                   E1.F_SECURITY_ID,
                   E1.F_FUND_ID AS F_FUND_ID1,
                   E1.F_DIRECTION AS F_DIRECTION1,
                   E1.F_BUSINESS_DATE AS F_BUSINESS_DATE1,
                   --
                   E2.F_FUND_ID       AS F_FUND_ID2,
                   E2.F_DIRECTION     AS F_DIRECTION2,
                   E2.F_BUSINESS_DATE AS F_BUSINESS_DATE2
              FROM (
                    --获取每个 T 的期初日  T日  T+N日
                    SELECT LAG(D.F_DATE) OVER(ORDER BY D.F_DATE) AS F_SETUP_DATE,
                            D.F_DATE AS F_DATE,
                            LEAD(D.F_DATE, VN_DAY_PASS - 1) OVER(ORDER BY D.F_DATE) AS F_DATE_N
                      FROM BSC_PRD_DAYS D
                     WHERE D.F_DATE_STYLE = 'CNSE00'
                       AND D.F_WORKR_FLAG = 1
                       AND D.F_DATE BETWEEN VC_SETUP_DATE AND VC_END_DATE) A,
                   --T日的交易
                   BSC_TRD_EX_DETAIL E1,
                   --T日到T+N日的交易
                   BSC_TRD_EX_DETAIL E2
             WHERE A.F_DATE BETWEEN PI_START_DATE AND PI_END_DATE
                  --T日的交易   
               AND E1.F_BUSINESS_DATE = A.F_DATE
               AND (INSTR(PI_FUNDID, E1.F_FUND_ID) > 0 OR PI_FUNDID IS NULL)
               AND E1.F_SEC_TYPE = VC_ASSET_TYPE
               AND E1.F_BUSINESS_TYPE IN ('BUY', 'SELL')
                  --T日到T+N日的交易      
                  -- 过滤 第个期间第一天的交易是否参加比对 
               AND (E2.F_BUSINESS_DATE > A.F_DATE AND PI_T_TYPE = 'A' OR
                   E2.F_BUSINESS_DATE >= A.F_DATE AND PI_T_TYPE IS NULL)
               AND E2.F_BUSINESS_DATE <= A.F_DATE_N
                  --过滤 单只基金 反向 或 所有基金反向
               AND (E2.F_FUND_ID = E1.F_FUND_ID AND PI_STAT_TYPE = 'A' OR
                   PI_STAT_TYPE = 'B')
               AND E2.F_SEC_TYPE = E1.F_SEC_TYPE
               AND E2.F_SECURITY_ID = E1.F_SECURITY_ID
               AND E2.F_DIRECTION <> E1.F_DIRECTION
               AND E2.F_BUSINESS_TYPE IN ('BUY', 'SELL')) B,
           SUM_HLD_ASSET S,
           BSC_HLD_SECURITY H,
           BSC_PRD_BASIC_INFO O1,
           BSC_PRD_BASIC_INFO O2,
           BSC_SEC_SBI I
     WHERE O1.F_FUND_ID(+) = B.F_FUND_ID1
       AND O2.F_FUND_ID(+) = B.F_FUND_ID2
       AND I.F_SECURITY_ID(+) = B.F_SECURITY_ID
          
       AND B.F_SETUP_DATE = S.F_DATE(+)
       AND B.F_FUND_ID1 = S.F_FUND_ID(+)
       AND S.F_ITEM_CODE(+) = 'HJ03'
          --
       AND B.F_SETUP_DATE = H.F_DATE(+)
       AND B.F_FUND_ID1 = H.F_FUND_ID(+)
       AND B.F_SECURITY_ID = H.F_SECUTITY_ID(+)
       AND H.F_SECURITY_STYLE(+) = VC_ASSET_TYPE
          --过滤 期初个券占净资产比例  输入比例为空则不过滤
       AND (H.F_FUND_MV / S.F_FUND_MV > PI_MV_RATE / 100 OR
            PI_MV_RATE IS NULL);  PO_ERRCODE := '0000';
  PO_ERRMSG  := '成功';EXCEPTION
  WHEN OTHERS THEN
    PO_ERRCODE := '9999';
    PO_ERRMSG  := '[' || SQLCODE || ']:' || SQLERRM;
END;

Answer 5

CREATE OR REPLACE PROCEDURE OQP_INFO_SECURITY_TRD_REV
------------------------------------------------
  --SYSTEM: 
  --SUBSYS: 
  --AUTHOR: 
  --DATE:   
  --DESC:   获取反向交易信息  /* 
   返回数据集包括    
   F_SECURITY_ID 股票代码,
   F_SECURITY_NAME 股票名,
   F_FUND_ID1 基金代码1,
   F_FUND_NAME1 基金名称1,
   F_DIRECTION1 买卖方向1,
   F_BUSINESS_DATE1 交易日期1,
   F_FUND_ID2 基金代码2,
   F_FUND_NAME2 基金名称2,
   F_DIRECTION2 买卖方向2,
   F_BUSINESS_DATE2 交易日期2  
  */
  --HISTORY：
  ------------------------------------------------
(PI_FUNDID     IN VARCHAR2, --资产代码 可传多个 传空则查出全部
 PI_STAT_TYPE  IN VARCHAR2, --计算方法 A 计算对于单只基金的反向交易 ， B计算对于所有基金的反向交易
 PI_ASSET_TYPE IN VARCHAR2, --资产类型 
 PI_START_DATE IN VARCHAR2, --开始日期
 PI_END_DATE   IN VARCHAR2, --结束日期
 PI_DAY_PASS   IN NUMBER, -- T+N 的N 。默认为3 
 PI_MV_RATE    IN NUMBER, --期初个券占净资产比例  如 0.5 则过滤只查询期初个券占组合净资产 0.5% 以上的个券,如果不传入则不过滤
 PI_T_TYPE     IN VARCHAR2, --第一天交易是否参加比对  A 不参加比对 传空 则参加比对
 PO_ERRCODE    OUT VARCHAR2, --错误代码，执行成功为0000
 PO_ERRMSG     OUT VARCHAR2, --错误信息
 PO_CURSOR     OUT SYS_REFCURSOR --输出记录集 
 ) AS  VC_END_DATE   VARCHAR2(8); --期间 +N的期末日期
  VC_SETUP_DATE VARCHAR2(8); --期间 期初日期
  VC_ASSET_TYPE VARCHAR2(20) := NVL(PI_ASSET_TYPE, 'STK');
  VN_DAY_PASS   NUMBER := NVL(PI_DAY_PASS, 3);BEGIN  --获取期初日期
  SELECT MAX(D.F_DATE)
    INTO VC_SETUP_DATE
    FROM BSC_PRD_DAYS D
   WHERE D.F_DATE_STYLE = 'CNSE00'
     AND D.F_WORKR_FLAG = 1
     AND D.F_DATE < PI_START_DATE;  VC_SETUP_DATE := NVL(VC_SETUP_DATE, PI_START_DATE);  --获取 期间 +N的期末日期
  SELECT MIN(A.F_DATE)
    INTO VC_END_DATE
    FROM (SELECT D.F_DATE, ROWNUM AS N
            FROM BSC_PRD_DAYS D
           WHERE D.F_DATE_STYLE = 'CNSE00'
             AND D.F_WORKR_FLAG = 1
             AND D.F_DATE > PI_END_DATE) A
   WHERE N = VN_DAY_PASS;  VC_END_DATE := NVL(VC_END_DATE, PI_END_DATE);  --返回交易详细 
  OPEN PO_CURSOR FOR
    SELECT /*+INDEX(S PK_SUM_HLD_ASSET)*/
     B.F_SECURITY_ID,
     I.F_NAME_SHORT AS F_SECURITY_NAME,
     B.F_FUND_ID1,
     O1.F_FUND_NAME AS F_FUND_NAME1,
     DECODE(B.F_DIRECTION1, 'B', '买', '卖') AS F_DIRECTION1,
     B.F_BUSINESS_DATE1,
     B.F_FUND_ID2,
     O2.F_FUND_NAME AS F_FUND_NAME2,
     DECODE(B.F_DIRECTION2, 'B', '买', '卖') AS F_DIRECTION2,
     B.F_BUSINESS_DATE2
      FROM (SELECT A.F_SETUP_DATE,
                   A.F_DATE,
                   A.F_DATE_N,
                   --
                   E1.F_SECURITY_ID,
                   E1.F_FUND_ID AS F_FUND_ID1,
                   E1.F_DIRECTION AS F_DIRECTION1,
                   E1.F_BUSINESS_DATE AS F_BUSINESS_DATE1,
                   --
                   E2.F_FUND_ID       AS F_FUND_ID2,
                   E2.F_DIRECTION     AS F_DIRECTION2,
                   E2.F_BUSINESS_DATE AS F_BUSINESS_DATE2
              FROM (
                    --获取每个 T 的期初日  T日  T+N日
                    SELECT LAG(D.F_DATE) OVER(ORDER BY D.F_DATE) AS F_SETUP_DATE,
                            D.F_DATE AS F_DATE,
                            LEAD(D.F_DATE, VN_DAY_PASS - 1) OVER(ORDER BY D.F_DATE) AS F_DATE_N
                      FROM BSC_PRD_DAYS D
                     WHERE D.F_DATE_STYLE = 'CNSE00'
                       AND D.F_WORKR_FLAG = 1
                       AND D.F_DATE BETWEEN VC_SETUP_DATE AND VC_END_DATE) A,
                   --T日的交易
                   BSC_TRD_EX_DETAIL E1,
                   --T日到T+N日的交易
                   BSC_TRD_EX_DETAIL E2
             WHERE A.F_DATE BETWEEN PI_START_DATE AND PI_END_DATE
                  --T日的交易   
               AND E1.F_BUSINESS_DATE = A.F_DATE
               AND (INSTR(PI_FUNDID, E1.F_FUND_ID) > 0 OR PI_FUNDID IS NULL)
               AND E1.F_SEC_TYPE = VC_ASSET_TYPE
               AND E1.F_BUSINESS_TYPE IN ('BUY', 'SELL')
                  --T日到T+N日的交易      
                  -- 过滤 第个期间第一天的交易是否参加比对 
               AND (E2.F_BUSINESS_DATE > A.F_DATE AND PI_T_TYPE = 'A' OR
                   E2.F_BUSINESS_DATE >= A.F_DATE AND PI_T_TYPE IS NULL)
               AND E2.F_BUSINESS_DATE <= A.F_DATE_N
                  --过滤 单只基金 反向 或 所有基金反向
               AND (E2.F_FUND_ID = E1.F_FUND_ID AND PI_STAT_TYPE = 'A' OR
                   PI_STAT_TYPE = 'B')
               AND E2.F_SEC_TYPE = E1.F_SEC_TYPE
               AND E2.F_SECURITY_ID = E1.F_SECURITY_ID
               AND E2.F_DIRECTION <> E1.F_DIRECTION
               AND E2.F_BUSINESS_TYPE IN ('BUY', 'SELL')) B,
           SUM_HLD_ASSET S,
           BSC_HLD_SECURITY H,
           BSC_PRD_BASIC_INFO O1,
           BSC_PRD_BASIC_INFO O2,
           BSC_SEC_SBI I
     WHERE O1.F_FUND_ID(+) = B.F_FUND_ID1
       AND O2.F_FUND_ID(+) = B.F_FUND_ID2
       AND I.F_SECURITY_ID(+) = B.F_SECURITY_ID
          
       AND B.F_SETUP_DATE = S.F_DATE(+)
       AND B.F_FUND_ID1 = S.F_FUND_ID(+)
       AND S.F_ITEM_CODE(+) = 'HJ03'
          --
       AND B.F_SETUP_DATE = H.F_DATE(+)
       AND B.F_FUND_ID1 = H.F_FUND_ID(+)
       AND B.F_SECURITY_ID = H.F_SECUTITY_ID(+)
       AND H.F_SECURITY_STYLE(+) = VC_ASSET_TYPE
          --过滤 期初个券占净资产比例  输入比例为空则不过滤
       AND (H.F_FUND_MV / S.F_FUND_MV > PI_MV_RATE / 100 OR
            PI_MV_RATE IS NULL);  PO_ERRCODE := '0000';
  PO_ERRMSG  := '成功';EXCEPTION
  WHEN OTHERS THEN
    PO_ERRCODE := '9999';
    PO_ERRMSG  := '[' || SQLCODE || ']:' || SQLERRM;
END;

Answer 6

最后的open cursor for后面的语句在sql windows里执行要多长时间?

Answer 7

我之前有个情况和你类似/
数据在8W左右/
是UPDATE一个字段的/
直接执行20s多/
在存储过程里超过20分钟还出不来/
但是数据早已经UPDATE了/
就是不出来/
后来删除了原有的索引/
重新建了唯一的组合索引/
直接执行2s/
在存储过程里执行一共也才3s/呵呵~

Answer 8

这个可能是在存储过程中与你直接在SQL中执行计划不一样，
如果数据库是基于COST优化的，可以重新收集一下相关表的统计数据。
如果不能收集，可以强制hint方式指定执行计划