专栏首页bisal的个人杂货铺优雅编写SQL的几个案例

优雅编写SQL的几个案例

罗老师写的这篇文章非常实用《优雅的SQL编写》,借鉴于此。

https://www.modb.pro/db/29713?xzs=

在分析v$sql或者dba_hist_sqlstat的时候,由于时常需要计算单次执行的相关指标,目前看到多数人用的是类似这样的写法,

elapsed_time / CASE 
    WHEN executions = 0
    THEN 1
    ELSE executions
    END

或者,

elapsed_time / decode(executions,0,1,executions)

目的是避免executions(或者executions_delta)为0时导致除数为0的错误。

不能说这样写有错,但是不够优雅。优雅的做法是什么呢?应该是使用greatest函数(可参考《Oracle的greatest和least函数》),写法是,

elapsed_time / greatest(executions,1) 

greatest函数返回参数列表中最大的一个,所以,达到了如果executions为0,就返回1的效果,是不是清爽优雅了许多?

还有一个用法,就是需要把时间由原始微秒转换成秒或者毫秒的时候,目前多数的写法是,

转成秒:
elapsed_time / 100000

或者

转成毫秒:
elapsed_time / 1000

转成毫秒还好,后面3个0,转成秒时,后面6个0,有没有特别担心写少一位或者多写一位?其实在上面的举例就少了一位,是错误的,但是有多少人能够及时看出来?

这时候可以优雅地利用科学计数法写为另一种写法, 这就很准确且易识别的转换成秒了,

转成秒:
elapsed_time / 1e6
SQL> select 1e6 from dual;
       1E6
----------
   1000000

Oracle的科学计数法很简单,前面是一个数字,中间跟一个e(大小写不限),后面跟一个整数(正负不限)就可以,

n.nEm = n.n * 10^m

例如,

1.1e1 = 1.1*10^1=11
10e6= 10 * 10^6 = 10^7 = 10,000,000
2e-2= 2 * 10^(-2)=0.02

而1e3,1e6,1e9就正好是K/M/G或者毫/微/纳的进制转换。

另外,在处理逻辑读/物理读的时候,如果希望把相关指标变成G或者M,还可以使用类似的写法。以绝对大多数的8k块数据库而言,标准写法是,

disk_reads*8192/1024/1024  -->转换为M
buffer_gets*8192/1024/1024/1024 -->转换成G

因为8192/1024/1024等于1/128,而128*1024约等于13万,上面的计算完全可以改写为,

disk_reads/128
buffer_gets/13e4

此处的128*1024约等于13万,其实不准确,因为128*1024=131072。lastwinner在文章的评论中提到了13e4,这个不如power来得优雅和精确,

power(2,30)/power(2,13)=power(2,17),这是什么意思?

其实是这样,power(2,13)=8192,power(2,10)=1024,

buffer_gets*8192/1024/1024/1024

=buffer_gets*power(2,13)/power(2,10)/power(2,10)/power(2,10)

=buffer_gets*power(2,13)/power(2,30)

=buffer_gets/powe(2,17)

只能感叹SQL的博大精深,我太渺小了。

当然,如果不是8k块大小,就不对了,需要相应调整,3个结合,就是,

select sql_id,elapsed_time/1e6 "执行时间(s)",
       elapsed_time/1e3/greatest(executions,1) as "单次执行(ms)",
       disk_reads/128 as "物理读(M)",
       buffer_gets/13e4 "逻辑读(G)" 
from v$sql

另外,如果分析的dba_hist_sqlstat, 不可避免的需要按时间段去做过滤,通常的做法是, 

select sql_id,
       to_char(begin_interval_time,'yyyymmdd') btime,
       round(elapsed_time_delta/1e6) "执行时间(s)",
       round(elapsed_time_delta/1e3/greatest(executions_delta,1)) as "单次执行(ms)",
       round(disk_reads_delta/128) as "物理读(M)",
       round(buffer_gets_delta/13e4) "逻辑读(G)" 
from dba_hist_sqlstat sq,dba_hist_snapshot sn
where sn.snap_id = sq.snap_id 
  and sn.instance_number = sq.instance_number 
  and sn.dbid = sq.dbid
  and begin_interval_time > sysdate - 3 -->限定3天内
order by 2 desc

其实,也可以利用Oracle支持自然连接(natural join)语法的特性,改写为:

select sql_id,
       to_char(begin_interval_time,'yyyymmdd') btime,
       round(elapsed_time_delta/1e6) "执行时间(s)",
       round(elapsed_time_delta/1e3/greatest(executions_delta,1)) as "单次执行(ms)",
       round(disk_reads_delta/128) as "物理读(M)",
       round(buffer_gets_delta/13e4) "逻辑读(G)" 
from dba_hist_sqlstat sq natural join dba_hist_snapshot sn
where  begin_interval_time > sysdate - 3 -->限定3天内
order by 2 desc

返回行数太多,不可避免需要分页,通常的写法是:

select * from (
select sql_id,
       to_char(begin_interval_time,'yyyymmdd') btime,
       round(elapsed_time_delta/1e6) "执行时间(s)",
       round(elapsed_time_delta/1e3/greatest(executions_delta,1)) as "单次执行(ms)",
       round(disk_reads_delta/128) as "物理读(M)",
       round(buffer_gets_delta/13e4) "逻辑读(G)" 
from dba_hist_sqlstat sq natural join dba_hist_snapshot sn
where  begin_interval_time > sysdate - 3 -->限定3天内
order by 2 desc
) where rownum <=20

如果是12c以上的系统,还可以利用上fetch first N rows only的分页方法,

select sql_id,
       round(elapsed_time_delta/1e3/greatest(executions_delta,1)) as "单次执行(ms)",
       round(disk_reads_delta/128) as "物理读(M)",
       round(buffer_gets_delta/13e4) "逻辑读(G)" 
from dba_hist_sqlstat sq natural join dba_hist_snapshot sn
where  begin_interval_time > sysdate - 3 -->限定3天内
order by 2 desc
fetch first 20 rows only

近期更新的文章:

优化前置思想的成本收益关系

v$视图存储SQL的bug

RPO和RTO是什么?

最近碰到的几个问题

Linux的inode是什么?

文章分类和索引:

《公众号800篇文章分类和索引

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 优雅编写Python3的62个小贴士!

    ▍42、创建一个迭代器,它从iterable中过滤元素,只返回谓词为False的元素

    AI科技大本营
  • 收藏 | 优雅编写Python3的66个小贴士!

    今天公众号为大家带来一篇有关Python技巧的文章,可以帮助你编写优雅的Python3代码!

    Python数据科学
  • 收藏级:优雅编写Python3的66个小贴士!

    今天公众号为大家带来一篇有关Python技巧的文章,可以帮助你编写优雅的Python3代码!

    量化投资与机器学习微信公众号
  • 一条SQL引发的“血案”:与SQL优化相关的4个案例

    导读:笔者早年间从事了多年开发工作,后因个人兴趣转做数据库。在长期的工作实践中,看到了数据库工作(特别是SQL优化)面临的种种问题。本文通过几个案例探讨一下SQ...

    用户5548425
  • MySQL中,21个写SQL的好习惯

    每一个好习惯都是一笔财富,本文分SQL后悔药, SQL性能优化,SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读,加油哈~

    xjjdog
  • MySQL 中的 21 个好习惯你要养成

    每一个好习惯都是一笔财富,本文分SQL后悔药, SQL性能优化,SQL规范优雅三个方向,分享写SQL的21个好习惯,谢谢阅读,加油哈~

    cxuan
  • MySQL中,21个写SQL的好习惯

    本文公众号来源:捡田螺的小男孩 作者:捡田螺的小男孩 本文已收录至我的GitHub

    Java3y
  • Python之禅:编写优雅Python代码的16个原则

    假如你刚参与了一个算法项目,当你第一次打开这个项目时,发现里面已经有上万行与算法相关的代码,仔细查看过后,发现如下一些让你抓狂的问题:

    用户7656790
  • 推荐几个自己写的Java后端相关的范例项目

    三哥
  • 如何快速优雅的编写一个脚本程序?用这个!

    在日常工作当中,我们会不时借助脚本程序来处理一些重复性工作,以帮助我们提升工作效率。

    GitHubDaily
  • 低端程序员的明显特征

    IT云清
  • exp和expdp几种常见的使用场景操作介绍

    同事前几天问到exp/expdp根据条件导出一张/多张表数据的问题,借此机会,依次对这些需求做个实验,仅供参考。

    bisal
  • Java 小记 — Spring Boot 的实践与思考

    捷义
  • 【程序源代码】非常棒的java学习面试指南

    最近好多同学想学习java,我在网上找了找终于找到这个指南。这一个非常不错的java学习指南。内容包含的比较全面,知识点也比较完整。

    程序源代码
  • 【数据工具】对比Pig、Hive和SQL,浅谈大数据工具差异

    作者:Manisha Nandy Mazumder 有人说对于大数据分析来说Hadoop才是炙手可热的新技术,SQL虽然久经考验但已经有些过时了。这话说得不错,...

    小莹莹
  • ADO.NET入门教程(四) 品味Connection对象

    摘要 前几篇文章,我都没有详细讲解Data Provider核心对象,因为我希望在讲解这些对象之前,让大家对一些基础的概念有很好的认识。在上一篇文章《你必须知道...

    刘皓
  • 为什么老外不愿意用 MyBatis,而在国内工程师却偏偏热衷?

    本文不会下关于 Mybatis 和 HIB两个持久层框架哪个更好这样的结论。只是摆事实,讲道理,所以,请各位读者勿喷。

    开发者技术前线
  • 你阅读源码的心态是什么?有哪些经验分享?

    以上种种,也许还有一些其它重要的原因,比如说qiong ... 一直以来让我非常非常有内驱力的坚持到现在。

    数据仓库践行者
  • 谷歌微软等科技巨头数据科学面试107道真题:你能答出多少?

    选自Learndatasci 机器之心编译 参与:李泽南 来自 Glassdoor 的最新数据可以告诉我们各大科技公司最近在招聘面试时最喜欢向候选人提什么问题。...

    机器之心

扫码关注云+社区

领取腾讯云代金券