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

xiaofirehu 2010-11-26 03:43:22
问题是这样的 ，一个SQL，教简单，也就进行了两三层嵌套期间关联了 五六个表，数据量的话有3个表的数据量较大。单独执行很快，1S多， 但是放到存储过程里作为游标的返回结果就很慢，确切的说 执行存储过程很快，执行成功后查看结果数据集的时候就很慢卡住了 ，需要300多 S 才能出来，请问有人遇到过这样的情况么？ 或者教一下怎么进一步调试存储过程的执行方式。
...全文
2398 18 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
18 条回复
切换为时间正序
请发表友善的回复…
发表回复
tangren 2010-11-26
  • 打赏
  • 举报
 回复
这个可能是在存储过程中与你直接在SQL中执行计划不一样，
如果数据库是基于COST优化的，可以重新收集一下相关表的统计数据。
如果不能收集，可以强制hint方式指定执行计划
WUNKANG 2010-11-26
  • 打赏
  • 举报
 回复
我之前有个情况和你类似/
数据在8W左右/
是UPDATE一个字段的/
直接执行20s多/
在存储过程里超过20分钟还出不来/
但是数据早已经UPDATE了/
就是不出来/
后来删除了原有的索引/
重新建了唯一的组合索引/
直接执行2s/
在存储过程里执行一共也才3s/

呵呵~
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
都走索引了，该指定的也指定了。。。
不纠结了 我 换个办法实现吧 这个先留着以后再 研究 谢谢大家了
WUNKANG 2010-11-26
  • 打赏
  • 举报
 回复
看看执行计划啊/
或许有必要建索引/
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
刚又单独执行才 1.125秒啊。。。
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
我也很纠结啊！！！ 十分无奈
minitoy 2010-11-26
  • 打赏
  • 举报
 回复
没道理啊..
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
1秒左右啊
minitoy 2010-11-26
  • 打赏
  • 举报
 回复
最后的open cursor for后面的语句在sql windows里执行要多长时间?
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复 
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;
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
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;
minitoy 2010-11-26
  • 打赏
  • 举报
 回复
如果方便最好贴下存储过程代码
iqlife 2010-11-26
  • 打赏
  • 举报
 回复
用PL SQL DEVELOP 单步调试下吧，看卡在哪里了，53行单步调试也很快。。
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
兄弟们 给点力啊 我 耐不住啦
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
存储过程的结果当然是TEST 一下查出来咯 ，
单独执行肯定是把SQL 复制出来单独执行咯
参数什么的都一样的啦
猫大叔 2010-11-26
  • 打赏
  • 举报
 回复
你查询数据集是手动查还是在存储过程里查的?
xiaofirehu 2010-11-26
  • 打赏
  • 举报
 回复
没 就 53行
iqlife 2010-11-26
  • 打赏
  • 举报
 回复
你返回的数据行行数很多？
是不是超过了数据库的最大游标量
看看open_cursors 参数
SHOW PARAMETER OPEN_CURSOR
存储过程很慢单独执行很快_SQL 存储过程施行很慢，但是复制出来单独执行很快！为什么呢(2)...
当前位置:我的异常网» Sql Server»SQL 存储过程施行很慢，但是复制出来单独执行很快！SQL 存储过程施行很慢，但是复制出来单独执行很快！为什么呢(2)www.myexceptions.net网友分享于：2013-12-17浏览：479次)AwhereA.YYYYMM=YYMonthHL.YMandA.A0188=YYMonthHL.A0188andA.YYY...
存储过程很慢单独执行很快_提高存储过程执行效率
曾经发现这样一种情况，存储过程中的语句提取出来单独执行很快，但在存储过程中执行就很慢。----************* 存储过程中的语句，变量为 avc_acct_monthSELECT 2101,B.BUNDLE,COUNT( Distinct CASE WHEN (substr(FAMILY_NUM_CREATE_T,1,6)<=&avc_acct_month AND SUB...
使用存储过程非常慢，但是执行SQL很快问题分析
问题一：存储过程放在查询分析器中执行，大概耗时5秒左右即可成功执行，但是在网站中执行却会超时 1. 将该存储过程放到查询分析器中执行，大概耗时5秒左右即可成功执行，但是在网站中执行却会超时； 2.经查阅资料，这里存储过程的执行计划是被缓存了，参数不同，还是按照老的执行计划查询，效率也会不同 3.解决办法： 在存储过程的定义上，加上WITH RECO...
存储过程很慢单独执行很快_DB2中存储过程执行慢问题故障处理
其实这个问题是以前同一个客户遇见的问题，当时一个工程师解决后记录的过程如下：应用同事反映但是对应到执行存储过程，执行了2，3个小时了，还没出来结果。存储过程主要是执行一条update sql语句，单独将语句拿出来，clp命令行执行很快，2－3s即可执行完成。执行的SP：call pdw.P_OCS_ACTIVE_UPDATE('20120304',?)存储过程主要业务SQL：/**********...
存储过程执行比单独执行慢
如果语句单独执行很快，但是放到存储过程中很慢，那可能是执行计划搞的鬼试试 WITH RECOMPILE
Oracle

17,140

社区成员

55,259

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章