优雅编写SQL的几个案例

Bertha 。 2022-09-10 03:21 176阅读 0赞

罗老师写的这篇文章非常实用《优雅的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函数][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

**近期更新的文章：**

《[优化前置思想的成本收益关系][Link 1]》

《[v$视图存储SQL的bug][v_SQL_bug]》

《[RPO和RTO是什么？][RPO_RTO]》

《[最近碰到的几个问题][Link 2]》

《[Linux的inode是什么？][Linux_inode]》

**文章分类和索引：**

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

[Oracle_greatest_least]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a49addf52d8c587878484b9818e5156867d3d8dace598b0da14a6e7ea2be00d2495e4510&idx=1&mid=2247491945&scene=21&sn=a8263a92dc9a32ac82d96c6a1fca6464#wechat_redirect
[Link 1]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a293ddf52b8577a6bc71c164d72c65026e7db9c71b289c9bd53b54b8ceef2e22dd1a7630&idx=1&mid=2247493472&scene=21&sn=d15854e84073618cf58e8fbe3c2c903d#wechat_redirect
[v_SQL_bug]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a2a9ddf52bbf0bde73afdb1df9e94e9c760c76b358c7e0394992f7edaa7b8002ba4cf404&idx=1&mid=2247493466&scene=21&sn=30923018edef405f179a0ec5f7bccdc0#wechat_redirect
[RPO_RTO]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a31fddf52a0912754eff08e7efad78fde1f7516c7803d87ba1d373bbcfd5fe723e5c8afb&idx=1&mid=2247493356&scene=21&sn=7c4e279f3eeee3b7803a87e1b07d0f52#wechat_redirect
[Link 2]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a34addf52a5cd95f7d1eae2542e021c2602c3213d99eafa8f4e00efdd119264abdbaf7bc&idx=1&mid=2247493305&scene=21&sn=a6c94bbedf62317694f6ae239ec81e0b#wechat_redirect
[Linux_inode]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a354ddf52a42e4edaf05e640f8ce190f8196e92e268f1b3398eee7c8448a38d8dcec74c4&idx=1&mid=2247493287&scene=21&sn=b3aea273f52cc4b7d400fedb10918659#wechat_redirect
[800]: http://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA%3D%3D&chksm=ea82a084ddf529920f2572e843967c4cb4248b6adc854a001315dd54264080349d3a5cc2ea14&idx=1&mid=2247492983&scene=21&sn=5e0514b86258eebed20740dce83a6d45#wechat_redirect