Compare the performance between two functions in Oracle

Sometimes when we can do something in Oracle in more than one way, but at that point we have to use the best one which can serves us better.

PERFORMANCE is the most important criteria we have to put into consideration when have more than one solution. Nice tip in Oracle enables us to measure performance -time spent- between more than one function which is get_cpu_time from dbms_utility package which returns the current CPU time in 100th's of a second and we use it before and after each function with loop of relatively big number of iteration.

Example:

DECLARE
   v_c1     NUMBER;
   v_c2     NUMBER;
   v_date   DATE;
   v_char   VARCHAR2 (10);
BEGIN
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_date := TRUNC (SYSDATE);
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TRUNC');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
   v_c1 := DBMS_UTILITY.get_cpu_time;

   FOR c IN 1 .. 100000
   LOOP
      v_char := TO_CHAR (SYSDATE, 'DD-MM-YYYY');
   END LOOP;

   v_c2 := DBMS_UTILITY.get_cpu_time;
   DBMS_OUTPUT.put_line ('For loop using TO_CHAR');
   DBMS_OUTPUT.put_line ('CPU seconds used: ' || (v_c2 - v_c1));
END;

The output is:

For loop using TRUNC
CPU seconds used: 25
For loop using TO_CHAR
CPU seconds used: 68
PL/SQL procedure successfully completed.

The numbers will change depending on CPU speed and tasks assigned, but anyway you can notice the difference.


Saad,