一个诡异的存储过程

江南小鱼 2013-12-02 09:21:11
存储过程，在PL/SQL进行test，输入201311，执行，瞬间执行完成，点击游标输出，要2分半钟才有结果
CREATE OR REPLACE PROCEDURE RP3005

(

 V_RPDATE     integer, --月份

 SMENU_CURSOR out query_pkg.Query_cur, --结果集

 SMENU_CURSOR2 out query_pkg.Query_cur --结果集

) 

IS



BEGIN



  OPEN SMENU_CURSOR for

  select S_CH,I_YUCETZ,i_xiaofeil

  ,to_char(round((i_xiaofeil-I_YUCETZ)/I_YUCETZ,2))*100||'%' I_WUCHALV

  from (

   select substr(yc.s_ch,1,2) S_CH

   ,sum(decode(yc.i_yucetz,null,yc.i_ycxyrjyl,yc.I_YUCETZ)*ceil(ly.d_xiacicbrq-ly.d_bencicbrq)) I_YUCETZ

   ,sum(sj.i_xiaofeil) i_xiaofeil 

   from cb_cebenly ly

   inner join GB_YUCEWCFX yc on yc.s_ch=ly.s_ch and yc.i_fenxiny=ly.i_zhangwuny 

   inner join cb_chaobiaosj sj on yc.i_fenxiny=sj.i_zhangwuny and yc.i_gongci=sj.i_gongci and yc.s_cid=sj.s_cid

   where ly.s_st='41' and sj.s_st='41' and ly.i_jlzt=0 and ly.i_lingyongzt in(2,3,4) and sj.i_zhangwuny=V_RPDATE

   and yc.I_FENXINY = V_RPDATE and yc.i_jlzt=0 and yc.i_jlzt=0 group by substr(yc.s_ch,1,2)

   ) tbl;



     OPEN SMENU_CURSOR2 for

     select V_RPDATE from dual;

END;


拷贝存储过程语句出来，把输入参数(V_RPDATE)替换成201311，在PL/SQL执行，秒级耗时查询出结果。

很是费劲！通过sp和直接执行语句，速度相差如此之大？


语句一、语句二，分别F5，执行计划Cost相差不大。
比较诡异的现象二：语句一走索引，语句二均是全部扫描。
为何全表扫描比走索引还快呢？

尝试在存储过程，添加/*all_rows*/，强制全表扫描，执行输出结果的速度依旧2分多钟。⊙﹏⊙b汗


语句一
select S_CH,I_YUCETZ,i_xiaofeil

  ,to_char(round((i_xiaofeil-I_YUCETZ)/I_YUCETZ,2))*100||'%' I_WUCHALV

  from (

   select substr(yc.s_ch,1,2) S_CH

   ,sum(decode(yc.i_yucetz,null,yc.i_ycxyrjyl,yc.I_YUCETZ)*ceil(ly.d_xiacicbrq-ly.d_bencicbrq)) I_YUCETZ

   ,sum(sj.i_xiaofeil) i_xiaofeil 

   from cb_cebenly ly

   inner join GB_YUCEWCFX yc on yc.s_ch=ly.s_ch and yc.i_fenxiny=ly.i_zhangwuny 

   inner join cb_chaobiaosj sj on yc.i_fenxiny=sj.i_zhangwuny and yc.i_gongci=sj.i_gongci and yc.s_cid=sj.s_cid

   where ly.s_st='41' and sj.s_st='41' and ly.i_jlzt=0 and ly.i_lingyongzt in(2,3,4) and sj.i_zhangwuny=V_RPDATE

   and yc.I_FENXINY = V_RPDATE and yc.i_jlzt=0 and yc.i_jlzt=0 group by substr(yc.s_ch,1,2)

   ) tbl;


语句二
select S_CH,I_YUCETZ,i_xiaofeil

  ,to_char(round((i_xiaofeil-I_YUCETZ)/I_YUCETZ,2))*100||'%' I_WUCHALV

  from (

   select substr(yc.s_ch,1,2) S_CH

   ,sum(decode(yc.i_yucetz,null,yc.i_ycxyrjyl,yc.I_YUCETZ)*ceil(ly.d_xiacicbrq-ly.d_bencicbrq)) I_YUCETZ

   ,sum(sj.i_xiaofeil) i_xiaofeil 

   from cb_cebenly ly

   inner join GB_YUCEWCFX yc on yc.s_ch=ly.s_ch and yc.i_fenxiny=ly.i_zhangwuny 

   inner join cb_chaobiaosj sj on yc.i_fenxiny=sj.i_zhangwuny and yc.i_gongci=sj.i_gongci and yc.s_cid=sj.s_cid

   where ly.s_st='41' and sj.s_st='41' and ly.i_jlzt=0 and ly.i_lingyongzt in(2,3,4) and sj.i_zhangwuny=201311

   and yc.I_FENXINY = 201311 and yc.i_jlzt=0 and yc.i_jlzt=0 group by substr(yc.s_ch,1,2)

   ) tbl
...全文
641 16 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
江南小鱼 2014-10-30
  • 打赏
  • 举报
 回复
引用 13 楼 BenChiM888 的回复:
那就把你的执行计划贴出来。 [quote=引用 12 楼 lovelj2012 的回复:] [quote=引用 11 楼 BenChiM888 的回复:] 如果没猜错， sj.i_zhangwuny 这个字段是 varchar2 类型， 你试试把 201311 改成 '201311' 再执行sql二。 如果速度也很快，基本就可以解释了。 plsql在test时候，输入的应该是按照字符串传了。
i_zhangwuny是int类型 varchar类型的字段，我们命名是以S_开头的 呵呵[/quote][/quote] 很久之前的事情了，后来通过其他方式把这个存储过程执行效率的问题处理了。现在让我拿执行计划出来，拿不出来的了
BenChiM888 2014-10-30
  • 打赏
  • 举报
 回复
那就把你的执行计划贴出来。
引用 12 楼 lovelj2012 的回复:
[quote=引用 11 楼 BenChiM888 的回复:] 如果没猜错， sj.i_zhangwuny 这个字段是 varchar2 类型， 你试试把 201311 改成 '201311' 再执行sql二。 如果速度也很快，基本就可以解释了。 plsql在test时候，输入的应该是按照字符串传了。
i_zhangwuny是int类型 varchar类型的字段，我们命名是以S_开头的 呵呵[/quote]
江南小鱼 2014-10-30
  • 打赏
  • 举报
 回复
引用 11 楼 BenChiM888 的回复:
如果没猜错， sj.i_zhangwuny 这个字段是 varchar2 类型， 你试试把 201311 改成 '201311' 再执行sql二。 如果速度也很快，基本就可以解释了。 plsql在test时候，输入的应该是按照字符串传了。
i_zhangwuny是int类型 varchar类型的字段，我们命名是以S_开头的 呵呵
BenChiM888 2014-10-30
  • 打赏
  • 举报
 回复
如果没猜错， sj.i_zhangwuny 这个字段是 varchar2 类型， 你试试把 201311 改成 '201311' 再执行sql二。 如果速度也很快，基本就可以解释了。 plsql在test时候，输入的应该是按照字符串传了。
卖水果的net 2014-10-30
  • 打赏
  • 举报
 回复
看样子，是你执行 test 时，并没有真正的去执行这两个 sql，只是确定了执行计划，你点击 cursor 输出时，才真正去跑这些语句。
bw555 2014-10-30
  • 打赏
  • 举报
 回复
时间是游标消耗掉的吧，遇到过类似问题，语句直接执行很快，放到游标里就慢了 不常见，原因未知
CT_LXL 2014-10-30
  • 打赏
  • 举报
 回复
引用 7 楼 lovelj2012 的回复:
刚到6L，结贴有点浪费，\(^o^)/~ 又原因分享Oracle知识的，请贴出来，结贴都给分。
我感觉跟你的表连接有关系
江南小鱼 2014-10-30
  • 打赏
  • 举报
 回复
刚到6L，结贴有点浪费，\(^o^)/~ 又原因分享Oracle知识的，请贴出来，结贴都给分。
江南小鱼 2014-10-30
  • 打赏
  • 举报
 回复
引用 15 楼 BenChiM888 的回复:
我擦，才看见13年的帖子。 一般情况你这种情况基本是因为隐士类型转换到值的索引失效。 既然时间已经久远就不提他了。 [quote=引用 14 楼 lovelj2012 的回复:] [quote=引用 13 楼 BenChiM888 的回复:] 那就把你的执行计划贴出来。 [quote=引用 12 楼 lovelj2012 的回复:] [quote=引用 11 楼 BenChiM888 的回复:] 如果没猜错， sj.i_zhangwuny 这个字段是 varchar2 类型， 你试试把 201311 改成 '201311' 再执行sql二。 如果速度也很快，基本就可以解释了。 plsql在test时候，输入的应该是按照字符串传了。
i_zhangwuny是int类型 varchar类型的字段，我们命名是以S_开头的 呵呵[/quote][/quote] 很久之前的事情了，后来通过其他方式把这个存储过程执行效率的问题处理了。现在让我拿执行计划出来，拿不出来的了[/quote][/quote] 恩，不再提它了。 可以在此帖分享Oracle知识，分享给分，当然了帖子分数不高:-)
BenChiM888 2014-10-30
  • 打赏
  • 举报
 回复
我擦，才看见13年的帖子。 一般情况你这种情况基本是因为隐士类型转换到值的索引失效。 既然时间已经久远就不提他了。
引用 14 楼 lovelj2012 的回复:
[quote=引用 13 楼 BenChiM888 的回复:] 那就把你的执行计划贴出来。 [quote=引用 12 楼 lovelj2012 的回复:] [quote=引用 11 楼 BenChiM888 的回复:] 如果没猜错， sj.i_zhangwuny 这个字段是 varchar2 类型， 你试试把 201311 改成 '201311' 再执行sql二。 如果速度也很快，基本就可以解释了。 plsql在test时候，输入的应该是按照字符串传了。
i_zhangwuny是int类型 varchar类型的字段，我们命名是以S_开头的 呵呵[/quote][/quote] 很久之前的事情了，后来通过其他方式把这个存储过程执行效率的问题处理了。现在让我拿执行计划出来，拿不出来的了[/quote]
bisal(Chen Liu) 2013-12-13
  • 打赏
  • 举报
 回复
最好贴出你的执行计划，10053的trace日志，这样可以更详细地看下为什么效果不同。
江南小鱼 2013-12-10
  • 打赏
  • 举报
 回复
引用 4 楼 bisal 的回复:
全表扫描与索引扫描对比，并不是索引扫描一定快，大师有句名言：“避免不必要的全表扫描”，因为全表扫描时多块读，大部分索引扫描是单块读，不同场景运用不同的方式。 不知道这里的Oracle版本是多少？从10g以后只有CBO这种优化器，它会根据统计信息自动计算一条SQL的不同执行路径，然后选择一条成本最低的作为他的执行计划，因此一条SQL的执行计划可能是全表扫描，也可能是索引扫描，例如一张表只有100条数据，全表扫描肯定比索引扫描要快，这要具体问题具体分析，如果想知道为什么上述不同的现象，请用10053做这两条SQL的trace，可以看下Oracle选择的成本值。
我用的是10G的版本。 表数据量大概有1000万，表做了分区。
bisal(Chen Liu) 2013-12-05
  • 打赏
  • 举报
 回复
全表扫描与索引扫描对比，并不是索引扫描一定快，大师有句名言：“避免不必要的全表扫描”，因为全表扫描时多块读，大部分索引扫描是单块读，不同场景运用不同的方式。 不知道这里的Oracle版本是多少？从10g以后只有CBO这种优化器，它会根据统计信息自动计算一条SQL的不同执行路径，然后选择一条成本最低的作为他的执行计划，因此一条SQL的执行计划可能是全表扫描，也可能是索引扫描，例如一张表只有100条数据，全表扫描肯定比索引扫描要快，这要具体问题具体分析，如果想知道为什么上述不同的现象，请用10053做这两条SQL的trace，可以看下Oracle选择的成本值。
江南小鱼 2013-12-03
  • 打赏
  • 举报
 回复
没人解答一下？
江南小鱼 2013-12-02
  • 打赏
  • 举报
 回复
引用 1 楼 huijianpang 的回复:
mark 一下，等高手解答
顶~~~
huijiangpang 2013-12-02
  • 打赏
  • 举报
 回复
mark 一下，等高手解答
ASP中解决“对象关闭时,不允许操作。”的诡异问题……
在ASP中进行数据库操作时 代码如下: rs.Open strsql,conn,1,3 while not rs.eof　　　　//对象关闭时,不允许操作。 找了好久，最后在strsql中发现问题所在…… 原因是： 这个strsql = “exec ***”，用的是存储过程，而这个***存储过程中的最后加了一句“print @sql”，导致这个问题，注释掉就OK了…… 您可能感兴趣的文章:asp sqlserver 执行存储过程返回记录集报对象关闭时不允许操作SQLServer ADODB.Recordset 错误“800a0e78”，对象关
c++调用mysql存储过程_C++中ADO调用MySQL存储过程失败,诡异的语法异常,求解中,附源码...
C++中ADO调用mysql存储过程失败,诡异的语法错误,求解中,附源码不管怎么调整，死活都出现下面的错误C++ADO代码我实在找不到什么错误了，难道是MySQL有什么问题，或者有其他需要注意的地方(类似存储过程权限之类的，我都看了，我用root连接啊)navicat 查询编辑器 执行 call PrGs_GameUser_Login('sdfdsfdsfd') 是没有问题的根据下面错误信息，难道...
Oracle存储过程ORA-00942: 表或视图不存在问题解决
今天在写存储过程的时候，发现一个很诡异的问题，存储过程里就一个最简单的SELECT动态SQL语句。编写完成以后，执行这个存储过程，总是提示ORA-00942: table or view does not exist（ERROR -942 ORA-00942: 表或视图不存在）这个错误，但是我将这个SQL语句复制出来，单独在命令行运行，而又没有任何错误。很纠结，很纳闷，很无解，好好的一个SEL
mysql通过存储过程迁移数据_用Mysql存储过程迁移数据
今天有一个需求是迁移tag的数据，之前写的存储过程到现在都忘记了，从新再写一个，并在这里纪录一下，防止自己下次还忘记 首先是修改一下Mysql的配置 大家可以看下 这是我们老大的测试结果 SET GLOBAL max_allowed_packet=1024*1024*1024;SET GLOBAL key_buf今天有一个需求是迁移tag的数据，之前写的存储过程到现在都忘记了，从新再写一个，并在这...
db2存储过程手动执行没有问题_db2执行存储过程的一次诡异报错
问题描述：UltraEdit本地写一个存储过程，上传到linux服务器，执行存储过程，db2 -td@ -vf AIP_DD_CLEAR500.sql ，报错：DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. Duri...
Oracle 高级技术

3,494

社区成员

18,710

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章