查看: 3182|回复: 9

[PL/SQL] 【讨论】请教大牛一个问题,万分紧急,在线等,感谢,万分感谢

[复制链接]
认证徽章
论坛徽章:
1
优秀写手
日期:2014-07-02 06:00:12
发表于 2014-6-27 15:56 | 显示全部楼层 |阅读模式
请各位大牛帮帮忙,小弟都快急死了,5555555555555555555555

问题描述:生产环境中执行时间非常长,但是在测试环境中又是瞬间完成。
个人发现问题:Cursor_Id     游标未关闭


请教各位大牛,执行时间长会跟游标未关闭有关系吗?还有就是游标未关闭会导致什么后果?


FUNCTION f_makeyspz ( n_ywxh VARCHAR2, d_rq DATE, d_qr DATE, d_zr DATE, v_zd VARCHAR2, v_yw VARCHAR2 ) RETURN VARCHAR2 IS
    v_formtext    VARCHAR2(4000);      -- 数据源定义
    v_return      VARCHAR2(300);
    n_pzbh        INTEGER;             -- 凭证编号
    n_beginpzbh   INTEGER;             -- 凭证编号
    Cursor_Id     INTEGER;             -- 动态Sql
    r_ywsj        rt_ywsj;             --
  BEGIN
    --
    -- 提取最大凭证号
    --
    n_Pzbh := F_Get_Curm_Pzbh ( Trunc ( d_rq, 'mm' ), Last_Day ( d_rq ) );
    n_beginpzbh := Nvl ( n_Pzbh, 0 ) + 1;
    IF n_pzbh < 0 THEN
      Return '取当月凭证号时出错!';
    END IF;
    --
    -- 按不同业务类型循环编制凭证
    --
    v_xz := df_xzdm;
    FOR r IN ( select dm, data_source, fsyw
                 from pz_mod
                where data_source is not null
                  and zwlb = df_zwlb
                  and dm = Decode ( v_yw, 'A', dm, To_Number ( v_yw ) )
                 order by dm) LOOP
      v_formtext := Replace ( r.data_source, ':d_qr',  sys_jc.To_Str( d_qr)  );
      v_formtext := Replace ( v_formText, ':d_zr',  sys_jc.To_Str( d_zr)  );
      --
      -- 动态产生数据-打开一个光标
      --
      cursor_id := Dbms_Sql.Open_Cursor;
      --
      -- 动态产生数据-分析SQL语句
      --
      Dbms_Sql.Parse ( cursor_id, v_formtext, Dbms_Sql.V7 );
      --
      -- 动态产生数据-绑定变量
      --
      Dbms_Sql.Define_Column ( cursor_id, 1, r_ywsj.v_ywywxh, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 2, r_ywsj.n_je1 );
      Dbms_Sql.Define_Column ( cursor_id, 3, r_ywsj.n_je2 );
      Dbms_Sql.Define_Column ( cursor_id, 4, r_ywsj.n_je3 );
      Dbms_Sql.Define_Column ( cursor_id, 5, r_ywsj.n_je4 );
      Dbms_Sql.Define_Column ( cursor_id, 6, r_ywsj.n_je5 );
      Dbms_Sql.Define_Column ( cursor_id, 7, r_ywsj.n_je6 );
      Dbms_Sql.Define_Column ( cursor_id, 8, r_ywsj.n_je7 );
      Dbms_Sql.Define_Column ( cursor_id, 9, r_ywsj.n_je8 );
      Dbms_Sql.Define_Column ( cursor_id, 10, r_ywsj.n_je9 );
      Dbms_Sql.Define_Column ( cursor_id, 11, r_ywsj.n_je10 );
      Dbms_Sql.Define_Column ( cursor_id, 12, r_ywsj.n_je11 );
      Dbms_Sql.Define_Column ( cursor_id, 13, r_ywsj.n_je12 );
      Dbms_Sql.Define_Column ( cursor_id, 14, r_ywsj.n_je13 );
      Dbms_Sql.Define_Column ( cursor_id, 15, r_ywsj.n_je14 );
      Dbms_Sql.Define_Column ( cursor_id, 16, r_ywsj.n_je15 );
      Dbms_Sql.Define_Column ( cursor_id, 17, r_ywsj.n_je16 );
      Dbms_Sql.Define_Column ( cursor_id, 18, r_ywsj.n_je17 );
      Dbms_Sql.Define_Column ( cursor_id, 19, r_ywsj.n_je18 );
      Dbms_Sql.Define_Column ( cursor_id, 20, r_ywsj.n_je19 );
      Dbms_Sql.Define_Column ( cursor_id, 21, r_ywsj.n_je20 );
      Dbms_Sql.Define_Column ( cursor_id, 22, r_ywsj.n_je21 );
      Dbms_Sql.Define_Column ( cursor_id, 23, r_ywsj.n_je22 );
      Dbms_Sql.Define_Column ( cursor_id, 24, r_ywsj.n_je23 );
      Dbms_Sql.Define_Column ( cursor_id, 25, r_ywsj.n_je24 );
      Dbms_Sql.Define_Column ( cursor_id, 26, r_ywsj.n_je25 );
      Dbms_Sql.Define_Column ( cursor_id, 27, r_ywsj.n_je26 );
      Dbms_Sql.Define_Column ( cursor_id, 28, r_ywsj.n_je27 );
      Dbms_Sql.Define_Column ( cursor_id, 29, r_ywsj.n_je28 );
      Dbms_Sql.Define_Column ( cursor_id, 30, r_ywsj.n_je29 );
      Dbms_Sql.Define_Column ( cursor_id, 31, r_ywsj.n_je30 );
      Dbms_Sql.Define_Column ( cursor_id, 32, r_ywsj.v_mx1, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 33, r_ywsj.v_mx2, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 34, r_ywsj.v_mx3, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 35, r_ywsj.v_mx4, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 36, r_ywsj.v_mx5, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 37, r_ywsj.v_mx6, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 38, r_ywsj.v_mx7, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 39, r_ywsj.v_mx8, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 40, r_ywsj.v_mx9, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 41, r_ywsj.v_mx10, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 42, r_ywsj.v_dwid, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 43, r_ywsj.v_zph, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 44, r_ywsj.v_zy, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 45, r_ywsj.v_dwdm, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 46, r_ywsj.v_ejbz, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 47, r_ywsj.v_bz1, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 48, r_ywsj.v_bz2, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 49, r_ywsj.v_bz3, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 50, r_ywsj.v_ywlx, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 51, r_ywsj.v_ywid, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 52, r_ywsj.v_bbid, 100 );
      Dbms_Sql.Define_Column ( cursor_id, 53, r_ywsj.d_rq1 );
      Dbms_Sql.Define_Column ( cursor_id, 54, r_ywsj.d_rq2 );
      --
      -- 动态产生数据-执行SQL语句
      --
      v_Return := Dbms_Sql.Execute ( cursor_id );
      --
      -- 动态产生数据-循环取出数据
      --
      LOOP
        IF Dbms_Sql.Fetch_Rows ( cursor_id ) = 0 THEN
          Exit;
        END IF;
        --
        -- 动态产生数据-取出数据
        --
        Dbms_Sql.Column_Value ( cursor_id, 1, r_ywsj.v_ywywxh );
        Dbms_Sql.Column_Value ( cursor_id, 2, r_ywsj.n_je1 );
        Dbms_Sql.Column_Value ( cursor_id, 3, r_ywsj.n_je2 );
        Dbms_Sql.Column_Value ( cursor_id, 4, r_ywsj.n_je3 );
        Dbms_Sql.Column_Value ( cursor_id, 5, r_ywsj.n_je4 );
        Dbms_Sql.Column_Value ( cursor_id, 6, r_ywsj.n_je5 );
        Dbms_Sql.Column_Value ( cursor_id, 7, r_ywsj.n_je6 );
        Dbms_Sql.Column_Value ( cursor_id, 8, r_ywsj.n_je7 );
        Dbms_Sql.Column_Value ( cursor_id, 9, r_ywsj.n_je8 );
        Dbms_Sql.Column_Value ( cursor_id, 10, r_ywsj.n_je9 );
        Dbms_Sql.Column_Value ( cursor_id, 11, r_ywsj.n_je10 );
        Dbms_Sql.Column_Value ( cursor_id, 12, r_ywsj.n_je11 );
        Dbms_Sql.Column_Value ( cursor_id, 13, r_ywsj.n_je12 );
        Dbms_Sql.Column_Value ( cursor_id, 14, r_ywsj.n_je13 );
        Dbms_Sql.Column_Value ( cursor_id, 15, r_ywsj.n_je14 );
        Dbms_Sql.Column_Value ( cursor_id, 16, r_ywsj.n_je15 );
        Dbms_Sql.Column_Value ( cursor_id, 17, r_ywsj.n_je16 );
        Dbms_Sql.Column_Value ( cursor_id, 18, r_ywsj.n_je17 );
        Dbms_Sql.Column_Value ( cursor_id, 19, r_ywsj.n_je18 );
        Dbms_Sql.Column_Value ( cursor_id, 20, r_ywsj.n_je19 );
        Dbms_Sql.Column_Value ( cursor_id, 21, r_ywsj.n_je20 );
        Dbms_Sql.Column_Value ( cursor_id, 22, r_ywsj.n_je21 );
        Dbms_Sql.Column_Value ( cursor_id, 23, r_ywsj.n_je22 );
        Dbms_Sql.Column_Value ( cursor_id, 24, r_ywsj.n_je23 );
        Dbms_Sql.Column_Value ( cursor_id, 25, r_ywsj.n_je24 );
        Dbms_Sql.Column_Value ( cursor_id, 26, r_ywsj.n_je25 );
        Dbms_Sql.Column_Value ( cursor_id, 27, r_ywsj.n_je26 );
        Dbms_Sql.Column_Value ( cursor_id, 28, r_ywsj.n_je27 );
        Dbms_Sql.Column_Value ( cursor_id, 29, r_ywsj.n_je28 );
        Dbms_Sql.Column_Value ( cursor_id, 30, r_ywsj.n_je29 );
        Dbms_Sql.Column_Value ( cursor_id, 31, r_ywsj.n_je30 );
        Dbms_Sql.Column_Value ( cursor_id, 32, r_ywsj.v_mx1 );
        Dbms_Sql.Column_Value ( cursor_id, 33, r_ywsj.v_mx2 );
        Dbms_Sql.Column_Value ( cursor_id, 34, r_ywsj.v_mx3 );
        Dbms_Sql.Column_Value ( cursor_id, 35, r_ywsj.v_mx4 );
        Dbms_Sql.Column_Value ( cursor_id, 36, r_ywsj.v_mx5 );
        Dbms_Sql.Column_Value ( cursor_id, 37, r_ywsj.v_mx6 );
        Dbms_Sql.Column_Value ( cursor_id, 38, r_ywsj.v_mx7 );
        Dbms_Sql.Column_Value ( cursor_id, 39, r_ywsj.v_mx8 );
        Dbms_Sql.Column_Value ( cursor_id, 40, r_ywsj.v_mx9 );
        Dbms_Sql.Column_Value ( cursor_id, 41, r_ywsj.v_mx10 );
        Dbms_Sql.Column_Value ( cursor_id, 42, r_ywsj.v_dwid );
        Dbms_Sql.Column_Value ( cursor_id, 43, r_ywsj.v_zph );
        Dbms_Sql.Column_Value ( cursor_id, 44, r_ywsj.v_zy );
        Dbms_Sql.Column_Value ( cursor_id, 45, r_ywsj.v_dwdm );
        Dbms_Sql.Column_Value ( cursor_id, 46, r_ywsj.v_ejbz );
        Dbms_Sql.Column_Value ( cursor_id, 47, r_ywsj.v_bz1 );
        Dbms_Sql.Column_Value ( cursor_id, 48, r_ywsj.v_bz2 );
        Dbms_Sql.Column_Value ( cursor_id, 49, r_ywsj.v_bz3 );
        Dbms_Sql.Column_Value ( cursor_id, 50, r_ywsj.v_ywlx );
        Dbms_Sql.Column_Value ( cursor_id, 51, r_ywsj.v_ywid );
        Dbms_Sql.Column_Value ( cursor_id, 52, r_ywsj.v_bbid );
        Dbms_Sql.Column_Value ( cursor_id, 53, r_ywsj.d_rq1 );
        Dbms_Sql.Column_Value ( cursor_id, 54, r_ywsj.d_rq2 );
        --
        -- 判断数据是否有效,有效则编制凭证
        --
        IF Abs ( Nvl ( r_ywsj.n_je1, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je2, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je3, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je4, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je5, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je6, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je7, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je8, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je9, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je10, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je11, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je12, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je13, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je14, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je15, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je16, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je17, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je18, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je19, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je20, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je21, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je22, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je23, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je24, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je25, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je26, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je27, 0 ) ) +
           Abs ( Nvl ( r_ywsj.n_je28, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je29, 0 ) ) + Abs ( Nvl ( r_ywsj.n_je30, 0 ) ) > 0 THEN
          n_pzbh := Nvl(n_pzbh, 0) + 1;
          --
          -- 调用函数f_make_jzpz编制当前凭证,形成每条分录的数据
          --
          v_return := F_Make_Jzpz ( r.dm, r.fsyw, n_ywxh, n_pzbh, r_ywsj, v_zd, d_rq, d_qr, d_zr);
        END IF;
        --
        -- 检验函数是否执行成功,不成功则回滚,返回错误信息。
        --
        IF v_return <> '0' THEN
          Rollback;
          Return v_return;
        END IF;
        --
        -- 修改业务数据的凭证顺序号(pzid)、退票顺序号(tpid)
        --
        IF Instr ( Lower ( r.data_source ), 'tpid') > 0 THEN
          update pz_ywsj set tpid = df_zwlb || Lpad ( To_Char ( Seq_Pzid. Currval ), 14, '0' )
           where ywxh = r_ywsj.v_ywywxh;
        ELSE
          update pz_ywsj set pzid = df_zwlb || Lpad ( To_Char ( Seq_Pzid. Currval ), 14, '0' )
           where ywxh = r_ywsj.v_ywywxh;
        END IF;
      END LOOP;
      -----------------END 当前业务---------------------
      --------------------------------------------------
    END LOOP;
    --
    -- 写日志报告
    --
    gv_msg := gv_msg || Chr ( 10 ) || '@凭证编号:' || n_beginpzbh || '—' || n_pzbh;
    gv_msg := gv_msg || Chr ( 10 ) || '@编制数量:' || ( n_pzbh - n_beginpzbh + 1 );
    Return '0';
  Exception

    WHEN Others THEN
      Return '$f_makeyspz$' || SqlErrm;
  END f_makeyspz;

论坛徽章:
1
优秀写手
日期:2014-07-01 06:00:12
发表于 2014-6-27 16:35 | 显示全部楼层
会话结束,会自动释放的吧

使用道具 举报

回复
求职 : 数据库开发
论坛徽章:
4
2014年新春福章
日期:2014-02-18 16:43:09马上有钱
日期:2014-02-18 16:43:09优秀写手
日期:2014-04-23 06:00:19红宝石
日期:2014-06-03 13:13:19
发表于 2014-6-27 16:37 | 显示全部楼层
先比较一下生产与测试环境中,所涉及表的数据量,是不是相差很多,如果相差很多,则比较一下里面select 与update几个查询的执行计划有无区别;

通过AWR报告或动态视图查询相关的SQL是否耗时很久或执行次数很多;

如果能用plsql dev连上生产,打开profiler跑一下,看时间花在哪里,对症下药。

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-07-02 06:00:12
发表于 2014-6-27 16:43 | 显示全部楼层
正在对比数据量,我想问下游标不关闭有什么影响啊?

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2014-6-27 16:44 | 显示全部楼层
在PL/SQL DEVELOPER中测试(加入调试,设置断点,一步一步走)一下,看看是慢在代码中的哪个地方了。然后确定慢的SQL语句,最后将生产系统和测试系统上这条语句的执行计划进行比对,分析问题的原因。

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-07-02 06:00:12
发表于 2014-6-27 17:50 | 显示全部楼层
zhangfengh 发表于 2014-6-27 17:20
游标应该可以自动关闭,但是在程序中最好有开就有关,这是一良好的习惯

应该可以自动关闭?

请问怎么看游标有没有关闭啊?请指教,谢谢,感谢,拜谢,跪谢......

使用道具 举报

回复
论坛徽章:
533
奥运会纪念徽章:垒球
日期:2008-09-15 01:28:12生肖徽章2007版:鸡
日期:2008-11-17 23:40:58生肖徽章2007版:马
日期:2008-11-18 05:09:48数据库板块每日发贴之星
日期:2008-11-29 01:01:02数据库板块每日发贴之星
日期:2008-12-05 01:01:03生肖徽章2007版:虎
日期:2008-12-10 07:47:462009新春纪念徽章
日期:2009-01-04 14:52:28数据库板块每日发贴之星
日期:2009-02-08 01:01:03生肖徽章2007版:蛇
日期:2009-03-09 22:18:532009日食纪念
日期:2009-07-22 09:30:00
发表于 2014-6-27 21:41 | 显示全部楼层
在 v$open_cursor 中有已经打开的游标。
你这些动态SQL在两个环境中是否一样,哪怕SQL是一样的,它们访问的数据量也可能有很大差别。必须跟踪一下才能明白问题在哪,但游标未关闭不应该是原因。

使用道具 举报

回复
认证徽章
论坛徽章:
1
优秀写手
日期:2014-07-02 06:00:12
发表于 2014-6-30 19:26 | 显示全部楼层
newkid 发表于 2014-6-27 21:41
在 v$open_cursor 中有已经打开的游标。
你这些动态SQL在两个环境中是否一样,哪怕SQL是一样的,它们访问的 ...

我现在怀疑是因为索引刷新导致的,这个包总共涉及到了7张表,其中有两张表数据量大约在2亿7千万左右,但是有分区,每个分区大约120W数据,并且用的事全局索引。
你们说是不是把它修改为本地索引,会好一些啊。
我现在想到的优化方法有:
1、        删除未使用的索引
2、        将分区表索引修改为本地索引
3、        手动关闭游标
4、        删除休眠代码
5、        清理大表数据
6、        分析表
7、        分析索引

使用道具 举报

回复
求职 : 数据库管理员
认证徽章
论坛徽章:
11
ITPUB社区千里马徽章
日期:2013-06-09 10:15:34懒羊羊
日期:2015-03-04 14:52:11懒羊羊
日期:2015-02-10 13:36:05马上有对象
日期:2015-02-02 12:29:02红宝石
日期:2015-01-19 09:44:10马上有车
日期:2014-11-11 14:16:07马上有车
日期:2014-03-27 15:59:39优秀写手
日期:2014-03-12 06:00:13马上有钱
日期:2014-02-18 16:43:092014年新春福章
日期:2014-02-18 16:43:09
发表于 2014-7-1 09:21 | 显示全部楼层
本帖最后由 moseslin 于 2014-7-1 09:21 编辑

跟统计信息有关系么? @大神

使用道具 举报

回复
论坛徽章:
2
2013年新春福章
日期:2013-02-25 14:51:24优秀写手
日期:2013-12-18 09:29:10
发表于 2014-7-3 09:18 | 显示全部楼层
做一个profile,看一下那里最消耗时间,你这样没法看

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表