罗老师写的这篇文章非常实用《优雅的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
近期更新的文章:
文章分类和索引: