[20200213]函数nullif使用.txt

--//nullif函数自己知道很少使用，我发现人家写的脚本使用它避免做除法时如果除数为0报ORA-01476的问题。
--//通过例子说明，比如我写的脚本：

$ cat d_buffer.sql
col executions1 new_value x1
col buffer_gets1 new_value x2
col ELAPSED_TIME1 new_value x3
col ROWS_PROCESSED1 new_value x4

col executions2 new_value y1
col buffer_gets2 new_value y2
col ELAPSED_TIME2 new_value y3
col ROWS_PROCESSED2 new_value y4

col var3 new_value var3

select executions executions1,buffer_gets buffer_gets1,elapsed_time elapsed_time1,rows_processed rows_processed1  from v$sqlarea where sql_id='&&1';

prompt ... sleep &&2 , waiting ....
host sleep &&2

select executions executions2,buffer_gets buffer_gets2,elapsed_time elapsed_time2 ,rows_processed rows_processed2 from v$sqlarea where sql_id='&&1';

select &y2-&x2 "总buffer_gets", (&y2-&x2)/(&y1-&x1) "每次buffer_gets",&y1- &x1 "执行次数" , &y3-&x3 "总执行时间", (&y3-&x3)/(&y1-&x1) "每次执行时间" , &y4-&x4 "总处理记录数",(&y4-&x4)/(&y1-&x1) "平均处理记录数" from dual ;


--//主要是优化sql语句后，我发现有时候清除sql语句从共享池后，如果再次执行后executions,buffer_gets等信息并没有彻底清除。
--//这样我很难比较优化后效果，因此写了上面脚本。脚本有1个问题如果检测时间内没有执行，这样executions没有变化，作为除数就会报错。

SYS@book> @ d_buffer 4xamnunv51w9j 2
EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1
----------- ------------ ------------- ---------------
          1           62          5988               1

... sleep 2 , waiting ....

EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2
----------- ------------ ------------- ---------------
          1           62          5988               1

select         62-        62 "总buffer_gets", (        62-        62)/(         1-         1) "每次buffer_gets",         1-          1 "执行次数" ,       5988-      5988 "总执行时间", (      5988-      5988)/(         1-         1) "每次执行时间" ,          1-         1 "总处理记录数",(         1-         1)/(         1-         1) "平均处理记录数" from dual
                                                                     *
ERROR at line 1:
ORA-01476: divisor is equal to zero

--//利用nullif函数就能很好的避免这个问题。
SCOTT@book> select nullif(1,0),nullif(0,0),nullif(1,1) from dual ;
NULLIF(1,0) NULLIF(0,0) NULLIF(1,1)
----------- ----------- -----------
          1
--//nullif就是比较参数1，参数2，如果相等就输出NULL。
--//只要第1个参数等于第2个参数，输出就是NULL类型。改写如下就不会报错了。

$ cat d_buffer.sql
col executions1 new_value x1
col buffer_gets1 new_value x2
col ELAPSED_TIME1 new_value x3
col ROWS_PROCESSED1 new_value x4

col executions2 new_value y1
col buffer_gets2 new_value y2
col ELAPSED_TIME2 new_value y3
col ROWS_PROCESSED2 new_value y4

col var3 new_value var3

select executions executions1,buffer_gets buffer_gets1,elapsed_time elapsed_time1,rows_processed rows_processed1  from v$sqlarea where sql_id='&&1';

prompt ... sleep &&2 , waiting ....
host sleep &&2

select executions executions2,buffer_gets buffer_gets2,elapsed_time elapsed_time2 ,rows_processed rows_processed2 from v$sqlarea where sql_id='&&1';

SELECT &y2 - &x2 "总buffer_gets"
      , (&y2 - &x2) / NULLIF (&y1 - &x1, 0) "每次buffer_gets"
      ,&y1 - &x1 "执行次数"
      ,&y3 - &x3 "总执行时间"
      , (&y3 - &x3) / NULLIF (&y1 - &x1, 0) "每次执行时间"
      ,&y4 - &x4 "总处理记录数"
      , (&y4 - &x4) / NULLIF (&y1 - &x1, 0) "平均处理记录数"
  FROM DUAL;


SYS@book> @ d_buffer 4xamnunv51w9j 1
EXECUTIONS1 BUFFER_GETS1 ELAPSED_TIME1 ROWS_PROCESSED1
----------- ------------ ------------- ---------------
          2           50          3834               2

... sleep 1 , waiting ....

EXECUTIONS2 BUFFER_GETS2 ELAPSED_TIME2 ROWS_PROCESSED2
----------- ------------ ------------- ---------------
          2           50          3834               2

总buffer_gets 每次buffer_gets   执行次数 总执行时间 每次执行时间 总处理记录数 平均处理记录数
------------- --------------- ---------- ---------- ------------ ------------ --------------
            0                          0          0                         0