签到成功

知道了

CNDBA社区CNDBA社区

代码中误用select xxx from dual案例一则

2017-03-22 15:22 3807 0 转载 Oracle 性能优化
作者: dave

本文已获原作者授权转载。文章出自微信公众帐号:老虎刘谈SQL优化

作者介绍:老虎刘,原oracle 研发部门 Real-World Performance TEAM 成员,现在售后部门SSC专职做数据库性能优化,主要为银行、通信、证券、制造等大型企业提供服务。

 https://www.cndba.cn/cndba/dave/article/1821


先看一个系统AWR的top CPU SQL, 其中排在第二位的SQL是这样的:


https://www.cndba.cn/cndba/dave/article/1821

SELECT TO_CHAR(:B1 / (60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') FROM DUAL; 


SQL虽然简单,但是执行次数却高达6.2亿次/天,消耗了大量的CPU资源。https://www.cndba.cn/cndba/dave/article/1821


经检查,这段SQL来自一个将number类型的时间字段转换成日期字符串的function。https://www.cndba.cn/cndba/dave/article/1821https://www.cndba.cn/cndba/dave/article/1821


这里面就存在2个问题:https://www.cndba.cn/cndba/dave/article/1821

1、使用number类型保存日期,使用起来非常不方便(使用varchar2类型保存日期也一样),建议使用date或timestamp保存日期类型。

2、使用sqlplus时,如果要计算一个值,我们会使用select xxx from dual;但是,在function、procedure、package、trigger中,这些纯计算的内容,就不再需要做select from dual的操作了,上面函数就可以将select xxx into ret_str from dual; return ret_str; 简写成:return xxx ;


原function代码示例如下:


CREATE OR REPLACE function number2date1(in_num number ) return varchar2
IS
  ret_str varchar2(30);
BEGIN
SELECT TO_CHAR(in_num / (60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') 
into ret_str FROM DUAL;
RETURN ret_str;
END number2date1;
/


优化修改后的function代码如下;https://www.cndba.cn/cndba/dave/article/1821https://www.cndba.cn/cndba/dave/article/1821


https://www.cndba.cn/cndba/dave/article/1821

CREATE OR REPLACE function number2date2(in_num number ) return varchar2
is
BEGIN
return TO_CHAR(in_num / (60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') ;
END number2date2;
/

https://www.cndba.cn/cndba/dave/article/1821

原function多次执行会消耗大量的recursive calls,经过上面的改写后,消耗的资源基本上可以忽略不计了。


简单的改写,节省了大量CPU资源,这就是SQL优化的力量。


用户评论
* 以下用户言论只代表其个人观点,不代表CNDBA社区的观点或立场
dave

dave

关注

人的一生应该是这样度过的:当他回首往事的时候,他不会因为虚度年华而悔恨,也不会因为碌碌无为而羞耻;这样,在临死的时候,他就能够说:“我的整个生命和全部精力,都已经献给世界上最壮丽的事业....."

  • 2237
    原创
  • 2
    翻译
  • 535
    转载
  • 185
    评论
  • 访问:6531743次
  • 积分:4229
  • 等级:核心会员
  • 排名:第1名
精华文章
    最新问题
    查看更多+
    热门文章
      热门用户
      推荐用户
        Copyright © 2016 All Rights Reserved. Powered by CNDBA · 皖ICP备2022006297号-1·

        QQ交流群

        注册联系QQ