大罗讲SQL：如何优雅的进行SQL编写？

墨墨导读：SQL可以写的很笨重，也可以写的很优雅，本文从一个简单的查询v$SQL/DBA_HIST_SQLSTAT的语句开始，介绍几种常用的优雅SQL编写方式。

在分析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函数，写法是：

elapsed_time /greatest(executions ,1)

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

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

转成秒：
elapsed_time / 100000

或者

转成毫秒：
elapsed_time / 1000

转成毫秒还好，后面3个0，转成秒时，后面6个0，有没有特别担心写少一位或者多写一位？

其实我上面的举例就少了一位，是错误的 ???? ,但是有多少人能够及时看出来？

这时候可以优雅地利用科学计数法写为另一种写法

转成秒：
elapsed_time / 1e6

这就很准确且易识别的转换成秒了。

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等于128，而128*1024约等于13万，上面的计算完全可以改写为

disk_reads/128
buffer_gets/13e4

当然，如果不是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的分页方法：

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

墨天轮原文链接：https://www.modb.pro/db/29713（复制到浏览器中打开或者点击“阅读原文”）

推荐阅读：144页！分享珍藏已久的数据库技术年刊

---

视频号，新的分享时代，关注我们，看看有什么新发现？

数据和云

ID：OraNews

如有收获，请划至底部，点击“在看”，谢谢！

点击下图查看更多 ↓

云和恩墨大讲堂 | 一个分享交流的地方

长按，识别二维码，加入万人交流社群

请备注：云和恩墨大讲堂

  点个“在看”

你的喜欢会被看到❤