概述

今天在用之前的一个看磁盘消耗最多的sql时居然提示了一个报错，之前明明还可以用的，报错信息如下：ORA-01476:divisor is equal to zero.

思路：

看报错很明显是ORACLE中进行除的时候出现了除数为0，加个判断条件或者decode（）改写就可以了。

decode改写sql

假设是a/b，用函数decode(b,0,null,a/b) ，这样如果b为0，输出null，不为0输出a/b

SELECT *
 FROM (SELECT sql_fulltext AS sql,
 SQL_ID,
 decode(executions,0,null,disk_reads/executions) AS "Reads/Exec",
 disk_reads,
 executions
 FROM V$SQLAREA v
 WHERE disk_reads > 1000
 ORDER BY "Reads/Exec" DESC)
 WHERE rownum <= 10;

添加判断条件

改写成如下：

SELECT *
 FROM (SELECT sql_fulltext AS sql,
 SQL_ID,
 disk_reads/executions AS "Reads/Exec",
 disk_reads,
 executions
 FROM V$SQLAREA v
 WHERE disk_reads > 1000 and executions>0
 ORDER BY "Reads/Exec" DESC)
 WHERE rownum <= 10;

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~