Calculate the run time of a program through PL/SQL

If you need to run SQL statements in Sqlplus, or if you want to count the run time, you only need to turn on the set timing on option.

Sql> Set Timing on
Sql>
Sql> Select COUNT (*) from cat;

COUNT (*)
----------
408

elapsed:00:00:00.15
If you need to calculate the time that the program runs when you run PL/SQL. Using set timing on is a weakness. This time you can consider using Dbms_utility.get_time to get a timestamp, and then get a timestamp after the program runs, and the two want to reduce the running time of the program.
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); --Here we try to keep the PL/SQL block stagnant for 2 seconds
Dbms_output.put_line (' Elapsed time: ' | | (dbms_utility.get_time-l_start_time)/100);
End
/
The results of the program run are as follows.

This is a test
Elapsed time:2.01

PL/SQL procedure successfully completed.
However, if this is calculated, a negative number may occur. In the PL/SQL Programming book, the author gives an explanation of the total number of milliseconds that dbms_utility_get_time obtains from a given point in time. And this number is very large, it is possible to cross the border, the time of the crossing will start from 0 to start counting again. If this is calculated, it is possible that the result of the calculation is a negative number.
We can use the following PL/SQL to make an improvement.
Set Serveroutput on
Declare
C_time_gap Number:=power (2,32);
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);
Dbms_output.put_line (' Elapsed time: ' | | MoD (DBMS_UTILITY.GET_TIME-L_START_TIME+C_TIME_GAP,C_TIME_GAP)/100);
End
/
The results of the operation are as follows:

This is a test
Elapsed Time:2

PL/SQL procedure successfully completed.
If we embed too much code in the program to maintain the start_time,end_time will inevitably cause the program's dependence, if the computation time function can be independent out of the good. The operation of this program does not have to rely entirely on time calculations and can be flexibly added and removed.
This implementation of the spring AOP is based on dynamic proxies, which we can use in PL/SQL.
The result we expect is
Begin
Pro_timing.start_timer; --Program calculation start time
Dbms_output.put_line (' This is a test '); --Program business logic
Dbms_lock.sleep (2); --Program business logic
pro_timing.show_elapsed (' test program '); --Program calculation termination time
End
/
We can use the following package to achieve this.

Create or Replace package pro_timing
Authid Current_User
Is
Procedure Start_timer;
Procedure show_elapsed (program_name in varchar2);
End
/

Create or replace package body pro_timing
Is
C_time_gap Number:=power (2,32);
L_start_time Pls_integer;
Procedure Start_timer
Is
Begin
L_start_time: = Dbms_utility.get_time ();
End

Procedure show_elapsed (program_name in VARCHAR2)
As
L_end_time varchar2 (100);
Begin
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. ');
End
End
/
Let's try to run the following PL/SQL block.
Begin
Pro_timing.start_timer;
Dbms_output.put_line (' This is a test ');
Dbms_lock.sleep (2);
pro_timing.show_elapsed (' test program ');
End
/
The results of the operation are as follows:

This is a test
Test program has elapsed time 2 s.

PL/SQL procedure successfully completed.
This basically achieves our goal. We can flexibly configure this function in the program, which is very important for the performance of the program.

Calculate the run time of a program through PL/SQL