通过pl/sql计算程序的运行时间

在sqlplus中运行sql语句或者pl/sql的时候如果需要统计运行的时间，只需要开启set timing on选项即可。

SQL> set timing on

SQL>

SQL> select count(*)from cat;

  COUNT(*)

----------

       408

Elapsed: 00:00:00.15

如果在运行pl/sql的时候如果需要计算程序运行的时间。使用set timing on就显得力不从心了。这个时候可以考虑使用dbms_utility.get_time来得到一个时间戳，然后在程序运行之后再得到一个时间戳，两者想减就是程序的运行时间。

set serveroutput on

declare

 l_start_time PLS_INTEGER;

begin

 l_start_time := dbms_utility.get_time();

 dbms_output.put_line('this is a test');

 dbms_lock.sleep(2);  --这里我们尝试使pl/sql块停滞2秒钟

 dbms_output.put_line('Elapsed time :'||(dbms_utility.get_time-l_start_time)/100);

end;

/

程序运行的结果如下。

this is a test

Elapsed time :2.01

PL/SQL procedure successfully completed.

但是如果这样计算，可能会出现负数的情况。在pl/sql程序设计这本书中，作者给出的解释是，dbms_utility_get_time得到的数字式从某一个时间点以来所经过的总的毫秒数。而这个数字很大，很可能越界，越界的时候就会从0开始重新开始计数。如果这样计算的话，很可能计算出来的结果就是一个负数了。

我们可以使用如下的pl/sql来做一个改进。

 c_time_gap number:=power(2,32);

 dbms_lock.sleep(2);

 dbms_output.put_line('Elapsed time :'||mod(dbms_utility.get_time-l_start_time+c_time_gap,c_time_gap)/100);

运行结果如下：

Elapsed time :2

如果我们在程序中嵌入过多的代码去维护start_time,end_time必然会造成程序的依赖性，如果能够把计算时间的功能独立出来就好了。这样程序的运行不必完全依赖于时间计算，可以灵活的添加和删除。

这种实现在spring的AOP是根据动态代理来实现的，在pl/sql中我们可以使用package来实现。

我们的期望结果就是

 pro_timing.start_timer;  --程序计算起始时间

 dbms_output.put_line('this is a test');  --程序业务逻辑

 dbms_lock.sleep(2);  --程序业务逻辑

 pro_timing.show_elapsed('test program');  --程序计算终止时间

我们可以使用如下的package来实现。

create or replace package pro_timing

authid current_user

is

  procedure start_timer;

  procedure show_elapsed(program_name in varchar2);

create or replace package body pro_timing

 procedure start_timer

 is

 procedure show_elapsed(program_name in varchar2)

as

 l_end_time varchar2(100);

 l_end_time:=mod(dbms_utility.get_time-l_start_time+c_time_gap,c_time_gap)/100;

dbms_output.put_line(program_name||' has elapsed time '||l_end_time||' s.');

我们来尝试运行如下的pl/sql块。

 pro_timing.start_timer;

 pro_timing.show_elapsed('test program');

test program has elapsed time 2 s.

这样就基本达到了我们的目标。我们可以在程序中灵活的配置这项功能，对于提升程序的性能来说也是功不可没。